Storing Passwords in MySQL

Securing plain text passwords in MySQL is NEVER a good idea. As a DBA you should take great care in protecting the users' information. Fortunately MySQL provides you with several options to protect passwords.

After a quick scan of the manual, you may be tempted to store the password by applying the password function to it which is NOT a good idea. MySQL itself advises against using PASSWORD to manage application passwords.

Instead of using PASSWORD(), we can use SHA1 or MD5. Unfortunately exploits for both of these encryption functions have been quite common these days. Still, SHA1 or MD5 keep your password more protected than storing them as plain text.

You can apply SHA1 algorithm to a password string:

mysql>  SELECT SHA1('mysecretpassword');
+------------------------------------------+
| SHA1('mysecretpassword')                 |
+------------------------------------------+
| 08cd923367890009657eab812753379bdb321eeb |
+------------------------------------------+
1 row in set (0.00 sec)

Since SHA is an alias for SHA1, it produces the same result

mysql>  SELECT SHA('mysecretpassword');
+------------------------------------------+
| SHA('mysecretpassword')                  |
+------------------------------------------+
| 08cd923367890009657eab812753379bdb321eeb |
+------------------------------------------+
1 row in set (0.00 sec)

To store passwords encrypted with SHA1, we need to be able to store 40 characters.

mysql> SELECT CHARACTER_LENGTH(SHA1('mysecretpasswordsssssss'));
+---------------------------------------------------+
| CHARACTER_LENGTH(SHA1('mysecretpassword')) |
+---------------------------------------------------+
|                                                40 |
+---------------------------------------------------+
1 row in set (0.00 sec)

On the other hand, to store passwords encrypted with MD5, we need the column to be able to hold 32 characters.

mysql>  SELECT MD5('secretpassword');
+----------------------------------+
| MD5('secretpassword')            |
+----------------------------------+
| 2034f6e32958647fdff75d265b455ebf |
+----------------------------------+
1 row in set (0.00 sec)

mysql>  SELECT CHARACTER_LENGTH(MD5('secretpassword'));
+-----------------------------------------+
| CHARACTER_LENGTH(MD5('secretpassword')) |
+-----------------------------------------+
|                                      32 |
+-----------------------------------------+
1 row in set (0.00 sec)

Using MD5 in your application is easy. Here's how your queries will need to be to take advantage of encryption offered by MD5.


First, let's create a table:

mysql> create table user_md5 (user_name VARCHAR(16), password VARCHAR(32));
Query OK, 0 rows affected (0.00 sec)

Now let's insert a record with MD5 applied to the password field.

mysql> INSERT INTO user_md5 VALUES ('member1',MD5('secretpassword') );
Query OK, 1 row affected (0.00 sec)

Finally, let's see if it all works when we try to authenticate a user.

mysql> SELECT * FROM user_md5 WHERE user_name='member1' AND password=MD5('secretpassword');
+-----------+----------------------------------+
| user_name | password                         |
+-----------+----------------------------------+
| member1   | 2034f6e32958647fdff75d265b455ebf |
+-----------+----------------------------------+
1 row in set (0.00 sec)

SHA1
Here's how we can store passwords encrypted with SHA1 algorithm, which is "cryptographically more secure" than MD5. Note that we are changing the definition of password field so it can store 40 characters.

mysql> create table user_sha1 (user_name VARCHAR(16), password VARCHAR(40));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO user_sha1 VALUES ('member1',SHA1('secretpassword') );
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM user_sha1 WHERE user_name='member1' AND password=SHA1('secretpassword');
+-----------+------------------------------------------+
| user_name | password                                 |
+-----------+------------------------------------------+
| member1   | edbd1887e772e13c251f688a5f10c1ffbb67960d |
+-----------+------------------------------------------+
1 row in set (0.00 sec)

Since the exploits for MD5 and SHA1, especially in the form of dictionary attacks, are out there, using them may not provide us the level of encryption and protection we desire. Fortunately, MySQL offers


AES
If you would like to implement AES (Advanced Encryption Standard) encryption, you will need to specify the password field to be of type BLOB. AES functions encode with a 128-bit key length which can be changed by modifying source and then recompiling MySQL.

mysql> create table user_aes (user_name VARCHAR(16), password BLOB);
Query OK, 0 rows affected (0.00 sec)

Now to insert a record, we will call AES function and in addition to providing the secret password to be protected, we will also provide the key_str.

mysql> INSERT INTO user_aes VALUES ('member1',AES_ENCRYPT('secretpassword','my_secret_key_to_encrypt') );
Query OK, 1 row affected (0.02 sec)

Now to authenticate a user, we encrypt the password which the user is using to login with the same key_str as we used before. Then we can match it against the records in the table.

mysql> SELECT * FROM user_aes WHERE user_name='member1' AND password=AES_ENCRYPT('secretpassword','my_secret_key_to_encrypt');
+-----------+------------------+
| user_name | password         |
+-----------+------------------+
| member1   | ñGa·`·
                    +'U |
+-----------+------------------+
1 row in set (0.00 sec)

Since AES provides reversible encryption (provided you have the key_str), we can obtain the password in plain text format.

mysql> SELECT AES_DECRYPT(password, 'my_secret_key_to_encrypt') AS unencrypted FROM user_aes ;
+----------------+
| unencrypted    |
+----------------+
| secretpassword |
+----------------+
1 row in set (0.00 sec)

According to MySQL AES functions (AES_ENCRYPT() and AES_DECRYPT()) were added in MySQL 4.0.2 and are currently the most cryptographically secure encryption functions in MySQL.

Note: A common error is to use AES function to encrypt instead of AES_ENCRYPT. If you do so you'll get the following error:

ERROR 1305 (42000): FUNCTION db_name.AES does not exist

Now we can see the table status for each of the table to see the differences in Avg_row_length etc.

Using SHA1

mysql> show table status like 'user_sha1' \G
*************************** 1. row ***************************
           Name: user_sha1
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 1
 Avg_row_length: 56
    Data_length: 104
Max_data_length: 281474976710655
   Index_length: 1024
      Data_free: 48
 Auto_increment: NULL
    Create_time: 2006-08-21 13:38:10
    Update_time: 2006-08-21 14:06:43
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

Using MD5

mysql> show table status like 'user_md5' \G
*************************** 1. row ***************************
           Name: user_md5
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 1
 Avg_row_length: 48
    Data_length: 48
Max_data_length: 281474976710655
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2006-08-21 13:58:59
    Update_time: 2006-08-21 14:01:55
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

For table with AES encryption

mysql> show table status like 'user_aes' \G
*************************** 1. row ***************************
           Name: user_aes
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 1
 Avg_row_length: 32
    Data_length: 32
Max_data_length: 281474976710655
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2006-08-21 14:08:22
    Update_time: 2006-08-21 14:17:25
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

If MySQL has been configured with SSL support, you can use the DES functions which use the Triple-DES algorithm to encrypt a string with the supplied key.

The syntax for these functions is

DES_DECRYPT(crypt_str[,key_str])
DES_ENCRYPT(str[,{key_num|key_str}])

I will discuss them in more detail in the near future.

So now that you have read this post do you mind sharing what algorithm do you use to store passwords in your application?

--Frank

References:
SHA1: Secure Hash Algorithm version 1.0
SHA functions
MD5: Message Digest Algorithm 5
RFC 1321: The Message Digest Algorithm
AES: Advaned Encryption Standard
Storing Passwords in MySQL
Storing passwords safely in mysql
Keeping passwords secure with MySQL