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

EDIT (2016): use Argon2, scrypt, bcrypt, or PBKDF2, in that order of preference. Use as large a slowdown factor as is feasible for your situation. Use a vetted existing implementation. Make sure you use a proper salt (although the libraries you're using should be making sure of this for you).

---

When you hash the passwords use DO NOT USE PLAIN MD5.

Use PBKDF2, which basically means using a random salt to prevent rainbow table attacks, and iterating (re-hashing) enough times to slow the hashing down - not so much that your application takes too long, but enough that an attacker brute-forcing a large number of different password will notice

From the document:

• Iterate at least 1000 times, preferably more - time your implementation to see how many iterations are feasible for you.
• 8 bytes (64 bits) of salt are sufficient, and the random doesn't need to be secure (the salt is unencrypted, we're not worried someone will guess it).
• A good way to apply the salt when hashing is to use HMAC with your favorite hash algorithm, using the password as the HMAC key and the salt as the text to hash (see this section of the document).

Example implementation in Python, using SHA-256 as the secure hash:

EDIT: as mentioned by Eli Collins this is not a PBKDF2 implementation. You should prefer implementations which stick to the standard, such as PassLib.

from hashlib import sha256
from hmac import HMAC
import random

def random_bytes(num_bytes):
  return "".join(chr(random.randrange(256)) for i in xrange(num_bytes))

def pbkdf_sha256(password, salt, iterations):
  result = password
  for i in xrange(iterations):
    result = HMAC(result, salt, sha256).digest() # use HMAC to apply the salt
  return result

NUM_ITERATIONS = 5000
def hash_password(plain_password):
  salt = random_bytes(8) # 64 bits

  hashed_password = pbkdf_sha256(plain_password, salt, NUM_ITERATIONS)

  # return the salt and hashed password, encoded in base64 and split with ","
  return salt.encode("base64").strip() + "," + hashed_password.encode("base64").strip()

def check_password(saved_password_entry, plain_password):
  salt, hashed_password = saved_password_entry.split(",")
  salt = salt.decode("base64")
  hashed_password = hashed_password.decode("base64")

  return hashed_password == pbkdf_sha256(plain_password, salt, NUM_ITERATIONS)

password_entry = hash_password("mysecret")
print password_entry # will print, for example: 8Y1ZO8Y1pi4=,r7Acg5iRiZ/x4QwFLhPMjASESxesoIcdJRSDkqWYfaA=
check_password(password_entry, "mysecret") # returns True

Answer 2

That was a problem of mine couple of weeks ago. We were deploying a large MIS project to 975 different geographical locations where our own user credential store will be used as an authenticator for different set of already implemented and in-use applications. We already provided both REST and SOAP based authentication service but customer insisted to be able to reach the user credential store from other applications with just a a DB connection to read-only view of related table or view. Sigh... (this highly coupled bad design decision is a subject of another question).

That forced us to sit and convert our salted and iteratively hashed password storage scheme to a specification and provide some different language implementations for easy integration.

We called it Fairly Secure Hashed Passwords or FSHP in short. Implemented it in Python, Ruby, PHP5 and released it to Public Domain. Available to be consumed, forked, flamed or spit on GitHub at http://github.com/bdd/fshp

FSHP is a salted, iteratively hashed password hashing implementation.

Design principle is similar with PBKDF1 specification in RFC 2898 (a.k.a: PKCS #5: Password-Based Cryptography Specification Version 2.0.) FSHP allows choosing the salt length, number of iterations and the underlying cryptographic hash function among SHA-1 and SHA-2 (256, 384, 512). Self defining meta prefix at the beginning of every output makes it portable while letting the consumer to choose its own password storage security baseline.

SECURITY:

Default FSHP1 uses 8 byte salts, with 4096 iterations of SHA-256 hashing. - 8 byte salt renders rainbow table attacks impractical by multiplying the required space with 2^64. - 4096 iterations causes brute force attacks to be fairly expensive. - There are no known attacks against SHA-256 to find collisions with a computational effort of fewer than 2^128 operations at the time of this release.

IMPLEMENTATIONS:

• Python: Tested with 2.3.5 (w/ hashlib), 2.5.1, 2.6.1
• Ruby : Tested with 1.8.6
• PHP5 : Tested with 5.2.6

Everyone is more than welcome to create missing language implementations or polish the current ones.

BASIC OPERATION (with Python):

>>> fsh = fshp.crypt('OrpheanBeholderScryDoubt')
>>> print fsh
{FSHP1|8|4096}GVSUFDAjdh0vBosn1GUhzGLHP7BmkbCZVH/3TQqGIjADXpc+6NCg3g==
>>> fshp.validate('OrpheanBeholderScryDoubt', fsh)
True

CUSTOMIZING THE CRYPT:

Let's weaken our password hashing scheme. - Decrease the salt length from default 8 to 2. - Decrease the iteration round from default 4096 to 10. - Select FSHP0 with SHA-1 as the underlying hash algorithm.

>>> fsh = fshp.crypt('ExecuteOrder66', saltlen=2, rounds=10, variant=0)
>>> print fsh
{FSHP0|2|10}Nge7yRT/vueEGVFPIxcDjiaHQGFQaQ==

Answer 3

For authentication purposes you should avoid storing the passwords using reversible encryption, i.e. you should only store the password hash and check the hash of the user-supplied password against the hash you have stored. However, that approach has a drawback: it's vulnerable to rainbow table attacks, should an attacker get hold of your password store database.

What you should do is store the hashes of a pre-chosen (and secret) salt value + the password. I.e., concatenate the salt and the password, hash the result, and store this hash. When authenticating, do the same - concatenate your salt value and the user-supplied password, hash, then check for equality. This makes rainbow table attacks unfeasible.

Of course, if the user send passwords across the network (for example, if you're working on a web or client-server application), then you should not send the password in clear text across, so instead of storing hash(salt + password) you should store and check against hash(salt + hash(password)), and have your client pre-hash the user-supplied password and send that one across the network. This protects your user's password as well, should the user (as many do) re-use the same password for multiple purposes.

Answer 4

• Encrypt using something like MD5, encode it as a hex string
• You need a salt; in your case, the username can be used as the salt (it has to be unique, the username should be the most unique value available ;-)
• use the old password field to store the MD5, but tag the MD5 (i.e.g "MD5:687A878....") so that old (plain text) and new (MD5) passwords can co-exist
• change the login procedure to verify against the MD5 if there is an MD5, and against the plain password otherwise
• change the "change password" and "new user" functions to create MD5'ed passwords only
• now you can run the conversion batch job, which might take as long as needed
• after the conversion has been run, remove the legacy-support

Answer 5

As with all security decisions, there are tradeoffs. If you hash the password, which is probably your easiest move, you can't offer a password retrieval function that returns the original password, nor can your staff look up a person's password in order to access their account.

You can use symmetric encryption, which has its own security drawbacks. (If your server is compromised, the symmetric encryption key may be compromised also).

You can use public-key encryption, and run password retrieval/customer service on a separate machine which stores the private key in isolation from the web application. This is the most secure, but requires a two-machine architecture, and probably a firewall in between.

Answer 6

Follow Xan's advice of keeping the current password column around for a while so if things go bad, you can rollback quick-n-easy.

As far as encrypting your passwords:

• use a salt
• use a hash algorithm that's meant for passwords (ie., - it's slow)

See Thomas Ptacek's Enough With The Rainbow Tables: What You Need To Know About Secure Password Schemes for some details.