Monday, August 21, 2006

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

44 comments:

Anonymous said...

Frank,

I just should mention for people looking for extra efficiency you can user char(16) binary for md5 storage (and similar for sha1) - use unhex function to convert it to binary and hex to convert it back to text.

Anonymous said...

md5, nice and easy, works in mysql and php (i know sha1 also works for both, but i started with md5, and will stick with it for now)
i have used aes, but that was not for passwords, but customer identification id's that were required to not be stored in plain text

Anonymous said...

Your blog is surely informative. But storing passwords is my major problem all the time. Maybe I have a short memory retention thing that I am incapable of storing passwords. Good thing what MySQL can do.

Frank said...

Peter -- Thanks for a great tip. I appreciate it.

Sheeri -- htpasswd can use a either MD5 (actually a specially modified version of MD5 for Apache) or the system's crypt function depending on how it's configured. A single htpasswd file can contain both types of passwords, i.e. some encrypted with MD5 and others encrypted with crypt().

Unfortunately since htpasswd uses MD5 passwords, the exploits for it are known especially dictionary attacks in which a malicious hacker keeps a dictionary of passwords that are encrypted with MD5 and crypt.

So far AES seems to be the best solution cyrptographically for protecting passwords from prying eyes.

Do you store credit card numbers along with the addresses? Hopefully they are PGP encrypted.

Frank

Frank said...

Sheeri-- I would like to also add that you can use the following htpasswd options to specify the algorithm used for encrypting passwords:

-m : MD5
-d : crypt (default on all but Windows)
-s : SHA
-p : Plain text (obviously never a good idea)

Frank

Anonymous said...

Very interesting article.

But I would still prefer md5, or sha1 - simply because they are one-way-encryptions. I will try to explain, why that fact is essential for me - and should be for you, too.

Actually storing a password encrypted with AES_ENCRYPT is just one "secret" word away from storing it in plain text, isn't it?
SELECT username, pass FROM user;
SELECT username, AES_DECRYPT(pass, 'secret') as plainpass FROM user;

It may be possible to hack single md5-password-hashes. But when storing passwords with AES_ENCRYPT and one secret key - it should be just as easy to hack *all* of the passwords stored in the database.

Just imagine you have a little site with a newsletter-tool. Users register with their email and password. You write a little admintool that lists all your users and their passwords, because you want to be able to respond to "forgot my password" by phone and send the password via email by request. Your secret cryptkey is stored somewhere in your application. Of course you yourself would never exploit your users, so you don't care that they are lazy... and that they use the same password for your site, eBay, PayPal and quite a few others.

Now imagine an evil intruder who somehow (maybe because your own password isn't strong enough) gets a hold of your admintool and sees the list... Or some technical assistent gets access to your database dump and the part of your code that contains your secret word. He will instantly have access to all email adresses and the corresponding passwords in plain text. I don't think I have to explain what happens next...

In my opinion it is best practice to never ever store a password in a form where you can reproduce the plain text version. And to my knowledge no decent system would even consider to do something like that. The MySQL PASSWORD() function used for MySQL database users is strictly one-way, so are passwords stored on UNIX systems. Checking a password is checking if the encodeded string of the entered password matches the stored encoded string. A forgotten password should mean resetting the password. Never should you be able to reproduce the original password.

I personally would never willingly create an account on a site where my password is stored in either plain text or reproduceable (i.e. decryptable) form.

Anonymous said...

THat last comment is right on the mark. The use of the AES two way cipher makes no sense for passwords because there is an implicit key management problem.

A good question, i think, is: in what cases is it useful ?

what if you have data that is sensitive that you do not want a casual user to see in the database but is regularly edited by certain users eg. How do you protect address information for your users in the database ? is that a valid case for using the two way cipher ?

.. and where do you store the damned key ?

Deus Eks

Anonymous said...

I think AES is useful after all.

After reading the article and the comments I had this idea: why not combine all of them:

Why don't we store the password like this AES_ENCRYPT (MD5 ('mypass'), SHA1 ('mypass'))
or in reverse order.

This way there's nothing a hacker can do for each password has a unique key and, because it's so over encrypted, it would be a hell of a job. This method also eliminates the AES_DECRYPT weakness.
Practically, the only way someone could obtain the pass in plain text from the database is to...know it from the start :)

What do you think of this?
If you like the idea e-mail me at matei_tene@yahoo.com

Matei Tene, 18, Romania

Anonymous said...

Really nice tutorial. Works great!

//Denmark//

Anonymous said...

SHA or SHA1 very easy for password encryption in mysql. Very nice info is provided in an easiest way.

Thank u....

Bret Hall said...

Matei,

Even sending the plaintext password past the client application is frowned on. At the very least, the only thing that should be sent from the client to the server (while not using SSL) is a hash of the password, otherwise, a man-in-the-middle attack can sniff the password as it passes from client to server.

Anonymous said...

Many thanks for this.
Helped me no end.

Cheers

Anonymous said...

Following up what Matei suggested regarding AES; what if you used some other field as the key, one that was set at the same time as the password (say a timestamp or a random string). That way you could call the value of that second field as part of any SELECT and not need to pass anything back across the network at all.

Anonymous said...

Great tutorial,
Thanks.

Anonymous said...

Great tutorial.

Personally, I use a mix of md5 and sha1. This prevents dictionary attacks, even when your users use the most basic passwords. The cool thing about mixing them is you can "make" your own algorithm. Use the two in any combination of your choosing.

md5(sha1(md5(md5(sha1($password)))));

Of course such extensive combinations aren't really necessary, but the more careful you want to be, the more the it makes sense.

That being said, no matter what encryption techniques you use, nothing is better for security than strong passwords.

Unknown said...

I want to display the orginal password that user type in the password box for admin purpose in local site and i use to store password in database by password keyword tell me how it will display the original passwordon page in select statement...
b/rgrds

Unknown said...

I'd like to point out that chaining algorithms like md5(sha1(md5(sha1(sha1(md5($password))))));
isn't actually as effective as you might think. This will prevent against rainbow table attacks (having a precomputed list of hashes) but will not prevent against a dictionary cracking attack with a custom cracker. (It's trivial to write)

Anonymous said...

to dan: it requires you to know how many times the user has encrypted the password and in what way, so thats one step more to figure out.

and to matei, but then again you would loose the possibility to be able to go back with AES, so you might just aswell do that without encrypting with md5 and sha1

AES_ENCRYPT ('mypass', 'mypass')

would be enough... right? but sure, it makes you feel a bit more secure. yours is the one i will use :)

Anonymous said...

Regarding the comment: "AES_ENCRYPT (MD5 ('mypass'), SHA1 ('mypass'))", or for that matter, just "AES_ENCRYPT ('mypass', 'mypass')", this sounds good to me, but I know so little about encryption to begin with. Does anyone who is a hardcore encryption junkie see anything inherently wrong with this?

-Bill, ATX

Unknown said...

When storing passwords using AES, you should actually use a known phrase as the plaintext value, and the user's password as the key. The AES function is resistant to known plaintext attack, and so even if everyone knows the plaintext and the encrypted output, you'll have to break AES before you can recover the key.

Since the key in this case is the user's password, it will be protected by AES. For the plaintext, I'd recommend something unique to the user (such as their username) so that it'll be harder to tell if two users have the same password.

This also solves the problem of storing the output of AES() since the length of the output is dependent on the length of the text, which you now control.

Anonymous said...

@Adam

You might as well use MD5 or SHA1 then cause you won't be able to get the password back or decrypt it anyway.

Unless there's some reverse decryption of the secret word you can use?

Anonymous said...

Hi,

I was wondering, can I change a vBulletin/Wordpress PHP software to use AES encryption rather than others?

regards,

Anonymous said...

What the hell is the point in reversible encryption for passwords, thats just stupid. Keys to the castle ?!?

Why not just use SHA512

Not reversible, no known exploits.

Anonymous said...

Wow, this was very usefull indeed. Everything is clear, there were no parts I didn't get, thanks for the info, I most definately am going to use this!!

al said...

HELP!!!

i am trying to use aes_encrypt but can't! do i need a particular config setting? eg. ssl etc. if so where and how do i set it?

i just want to be able to start using some sort of encryption.

thanks.

Anonymous said...

Thank you! Your examples are very clean and helpful. Best blog about Mysql I've seen yet

Najm Abideen said...

Great info, i was finding a way to increase the security and here it is, all discussed already. :)

I will go with @Matei, using sha1 with md5 combination, and additionaly using the different field to make it reversable.

Reversing encryption can come in handy when you will be encrypting the client details.

Rafael Beckel said...

What about...

AES_ENCRYPT( 'password' , md5(current_timestamp()) ) ?

Rafael Beckel said...

I recently needed to use both functions to encrypt and decrypt passwords.

We have a client who uses a Webservice to register costumers on their PostgreSQL database. That webservice requires that I send a plain text password to it.

However, I needed to store this password for 3 days in our local MySQL database while the costumer didn't click in the confirmation link sent to his e-mail.

I did this way: in the moment the client send the form with his password, I generate a MD5 hash of the current timestamp multiplied for a random value between -5 and 5. Then I save his password on our MySQL database using AES_ENCRYPT("password",random hash) and I send him the confirmation link with this random hash as a parameter.

When he clicks on this link, my code picks that hash and uses AES_DECRYPT(password_field,random hash) to recover the plain text format of his password and send it to the webservice provided by our client.

Then, I delete the register of that costumer from our database.

Anonymous said...

If using SHA1 for storing the password, the output of SHA1 hash is always exactly 40 characters in length (regardless of the plaintext password length). So instead of:
password VARCHAR(40)
use:
password CHAR(40)
Thus saving the 1-byte overhead used to store VARCHAR (see MySql docs). Same tip applies to MD5 (but with 32 instead of 40, of course).

Anonymous said...

@Rafael - While at first glance your suggestion appears to produce a one-way AES encryption solution, if you use MD5(current timestamp) for your key, it is still possible to break. Think of record creation timestamps within log files, for example. If you have access to a log entry that tells you when the record was created, you have the key, and therefore can use AES_DECRYPT to reverse the encrypted field.

I like Matei's idea for AES encryption of passwords - but wouldn't use it for fields that are accessed too frequently as there may be a performance hit with all the encryption and decryption for comparison/verification purposes. For passwords, it's generally a one-time hit with a session flag set to indicate that the user is authenticated when accessing subsequent pages of the site that are secured. Just my two-cents'. :-)

Anonymous said...

Sounds to me that we can use AES_ENCRYPT('mytext', 'mypassword') to make an one way password encryption. Is this right?

sjmach said...

A good post, solved a lot of doubts. Thank You.

nitro2k01 said...

I know this post is three years old, but there are a couple of things that ought to be pointed out, especially for the noobs who are looking for advice.

AES is not a suitable way of encrypting passwords, since it's two way. The only reason we encrypt password in the DB in the first place is to make sure the passwords are safe, if someone gets unauthorized access to it. And since you need the secret key to encrypt a password, we can assume that the hacker has access to it, if he got into the DB. And at that point we could just have stored the passwords in plaintext to begin with.

MD5 and SHA1 are vulnerable to dictionary to reverse lookup attacks, ie you have a list of common words and their hashes which enables you to reverse lookup hashes easily. The way to solve that problem is to salt the hash, something you don't mention at all in the post. Salting means you add a random string to the password string, which makes it less likely that the word exists in a reverse lookup database. So if you're password is mike, and your salt is csidubcsiudbcsdiucb you store sha1('mikecsidubcsiudbcsdiucb') this way you can easily check the password by comparing sha1(password || 'csidubcsiudbcsdiucb') with the stored hash, but you can't recover the password easily. That's how you keep passwords safe.

TheChad said...

I once read a thesis explaining that nested encryption actually reduces the strength of the encryption and makes it easier to hack. It suggested that a secret 'salt' (http://en.wikipedia.org/wiki/Salt_(cryptography)) be used instead. Wish I could remember where I read that. But I'd seriously research it before wasting CPU cycles only to make it weaker.

Anonymous said...

@Rafael: Then there is no way to decrypt the password, and also no way to check this password when the users gives it for authentication, which is the whole point of passwords, isn't it? :)

@Moe: it is very well possible that AES can be cracked if the cracker knows the password and the key are equal. With the key and the password equal it is no longer AES encryption.

However, I'm not a specialist in encryption, so I don't know the details of AES. However, I did some course on encryption so I have some general idea. At least I know that encryption is tricky stuff: specialist study for years on some encryption algorithm to make sure it is secure if used in a certain way. So such an encryption algorithm is implemented with recommendations how to use it. As soon as you start using it differently (like in AES_ENCRYPT (MD5 ('mypass'), SHA1 ('mypass')) ) these security guarantees are no longer valid, and it will probably be very hard to prove it is secure.

In this specific case, there is some relation between MD5 ('mypass') and SHA1 ('mypass') so the AES is no longer AES. As soon as your cracker knows you store passwords like this, he/she may find an exploit.

The bottomline is: I think you should encryption algorithms the way they were meant to be used. If AES_ENCRYPT (MD5 ('mypass'), SHA1 ('mypass')) ) was so secure, some encryption genius would probably have realized that already :). There's no point in constructing your own encryption layers on top of existing, it's only a risk of creating a security leak.

Frank said...

nice md5 description. helped a lot. thx

Kartik said...

just reading up on this by chance, and had a few notes to all your suggestions, especially the ones who have commented on using AES with either MD5/SHA with current timestamp. Could they pray explain, how they will authenticate after the password has been stored, since the current timestamp has changed. Storing the timestamp would be downright foolish. I really wish MySQL would come with a 512 SHA, instead of forcing us to recompile as then we would be forced to redistribute MySQL to clients, and users will lose out on upgrades.

jfranco said...

@Rafael Beckel
That won't work. Your timestamp is gonna be different everytime, which makes your key_str different everytime, so you cannot possibly check against the original AES encrypted password you stored, 'cos for that, you need to have that original timestamp that you encrypted the password with.

Rafael Beckel said...

I works and I have it in production.

I don't store the original timestamp itself, but its MD5 Hash. ;)

Rob said...

@Rafael Beckel

which means that the key to decrypting all the passwords are also stored in your database. Not secure.

Rob said...

I think a good way to use AES is to use both username and password as key by concatenating both.

Like this:

AES_ENCRYPT(password, CONCAT(username, password))

Anonymous said...

when a user is creating a new account, and you try to use the browser's javascript to encrypt their typed password before sending it to your script, how can you use regular expressions on the server side to ensure their submitted password is a minimum length, contains at least 1 uppercase alphacharacter, and contains at least 1 special character (that you provide a list of valid special chars @#$) in your script?

if it's md5 hashed at the browser before being submitted, their is no way to unhash it at the server to check for these conditions using a regex, is there?

i know you could us an SSL connection during the signup phase, but i'm wondering about the first question. thanks

Chandra said...

Rafael: any system where an insider can reverse engineer the password is unacceptable. In your proposal, an insider can take a stored md5 and can get the cleartext password, could he/she not?

I think a better idea would be actually encrypt the md5 of timestamp with the password and store that.

create table secure_passwords (password blob,salt char(40));

// '4bf371a084fc186e6b7913dcffc8e975' is the md5(current_timestamp())

insert into secure_passwords values (AES_ENCRYPT('4bf371a084fc186e6b7913dcffc8e975','mySecurePassword'),'4bf371a084fc186e6b7913dcffc8e975');

// When user logs in, try to decrypt using what they provided. If it is correct it will succeed.
select true from secure_passwords where AES_DECRYPT(password,'mySecurePassword') = salt;

// This will fail
select true from secure_passwords where AES_DECRYPT(password,'wrongPassword') = salt;