What data type to use for hashed password field an

问题:

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?

回答1:

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)

NIST recommends using SHA-256 or higher for passwords. Lesser hashing algorithms have their uses, but they are known to be crackable.

You should salt your passwords before applying the hashing function. Salting a password does not affect the length of the hash result.

回答2:

You can actually use CHAR(length of hash) to define your datatype for MySQL because each hashing algorithm will always evaluate out to the same number of characters. For example, SHA1 always returns a 40-character hexadecimal number.

回答3:

As a fixed length string (VARCHAR(n) or however MySQL calls it). A hash has always a fixed length of for example 12 characters (depending on the hash algorithm you use). So a 20 char password would be reduced to a 12 char hash, and a 4 char password would also yield a 12 char hash.

回答4:

You might find this Wikipedia article on salting worthwhile. The idea is to add a set bit of data to randomize your hash value; this will protect your passwords from dictionary attacks if someone gets unauthorized access to the password hashes.

回答5:

Hashes are a sequence of bits (128 bits, 160 bits, 256 bits, etc., depending on the algorithm). Your column should be binary-typed, not text/character-typed, if MySQL allows it (SQL Server datatype is binary(n) or varbinary(n)). You should also salt the hashes. Salts may be text or binary, and you will need a corresponding column.

回答6:

It really depends on the hashing algorithm you\'re using. The length of the password has little to do with the length of the hash, if I remember correctly. Look up the specs on the hashing algorithm you are using, run a few tests, and truncate just above that.

回答7:

I\'ve always tested to find the MAX string length of an encrypted string and set that as the character length of a VARCHAR type. Depending on how many records you\'re going to have, it could really help the database size.

回答8:

for md5 vARCHAR(32) is appropriate. For those using AES better to use varbinary.

回答9:

You should use TEXT (storing unlimited number of characters) for the sake of forward compatibility. Hashing algorithms (need to) become stronger over time and thus this database field will need to support more characters over time. Additionally depending on your migration strategy you may need to store new and old hashes in the same field, so fixing the length to one type of hash is not recommended.