Convert stored passwords to MD5 Hashed versions

I have a web project that I am rewriting for a customer, their old version was written using Java and we are rewriting it to use PHP and add some new features. The reasons for that aren’t important, what I have encountered however, is that the passwords for all the users are stored in their plain text format in the MySQL database.

I’ve written the PHP code to store new user passwords and retrieve them by adding a pre and post random string, and then hashing them using MD5. What I need to do now is update the existing passwords and store their new versions in the database using this formula.

Is there a MySQL command I could hand write (it only needs to run this one time) that would iterate through the users in the table, retrieve their current passwords, and then store them using my MD5 formula? I’ve considered making a new temporary field, duplicating their passwords into that field, and then running the conversion, so if there is a problem I will still have their old password to match against (in case I should get the format wrong).

I realize I could reset all their passwords and have them log in when we launch the new site and reset their passwords, but the client would prefer that the users don’t notice much of a change as we transition to the new system.

Sorry for the long winded question, hope someone might have a quick solution to this issue.


Why not just do it in PHP as part of your upgrade process?

I think you’ll find sha1 is a better hashing algorithm than md5 but I’m not sure why. if you google it I’m sure the answer is on the www somewhere.

but in any case, something like this should work

UPDATE tblUsers
SET fldPassword = SHA1(fldPassword);

and then in your user authentication php script compare the hashed user entered password with the value in tblUsers.

I considered doing it in the upgrade, but there isn’t much else that will have to be “upgraded” on the database side of things (at least not that could be replaced with a script, the other items will require manual updates as we will be breaking out details that were put into text boxes into separate item fields (i.e. products where we spent $10 and it was 20 items at $.50 each are currently listed as a dollar total in the amount field but in the description it states (Widgets - 20x.5) but that wasn’t always done exactly the same so we will have an operator going item by item to break it down properly.

I was hoping I might be able to do something like you mentioned Kalon, but I’m concerned about adding in my salt string. So

UPDATE tblUsers SET fldPassword = SHA1(fldPassword);

might actually need to be something like this (but I know this isn’t formatted properly)

UPDATE tblUsers SET fldPassword = SHA1(r4s5g2da6gsclufldPassword);
Where - r4s5g2da6gsclu - is my salt value that will be added to every stored password. I supposed I could do it in 2 steps, step 1 I would prepend all stored passwords with my salt, and step 2 I would run a command like you suggest.

Thanks for the ideas, as far as using MD5 versus SHA1 it isn’t a high security requirement so MD5 is adequate, but after your comment I was reviewing some different articles comparing the 2 and it seems performance is greatly improved with SHA1 along with the increased bit values, so I probably will switch and use SHA1 instead.


UPDATE tblUsers SET fldPassword = SHA1(CONCAT(“r4s5g2da6gsclu”,‘fldPassword’));

Thanks everyone, I managed to get them updated. It wasn’t as automated as I had originally planned, but I ended up starting work on the “User Settings Form” and just tweaked that code so it would pull the password that I could see and update it with my salt. I’m kind of glad I did it that way since I had the password field on the user/login forms locked at 16 characters and I had forgotten the database was also set to a varchar16 so when I started to hash them I was unable to successfully login because the database was chopping off over half the rest of the password. :slight_smile:

Anyway, I finally got it all worked out, changed the database field limit to 40 and it all worked like a charm. As always, the Sitepoint forums come to the rescue!