[SOLVED] Encrypting Varchar passwords to SHA1 hash.

[ZimmerX]

Hello,

 

MYSQL Version - 5.0.27-standard

 

I am having a problem, I need to change a password column in a table from VARCHAR to SHA1 encryption.

The colummn is in a table named "users" and is called "password". Right now its in text form, and I need to change all the existing passwords that are already there to SHA1. Because I have the login form which encrypts the password, but if I do that, then the users who have registered before will not be able to login as they will not have an SHA1 encrypted password.

 

So, the question is, how do I execute this in MYSQL so they are all encrypted?

 

Thanks,

Zimmer

[revraz]

So their PW's right now are in plain text?

 

If so, you can read each one, convert it to sha1, then write it back.

[Aureole]

So just do something like...

 

<?php
// Connect to database here...

$query = "SELECT * FROM `users`";
$result = mysql_query($query);

while($row = mysql_fetch_assoc($result))
{
   $mem_id = $row['member_id'];
   $old_pass = $row['password'];
   $new_pass = sha1($old_pass);

   $query2 = "UPDATE `users` SET `password` = '{$new_pass}' WHERE member_id = '{$mem_id}'";
   $result2 = mysql_query($query2);
}
?>

 

EDIT: Wtf happened to my post? ???

[fenway]

You wrapped it in PHP, not code.

 

MySQL has an SHA1() function too, so a simple update statement will work:

[ZimmerX]

Yeah fenway, that is the code I need. I don't know how to do that. Is it not something like

UPDATE $password blah blah ?

 

Thanks a bunch.

[PFMaBiSmAd]

I'll assume that you are just doing this on your development system. If doing this on your actual online data, make sure you have a working backup of your database in case anything goes wrong.

 

It is better to add a column to your database, for example "sha_password", insuring that the column width is sufficient to hold a SHA1 value (just think what would happen if your existing column was not big enough to hold the SHA1 results and you blindly updated it, ouch, all those passwords would be lost.) Then run an UPDATE query to populate the new column with the SHA1 of the existing column. After you make sure that the new column contains what you expect and you have modified your code so that it puts new passwords into the new column with the proper SHA1 encoding and is able to query and/or retrieve the SHA1 passwords from the new column, then and only then should you remove the old password column from the database.

 

See the following query for an example of how to populate the new column with the SHA1 value of the old password column (untested) -

 

UPDATE users SET sha_password = SHA1(password) WHERE 1 = 1