Database Modeling Tip: How to Store Passwords in a Database

Today I will talk about how to store passwords in your database. This post is addressed to both programmers, regardless of the language they use, and database architects.

Never store passwords in plaintext

This is Security 101. You should never, never, never store passwords in plaintext. This is also true even if your application is relatively unimportant, like a discussion forum for your college friends. Remember that users tend to reuse passwords. The password used in a niche forum may also be the user's PayPal or bank password. And that's where real harm can be done.

Hash passwords

If you don't store passwords in plaintext, what should you do instead? You should store a cryptographic hash of the password. A hash function is a function which maps a large set of data into a fixed-sized set of data.
How hash function works
A cryptographic hash function is a hash function which is secure. A cryptographic hash function has several advantangeous properties:

  • It's difficult to reverse. One you have a hash, it's difficult to find an input that produced that hash.
  • The hash changes when you change the input. If you make a small change in the input, the hash changes drastically.
  • It's collision-resistant. It's difficult to find two inputs which produce the equal hashes.

There are many cryptographic hash functions. The most popular ones are MD5, SHA-1, SHA-2 (this is a family of hash functions: SHA-256, SHA-384, SHA-512, and more), SHA-3 (again a family of hash functions). However, MD5 has been broken, and a theoretical attack on SHA-1 has been reported, so it's best not to use them.

How do you check passwords? To check password, compute the hash of the password provided by the user logging in and compare it with the hash stored in the database. If they match, there is a very high probability that the user has provided the right password.

hash = hashFunction(password);
storedHash = userDAO.getByPK(userId).getPasswordHash();

if (hash.equals(storedHash)) {
    return OK;
}

Database architects take note: make sure the password field is long enough! A common mistake is to make a password field too short. The popular hash algorithms produce lengthy message digests. The exact length of the password field depends on the encoding you use to store hash in your database: a message digest is usually a sequence of bits and you have to convert it to a string representation somehow. If you use Base64 encoding, the following table might be helpful.

Algorithm Hash length
in bits
Hash length
in Base64
Database type Notes
PLAIN password length -- -- Do not use!
MD5 128 24 VARCHAR(24) For reference only
SHA-256 256 44 VARCHAR(44)  
SHA-512 512 88 VARCHAR(88)  

 

Do not encrypt passwords. You may think that encrypting passwords is as secure as hashing them. Well, it isn't. Watch a video which explains it in detail. The thing is that encypting passwords is reversible: anyone with administrative privileges will be able to decrypt passwords and know them in plaintext. You don't want and don't need anyone to know the password in plaintext.

Salt passwords

Salt is a random data that is added to a password to produce hash, for example like this:

    hash = sha512(salt + password);

The idea is simple: if you have a whole database of hashed unsalted passwords, you can use brute-force or rainbow tables to consecutively generate password hashes. Start with, say, 5-letter passwords and go on. Finally, you will find a hash which is in a database and voila, you have found a password. Salting passwords adds the necessary randomness to the password hash.

Rules for creating successful salts:

  1. The salt should be long. The point of using salt is to overcome the fact that user passwords tend to be short.
  2. The salt should be unique for each user. Then the attacker will have to attack each salt separately. Cracking just one password does not help with cracking others.
  3. Do not use username as a salt. Use random-generated salt. Usernames have lower entropy (i.e., randomness) than random salts.

 

As I finish writing this post, a question comes to my mind:

Can the password hash column be UNIQUE? What do you think?