change all mysql entries one by one...correct a rookie mistake

Question

i have a problem.

i am a rookie programmer, about a year ago i started learning php by myself to write an application.

like all rookies i made some big mistakes that i realized after the application was launched.

things like missing foreign keys un-encrypted cookie stored password....and stuff like that.

now before you judge me i got the application to work and i wasnt anticipating the application to well, and was expecting well below 500 users.

now a year and 25k users later i find my self in a bind, i have improved the original code, made all the necessary security improvements and what not and i am ready to launch a 2.0 version.

the problem is this the passwords are stored in mysql in plain text. after i launch the 2.0 version the passwords will be salted and sha256'd. the problem is how do i update the existing records so that i can slat and encrypt them.

i need to pull each password from mysql run it through a script and save them back to the database again. what would be the best way to do that?

thanks in advance.

If you can, I'd recommend using [bcrypt](http://stackoverflow.com/questions/4795385/how-do-you-use-bcrypt-for-hashing-passwords-in-php) instead of SHA256. It will make your scripts more long-running but they'll be more secure. — Alfo, Jul 31 '12 at 22:31

score 2 · Answer 1 · answered Jul 31 '12 at 21:34

There's no magic going on here. You can write a piece of php code to perform an update procedure. Basically what you will do is...

<?php
    select * from users
    for each (user row in database) {
        work on 'password' to get 'new password'
        update users set password='new password' where id=id
    }
?>

Now with 25k users you may wish to stagger it.. do 1000 at a time maybe. I assume you have IDs assigned to your users.. so just "select * from users where id < 0 and id > 1000" and increment the numbers in the condition to go through all of the users.

Nick ODell · Accepted Answer · 2012-07-31T22:28:17.763

First off, try all of this stuff on a development machine before you do it in production, and backup your database before you do it in production as well. Migration scripts like this are the single easiest way to lose data.

1) Create a field in your database (of type ENUM) that states what type of encryption the password field is. Put the default value as 'unencrypted'

If you don't have this, and the update script fails for some reason, you will be left with a half-hashed database.

How to do that:

ALTER TABLE application.users
 ADD password_type ENUM('unencrypted', 'SHA256') NOT NULL;

2) Make a php script that looks for 1 unencrypted password, and hashs/salts/stores it.

<?php

mysql_connect("hostname", "user", "pass") or die(mysql_error());
mysql_select_db("application") or die(mysql_error());

// Get all the data from the "example" table
$result = mysql_query("SELECT * FROM users WHERE password_type = 'unencrypted'")
or die(mysql_error());

// keeps getting the next row until there are no more to get
$row = mysql_fetch_array( $result );

$pw = $row['password'];
$id = $row['user_id_num'];
$id = (int) $id;

$hashed_password = hash("sha256", $pw);

mysql_query("UPDATE users SET password_type = 'SHA256' AND SET password = '$hashed_password' WHERE user_id_num = $id")
or die(mysql_error());


?>

3) Change that script to do, say, 100 at a time.

4) Fire off the script with the linux tool cron every minute. Leave it until the whole database is hashed.

Note that I've made some assumptions about the names of stuff in your database.

change all mysql entries one by one...correct a rookie mistake

2 Answers2