[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