How to check a mysql encrypt value with a salt in

问题:

For my website I've stored my user passwords in the database using this MySQL function:

ENCRYPT('password', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16)))

Now for my users to login I need to check the password they supply with the value in the database.

I assumed it would be as easy as this:

SELECT user FROM users WHERE id = $id AND password = ENCRYPT('$password', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16)))

However, it became apparent this would not work due to the RAND() function...

So how would I recreate this password in PHP (or mysql) to match it against encrypted password? I assume I would need to make use of crypt() or hash(), but I'm honestly not sure if PHP should be used or MySQL.

I am using MySQL version 5.5, PHP version 5.3

回答1:

The salt used by ENCRYPT() (better known as the crypt() function) is stored as part of the hash, and can be used as part of the hash:

SELECT ... FROM users WHERE ... AND password = ENCRYPT('swordfish', password);

(That is, if the password the user entered was "swordfish". I'm avoiding "password" because it's also a column name.)

You can (and should) do the same thing in PHP by checking:

crypt($user_password, $hashed_password) == $hashed_password

Note that crypt() is not a particularly secure method of password storage. Please see Secure hash and salt for PHP passwords for details.