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
Monday, July 30, 2007
MySQL Lock Contention
Lock contention can be a serious performance issue with MySQL if both read and write happen concurrently on the same table. BlogScope has tables with around 100 million rows, stored in the MyISAM storage engine. Usual workload consists of 2-3 updates (mainly INSERT) per second and several SELECT operations. Since MyISAM provides only table-level locking, every operation locks the complete table. Although SELECT operations can take place in parallel, it turns out that a long running SELECT can block the complete application. After investigating a bit in why a SELECT was blocking other SELECT operations, I found an explanation in MySQL docs.
A client issues a SELECT that takes a long time to run. Another client then issues an UPDATE on the same table. This client waits until the SELECT is finished.Another client issues another SELECT statement on the same table. Because UPDATE has higher priority than SELECT, this SELECT waits for the UPDATE to finish, and for the first SELECT to finish.
This basically means that if there is any long running SELECT query (e.g., sequential scan), the whole application will just PAUSE for hours. This also means that you can not take backups (using mysqldump), as not only the process willing to write, but even those just reading will be blocked. Fortunately, a fix is available: set the priority of SELECT higher than UPDATE. In this case, the second SELECT statement in the preceding scenario would execute before the UPDATE statement, and would not need to wait for the first SELECT to finish. To set low priority for UPDATES, add the following to /etc/my.cnf
low_priority_updates=1
and execute the following
set global LOW_PRIORITY_UPDATES=1;
While setting updates to be of low priority fixes the locking issue, I don't really like it. In this case, the UPDATE may never actually get a chance to execute if the load is too high. In my opinion, the best solution is to have a replica server. A master MySQL server where all the updates take place, and a slave for all the reads. Once we install our new hardware, I will setup a replica server for BlogScope as well.
Labels: mysql, performance
