Encrypting already existing database help

Discussions of secure PHP coding. Security in software is important, so don't be afraid to ask. And when answering: be anal. Nitpick. No security vulnerability is too small.

Moderator: General Moderators

Post Reply
nedpwolf
Forum Newbie
Posts: 3
Joined: Fri Jul 15, 2011 11:38 am

Encrypting already existing database help

Post by nedpwolf »

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!
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Encrypting already existing database help

Post by social_experiment »

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
nedpwolf
Forum Newbie
Posts: 3
Joined: Fri Jul 15, 2011 11:38 am

Re: Encrypting already existing database help

Post by nedpwolf »

Yes, that's what I intended. Sorry for the lack of clarity in the original post.
User avatar
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

Post by AbraCadaver »

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.
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.
nedpwolf
Forum Newbie
Posts: 3
Joined: Fri Jul 15, 2011 11:38 am

Re: Encrypting already existing database help

Post by nedpwolf »

I like it!
Post Reply