PHP Prepared Statement Issues

Question

I am having trouble implementing the below prepared statement. I have used this same prepared statement code and made it work elsewhere so not sure where I am going wrong. I had the below script up and running without a prepared statement which used concatenation so I know there is no issue with my SQL or the initial email/password validation.

The database query only returns one row, i dont think this should affect my while array fetch? Email address and password set to strings in the prepared statement ('ss'), assuming no issues with that.

I used get_password_hash($p) in my initial query concatenation and it worked. Perhaps I should include it in the bind param like this:

$loginQuery->bind_param('ss',$e,get_password_hash($p));

instead of below...

Any advice would be much appreciated.

// Array for recording errors:
$login_errors = array();


// Validate the email address:
if (filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)) {
$e = $_POST['email'];
} else {
$login_errors['login'] = 'Please enter a valid email address!';
}

// Validate the password:
if (!empty($_POST['pass'])) {
$p = $_POST['pass'];
} else {
$login_errors['pass'] = 'Please enter your password!';
}


if (empty($login_errors)) { // OK to proceed!
// Query the database:

/************NO ISSUES WITH ABOVE; THIS WORKED FINE BEFORE I TRIED CONVERTING TO PREPARED STATEMENTS************/

    $pas = get_password_hash($p);
    $loginQuery = $dbc->prepare("SELECT id, username, type, IF(date_expires >= NOW(), true, false) FROM user WHERE (email= ? AND pass= ?)");
    $loginQuery->bind_param('ss',$e,$pas);
    $loginQuery->execute();
    $loginQuery->bind_result($l);

        while(loginQuery->fetch()){
        $login = $l;
        $_SESSION['user_id'] = $login[0];
        $_SESSION['username'] = $login[1];
        } 

}

You dont need to escape anything when using prepared statements. — Mihai, May 12 '14 at 19:23
Mihai - not sure, put this up to see if there were any obvious issues with my code. — DVCITIS, May 12 '14 at 19:30
try --- fetch_all --- instead of --- fetch --- http://www.php.net/manual/en/mysqli-result.fetch-all.php — Tasos, May 12 '14 at 19:30
no luck there. I am not getting any errors at the moment. The page that called the script is just blank, nothing in the page source. — DVCITIS, May 12 '14 at 19:34
I got an error earlier saying that the number of items in my bind_param line didnt match what was expected in the prepared statement. This didnt make sense; two question marks, and two parameters to be bound ('ss', $e, $pas) — DVCITIS, May 12 '14 at 19:35
You did not salt your hash. It is not possible to verify a salted password safely with pure SQL, i tried to explain it in this [answer](http://stackoverflow.com/a/23563904/575765). First you should get the hash with a query, then you can verify the hash with [password_verify()](http://www.php.net/manual/en/function.password-verify.php). — martinstoeckli, May 12 '14 at 20:32
Refined the question and posted here: http://stackoverflow.com/questions/23636643/prepared-statement-fetch-or-fetch-all-issue-with-while-statement — DVCITIS, May 13 '14 at 16:26

score 1 · Accepted Answer · answered May 12 '14 at 19:35

1

You're using the variable $p up top but you're using $pas down below.

You shouldn't be doing any of that mysql_escape_blahblahblah stuff with prepared statements, anyway. Just do this directly:

$loginQuery->bind_param('ss',$_POST['email'],$_POST['pass']);

answered May 12 '14 at 19:35

Andy Lester

91,102
13
100
152

Thanks. I got rid of the escaping and edited the original question. $p is the actual password, I store hashes in the DB. $pas is the hashed version. I also have some IF statements up top to create login errors which I display on the page if incorrect email format or no password entered. Cant see any issues with the fetching of my array? – DVCITIS May 12 '14 at 19:53
$pas = get_password_hash($p); – DVCITIS May 12 '14 at 19:54
Mea culpa, I missed that `get_password_hash()`. But the point still stands that you should not be doing any of that mysql escaping hoohah. – Andy Lester May 12 '14 at 20:00

PHP Prepared Statement Issues

1 Answers1