Convert text passwords in database to hashed passwords?

Question

In my database I have over 600 users. The passwords were previously stored as plain text (slap on hand, I know). Anyways, I have changed my code to store STA1 hashed passwords, but I need to convert the existing passwords in my database so each user doesn't need to go in and modify their account.

Any help?

Answer 1

Try this:

update your_table set passfield = sha1(passfield);

Make a proper backup before doing this, since you won't be able to reverse the operation (that's the whole purpose of a HASH function).

Keep in mind that SHA1 will return HEX encoding of the binary value.

Answer 2

First, backup your database with mysqldump. For example

bash#> mysqldump -u username -p nameoftable >file_to_write_to.sql

Also, make sure that your password field is 40 characters long. If not, execute this SQL command:

alter table nameoftable modify column password varchar(40);

and then this to change the passwords:

update nameoftable set password=sha1(password) where 1;

Answer 3

If you already have users in your database with hashed passwords, then be careful about replacing the table all at once. You may end up with SHA1 hashes that got converted again to a new SHA1 hash.

If you already have new users with hashed passwords, write a script that queries the database for all passwords, if their length is less than 40 (or 20 if you're setting the second parameter to true) then generate a new SHA1 hash based on the current password (which you'll know is plaintext if it is less than 40 characters) and replace the old password with the new one.