Ars Informatica
September 28, 2020
Health Care Informatics
Web-based MySQL/PHP Databasing
Web Development
Favourite Software
Hardware for the Frugal Fanatic
Graphic Design and Image Processing
Free Scripts and Software
About Us
Contact Us

 Article Feed for this site

Finding and resolving "new" passwords in a old_password-encoded MySQL database

September 30, 2009

This article is for those folks who, for some reason, had to revert to a MySQL database using the old_passwords option, but who may have had new passwords created using the version 4.1-and-above password protocols.

(For specifics on forcing MySQL to revert to the old-style password algorithms, see our article on "mysqlnd cannot connect to MySQL 4.1+ using old authentication" errors, Part Two: Fixing MySQL Password Authentication on PHP before version 5.3.)

The new password format uses a longer data string in the user tables. Find them using query

SELECT Host, User, Password FROM mysql.user WHERE LENGTH(Password) > 16;

For each user, re-assign a password using the OLD_PASSWORD() function and either SET PASSWORD or UPDATE, i.e.

SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('newpassword');


UPDATE mysql.user SET Password = OLD_PASSWORD('newpassword') WHERE Host =
'some_host'  AND User = 'some_user';

Please note, again, that sticking to the old password protocols is not usually desirable. It might be easier, in that passwords employ one-way encryption, so cannot be reconstructed from the data in the user tables, and therefore can't be converted into the new format.

However, if you need maximum security, use the new password protocols.

Copyright © 2020 Ars Informatica. All Rights Reserved.