encrypting list of passwords

Question

I have a database full of users who I generated passwords for using the code:

UPDATE users SET password = SUBSTRING(MD5(RAND()) FROM 1 FOR 8)

I made another table with just the users email / passwords for quick reference

I now want to encrypt the passwords on the main user table. I attempted this with the following code but it doesn't work. What's wrong with it?

$query = "SELECT * FROM usersreference";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {
    $password = $row['password'];
    $email = $row['email'];
    $encrypted_password = md5($password);
}

$query = 'UPDATE users SET password = "' . $encrypted_password . '" WHERE email = "' . $email . '"';
$result = mysql_query($query);

if (mysql_affected_rows() == 1) {
    header('Location: index.php?page=Admin');
} else {
    die("there was a problem");
}
mysql_close();

btw: think about a better way to hash password than `md5` and add a salt if you want to make it really worth! See http://stackoverflow.com/questions/401656/secure-hash-and-salt-for-php-passwords for reference. — Markus, May 26 '11 at 12:18

score 3 · Accepted Answer · answered May 26 '11 at 11:55

3

You can encrypt all passwords using SQL:

UPDATE users SET `password` = MD5(`password`)

Btw, the SQL query in your PHP code doesn't work because password is a reserved word in MySQL so you need to backtick-quote it.

answered May 26 '11 at 11:55

ThiefMaster

310,957
84
592
636

score 0 · Answer 2 · answered May 26 '11 at 12:18

First: "Doesn't work" in not helpful. You should determine what doesn't work. You should define for yourself what you expect to see and what you actually get at every point of your program. Then you can determine where the two things diverge.

Issue 1: You loop through all users of your select query but only save the last one into your variable. Also if your select does not return any rows then you'll be performing the update with uninitialized variables.

Issue 2: Your table has a column named "password". That is a reserved keyword with mysql. You can use that name but you should reference it like this instead:

... SET `password` = ...

It can work without the quotes but why take chances...

Other than that your update query looks correct. If it doesn't update anything you should test run it using the mysql command line or a management tool like phpmyadmin.

score 0 · Answer 3 · answered May 26 '11 at 12:23

The others already gave solutions. But your code seems to be wrong too. You are only updating the last result. You should put the update query in the while loop:

$query = "SELECT * FROM usersreference";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {
    $password = $row['password'];
    $email = $row['email'];
    $encrypted_password = md5($password);

    $updatequery = 'UPDATE users SET password = "' . $encrypted_password . '" WHERE email = "' . $email . '"';
    $updateresult = mysql_query($updatequery);
    ...
}

encrypting list of passwords

3 Answers3