Encrypting user’s information like their passwords needs to be easy. All the old MySql functions like PASSWORD() and ENCRYPT() still exist but are considered not secure enough anymore. These days only MD5 and SHA1 are strong enough to withstand an attack, but even so, both by themselves have vulnerabilities that can be exploited requiring some extra code. What I want is a simple code snipit to take care of the password encoding for me, all in MySql. So here it is:

SELECT SHA1(CONCAT(@pwd, salt)) sha_pwd, salt FROM (
SELECT FLOOR(RAND() * 0xFFFFFFFF) AS salt) t1

And to check later if the user entered their password correctly, use something like:

SELECT SHA1(CONCAT(@entered_pwd, @salt)) = @stored_sha_pwd

For the top snipit, you pass in the “@pwd” and it will return both the encoded SHA and a “salt” value, both of which need to be stored in the user’s record. For the second snipit, pass in both the values stored in the user record, plus the password you are checking to return a true/false (1/0) response.

The strength and the problem with the SHA function (and MD5) is that it always returns the same hash. For example, SHA(“password”) will always return the number “5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8″. Sure its a long, ugly, unintuitive number, but if you’ve got a database of 100k users and you see this or any other number repeated then they are probably common phrase passwords. Sure the bad guys can crack a password of “password”, but repeating patterns gives them too much of a leg up.

The solution is salting, which is just appending a unique number onto the password before using the SHA function. Sprinkling a bit of salt on “password” gives it a different SHA hash. Each user gets a different amount of salt sprinkled on their passwords, so they each get different SHA numbers.

The salt number needs to be random. The MySql RAND() function returns a random floating point number from 0 to 1 (or more precisely 0 <= RAND() < 1) which I’ve chosen to use a 16-bit random number. The RAND() function can take a seed, but this is a bad idea since the seed itself needs to be sufficiently random. Traditionally people have used the clock as a seed, but if you use NOW() as the seed and are time stamping your records then the seed can be determined. Worse yet, if two users make accounts at the same time and the RAND(NOW()) is used then they will both get the same salt value. You can generate the seed externally and pass it in, but my stated goal was to keep it all within MySql, and for salt generation this would be overkill; the nuances of random numbers are fascinating, but fortunately aren’t too important here. The RAND() is only used to generate the salt, which is stored in clear text in the database (or in a decryptable field). So long as the salt is sufficiently random and isn’t generating the same value at any frequency or in a predictable way then it is fine. According to the MySql documentation, “RAND() is not meant to be a perfect random generator”, but for this purpose that’s OK.

If you don’t think its secure enough since the user’s salt is visible to someone who has hacked your database, you can add some pepper too. Have a system-wide number that isn’t stored in the database, and append that to the salted passwords. If the live database is compromised then a good hacker will probably be able to get the pepper number when it is passed to the MySql SHA function, but at least it will practically shut down cracking of offline copies of the database. Its an easy add to the code snipits.

As an integration note if you use the snipit, the top query salt is generated in a sub-query. This is important so that the generated salt can be generated once and used twice (once for the SHA and once as a returned value). Otherwise you could end up with two salt values.

This obviously doesn’t solve the problem of users entering weak passwords like “password”. The weak password problem should be handled in the web page entry validation, preferably using a dictionary lookup validation plus some password strength heuristics.