Convert from mysqli_query to mysqli prepared statement using mysql PASSWORD function

Question

I have converted my website from mysql to mysqli prepared statements except for one query. The query I can't figure out is:

$sql = "SELECT customerID FROM customer WHERE customerEmail = '$loginEmailAddress' AND customerPassword = PASSWORD('$loginPassword');";
$result = mysqli_query($mysqli, $sql);

This works fine. When I try to make an mysqli prepared statement, the problem is the mysql PASSWORD function. Is it even possible to convert this?

I tried things like:

$loginPassword = PASSWORD($loginPassword);

$stmt = $mysqli -> prepare("SELECT customerID from customer WHERE customerEmail = ? AND customerPassword =  ? ");
$stmt -> bind_param("ss", $loginEmailAddress,$loginPassword);
$stmt -> execute();
$stmt->store_result();
$stmt -> bind_result($customerID);
$stmt -> close();

and of course no success. I also tried things like:

$loginPassword  = '" . PASSWORD('$loginPassword') . "';

I am working toward using phpass, but in the meantime I need to keep using PASSWORD for my existing customers until they login and I can move them to the new hash.

Where is the `$stmt->fetch()` before you do a `$stmt->close();`? — argentum47, Aug 15 '12 at 01:59
there is no need to "convert" the PASSWORD() function. It doesn't matter if you are using mysql_*, mysqli_* or PDO to access the mysql server. — Jocelyn, Aug 15 '12 at 23:47
Missed putting in $checkRow = $stmt->num_rows(); Don't think I need fetch if only checking the num_rows. I guess I don't understand how to use an mysql function in a prepared statement. loginPassword is text and the password in the database is hashed so how do I compare them? In the old mysql query I did PASSWORD('$loginPassword') so I don't know how to do it in a prepared statement. — user80523, Aug 16 '12 at 14:10

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

PASSWORD() is a MySQL function. It is part of the SQL. You only need to parameterize the argument you pass to this function.

$stmt = $mysqli -> prepare("SELECT customerID 
    FROM customer 
    WHERE customerEmail = ? AND customerPassword = PASSWORD(?) ");
$stmt -> bind_param("ss", $loginEmailAddress,$loginPassword);
$stmt -> execute();

Warning:
Only store password hashes created using PHP's password_hash(), which you can then verify using password_verify(). Take a look at this post: How to use password_hash and learn more about bcrypt & password hashing in PHP

Warning:

This function is deprecated as of MySQL 5.7.6 and will be removed in a future MySQL release.

PASSWORD() is used by the authentication system in MySQL Server; you should not use it in your own applications.

Convert from mysqli_query to mysqli prepared statement using mysql PASSWORD function

1 Answers1

Linked