PHP Can't use a hashed password in a PDO query

Question

I'm currently a 3th year web-dev student, and for a school project we have to build a website with a change password function using PDO and hashes, however I have run into a problem I cannot solve on my own, I also haven't found any similar questions on both google and stackoverflow.

we have to store 2 passwords in the database, the old password (which is the password the user has lastly used); and their current password (which will be used to login.)

all of the passwords are stored with a standard PHP hash (the password_hash() method)

When changing the user's password, I have to get the current password (the one used to login) from the database, and move it to the oldpassword (the last used password) column while putting the new hashed password into the database.

the problem is: I can't seem to use a hashed password in a PDO query, i do need to point out that I have never worked with PDO before, but after googling a bit I don't think I have an error in my query, but in the data being passed to that query.

this is where i get all the current data from the database

/*prepare and execute a query*/
$sqlStatement = $this->db->prepare("SELECT username, password, oldpassword FROM users WHERE username = :username");
$sqlStatement->bindParam(":username", $username, PDO::PARAM_STR);
$sqlStatement->execute();

/*Fetch the query results*/
$values = $sqlStatement->fetch(PDO::FETCH_ASSOC);

And this is where I try to store the data into the database using a PDO SQL query

/*Create variables to use in querys*/
$queryPassword = password_hash($password, PASSWORD_DEFAULT);

/*Update the passwords*/
$sqlStatement2 = $this->db->prepare("UPDATE password, oldPassword VALUES (:password, :oldpassword)");
$sqlStatement2->bindParam(":password", $queryPassword, PDO::PARAM_STR);
$sqlStatement2->bindParam(":oldpassword", $values["password"] , PDO::PARAM_STR);
$sqlStatement2->execute();

$password and $username are variables passed from a form, to the function to change the user's password.

public function updateUserPasswords($username, $password){

What I find odd is: if i do a var_dump on $values["password"] I get the following data:
string(60) "$2y$10$BBCpJxgPa8K.iw9ZporxzuW2Lt478RPUV/JFvKRHKzJhIwGhd1tpa"

Where my query error gives me the following data:
$2y$10$

it's as if only the hash is being sent to the query.

==EDIT==

The SQL error being sent to the browser is:

Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VALUES ('$2y$10$AC.aMG/gNV9zwGB/v/g7keW9jsZ80kuejrSh693DPuhOYChFxA6wu', '$2y$10$' at line 1' in /srv/jip/www/www.jip.nl/classes/user.class.php:92 Stack trace: #0 /srv/jip/www/www.jip.nl/classes/user.class.php(92): PDOStatement->execute() #1 /srv/jip/www/www.jip.nl/pages/first_login.php(26): User->updateUserPasswords('username', 'password') #2 {main} thrown in /srv/jip/www/www.jip.nl/classes/user.class.php on line 92

What is your max length on your password field in the database? I'm guessing it's 7 — Farkie
– Farkie, Commented Oct 15, 2015 at 10:51
@noobie-php Why would you use MySQL hashing functions? PHP has much more secure hashing functions available than MySQL does (for instance password_hash() uses bcrypt with multiple rounds). — Phylogenesis
– Phylogenesis, Commented Oct 15, 2015 at 10:52
User inputs password in form > form is sent > PHP hashes input from form > PHP stores hash in database. Everything's fine here. — alanmm
– alanmm, Commented Oct 15, 2015 at 10:56

Mark Baker · Accepted Answer · 2015-10-15 11:19:00Z

5

Don't retrieve the data using the hashed password, because password_hash() generates a new hash with a different salt whenever it's used

Retrieve the record using just the username, then use password_verify() to validate the password

EDIT

Your update query references columns, but no table, and without a WHERE clause:

$sqlStatement2 = $this->db->prepare("UPDATE users SET password=:password, oldPassword=:oldpassword WHERE username = :username");
$sqlStatement2->bindParam(":password", $queryPassword, PDO::PARAM_STR);
$sqlStatement2->bindParam(":oldpassword", $values["password"] , PDO::PARAM_STR);
$sqlStatement2->bindParam(":username", $username, PDO::PARAM_STR);
$sqlStatement2->execute();

binding with the appropriate values

edited Oct 15, 2015 at 11:19

answered Oct 15, 2015 at 10:59

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jip Over a year ago

allright, thanks for your quick response, i'll give it a try and check back when I have done so.

noobie-php Over a year ago

Well explained and quite logical

Jip Over a year ago

the where clause is indeed missing, haven't added it yet because the table only has a single result for testing at the moment.. As for the table, it's included in the $this->db

Jip · Accepted Answer · 2015-10-15 15:07:14Z

1

Turned out my query was indeed incorrect, it's been a while since i last made an update query and I put it in the same format as an insert query.

Mark Baker's solution also fixed the problem with retrieving the old password from the database. retrieving the information using just the username has worked.

answered Oct 15, 2015 at 15:07

Jip

236 bronze badges

Collectives™ on Stack Overflow

PHP Can't use a hashed password in a PDO query

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related