Solution: Store a Salted Hash of the Password

The chief problem in the antipattern Readable Passwords is that the original form of the password is readable. But we can authenticate the user’s input against a password without reading it. This section describes how to implement this kind of secure password storage in an SQL database.

Understanding hash functions

We can do this by encoding the password using a one-way cryptographic hash function. This transforms the input string into a new string, called hash, which is unrecognizable. Even the length of the original string is obscured because the hash returned by a hash function is a fixed-length string. For example, the SHA-256 algorithm converts our example password, “xyzzy”, to a 256-bit string of bits, usually represented as a 64-character string of hexadecimal digits:

SHA2('xyzzy') = '184858a00fd7971f810848266ebcecee5e8b69972c5ffaed622f5ee078671aed'

Another characteristic of a hash is that it’s not reversible. We can’t recover the input string from its hash because the hashing algorithm is designed to “lose” some information about the input. A good hashing algorithm should take as much work to crack as it would to simply guess the input through trial and error.

A popular algorithm in the past has been SHA-1, but researchers have recently proved that this 160-bit hashing algorithm has insufficient cryptographic strength so that it is possible to infer the input from a hash string. The technique to infer the encrypted string is very time-consuming but it takes less time than guessing the password by trial and error. The National Institute of Standards and Technology (NIST) announced a plan to phase out SHA-1 as an approved secure hashing algorithm in the U.S. after 2010 in favor of these stronger variants: SHA-224, SHA2 256, SHA-384, and SHA-512. Whether we need to comply with NIST standards or not, it’s a good idea to use at least SHA-256 for passwords.

The MD5() function is another popular hash function, producing hash strings of 128 bits. This function has also been shown to be cryptographically weak, so we shouldn’t use it for encoding passwords. Weaker algorithms still have been used but not for sensitive information like passwords.

Using a hash in SQL

The following is a redefinition of the Accounts table. The SHA-256 password hash is always 64 characters long, so we define the column as a fixed-length CHAR column of that length.