mysql - What data type to use for hashed password field and what length?

Question

I'm not sure how password hashing works (will be implementing it later), but need to create database schema now.

I'm thinking of limiting passwords to 4-20 characters, but as I understand after encrypting hash string will be of different length.

So, how to store these passwords in the database?

Answer 1

Update: Simply using a hash function is not strong enough for storing passwords. You should read the answer from Gilles on this thread for a more detailed explanation.

For passwords, use a key-strengthening hash algorithm like Bcrypt or Argon2i. For example, in PHP, use the password_hash() function, which uses Bcrypt by default.

$hash = password_hash("rasmuslerdorf", PASSWORD_DEFAULT);

The result is a 60-character string similar to the following (but the digits will vary, because it generates a unique salt).

$2y$10$.vGA1O9wmRjrwAVXD98HNOgsNpDczlqm3Jq7KnEd1rVAGv3Fykk1a

Use the SQL data type CHAR(60) to store this encoding of a Bcrypt hash. Note this function doesn't encode as a string of hexadecimal digits, so we can't as easily unhex it to store in binary.

Other hash functions still have uses, but not for storing passwords, so I'll keep the original answer below, written in 2008.

---

It depends on the hashing algorithm you use. Hashing always produces a result of the same length, regardless of the input. It is typical to represent the binary hash result in text, as a series of hexadecimal digits. Or you can use the UNHEX() function to reduce a string of hex digits by half.

• MD5 generates a 128-bit hash value. You can use CHAR(32) or BINARY(16)
• SHA-1 generates a 160-bit hash value. You can use CHAR(40) or BINARY(20)
• SHA-224 generates a 224-bit hash value. You can use CHAR(56) or BINARY(28)
• SHA-256 generates a 256-bit hash value. You can use CHAR(64) or BINARY(32)
• SHA-384 generates a 384-bit hash value. You can use CHAR(96) or BINARY(48)
• SHA-512 generates a 512-bit hash value. You can use CHAR(128) or BINARY(64)
• BCrypt generates an implementation-dependent 448-bit hash value. You might need CHAR(56), CHAR(60), CHAR(76), BINARY(56) or BINARY(60)

As of 2015, NIST recommends using SHA-256 or higher for any applications of hash functions requiring interoperability. But NIST does not recommend using these simple hash functions for storing passwords securely.

Lesser hashing algorithms have their uses (like internal to an application, not for interchange), but they are known to be crackable.