Monday, June 16, 2008


Password Encryption: MySQL, Apache, and Standards

Recently while playing with user passwords and encryption schemes I realized standards MD5 or SHA are not really standards. At least not for MySQL and Apache. Both claim to implements MD5 and SHA. The only problem is MD5 hash and SHA of same string is different on MySQL and Apache. Both, despite being components of the legendary LAMP stack make no effort of compatibility.

To prove this lets experiment by encoding the string test. Here is what MySQL outputs when using MD5 or SHA1

mysql> select md5("test");
| md5("test")                      |
| 098f6bcd4621d373cade4e832627b4f6 |

mysql> select sha1("test");
| sha1("test")                             |
| a94a8fe5ccb19ba61c4c0873d391e987982fbbd3 |

But here is what Apache expects

[nilesh@krakow ~]$ htpasswd -nbm test test
[nilesh@krakow ~]$ htpasswd -nbs test test

Obviously Apache expects different strings than what MySQL produces. Turns out we need to encode MySQL's output in base64 encoding. Here is the code snippet for encoding in Base64 in Java.

System.out.println(new String(
       new org.apache.commons.codec.binary.Base64()
System.out.println(new String(
       new org.apache.commons.codec.binary.Base64()

This outputs MDk4ZjZiY2Q0NjIxZDM3M2NhZGU0ZTgzMjYyN2I0ZjY= and YTk0YThmZTVjY2IxOWJhNjFjNGMwODczZDM5MWU5ODc5ODJmYmJkMw==. Even this does not match the Apache's required strings qUqP5cyxm6YcTAhz05Hph5gvu9M= or qUqP5cyxm6YcTAhz05Hph5gvu9M=. To conclude, MySQL and Apache are not compatible, at least not when it comes to encrypting passwords.

I don't know why both projects chose not to implement exactly same algorithm. Anyway, after a few hours of debugging, I decided not to use MySQL's SHA implementation so that the encryption is now taken care by Java on application level using the code snippet in apache's documentation.

UPDATE: Apache adds a random salt to protect the passwords against dictionary attacks. But the value of the salt used is stored with the encrypted string (it is the one between $apr1$ and the following $ sign, i.e., EzKMy in the above example), so I am now confused how would that protect against any kind of attack?

Labels: , , , ,

So the MySQL implementation is the correct one.

Apache based on some research I did on the web 'salts' the generated password with some random characters to make it harder to crack.

According to the Apache docs: "The crypt() and MD5 formats permute the representation by prepending a random salt string, to make dictionary attacks against the passwords more difficult."

I'm not sure then how it knows what salt it used when the user enters a password on a website to try and match against the encrypted form, but there must be some trick to that.
@robert thanks for the info, see my update to the post. but since the salt is stored alongside the encrypted password anyway, how would that help make things more secure??
The salt makes dictionary attacks more difficult. Rather that comparing the hash of one dictionary entry against all the password hashes, it must compute the hash of each dictionary salted with each user's salt. So, cracking the passwords of 100 users takes 100 times longer than cracking 1 user's password.
This comment has been removed by a blog administrator.
This comment has been removed by a blog administrator.
code in php to convert MySQL (hex) encoded to htpasswd (base64) encoded SHA1:

function sha1_to_htpasswd ( $user, $sha )
return $user . ':{SHA}' . base64_encode( pack( "H" . strlen( $sha ), $sha ) );
An explanation for why the salt is more secure is the reason why I came to your site: I googled for "qUqP5cyxm6YcTAhz05Hph5gvu9M=" - so it's a hash easy to revert. You just need to calculate one dictionary of hashed values in order to look up such values.

If you add salt, a single dictionary isn't enough as the salt completely changes the hash. And the size of the dictionary required grows with the size of the salt, thus protecting even ridiculously simple passwords from being looked up in a dictionary of hashes.
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?