destroying session if not set using php pdo

Question

I have this old code that see if session is not registered to destroy it and go back to login page:

<?php
session_start();
$host="localhost"; // Host name 
$username="root"; // Mysql username 
$password=""; // Mysql password 
$db_name=""; // Database name 
//if(!session_is_registered(myusername)){
//header("location:index.html");
if(isset($_SESSION['username'])) {
  echo "Page seen by " . $_SESSION['username']."<br>";
  $con=mysqli_connect($host,$username,$password,$db_name);
  mysqli_set_charset($con, 'utf8mb4');
}
else{
    session_destroy();
    header("location: index.php");
}
?>

I am trying to convert this code to pdo but I can't know how to destroy the session in this method. I just stopped after writing those lines:

<?php

session_start();

$DB_host = "localhost";
$DB_user = "root";
$DB_pass = "";
$DB_name = "";

try
{
     $conn = new PDO("mysql:host={$DB_host};dbname={$DB_name}",$DB_user,$DB_pass);
     $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
     $conn->exec("SET CHARACTER SET utf8mb4");
}
catch(PDOException $e)
{
     echo $e->getMessage();
}
?>

Plus, In the following code, always when I click on login it will take me to the next page even if the username and password are incorrect:

<?php
$DB_host = "localhost";
$DB_user = "root";
$DB_pass = "";
$DB_name = "";

$conn = new PDO("mysql:host={$DB_host};dbname={$DB_name}",$DB_user,$DB_pass);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->exec("SET CHARACTER SET utf8mb4");

if(isset($_POST['login'])){
    $username = $_POST['username'];
    $password = $_POST['password'];
        if($username != '' && $password!=''){

        try{
            session_start();
            $sql = "SELECT * FROM login WHERE username = :u AND password = :p LIMIT 1";
            $stmt = $conn->prepare($sql);
            $stmt->bindValue(":u", $username);
            $stmt->bindValue(":p", $password);
            $exec = $stmt->execute();
            $count = $stmt->fetch(PDO::FETCH_ASSOC);
            if((count($count)==1)){//&& password_verify($password, $count['password']
                $_SESSION['username'] = $username;
                header("Location: ./pages/home.php");       
            } 
            else {
                header("Location: index.php");
            }
        }
        catch(PDOException $e) {
            $sql_fail = "INSERT INTO login_attempts(username, password, date_now, time_now) 
                             VALUES (:uf, :pf, :date, now())";
                $stmt_fail = $conn->prepare($sql_fail);
                $stmt_fail->bindValue(":uf", $username);
                $stmt_fail->bindValue(":pf", $password);
                $stmt_fail->bindValue(":date", date("y-m-d"));
                $exec_fail = $stmt_fail->execute();
                header("Location: index.php");
            echo $e->getMessage();
        }
    }
}
?>

Answer 1

Use the code in this link here.

You should use fetch(PDO::FETCH_NUM) so your code will be something like this:

$result = $conn->prepare("SELECT * FROM users WHERE username= :hjhjhjh AND password= :asas");
$result->bindParam(':hjhjhjh', $user);
$result->bindParam(':asas', $password);
$result->execute();
$rows = $result->fetch(PDO::FETCH_NUM);
if($rows > 0) {
header("location: home.php");
}
else{
    $errmsg_arr[] = 'Username and Password are not found';
    $errflag = true;
}
if($errflag) {
    $_SESSION['ERRMSG_ARR'] = $errmsg_arr;
    session_write_close();
    header("location: index.php");
    exit();
}

Answer 2

I think the key to your login is that you need some little self-contained applications (functions) to break down simple tasks. See if this works better:

/classes/class.PDOConn.php

<?php
class PDOConn
    {
        // Create a singleton variable to store persistent connection
        private static  $singleton;
        // Set your database credentials here
        public  static function connect($DB_host = "localhost",$DB_user = "root",$DB_pass = "",$DB_name = "")
            {
                // first check if the connection has been already set
                if(empty(self::$singleton)) {
                    try {
                            $conn = new PDO("mysql:host={$DB_host};dbname={$DB_name}",$DB_user,$DB_pass);
                            $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                            $conn->exec("SET CHARACTER SET utf8mb4");
                            self::$singleton    =   $conn;
                            return self::$singleton;
                        }
                    catch (PDOException $e) {
                            die("connection failed");
                        }
                }
                // Return the current connection
                return self::$singleton;
            }
    }

/functions/function.query.php

<?php
// This function will make automatic queries to your database
// It accepts a bind array as a second parameter
function query($sql = false,$bind = false)
    {
        // Create connection
        $conn   =   PDOConn::connect();
        // Two ways to query, with and without a bind array
        if(!empty($bind) && is_array($bind)) {
            $query  =   $conn->prepare($sql);
            $query->execute($bind);
        }
        else {
            $query  =   $conn->query($sql);
        }
        // Loop through returned values
        while($row = $query->fetch(PDO::FETCH_ASSOC)) {
            $result[]   =   $row;
        }
        // Send back array OR send back 0 (for zero results)
        return (!empty($result))? $result : 0;
    }

/functions/function.write.php

<?php
// This function is the same as query(), just no return array
function write($sql = false,$bind = false)
    {
        $conn   =   PDOConn::connect();
        if(!empty($bind) && is_array($bind)) {
            $query  =   $conn->prepare($sql);
            $query->execute($bind);
        }
        else {
            $conn->query($sql);
        }
    }

/functions/function.check_user.php

<?php
// This will check the user.
// Do not store plain text passwords
// Instead use password_hash() and password_verify()
function check_user($username,$password)
    {
        $query  =   query("SELECT * FROM `login` WHERE `username` = :u LIMIT 1",array(":u"=>$username));

        if($query == 0)
            return false;

        return ($query[0]['password'] == $password);
    }

/functions/function.AutoloadFunction.php

<?php
// This is just an autoloader for your functions
// I use it to help cut down on bulk loading of functions
function AutoloadFunction($function = false)
    {
        // If input is not array, just stop
        if(!is_array($function))
            return false;
        // Set the load folder as this folder
        // (all functions should be in the same folder)
        $function_dir   =   __DIR__.'/function.';
        // Loop through the array and add the function(s)
        for($i = 0; $i < count($functions); $i++) {
            // Function name
            $addfunction    =   $functions[$i];
            // See if function exists
            if(!function_exists($addfunction)) {
                $dir    =   $function_dir.$addfunction.'.php';
                if(is_file($dir)) {
                    include_once($dir);
                }
            }
        }
    }

login.php

<?php
// Session start regardless
session_start();
// Check if login attempted
if(isset($_POST['login'])){
        $username = $_POST['username'];
        $password = $_POST['password'];
        // If user or pass is empty OR there is already a session, just stop
        // You may want to do a redirect here, not sure....
        if(empty($username) || empty($password) || !empty($_SESSION['username']))
            return false;
        // Include the autoloader function
        include_once(__DIR__.'/functions/function.AutoloadFunction.php');
        // Maybe look into using spl_autoload_register() to autoload classes
        include_once(__DIR__.'/classes/class.PDOConn.php');
        // Autoload functions
        AutoloadFunction(array("check_user","write","query"));
        // Verify with handy-dandy function
        if(check_user($username,$password)) {
            $_SESSION['username'] = $username;
            $location   =   "./pages/home.php";
        }
        // Write the attempt
        else {
            write("INSERT INTO `login_attempts` (`username`, `password`, `date_now`, `time_now`) VALUES (:uf, :pf, :date, NOW())",array(":uf"=>$username,":pf"=>$password,":date"=>date("y-m-d")));
            $location   =   "index.php?errror=invalid";
        }
        // Forward
        header("Location: {$location}");
        exit;
}