Securing MySQL Passwords

There’s been a bit of buzz lately where MySQL databases have been compromised and user passwords being stored in plain text. Bad fu. It’s pretty simple to encrypt passwords in MySQL, here’s how you do it.

I’m using MySQL’s AES which is better than older standards. Basically 128 bit key encryption …

To begin, let’s create a demonstration table. e.g.
CREATE TABLE users ( user_name VARCHAR(20) NOT NULL, user_password BLOB );

So now we have a simple table with a user_name field and user_password field. e.g.

mysql> DESCRIBE users;
+---------------+-------------+------+-----+---------+-------+
| FIELD         | Type        | NULL | KEY | DEFAULT | Extra |
+---------------+-------------+------+-----+---------+-------+
| user_name     | varchar(20) | NO   |     | NULL    |       |
| user_password | blob        | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+

If we were creating a new user we would insert using the AES_ENCRYPT function. e.g.

INSERT INTO users VALUES ('tim.koopmans', AES_ENCRYPT('mypassword', 'Q4QjOyKANXj3qO'));

In this case I’ve used AES_ENCRYPT which encrypts a string and returns a binary string. The AES (Advanced Encryption Standard) algorithm is encoded with a 128-bit key. The second parameter is the key string, important to keep that secure!

On its own, this offers a good level of security. If the database was compromised the hacker will see information like this. e.g.

mysql> SELECT * FROM users;
+--------------+------------------+
| user_name    | user_password    |
+--------------+------------------+
| tim.koopmans | ??iu?hQ:???Ti?} |
+--------------+------------------+

The problem with this, is that the user_password field can still be ‘guessed’ via brute force methods. Every password that is the same i.e. ‘mypassword’ will also have the same binary string.

You can add a salt to this by prefixing random digits to the plain text password before you encrypt. For example, we can use the first 12 digits of a UUID. e.g.

INSERT INTO users VALUES ('joe.blow', AES_ENCRYPT(CONCAT(LEFT(UUID(),12), 'mypassword'), 'Q4QjOyKANXj3qO'));

The result set now looks like this if compromised.

mysql> SELECT * FROM users;
+--------------+----------------------------------+
| user_name    | user_password                    |
+--------------+----------------------------------+
| tim.koopmans | ??iu?hQ:???Ti?}                  |
| joe.blow     | ?M}69?H?0?H}?/{.?we|?A?P7ZD?R?   |
+--------------+----------------------------------+

Every password that is encrypted should look different which will further complicate dictionary attacks.

To determine a password from a stolen hash, an attacker cannot simply try common passwords (such as English language words or names). Rather, they must calculate the hashes of random characters (at least for the portion of the input they know is the salt), which is much slower.

For best results, the value/length of your salt should be kept secret (and separate from your database). You should also keep the key secret of course!

Now if we ever need to extract the password we use the AES_DECRYPT function. e.g.

mysql> SELECT SUBSTR(AES_DECRYPT(user_password, 'Q4QjOyKANXj3qO'),13) AS password FROM users WHERE user_name = 'joe.blow';
+------------+
| password   |
+------------+
| mypassword |
+------------+

In this case we know the first 12 digits were the salt so I used SUBSTR after decrypting the hash to get the original password.

A couple of caveats, encrypting passwords in this fashion is reversible i.e. If your key gets compromised, then the hacker has access to all your passwords. Another option is to reverse the plain text and key parameters of AES_ENCRYPT, where the user’s password now becomes the key, and the key becomes the plain text. Even if the database and key is compromised, it is hard to crack user passwords. You would not be able to recover lost passwords of course … Security through obscurity!