288

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?

jww
  • 83,594
  • 69
  • 338
  • 732
z-boss
  • 14,861
  • 12
  • 46
  • 79
  • Also see Openwall's [PHP password hashing framework](http://www.openwall.com/phpass/) (PHPass). Its portable and hardened against a number of common attacks on user passwords. The guy who wrote the framework (SolarDesigner) is the same guy who wrote [John The Ripper](http://www.openwall.com/john/) and sits as a judge in the [Password Hashing Competition](http://password-hashing.net/). So he knows a thing or two about attacks on passwords. – jww Oct 12 '14 at 01:33
  • 2
    Please do not put an upper limit on your passwords. You are hashing them, there is no storage reason for an upper limit. If you are worried about DoS attacks using the password hash, 1000 or 1024 is a reasonable upper limit. – Iiridayn Feb 15 '16 at 04:57
  • why limit password length? At least let a user create a 100 character password :) – Andrew Mar 20 '16 at 19:33
  • 4 characters is a pretty dangerous lower bound for passwords as those are trivial to crack. At the very least use 8 but 14 or 16 is much better. – quikchange Nov 20 '16 at 01:50
  • This is a very old question with an outdated answer. See the Gilles [answer](https://stackoverflow.com/a/55753734/1820553) for up to date. – kelalaka Apr 18 '19 at 21:43

10 Answers10

463

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.

Bill Karwin
  • 462,430
  • 80
  • 609
  • 762
  • Salting can be done by prefixing a unique value (e.g. the username) to every password. Here's an article that explains why it is necessary: http://www.codinghorror.com/blog/2007/09/rainbow-hash-cracking.html. In short, it helps prevent password cracking attempts that use the rainbow attack method. – AbdullahC Sep 15 '10 at 18:15
  • 48
    @Hippo: Please, don't use the username as the salt. Generate a random salt per user. – Bill Karwin Sep 15 '10 at 18:38
  • 3
    Is the randomly generated salt stored in the same table row too? – AbdullahC Sep 16 '10 at 05:59
  • 11
    Yes, there's no reason not to store it in the same row. Even if an attacker gains access to your database, they'd have to construct their rainbow table based on that salt. And that's just as much work as simply guessing the password. – Bill Karwin Sep 16 '10 at 07:14
  • @Bill Karwin: So you're saying to just set the salt to be x number of characters appended to the password? When the user types in their password to sign in, how do you know what random salt you used so you can get an equivalent hash? – SgtPooki Oct 14 '11 at 03:23
  • 5
    @SgtPooki: You need another column to store the salt in plaintext. Then you can hash the user's password with the same salt when they type it in, and compare the result to the hash digest stored in the table. – Bill Karwin Oct 14 '11 at 06:23
  • @Bill Karwin: If I were hacking a password database, which I freely admit I would not be able to do, I would surely try some unidentified field as the salt. I understand a little bit of rainbow tables, but maybe not enough to think that someone would be able to write a workaround/salt script, and just apply each column in a password table as the salt, and then run the rainbow table. so instead of one rainbow table for a table without salts, you'd have column-num number of rainbow tables for each row? Am I confused? or is the effort too much for a hacker to worry about row*column for all users? – SgtPooki Oct 19 '11 at 03:44
  • 1
    @SgtPooki: Keep in mind that generating the rainbow table for a given salt takes a lot of work and a lot of space. It can actually be easier to guess the password by exhaustive search, even if you know the salt. – Bill Karwin Oct 19 '11 at 05:10
  • 12
    If you're storing the salt in the same table (or any other location with the same access permissions) there's no reason not to use the username as the salt, since it will be unique per user. However, any known salt makes the hash cryptographically weaker than if there were no known salt. A salt only adds value if it is also unknown. – fijiaaron May 18 '12 at 13:39
  • Also, it is not true that lower bit hashes are any more crackable. There are fewer possibilities so a brute force crack is more feasible, but while there are ways to mimic an MD5 or SHA1 hash, their algorithms have still no proven flaws. A simple strlen() check makes duplication of a password hash unfeasible. Not that I'm saying MD5 or SH1 are a completely secure solution, just being pedantic. – fijiaaron May 18 '12 at 13:51
  • 9
    I don't understand the deal with known vs. unknown salt. If you're implementing a site - the salt needs to be known to the login page/script/sevice that's testing the password. So - you "unknown" salt advocates - are you assuming that the code for the login process is unknown to the attacker? Otherwise - won't the attacker *always* know the salt, whether it's random, unique, stored together with the hashed password or apart? – mattstuehler Jan 04 '13 at 19:31
  • I am confused as to why you would need twice as many for CHAR, For example CHAR(64), When a char is only a byte long, couldn't you fit a 256 bit value in CHAR(32) ? – Sean_A91 Jul 22 '15 at 22:31
  • 1
    @Sja91, Yes, if you want to store 0x00-0xFF in one byte, use BINARY(). This is a string data type with no character set. But if you read non-printable binary strings directly it tends to make your screen go crazy. You'd use CHAR() if you want to store display-friendly strings of hexadecimal digits. But it requires two hex digit characters to represent values in the range 0x00-0xFF. – Bill Karwin Jul 23 '15 at 00:29
  • [What data type to use when storing PBKDF2 encrypted passwords ?](http://stackoverflow.com/questions/12855336/what-data-type-to-use-when-storing-pbkdf2-encrypted-passwords) it seems `nvarchar(max)` has performance issue , what you think can is use [128 length](http://stackoverflow.com/questions/2863034/sql-serverwhat-data-type-to-use-for-password-salt-and-hash-values-and-what-len) will the length change ? – Shaiju T Jan 13 '16 at 11:27
  • 1
    @stom, yes, a length of 128 can hold a SHA512 hash, so that would be enough. – Bill Karwin Jan 13 '16 at 16:59
  • 5
    In response to @fijiaaron, wrt "no reason not to use username as salt", see http://security.stackexchange.com/a/41618/2572 . A salt should be _globally_ unique, as in, across the entire Internet. Storing the salt in the database alongside the password is _fine_. Using the username as the salt weakens it. – Iiridayn Feb 15 '16 at 05:06
  • @mattstuehler your login page should never do the password testing, you might do a preliminary hash in the login page if you aren't using SSL, but the actual password hash should never leave the storage, so you pass the login details to the server it then returns if it was successful or not, – MikeT Jan 17 '17 at 11:49
  • @Iiridayn this is a case of you both being right, a unique salt will always be better than username, but if you aren't keeping the salt unknown then the protection level does drop dramatically until its comparable but still better than using the username – MikeT Jan 17 '17 at 11:57
  • 1
    @MikeT you can precompute rainbow tables effectively with usernames as salts for common usernames. Preventing precomputation attacks is increased security. See http://security.stackexchange.com/a/5506/2572, and https://www.owasp.org/index.php/Password_Storage_Cheat_Sheet#Use_a_cryptographically_strong_credential-specific_salt, which recommends 32 or 64 _random_ bytes. Usernames are predictable and short. – Iiridayn Jan 18 '17 at 19:04
  • @Iiridayn and you can precompute with a **known salt** so if you know that site X use a salt of 123 to compute passwords then you precompute your password list with the salt, as the resultant tables can't be used between sites then that is a slight improvement over username, but not much of one when you consider that the attacker probably targets 1 site at once, that is why using Known Salts is not recommended – MikeT Jan 18 '17 at 19:23
  • 2
    @MikeT, Are you thinking that every user in a given site uses the same salt? No—use a random salt **per user**. Then a different rainbow table has to be created for each user, which makes it no less expensive (probably more, considering storage) than brute-force password guessing. – Bill Karwin Jan 18 '17 at 19:27
  • @BillKarwin, no bill i'm saying that if you don't use salts correctly they arn't much better than just using the username, some systems hash passwords with a shared or predefined salt, this is a common mistake that creates an unsecure system, this means that you have to keep salts unknown both by creating new ones per password and keeping salts out of the public domain, Ideally neither the password hash nor the salt should ever leave your server and if your server is compromised you should require all logins to change password and create a new salt when they do – MikeT Jan 18 '17 at 19:35
  • this means that if fijiaaron is comparing using the username as a Known salt and any other known salt they are perfectly correct there is little difference, however if you are talking about a properly used unknown salt then Iiridayn is correct and the username is useless – MikeT Jan 18 '17 at 19:49
  • Yes, there's no reason for the salt be public. I don't think anyone suggests to do that. The server-side code needs to query the respective user's salt, then use it to hash user input, then compare that result to the hashed version of the password previously stored in the database. At no time does the salt value leave the server or become public. – Bill Karwin Jan 18 '17 at 20:05
  • 1
    Having a salt is better than not having a salt, but it's still not sufficient, and you forgot to take it into account when giving the size for MD5 and the like. MD5, SHA-256 and other “ordinary” hash algorithms are not suitable for passwords (and no, NIST does not recommend any of them _for passwords_). You must store the salt and you must use a password hashing algorithm such as PBKDF2, bcrypt, scrypt or Argon2. – Gilles 'SO- stop being evil' Apr 18 '19 at 21:11
  • 1
    @Gilles, See the update I wrote in my answer above. Is this better? – Bill Karwin Apr 18 '19 at 22:58
  • 1
    No. The size of MD5/SHA2/… is not relevant since at the very minimum the database needs to store the salt. Using normal hash functions for passwords was already bad practice in 2008: a [slow salted hash](https://en.wikipedia.org/wiki/Crypt_(C)#Traditional_DES-based_scheme) appeared in Unix in 1979, what changed since then was how to make a slow salted hash, with [PBKDF2](https://tools.ietf.org/html/rfc2898) being codified in 2000. – Gilles 'SO- stop being evil' Apr 19 '19 at 10:54
  • 1
    @Gilles, Fair enough, I made another try. Your answer is of course more thorough, but I'm sure most readers will focus on mine since it's the accepted answer. We'll try together to lead developers to use the best method. – Bill Karwin Apr 19 '19 at 12:54
  • 1
    That's a lot better, thank you! Just a nitpick, “Less strong hash functions” isn't quite right. The difference between functions like SHA-256 and functions like Bcrypt is really a matter of nature, not just a matter of degree. Password hashes are a different kind of cryptographic mechanism from cryptographic hashes, not a stronger kind of cryptographic hashes. It's unfortunate that there isn't a well-accepted non-confusing name for them. – Gilles 'SO- stop being evil' Apr 19 '19 at 13:13
  • 1
    I've changed it to "other hash functions." – Bill Karwin Apr 19 '19 at 14:58
  • **Don't** use the `username` as salt. If you do: the user cannot change their username (*e.g.* `iboyd` `wildthang`). This is why *password hashing algorithms* worth their salt a) generate random salt for each password, b) store the salt in the resulting password hash, and c) return everything as a string. – Ian Boyd May 22 '20 at 13:54
  • Yes, I agree. I think that has been covered in this comment thread. – Bill Karwin May 22 '20 at 15:36
  • @InformedA Since I wrote this answer in 2008 the best practice that has become more popular is to use self-salting hash algorithms like Bcrypt or PBKDF2. See https://stackoverflow.com/questions/6832445/how-can-bcrypt-have-built-in-salts – Bill Karwin Sep 11 '20 at 21:47
  • The best practice is not to use salt at all in my opinion. If there is a simpler solution that is just as good and it is not being used, then Sisko is doing his work and Garak is working on the assurance back up plan. Sleep tight then. – InformedA Sep 14 '20 at 12:28
  • Why is the length within the brackets different between `BINARY` and `CHAR`? I see that `BINARY` is a straightfoward division by 8 but how is the number for `CHAR` calculated? Thanks. – Kok How Teh Nov 02 '20 at 07:55
  • @KokHowTeh Encoding 8 bits of information in a binary byte takes 1 byte per 8 bits, that's easy. But showing 8 bits of information in hexadecimal symbols (0-9 and A-F) takes 2 characters per 8 bits. Hex would show the value `FF` for the highest 8-bit value, and that takes two characters. Whereas in binary, you could store the same 8-bit value in a single binary byte. – Bill Karwin Nov 02 '20 at 14:01
  • This was confusing. I was thinking `sizeof(char) = 1 byte = 8 bits` so `BINARY(8) = CHAR(1)` but this is a string literal character count. – Kok How Teh Nov 03 '20 at 00:41
  • Right, if one encodes the has as human-readable hexadecimal digits, one has to use a string. You can use only 16 distinct symbols in each character, but a character still takes 1 byte to store. Therefore you only have 4 bits of information in each byte, and it takes two characters to encode 8 bits of data. – Bill Karwin Nov 03 '20 at 00:50
22

Always use a password hashing algorithm: Argon2, scrypt, bcrypt or PBKDF2.

Argon2 won the 2015 password hashing competition. Scrypt, bcrypt and PBKDF2 are older algorithms that are considered less preferred now, but still fundamentally sound, so if your platform doesn't support Argon2 yet, it's ok to use another algorithm for now.

Never store a password directly in a database. Don't encrypt it, either: otherwise, if your site gets breached, the attacker gets the decryption key and so can obtain all passwords. Passwords MUST be hashed.

A password hash has different properties from a hash table hash or a cryptographic hash. Never use an ordinary cryptographic hash such as MD5, SHA-256 or SHA-512 on a password. A password hashing algorithm uses a salt, which is unique (not used for any other user or in anybody else's database). The salt is necessary so that attackers can't just pre-calculate the hashes of common passwords: with a salt, they have to restart the calculation for every account. A password hashing algorithm is intrinsically slow — as slow as you can afford. Slowness hurts the attacker a lot more than you because the attacker has to try many different passwords. For more information, see How to securely hash passwords.

A password hash encodes four pieces of information:

  • An indicator of which algorithm is used. This is necessary for agility: cryptographic recommendations change over time. You need to be able to transition to a new algorithm.
  • A difficulty or hardness indicator. The higher this value, the more computation is needed to calculate the hash. This should be a constant or a global configuration value in the password change function, but it should increase over time as computers get faster, so you need to remember the value for each account. Some algorithms have a single numerical value, others have more parameters there (for example to tune CPU usage and RAM usage separately).
  • The salt. Since the salt must be globally unique, it has to be stored for each account. The salt should be generated randomly on each password change.
  • The hash proper, i.e. the output of the mathematical calculation in the hashing algorithm.

Many libraries include a pair functions that conveniently packages this information as a single string: one that takes the algorithm indicator, the hardness indicator and the password, generates a random salt and returns the full hash string; and one that takes a password and the full hash string as input and returns a boolean indicating whether the password was correct. There's no universal standard, but a common encoding is

$algorithm$parameters$salt$output

where algorithm is a number or a short alphanumeric string encoding the choice of algorithm, parameters is a printable string, and salt and output are encoded in Base64 without terminating =.

16 bytes are enough for the salt and the output. (See e.g. recommendations for Argon2.) Encoded in Base64, that's 21 characters each. The other two parts depend on the algorithm and parameters, but 20–40 characters are typical. That's a total of about 82 ASCII characters (CHAR(82), and no need for Unicode), to which you should add a safety margin if you think it's going to be difficult to enlarge the field later.

If you encode the hash in a binary format, you can get it down to 1 byte for the algorithm, 1–4 bytes for the hardness (if you hard-code some of the parameters), and 16 bytes each for the salt and output, for a total of 37 bytes. Say 40 bytes (BINARY(40)) to have at least a couple of spare bytes. Note that these are 8-bit bytes, not printable characters, in particular the field can include null bytes.

Note that the length of the hash is completely unrelated to the length of the password.

Gilles 'SO- stop being evil'
  • 92,660
  • 35
  • 189
  • 229
13

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.

Yousha Aleayoub
  • 3,221
  • 2
  • 42
  • 58
Noah Goodrich
  • 23,504
  • 12
  • 61
  • 95
10

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.

Dana the Sane
  • 13,774
  • 8
  • 51
  • 77
7

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.

Treb
  • 19,065
  • 6
  • 50
  • 83
  • 3
    'or however MySQL calls it' - MYSQL call it CHAR. This type is for fixed length value. So I think CHAR is better type than VARCHAR. – v1000 Oct 13 '16 at 20:35
5

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.

bart
  • 13,952
  • 20
  • 65
  • 93
3

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.

willasaywhat
  • 2,364
  • 20
  • 23
3

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.

yfeldblum
  • 63,188
  • 11
  • 126
  • 168
  • Justice is completely correct here - MySQL will store these as numerical values and will make searching on this column much more efficient than doing a string match, however salts should not be stored in the database beside the salted data - that eliminates the safety that salts provide. – Tony Maro Sep 20 '11 at 14:54
  • 6
    Salts are *not* secret. The *only* secret is the password. Just make sure that every new password gets a new salt. Each time the user changes his password, the system should generate a new salt for that password. Salts should be long and random, such as 16 bytes generated from a cryptographically secure PRNG. – yfeldblum Sep 20 '11 at 15:31
  • 1
    @TonyMaro Not sure whether a password string match on the SQL level is a good strategy. In other words, you should not search your database for a password, instead retrieve the user based on his username and compare passwords in code, rather than SQL. – bart Jul 25 '17 at 22:27
1

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.

Stephen Walcher
  • 2,555
  • 1
  • 21
  • 22
-1

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

Hare Srinivasa
  • 31
  • 1