login/password

[ihopesew]

I have a MySQL database on Powweb mysql02.powweb.com . As of 1/13 it appears to me that the encryped passwords stored in one of the tables are no longer being recognized by my php webpages that require login/password - pulled from the database. I have over 300 registered login/password combinations for people who use my website. I did not make any changes to my website scripts. I tried using my website to reset the password for one of the logins and that works just fine. This happened at about that same time as an upgrade to phpadmin took place. Could that upgrade have done something to the encryped passwords? Can it be undone... what can I do to fix all the passwords. I have about 300 users in this database who each registered thier own password. It will be difficult for me to ask them all to reset their passwords! Thanks for any assistance.

[BerksWebGuy]

Did you try to repair the database?

Can you see the password entries in the table?

[ihopesew]

no, i have not tried any "repair". I can see only encrypted passwords. the new one I created is very long compared to the others. I have just been reading another thread that is saying something about in the new upgraded mysql, that passwords are handled differently and that field has to be longer or something.
http://dev.mysql.com/doc/mysql/en/Upgrading-from-4.0.html

[BerksWebGuy]

From James:
The mysql upgrade consisted of replacing the mysqld binary, its even a static binary. The mysql data was not changed. If the upgrade had to do anything with your tables the upgrades would have taken days not minutes.

If your data has changed it was do to something that was accessing it, the upgrade itself did not do this.

Try repairing the database...and that solved a few others problems.

[ihopesew]

I just tried that. Admitedly I don't know what I am doing - but I went to one of the tables that has the encrypted passwords in it (does it matter which table I do this in?) and clicked on Optimize, then selected Repair under that screen. Didn't seem to make any difference. I still cannot login with the existing passwords.

[ihopesew]

opps - I meant to say operations, Repair. Anyhow - it didn't help. I read something about new passwords now have to begin with * .
I found info in the upgrade help:
Upgrade to 4.1 and run the mysql_fix_privilege_tables script to widen the Password column in the user table so that it can hold long password hashes. But run the server with the --old-passwords option to provide backward compatibility that allows pre-4.1 clients to continue to connect to their short-hash accounts. Eventually, when all your clients are upgraded to 4.1, you can stop using the --old-passwords server option. You can also change the passwords for your MySQL accounts to use the new more secure format.

My password column is the long length now - but most of my password are the short length. What can I do to make the old passwords compatible to the new upgrade? is there anything I can do - short of having all my users to update their own passwords?

[clubcosmic]

This is a good thread. I too am having the same problem and after much trial and error, i've come to the conclusion that it is a problem with the db and the way it inteprets passwords. I am trying to figure it out but i believe the solution could lay in the hands of a db admin and not a user.

[ihopesew]

i am the admin

[riskynil]

My password column is the long length now - but most of my password are the short length. What can I do to make the old passwords compatible to the new upgrade? is there anything I can do - short of having all my users to update their own passwords?

I had this problem too. The MySQL function PASSWORD() works differently, but this new version has the function OLD_PASSWORD() which works like the old PASSWORD() function. Use that that it'll compare the short passwords to the old way the encryption was working.

Additionally, to support it either way, create your login query to check pwd=PASSWORD('secretPassword') OR pwd=OLD_PASSWORD('secretPassword')

That way, it'll work regardless of which form is stored in the database.

Additionally, it might be a good idea to start "upgrading" their encrypted passwords to the new version since it's supposed to be more secure and there's no guarantee future versions will continue to support OLD_PASSWORD(). After confirming they are who they say they are, you can update their encrypted password to use the new function.

ALSO, I found a note somewhere that said you shouldn't be PASSWORD() for your own applications--it's meant to be used for grant tables and such, and instead they recommend using the MD5() or SHA1() functions--which are also one way encryption algorithms that are NOT supposed to change from update to update. (SHA1 is supposed to be "more secure" than MD5)

So if you already have long and short encryptions in your database, you'd end up with code like:

pwd=PASSWORD('secretPassword') OR pwd=OLD_PASSWORD('secretPassword') OR pwd=SHA1('secretPassword')

Then update the entry in the database to make sure you're storing the SHA1('secretPassword') version. Otherwise, you'll have this problem again as soon as they update the PASSWORD() function yet again. (Apparently, in future versions, it's supposed to generate even longer hashes, so it is changing!)

Happy trails! =)

-- Ryan

[clubcosmic]

Thanks for the tips Ryan. I'll try to work them out. :)

[ihopesew]

me too, Thanks

[ihopesew]

FYI - the "fix" posted above by "riskynil" WORKED for us. Thanks so much for the information!

[riskynil]

FYI - the "fix" posted above by "riskynil" WORKED for us. Thanks so much for the information!

Glad I could help. Took me most of the day to figure it out all and get it working for my site, so as soon as I saw this thread I knew EXACTLY what the problem was. =)

-- Ryan, who now knows more about passwords than I thought I ever needed to know....