Migrating from cleartext to encrypted passwords
I have a MySQL table that contains user data including passwords in cleartext, about 10,000 records, and my client has decided to move to encrypted passwords.
As I see it the steps to complete in this order are as follows:
1. Create new column in table to store encrypted passwords.
2. Create and run a script to read cleartext passwords, encrypt them, and insert values into new column.
3. Modify login and profile creation scripts to support encryption and use new column for values.
4. Delete original cleartext passwords.
5. Mandate password changes to users logging in.
Step two is what I most would like some advice on, but any thoughts are helpful. I foresee reading each value, encrypting it, and inserting the new value into the table with a loop.
Does anyone see any pitfalls or have any advice for this process? All comments are welcome.
Thanks!
Encrypting already existing database help
Moderator: General Moderators
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: Encrypting already existing database help
You shouldn't store encrypted passwords. What you should do is to store hashed passwords along with a salt and pepper combination. Use a hash algorithm like sha384 or sha512 at least. The security section of the forum has multiple posts on this issue.
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Re: Encrypting already existing database help
Yes, that's what I intended. Sorry for the lack of clarity in the original post.
- AbraCadaver
- DevNet Master
- Posts: 2572
- Joined: Mon Feb 24, 2003 10:12 am
- Location: The Republic of Texas
- Contact:
Re: Encrypting already existing database help
At the simplest you could do something like this in your login code (temporarily):
[text]SELECT * FROM `users` WHERE `username` = $username AND ( `password` = $password OR `password` = SHA1($password) )[/text]
Then run this once on your DB from the command line, PHP, phpmyadmin or whatever:
[text]UPDATE `users` SET `password` = SHA1('password')[/text]
Then you can change the login code back to:
[text]SELECT * FROM `users` WHERE `username` = $username AND `password` = SHA1($password)[/text]
There are more secure hashes than SHA, but it's better than MD5. Just a thought to keep it simple.
[text]SELECT * FROM `users` WHERE `username` = $username AND ( `password` = $password OR `password` = SHA1($password) )[/text]
Then run this once on your DB from the command line, PHP, phpmyadmin or whatever:
[text]UPDATE `users` SET `password` = SHA1('password')[/text]
Then you can change the login code back to:
[text]SELECT * FROM `users` WHERE `username` = $username AND `password` = SHA1($password)[/text]
There are more secure hashes than SHA, but it's better than MD5. Just a thought to keep it simple.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.