MySQL Function PASSWORD() To Be Deprecated

[Len_Jacobson]

Apparently, the MySQL function, PASSWORD(), is to be deprecated in a future release of MySQL. The dev.MySQL.com website has a write-up at https://dev.mysql.com/worklog/task/?id=6409.

That write-up speaks only of the PASSWORD() function used to add/alter one’s MySQL password. It doesn’t address the use of PASSWORD() in a SQL statement.

I use that function in a login page where my user is logging into my website. (S)he enters a username and a password, and I compare it to the hashed password that is in a table of usernames and passwords.

Can anyone lead me to documentation on how to replace PASSWORD() in a SQL query?

Thank you.

Len Jacobson

[phdr]

The mysql password() function was NEVER intended for user applications.

You should be using php’s password_hash() and password_verify().

What you need to do to eliminate the password() function, is add a new column to hold the password hash from php’s password_hash() function. When a user successfully logs in using the existing password hashing, hash the submitted password using password_hash() and update the hashed value into the new column. In your login logic, if a value exists in the new column, use password_verify() to test if the submitted password matches the hashed value. Once you have a new password_hash() value stored, you can remove the existing mysql password hash value and eventually remove the old column once all users have password_hash() values stored in the new column.

[Len_Jacobson]

I appreciate the quick response. I will change my code as you suggest.

Thanks very much!!

Len Jacobson

[Len_Jacobson]

I am running into a problem in my implementation of your recommendation.

Let’s say that my password table has two fields, “Username” and “Pass”, where the “Pass” field is defined to be VARCHAR(255).

To create his account, the user, say, has entered “abc” as the password, and that is stored in $given_pswd. I then compute a hashed password with the command:

$pswd = password_hash($given_pswd, PASSWORD_DEFAULT);

and I insert the password into the database table with the SQL query:

INSERT INTO password_table VALUES (“Username”, ‘$pswd’);

When that user now tries to log in and enters “abc” as the password, I go to the database to check its validity. But first I compute the hashed value of “abc” with

$pswd = password_hash($provided_pswd, PASSWORD_DEFAULT);

where $provided_pswd is “abc”.

What is happening is that the two password entries are different!! That is, when I created the hashed password with

$pswd = password_hash("abc", PASSWORD_DEFAULT);

and when I compute it again with

$pswd = password_hash("abc", PASSWORD_DEFAULT);

I get a different value for the hashed password. In fact, every time I get a hashed value for the very same input, I get a different result.

I don’t see that using a different option in the second field is possible.

What am I doing wrong?

Thanks again.

Len Jacobson

[phdr]

I get a different value for the hashed password. In fact, every time I get a hashed value for the very same input, I get a different result.

That IS the intended operation of password_hash(). A random salt value is generated every time a password is hashed.

Password_verify() is used when testing if an entered password matches the previously hashed value.

You should be using php’s password_hash() and password_verify().

What you need to do to eliminate the password() function, is add a new column to hold the password hash from php’s password_hash() function. When a user successfully logs in using the existing password hashing, hash the submitted password using password_hash() and update the hashed value into the new column. In your login logic, if a value exists in the new column, use password_verify() to test if the submitted password matches the hashed value. Once you have a new password_hash() value stored, you can remove the existing mysql password hash value and eventually remove the old column once all users have password_hash() values stored in the new column.

[Len_Jacobson]

Thank you for alerting me to the ‘password_verify()’ function. I now have my code working to my satisfaction. But my coding is simpler than what I understood you to say, and that has me worried – maybe I am missing something.

When I create the password record in the database, my code is the following:

	//	Create a hashed value for the entered password
	$pswd = password_hash($pswd, PASSWORD_DEFAULT);

	//	Construct the SQL query to insert the new acct values into the database
	$sqlQuery = "INSERT INTO $tbl_name VALUES (NULL, '$id', '$pswd');"; 

And when the user logs in and I need to verify the entered password, my code says:

	//	Extract the hashed value of the password from the table
	$sqlQuery = "SELECT password FROM $tbl_name WHERE id = '$id';";

and once I have run that SQL query in the database, I compare

	//	Are the two passwords equivalent?
	if (password_verify($pswd, $value_from_database)) {

This code seems to work fine. But am I kidding myself?

Len Jacobson

[benanamen]

You got it, but you need to use Prepared Statements and get rid of the NULL insert.

[phdr]

$sqlQuery = “INSERT INTO $tbl_name VALUES (NULL, ‘$id’, ‘$pswd’);”;

Also, list the columns in your INSERT query (insures that if the columns in your table ever get re-arranged, your query will still work and gets your query to self-document what you are doing) and remove the semi-colon from the end of the sql statements (not used when a query gets executed through php.)

You would also want to SELECT the auto-increment integer primary index column when logging in and ONLY store this value in a session variable to identify who the logged in user id.

[benanamen]

Also, list the columns in your INSERT query

Ah, yes. Didn’t notice that. Good catch.