PHP Prepared statement login

Question

I'm in the process of adding password hashing and SQL injection defenses into my Login system. Currently, I've ran into an error.

    <?php
session_start(); //start the session for user profile page

define('DB_HOST','localhost'); 
define('DB_NAME','test'); //name of database
define('DB_USER','root'); //mysql user
define('DB_PASSWORD',''); //mysql password

$con = new PDO('mysql:host=localhost;dbname=test','root','');

function SignIn($con){
    $user = $_POST['user']; //user input field from html
    $pass = $_POST['pass']; //pass input field from html
    if(isset($_POST['user'])){ //checking the 'user' name which is from Sign-in.html, is it empty or have some text
        $query = $con->prepare("SELECT * FROM UserName where userName = :user") or die(mysqli_connect_error());
        $query->bindParam(':user',$user);
        $query->execute();

        $username = $query->fetchColumn(1);
        $pw = $query->fetchColumn(2);//hashed password in database
        //check username and password
        if($user==$username && password_verify($pass, $pw)) {
            // $user and $pass are from POST
            // $username and $pw are from the rows

            //$_SESSION['userName'] = $row['pass'];
            echo "Successfully logged in.";
        }

        else { 
            echo "Invalid."; 
        }
    }
    else{
        echo "INVALID LOGIN";
    }
}

if(isset($_POST['submit'])){
    SignIn($con);
}
?>

In the above code, when I enter a valid username and password, the system prints out "Invalid". It could be a error in the password_verify() in the if statement(because if I remove it, I login successfully). I'm pretty sure I've done the preparing, binding and execution of the query properly? Does anyone know why it is doing this?

Thanks!

Answer 1

You're doing a SELECT *, and using fetchColumn, so the results are dependent of the returned columns order. You should either select the specific columns you need, or fetch the whole row as an associative array , and access it by column name.

There are other two issues that you should fix:

• You shouldn't be using mysqli_connect_error() as you're using PDO. The right function would be $con->errorInfo().
• You're defining some constants with the connection settings, yet you don't use them on the PDO() call, repeating the values instead.

Answer 2

Use

// it will be an array('name' => 'John', 'password_hash' => 'abcd')
// or FALSE if user not found
$storedUser = $query->fetch(PDO::FETCH_ASSOC);

instead of

$username = $query->fetchColumn(1);
$pw = $query->fetchColumn(2);

Because fetchColumn moves cursor of result. So first call extracts 1 column of first row, and second call will extract data from SECOND row!