One and one MySQL 5 update and password problems

[0tter]

Hi there I wondered if there was anyone out there who could help me with a problem.

 

I designed a PHP site, plain code and all, a number of years ago and it's worked great.

 

Recently though I've been informed from my hosting company 1&1 that it would need to be updated from MySQL4 to 5 and I've spent the last fortnight trying to get things to work.

 

The export and import into MySQL5 have all gone fine but on trying to login to the site the password would not autthenticate. Changing the passwd column from 16 to 41 does not work and the site only works if one requests a new password and the login then works fine - with the 41 character passwd column that is.

 

Now this wouldn't pose a problem but there are now 20,000+ users and getting them all to request a new password would be tricky to say the least.

 

I've tried everything - well everything apart from the correct thing - and I'm running out of patience. 1&1 are not replying to my mails and it's all going a bit wonky, I'm working evenings on this after a full day at work and this has now lasted a fortnight and I would love to see my daughter again.

 

Any suggestions would be really welcome and I must apologise if I've posted this in the wrong forum but I'm steadily going a bit insane :-)

 

If I could get them to set old_password to true on the server I think this would work but I get the feeling they can't do this on a none server hosted version.

 

Please help.....

[Jessica]

What? (In regards to your last paragraph)

[mac_gyver]

lol, you should have never used the password() function for your user's passwords.

 

there is now an old_password() mysql function that you can use and it should return values that match what you have stored in the database table.

 

---

 

i would also recommend the following -

 

1) add two new columns to your database table to hold a new hash value and a unique random salt per user.

 

2) when anyone successfully logs in, take the entered password, apply a new salted-hash algorithm of your choice to the password and store the new hash value and random salt string in the database table.

 

3) if any one tries to log in and the new columns already hold values for them, use the new columns to log in the user.

 

4a) at some point your active users will be switched over to use the new hash/salt columns. email all the users who haven't logged in yet to do so with a deadline date or they will need to specifically request a password reset to log in after that date.

 

4b) on the date you have determined remove the old password column and any code that is using the password() or old_password() function.

 

make backups of your database and code before making any functional changes and test the changes off-line before putting them onto a iive server.

[0tter]

What? (In regards to your last paragraph)

Apologies for the last paragraph I was just trying to say that I believe that there's a mysql.ini file (or something similar) where one can put in a setting that'll force it to take the old 16 character passwords. As I don't control the server this is a file I don't have access to and hence I'm a bit stuck. I think this would work and again I've not done my homework with the filename or the exact comment but I do believe that there lies a solution.

[0tter]

lol, you should have never used the password() function for your user's passwords.

 

there is now an old_password() mysql function that you can use and it should return values that match what you have stored in the database table.

 

---

 

i would also recommend the following -

 

1) add two new columns to your database table to hold a new hash value and a unique random salt per user.

 

2) when anyone successfully logs in, take the entered password, apply a new salted-hash algorithm of your choice to the password and store the new hash value and random salt string in the database table.

 

3) if any one tries to log in and the new columns already hold values for them, use the new columns to log in the user.

 

4a) at some point your active users will be switched over to use the new hash/salt columns. email all the users who haven't logged in yet to do so with a deadline date or they will need to specifically request a password reset to log in after that date.

 

4b) on the date you have determined remove the old password column and any code that is using the password() or old_password() function.

 

make backups of your database and code before making any functional changes and test the changes off-line before putting them onto a iive server.

 

A good idea and it sounds a bit out of my depth (I wrote this site with years ago with a PHP manual on my lap all the way through and I really have forgotten everything that I did.... and it took me months of late nights), but the problem lies with the old database been switched off in a week or so. Also it's a very rarely visited site but well subscribed site and I would probably have only a few logons in that period, so it would have to email thousands of users to tell them to login and change their passwords and I would imagine that I'd then lose a heap of users.

 

Referring to a scenario based webpage http://dev.mysql.com/doc/refman/4.1/en/password-hashing.html I would have thought that it should just work if I left the column as it was, so I'm a bit lost for why it's not working when I just port it across to the new database.

 

As I mentioned though, increasing the column to 41 and requesting a new password works fine with the new password, it fills out the new column with the full hashed string and hey presto they can login. If I keep the column at 16 characters a new passord request doesn't work then and the hashed (am I using that correctly) value is strange in that it is now all upper case, starts with an * and doesn't work.

 

Isn't there something that will take an old pre-4.1 database and update it?

 

Could it be that I'm just using a really old version of PHP (version 4)?

 

Someone must have come across this before?

 

Am I ever going to leave this computer and grab some rays - it is sunny outside isn't it :-)

 

Thanks again for the really well written solution, I'm just being dim and looking for a quick and dirty solution.

 

Cheers...

[0tter]

lol, you should have never used the password() function for your user's passwords.

 

there is now an old_password() mysql function that you can use and it should return values that match what you have stored in the database table.

 

---

 

i would also recommend the following -

 

1) add two new columns to your database table to hold a new hash value and a unique random salt per user.

 

2) when anyone successfully logs in, take the entered password, apply a new salted-hash algorithm of your choice to the password and store the new hash value and random salt string in the database table.

 

3) if any one tries to log in and the new columns already hold values for them, use the new columns to log in the user.

 

4a) at some point your active users will be switched over to use the new hash/salt columns. email all the users who haven't logged in yet to do so with a deadline date or they will need to specifically request a password reset to log in after that date.

 

4b) on the date you have determined remove the old password column and any code that is using the password() or old_password() function.

 

make backups of your database and code before making any functional changes and test the changes off-line before putting them onto a iive server.

Hang on I didn't see the first bit....

 

If I were to replace every instance of password(xxx) with old_password(xxx) then this might be goer???

 

I think I would have to be carefull about the ones I replaced but basically should this work.

 

This sounds like a very quick and dirty solution should it be a goer.

 

Hmmmm, I think I need to get to bed and give this a go in the morning.

 

Cheers again

[0tter]

lol, you should have never used the password() function for your user's passwords.

 

there is now an old_password() mysql function that you can use and it should return values that match what you have stored in the database table.

 

Brilliant, brilliant, brilliant.....

 

Thanks Mac_Gyver that's fettled it..... I can now go back to a healthy existence and start seeing my daughter again.

 

Waheeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee

 

Bill