Encrypting already existing database help

Migrating from cleartext to encrypted passwords

[nedpwolf]

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!

[penders]

You mention 'encryption'. This implies to me the passwords can be decrypted? It is usual to hash passwords with a one-way hashing algorithm such as md5() or sha1() (or a mixture) so there is no chance they can be decrypted. I would perhaps avoid a simple md5 hash since there are reverse databases around that could effectively 'decrypt' simple passwords.

Since there is no way to decrypt the hashed password, without actually knowing the password you might need to implement some kind of alternative password reset in case the user forgets their password, since you can't simply tell them what it is in an email.

Be careful of character encodings. If your page is UTF-8 and your database is UTF-8 things should be OK. But you could find that 'thesamepassword' hashes to different values in different encodings and suddenly your user is unable to login.

[nedpwolf]

Yes, I intended to say "hashed", not "encrypted". Thanks much!

[d3vrandom]

You can do this with a simple SQL statement:

UPDATE table_name SET password_column=md5(concat("salt",password_column)) ;

Replace salt with your secret salt key. This way if someone gets to your database they won't be able to recover the passwords using known hashes.

You can also test the above on one row if you like:

UPDATE table_name SET password_column=md5(concat("salt",password_column)) WHERE row_id=1;

Replace row_id with your table key column name and 1;

[brotherhood of LAN]

I often see MD5's stored as 32 byte characters but they can and should be stored in 16 byte binary fields.

UNHEX(MD5('plaintextpassword')) converts to binary
HEX(passwordcolumn) can return the 32 byte string that's a bit more readable.

Just like d3vrandom says, you can more than likely populate your new password column with an SQL statement rather than a script.