Mysql password() function - not recommended

[fos]

While working on a mysql project, I started receiving an error warning from the mysql server. It was truncating the data generated by the password() function to encrypt a user's password. In addition, the function was not producing a usable encrypted password.

I did a search on the mysql 5 manual and found the following statement:

"The PASSWORD() (http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html#function_password) function is used by the authentication system in MySQL Server; you should not use it in your own applications. For that purpose, consider MD5() (http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html#function_md5) or SHA1() (http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html#function_sha1) instead."

Experimentation proved that the sha() function worked well producing expected results without generating an error. Using this method, you must set define the variable to char(40) in order to hold the encrypted data.

This info might save a fair amount of research time if you need to use passwords in an secure mysql application.

fos

[danieldk]

While working on a mysql project, I started receiving an error warning from the mysql server. It was truncating the data generated by the password() function to encrypt a user's password.

Ah yes, password(). I once had to maintain an application that used that extensively, and with MySQL 5 the semantics changed (aka a different hash was used). I guess the original programmer missed this in the documentation ;).

Experimentation proved that the sha() function worked well producing expected results without generating an error. Using this method, you must set define the variable to char(40) in order to hold the encrypted data.

This info might save a fair amount of research time if you need to use passwords in an secure mysql application.

This alone is not enough. You also need to salt your password to prevent that two passwords are the same given the same hash (this may also prevent attacks based on dictionary-based rainbow tables a bit). You may have to extend the table password column to store the salt as well.

[fos]

Salting sounds like a good idea.

Do you mean to add a random value to the initial password selection to prevent duplication?

[danieldk]

Salting sounds like a good idea.

Do you mean to add a random value to the initial password selection to prevent duplication?

Yes, concatenate (or xor) the password with some random material before hashing. After hashing you can provide the salt in the password field before or after the password. (So that you'll be able to verify a password.)