password() problem [RESOLVED]

[tripwater]

Hello, we just moved our websites over to another datacenter and in the process noticing that we are having troubles logging into some of our sites.

Just as a quick test I manually updated a test user's password to password=password('hello') but the funny thing is when I try to login with hello, no record found. I echo the query and paste it into mysql and it shows empty results. SO I then queried for it to show me the record for this user and it shows the encrypted password to start with an * and then all characters are CAPS. No matter what I update the password to, this is the case. All alpha chars are caps and the pass starts off with an *.

Has anyone run into this before? All of this worked before and our data looks ok in our tables but if you update the pass, it screws it up bad.

Thank you for any help with this.

[tripwater]

Using php 4.4.4
mysql 4.1.21

[tripwater]

Found out that mysql changed it's password mech in 4.1. So instead of doing the intelligent thing and creating a new_password() they made it so you had go back and change all of your password() to old_password() in order for your old databases to work properly. Got to love it

Thanks

[guelphdad]

the issue isn't mysql so much as it is php. it is php that doesn't recognize the different password hashing algorithm.

[tripwater]

Sorry but at the time I had this issue, I was in a shell dealing with mysql only. Using no php. So I have to disagree. I had passwords that were stored in the DB tables from a previous version of mysql when it was on the old server and I could not retrieve any of the records in Mysql with a standard select query in the shell until I called old_password() instead of password(). These are mysql functions used in a shell prompt on the server.

Thanks for the response though

[guelphdad]

then your version of mysql was not upgraded properly. From the manual:

The password hashing mechanism was updated in MySQL 4.1 to provide better security and to reduce the risk of passwords being intercepted. However, this new mechanism is understood only by MySQL 4.1 (and newer) servers and clients, which can result in some compatibility problems. A 4.1 or newer client can connect to a pre-4.1 server, because the client understands both the old and new password hashing mechanisms. However, a pre-4.1 client that attempts to connect to a 4.1 or newer server may run into difficulties. For example, a 3.23 mysql client that attempts to connect to a 5.1 server may fail with the following error message:

shell> mysql -h localhost -u root
Client does not support authentication protocol requested
by server; consider upgrading MySQL client

if you were using a 4.1 or newer client you could be able to connect to an older server.


Here is the relevant area in the manual.

This too from the manual on upgrading from 4.0 to 4.1:

After upgrading, update the grant tables to obtain the new longer Password column that is needed for more secure handling of passwords. The procedure uses mysql_fix_privilege_tables and is described in Section 5.5.1, “mysql_fix_privilege_tables — Upgrade MySQL System Tables”. If you do not do this, MySQL does not use the new more secure protocol to authenticate. Implications of the password-handling change for applications are given later in this section.

Which is here in the manual.

I'm not saying you should have been aware of both issues, but if your upgrade had updated the grant table you wouldn't have run into this issue.

But again, you are correct, it isn't specifically a php error.

[GJay]

are you not talking about mysql-users, while the OP is talking about users of an application running off the database?

[guelphdad]

GJay, you are right, I'm specifically talking of switching between versions of mysql. Adding users to the mysql database for permissions to interface with mysql server is the ONLY place you should use the mysql password hash. in all other circumstances you should NOT use it for that very reason.

I was guessing they were talking about the back end for an upgrade.

Obviously if tripwater is using the mysql password hash for regular tables you should look into using MD5 or SHA1 passwords and use them instead. otherwise the next time mysql changes their hashing algorithm you will run into the same problem. note too that SHA1 and MD5 passwords are transferable across database applications (should you move to mssql for instance) and mysql password hash is applicable to mysql only. They are also more secure and more difficult to hack than the mysql password hash.

[tripwater]

Yes I am using this for a table field. To store someone's password, not the mysql login.

SO if I was to use the MD5 encryption how do I check when decrypting? For example if I was to insert a user with

insert into new_accounts values ('login', MD5('password'));

then want to check against what they type for a login, would it be

select * from new_accounts where Login='login' and Password=MD5('password')

Is this the method? If not, could you please explain how to accomplish this? And I will start using this instead. Also how large does the field in the table need to be to hold a password that is encrypted with md5? Right now I use a char(16) binary.

THank you again for your time.

[GJay]

that's exactly how to do it, and I believe md5 requires a 32-length field