Encrypting/Hashing plain text passwords in databas

Question

I've inherited a web app that I've just discovered stores over 300,000 usernames/passwords in plain text in a SQL Server database. I realize that this is a Very Bad Thing™.

Knowing that I'll have to update the login and password update processes to encrypt/decrypt, and with the smallest impact on the rest of the system, what would you recommend as the best way to remove the plain text passwords from the database?

Any help is appreciated.

Edit: Sorry if I was unclear, I meant to ask what would be your procedure to encrypt/hash the passwords, not specific encryption/hashing methods.

Should I just:

1. Make a backup of the DB
2. Update login/update password code
3. After hours, go through all records in the users table hashing the password and replacing each one
4. Test to ensure users can still login/update passwords

I guess my concern is more from the sheer number of users so I want to make sure I'm doing this correctly.

Answer 1

To hash the password you can use the HashBytes function. Returns a varbinary, so you'd have to create a new column and then delete the old varchar one.

Like

ALTER TABLE users ADD COLUMN hashedPassword varbinary(max);
ALTER TABLE users ADD COLUMN salt char(10);
--Generate random salts and update the column, after that
UPDATE users SET hashedPassword = HashBytes('SHA1',salt + '|' + password);

Then you modify the code to validate the password, using a query like

SELECT count(*) from users WHERE hashedPassword = 
HashBytes('SHA1',salt + '|' + <password>)

where <password> is the value entered by the user.

Answer 2

MD5 and SHA1 have shown a bit of weakness (two words can result in the same hash) so using SHA256-SHA512 / iterative hashes is recommended to hash the password.

I would write a small program in the language that the application is written in that goes and generates a random salt that is unique for each user and a hash of the password. The reason I tend to use the same language as the verification is that different crypto libraries can do things slightly differently (i.e. padding) so using the same library to generate the hash and verify it eliminates that risk. This application could also then verify the login after the table has been updated if you want as it knows the plain text password still.

1. Don't use MD5/SHA1
2. Generate a good random salt (many crypto libraries have a salt generator)
3. An iterative hash algorithm as orip recommended
4. Ensure that the passwords are not transmitted in plain text over the wire

Answer 3

I would imagine you will have to add a column to the database for the encrypted password then run a batch job over all records which gets the current password, encrypts it (as others have mentiond a hash like md5 is pretty standard edit: but should not be used on its own - see other answers for good discussions), stores it in the new column and checks it all happened smoothly.

Then you will need to update your front-end to hash the user-entered password at login time and verify that vs the stored hash, rather than checking plaintext-vs-plaintext.

It would seem prudent to me to leave both columns in place for a little while to ensure that nothing hinky has gone on, before eventually removing the plaintext passwords all-together.

Don't forget also that anytime the password is acessed the code will have to change, such as password change / reminder requests. You will of course lose the ability to email out forgotten passwords, but this is no bad thing. You will have to use a password reset system instead.

Edit: One final point, you might want to consider avoiding the error I made on my first attempt at a test-bed secure login website:

When processing the user password, consider where the hashing takes place. In my case the hash was calculated by the PHP code running on the webserver, but the password was transmitted to the page from the user's machine in plaintext! This was ok(ish) in the environment I was working in, as it was inside an https system anyway (uni network). But, in the real world I imagine you would want to hash the password before it leaves the user system, using javascript etc. and then transmit the hash to your site.

Answer 4

I would like to suggest one improvement to the great python example posted by Orip. I would redefine the random_bytes function to be:

def random_bytes(num_bytes):
    return os.urandom(num_bytes)

Of course, you would have to import the os module. The os.urandom function provides a random sequence of bytes that can be safely used in cryptographic applications. See the reference help of this function for further details.

Answer 5

As the others mentioned, you don't want to decrypt if you can help it. Standard best practice is to encrypt using a one-way hash, and then when the user logs in hash their password to compare it.

Otherwise you'll have to use a strong encryption to encrypt and then decrypt. I'd only recommend this if the political reasons are strong (for example, your users are used to being able to call the help desk to retrieve their password, and you have strong pressure from the top not to change that). In that case, I'd start with encryption and then start building a business case to move to hashing.

Answer 6

I think you should do the following:

1. Create a new column called HASHED_PASSWORD or something similar.
2. Modify your code so that it checks for both columns.
3. Gradually migrate passwords from the non-hashed table to the hashed one. For example, when a user logs in, migrate his or her password automatically to the hashed column and remove the unhashed version. All newly registered users will have hashed passwords.
4. After hours, you can run a script which migrates n users a time
5. When you have no more unhashed passwords left, you can remove your old password column (you may not be able to do so, depends on the database you are using). Also, you can remove the code to handle the old passwords.
6. You're done!