3

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

Andy
  • 875
  • 9
  • 15
  • Would any language, other than MySQL/PHP, be able to do this? – Andy May 23 '13 at 01:09
  • @Orangepill: No, he's fine. `ENCRYPT()` is badly named; it's actually a hash function. –  May 23 '13 at 01:10
  • Whew that is great news! Well then I'm still searching google for an answer unless someone can post it below, if I find the answer I will post it accordingly – Andy May 23 '13 at 01:13

1 Answers1

3

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.

Community
  • 1
  • 1
  • Thanks for the answer, so I would use crypt() on the value the user supplies and then feed it into the query, no salt? I'm confused because I'm unsure of what the value of $hashed_password should be and I assume my salt is the CONCAT('$6$', SUBSTRING(SHA(RAND()), -16)), or am I misguided on that also? – Andy May 23 '13 at 01:24
  • 1
    `$hashed_password` would be the password as stored in the database. But please read the question I linked to. –  May 23 '13 at 01:25
  • Ah ok, I understand now! I've just read it and am now looking into using scrypt, and I'm going to stick to those do's and don'ts. Thanks for all your help, you just made another website secure :D – Andy May 23 '13 at 01:35