[thelist] md5 hashed password problem

Hassan Schroeder hassan.schroeder at gmail.com
Wed Aug 15 14:41:56 CDT 2007


<tip type="MySQL" author="Hassan Schroeder">

MySQL password authentication changed between 4.0 and 4.1.

For compatibility an OLD_PASSWORD function is available on 4.1 and above,
and a variable OLD_PASSWORDS = ["OFF","ON"] defines default behavior.

If for some reason -- DB migration, consolidation of apps -- you have a
mix of old- and new-style passwords and the MySQL instance is defaulted to
the old password style, it's possible to set the OLD_PASSWORDS variable on
a per-connection basis, so as not to affect possible older clients that
need that compatibility, e.g.

  SET OLD_PASSWORDS="OFF";

/* Thanks to Anthony Baratta for pointing out the length difference could
   be used to determine which routine to employ.
 */

mysql> INSERT INTO users SET user='Fred',password=PASSWORD("bananas");
Query OK, 1 row affected (0.12 sec)

mysql> INSERT INTO users SET user='Barney',password=OLD_PASSWORD("bananas");
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM users;
+----+--------+-------------------------------------------+
| id | user   | password                                  |
+----+--------+-------------------------------------------+
|  1 | Fred   | *9E303C97B1C59D393AFCCAEB156C148C1F9E0D67 |
|  2 | Barney | 0b0d276260c19cd1                          |
+----+--------+-------------------------------------------+

mysql> SELECT IF(LENGTH(password)=16,
IF(password=OLD_PASSWORD("bananas"), true, false),
IF(password=PASSWORD("bananas"),true, false)) AS result FROM users;
+--------+
| result |
+--------+
|      1 |
|      1 |
+--------+

mysql> SELECT IF(LENGTH(password)=16,
IF(password=OLD_PASSWORD("bagels"), true, false),
IF(password=PASSWORD("bagels"),true, false)) AS result FROM users;
+--------+
| result |
+--------+
|      0 |
|      0 |
+--------+

More info on MySQL encryption:
<http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html>

</tip>

-- 
Hassan Schroeder ------------------------ hassan.schroeder at gmail.com



More information about the thelist mailing list