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
test:$apr1$EzKMy...$yHUugfRt.9lc.Jc4Z4KzJ/
[nilesh@krakow ~]$ htpasswd -nbs test test
test:{SHA}qUqP5cyxm6YcTAhz05Hph5gvu9M=
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()
.encode("098f6bcd4621d373cade4e832627b4f6"
.getBytes())));
System.out.println(new String(
new org.apache.commons.codec.binary.Base64()
.encode("a94a8fe5ccb19ba61c4c0873d391e987982fbbd3"
.getBytes())));
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: apache, encryption, mysql, password, security
