When it comes to protecting user passwords, MySQL offers a lot of options, from minimal to incredibly robust.  Of course, password encryption is not a one-size-fits-all type solution.  It all depends on your data and what priority you place on its safety.  To that end, today’s article will cover a few different encryption algorithms supported in MySQL that will help you make a sound decision as to which suits your needs best. 

Using the Default Password Encryption

MySQL stores passwords in the User table, along with all things user:

mysql> SELECT host,user,password FROM mysql.user; +-----------+------+-------------------------------------------+ | host      | user | password                                  | +-----------+------+-------------------------------------------+ | localhost | root | *D4FA16B3275E6619F3029FDDBA9A90EBA0DDFBEA | +-----------+------+-------------------------------------------+

Smartly, MySQL doesn’t store passwords as plaintext, but rather, as a hashed value that is calculated by the Password() function.

A hash is a special one-way encryption algorithm that produces an encrypted value for a given string. 

When you log into MySQL, it runs the supplied password through the same encryption algorithm and compares the result to the stored value in the User table. Being a one-way algorithm makes it harder to crack because even MySQL can’t derive the plaintext string from the hashed value! (Note to self: don’t forget password.) Before MySQL 4.1, these were 16 bytes long. 
Since then, the Password() function has been modified to produce a longer – and more secure – 41-byte hash value.

When we create a new user account using the CREATE USER command, MySQL takes the IDENTIFIED BY value and runs it through the Password() function behind the scenes:

mysql> CREATE USER 'robg'@'localhost' IDENTIFIED BY 'test1234'; 0 rows affected, 0 rows found. Duration for 1 query: 0.328 sec.   In fact, you can't set the password directly using an INSERT or UPDATES statement:   mysql> UPDATE 'user' SET Password = 'test1234' WHERE User = 'RobG'; /* SQL Error (1364): Field 'ssl_cipher' doesn't have a default value */

Using the Password() function to encrypt passwords is a whole lot better than nothing, but you can use stronger encryption if your data requires it.  The MySQL docs explicitly state that Password() should only be used to manage passwords for MySQL accounts and that you should not use it in your own applications.  For that purpose, they recommend going with something a little more potent like MD5 or SHA1.

MD5 creates a hash string of 32 hex digits.  SHA1 produces a 160-bit checksum for the string, according to the RFC 3174 (Secure Hash Algorithm) spec, resulting in a string of 40 hex digits. Of the two, SHA1 is considered to be more secure than MD5.

MySQL already has built-in MD5() and SHA1() functions.  All you have to do is call them, but use a regular INSERT or UPDATE statement for those.

For instance, this won’t work:

SET PASSWORD FOR 'robg'@'localhost' = MD5('newpassword');

However, this will:

UPDATE User SET Password = MD5('newpassword') WHERE user = 'robg';

To store passwords encrypted with SHA1, you’ll need to be able to store 40 characters. When in doubt, the CHARACTER_LENGTH() function will tell you how large the password field has to be:

mysql> SELECT CHARACTER_LENGTH(MD5('newpassword')); +-----------------------------------------+ | CHARACTER_LENGTH(MD5('newpassword'))    | +-----------------------------------------+ |                                      32 | +-----------------------------------------+

Exploits have been documented for both the SHA and MD5 ciphers, so if you want to get ahead of the curve, you might consider going to SHA-2, using the SHA2() function. It calculates the SHA-2 family of hash functions, which include SHA-224, SHA-256, SHA-384, and SHA-512, using two arguments: the cleartext string to be hashed and the desired bit length of the result.  The latter must have a value of 224, 256, 384, 512, or 0 (which defaults to 256).  The function returns a hash value containing the desired number of bits:

mysql> SELECT SHA2('newpassword', 224); +-----------------------------------------------------------+ | SHA2('newpassword', 224)                                  | +-----------------------------------------------------------+ | 4a574b42e32e03846eda8fc71b667a527c3840614896f0376bfca92b  | +-----------------------------------------------------------+        

mysql> SELECT SHA2( 'newpassword', 384); +-----------------------------------------------------------------------------------------------+  | SHA2('newpassword', 384)                                                                      | +-----------------------------------------------------------------------------------------------+  |0f60c17a9c7df029682066d18836e4213803b62f766b1555efaf14e8b0cf61b81b838deb56ef3397c07e7b7bb8e96df| +-----------------------------------------------------------------------------------------------+

I think we’re gonna need a bigger field!

Using Two-way Encryption

Using two-way encryption on passwords is somewhat controversial because it opens the door – at least in theory – for obtaining the original password string from the encrypted version.  Two-way ciphers also require an extra crypt_str argument, so be prepared to take on some additional key management overhead.

One such encryption scheme is DES. It uses the Triple-DES algorithm to encrypt a string with the supplied key. The syntax for the encrypting and corresponding decrypting function is:

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

As the following example demonstrates, two-way ciphers produce binary encoding:

mysql> SELECT DES_ENCRYPT('text','newpassword'); +--------------------------------------+ | DES_ENCRYPT('text','newpassword');   | +--------------------------------------+ | sN"                                  | +--------------------------------------+

Likewise, the AES_ENCRYPT() and AES_DECRYPT() functions were added in MySQL 4.0.2 to enable encryption and decryption of data using the Advanced Encryption Standard (AES) algorithm, also previously known as “Rijndael.” Encoding is effectuated using a 128-bit key length because it is much faster and it is secure enough for most purposes, but you can extend it up to 256 bits by modifying the source.

Here’s an SQL statement to encrypt a password using AES encoding:

mysql> SELECT AES_ENCRYPT('text','newpassword'); +--------------------------------------+ | AES_ENCRYPT('text','newpassword');   | +--------------------------------------+ | ` [1]P] s                              | +--------------------------------------+

Again, binary output is produced.

AES_ENCRYPT() and AES_DECRYPT() are considered to be the most cryptographically secure encryption functions currently available in MySQL.

Note that the SHA-2, DES, and AES functions require MySQL to be configured with SSL support.

Some Additional Caveats

What ever encryption algorithm you choose to go with, be aware that the statements that invoke encryption functions may be recorded by MySQL in server logs or in a history file such as ~/.mysql_history, meaning that the original plaintext passwords may be read by anyone having read access to that information!  Good to know!

Very rarely, a hashing function can produce the same value for two different input values. If you want to be able to detect such collisions, one way to do that would be to make the hash column a primary key so that the database will reject duplicates.

For more information on hashing, be sure to visit the National Institute of Standards and Technology (NIST) Computer Security Division Secure Hashing page.

See all articles by Rob Gravelle

via databasejournal.com

This article covers a few different encryption algorithms supported in MySQL