MySQL password function

Is it considered good or bad practice to use MySQL's password function to hash passwords used by an application? I can see pros and cons. I'm curious if there is a general consensus on whether it is good or bad.

The docs for MySQL's PASSWORD() function states:

The PASSWORD() function is used by the authentication system in MySQL Server; you should not use it in your own applications.

Read "You're Probably Storing Passwords Incorrectly" for better advice on hashing and storing passwords.

MD5 and SHA-1 are considered to be too weak to use for passwords. The current recommendation is to use SHA-256.

I contributed a patch to MySQL to support a SHA2() function, and the patch was accepted, but since their roadmap has changed it's not clear when it will make it into a released product.

In the meantime, you can use hashing and salting in your programming language, and simply store the result hash digest in the database. If you use PHP, SHA-256 is available in the hash() function.

update: MySQL 5.5.8 was released in December 2010, and that release contains support for the SHA2() function.

If you are using a database function to hash passwords then by definition they have to arrive in the database unhashed: I would therefore prefer to do it much nearer the "source" i.e. in the frontend application so you're not passing around exposed information.

I believe the actual PASSWORD function in MySQL is insecure, and has been broken, but I can't find a link at the moment. I know the older one (OLD_PASSWORD in 5 and up) is definitely insecure.

Of course, all passwords should always be stored with a salt (for further obscurity). Example:

UPDATE users SET password=MD5(CONCAT('salt', 'user provided value')) WHERE id=54

There is also the MD5 function, but with the rise of colossal rainbow tables, it's not 100% reliable as a way of completely obfuscating stored passwords.

A better method is hashing the password (with a salt) before it reaches the database. Example:

<?php
$password = sha1(SALT.$_POST["password"]);
$sql = "UPDATE users SET password='".$password."' WHERE id=54";
?>

Introduction Without going into too technical and mathematical detail, it might be useful to explain a little bit about the difference between encryption, hashing, and salting.

Encryption Encryption has been around for an awfully long time. The Egyptions used it to create mystery and amusement, and the Romans used it to send secret messages. When you encrypt a password, you apply some sort of algorithm which scrambles it up. Applying the key, unscrambles it.

ROT13 is a simple example of an encryption algorithm. It basically replaces each letter with one 13 places away in the alphabet.

Don't drink the wine. = Qba'g qevax gur jvar. ROT13 is obviously quite a weak algorithm, but it's useful to illustrate the key point here - Encrypted data is reversible. It's like that by design. There's no point encrypting a secret message if the person at the other end is unable to decipher it. Therefore, it's useful for things like credit card numbers, or emails. The web browser you're reading this on is also using encryption.

The server encrypts the data, sends it over a secure SSL connection to your browser, which decrypts it so you can read it.

Hashing Hashing is different from encryption in that once the data is encoded, it can't be decoded. Well, at least it's extremely difficult to do so. Unlike encryption, the output is always of a fixed length, depending on the algorithm you use.

Using our phrase from before, and the MD5 algorithm we get ...

Don't drink the wine. = b290557177ec5dd7098d1de84616dd04 If we try a longer phrase ...

Please don't drink the wine, it tastes terrible. = fd870b20869d9ae05d84e3d7fbed0c94 You will see that the results are both the same length. This means, that multiple inputs could result in the same output, called a collission.

Hashing is useful when storing things which you don't need to read back, but you need to check. Passwords are the primary example. Instead of storing the clear text, you store the hashed version. Then, when someone types in their password, you apply the same hashing algorithm and compare it with what you have in the database. If they match, then the gates open.

Hash functions can also be used to test whether information has been tampered with. When sending an email, you first share a secret value that only you and the receiver know of. Before sending the email, you sign it with your secret value and produce the hash value. Then send your clear text email (without the secret value) along with the hash value. Your friend can then do the same process and if the hashes are the same, then they know your message hasn't been tampered with along the way. This technique is called Message Authentication Code or Hash Based Message Authentication Code.

The important factor for hashing algorithms is that they only work one way. The only way to work out the original value, is by brute force. Trying multiple values to see if they produce the same hash.

This is particularly problematic with passwords which are generally short and use commonly found words. It wouldn't take a modern computer very long to run through a large dictionary (or use existing rainbow tables) and figure out the hashed result of every common password.

That's where salting comes in.

Salting Beside clogging up your arteries, salts can clog up anyone trying to crack a hashed password. They work by adding an extra secret value to the end of the input, extending the length of the original password.

Say your password is rocky and the salt value is i.love.salt. The hash value would be made up from both of these together rockyi.love.salt. This provides some protection for those people who use common words as their password. However, if someone learns of the salt value you use, then they just add it to the end (or start) of each dictionary word they try in their attack.

To make this more difficult you can use random salts, one for each password. It obviously needs to be stored in the database somewhere matched up with the user account, but it does make brute force attacking much more difficult. Finally, you can create a salt from multiple parts, you can use the current date-time, the username, a secret phrase, a random value, or a combination of all of these.