MySQL 用于散列密码字段的数据类型和长度是多少？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/247304/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What data type to use for hashed password field and what length?
提问by z-boss
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.
我正在考虑将密码限制为 4-20 个字符，但据我所知，加密哈希字符串后将具有不同的长度。
So, how to store these passwords in the database?
那么，如何将这些密码存储在数据库中呢？
回答by Bill Karwin
Update: Simply using a hash function is not strong enough for storing passwords. You should read the answer from Gilles on this threadfor a more detailed explanation.
更新：仅使用哈希函数不足以存储密码。您应该在此线程上阅读Gilles 的答案以获得更详细的解释。
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.
对于密码，请使用密钥强化哈希算法，如 Bcrypt 或 Argon2i。例如，在 PHP 中，使用password_hash() 函数，该函数默认使用 Bcrypt。
$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).
结果是类似于以下的 60 个字符的字符串（但数字会有所不同，因为它会生成唯一的盐）。
y$.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.
使用 SQL 数据类型CHAR(60)来存储 Bcrypt 哈希的这种编码。请注意，此函数不会编码为十六进制数字字符串，因此我们无法轻松地将其解开以二进制存储。
Other hash functions still have uses, but not for storing passwords, so I'll keep the original answer below, written in 2008.
其他哈希函数仍然有用，但不能用于存储密码，所以我将保留下面的原始答案，写于 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.
这取决于您使用的哈希算法。无论输入如何，散列总是产生相同长度的结果。通常将二进制哈希结果用文本表示为一系列十六进制数字。或者您可以使用该UNHEX()函数将一串十六进制数字减半。
- 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)
- MD5 生成 128 位哈希值。您可以使用 CHAR(32) 或 BINARY(16)
- SHA-1 生成 160 位哈希值。您可以使用 CHAR(40) 或 BINARY(20)
- SHA-224 生成 224 位哈希值。您可以使用 CHAR(56) 或 BINARY(28)
- SHA-256 生成 256 位哈希值。您可以使用 CHAR(64) 或 BINARY(32)
- SHA-384 生成 384 位哈希值。您可以使用 CHAR(96) 或 BINARY(48)
- SHA-512 生成 512 位哈希值。您可以使用 CHAR(128) 或 BINARY(64)
- BCrypt 生成依赖于实现的 448 位哈希值。您可能需要 CHAR(56)、CHAR(60)、CHAR(76)、BINARY(56) 或 BINARY(60)
As of 2015, NIST recommends using SHA-256 or higherfor any applications of hash functions requiring interoperability. But NIST does not recommend using these simple hash functions for storing passwords securely.
截至 2015 年，NIST建议对任何需要互操作性的哈希函数应用程序使用 SHA-256 或更高版本。但是 NIST 不建议使用这些简单的哈希函数来安全地存储密码。
Lesser hashing algorithms have their uses (like internal to an application, not for interchange), but they are known to be crackable.
回答by Noah Goodrich
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, SHA1always returns a 40-character hexadecimal number.
您实际上可以使用CHAR(length of hash)来定义 MySQL 的数据类型，因为每个散列算法将始终计算出相同数量的字符。例如，SHA1始终返回 40 个字符的十六进制数。
回答by Gilles 'SO- stop being evil'
Always use a password hashing algorithm: Argon2, scrypt, bcryptor PBKDF2.
始终使用密码散列算法：Argon2、scrypt、bcrypt或PBKDF2。
Argon2won the 2015 password hashing competition. Scrypt, bcryptand PBKDF2are 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.
Argon2赢得了 2015 年密码哈希竞赛。Scrypt、bcrypt和PBKDF2是较旧的算法，现在被认为不太受欢迎，但从根本上来说仍然是合理的，所以如果你的平台还不支持 Argon2，现在可以使用另一种算法。
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 hashhas 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.
甲密码哈希具有从哈希表散列或密码散列不同的特性。切勿对密码使用普通的加密哈希，例如 MD5、SHA-256 或 SHA-512。密码散列算法使用唯一的salt（不用于任何其他用户或其他任何人的数据库）。盐是必要的，这样攻击者就不能只是预先计算常用密码的哈希值：使用盐，他们必须为每个帐户重新开始计算。密码散列算法本质上很慢——尽可能慢。缓慢对攻击者的伤害比你大得多，因为攻击者必须尝试许多不同的密码。有关详细信息，请参阅如何安全地散列密码。
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.
- 使用哪种算法的指标。这对于敏捷性是必要的：加密建议会随着时间而变化。您需要能够过渡到新算法。
- 难度或硬度指示器。此值越高，计算散列所需的计算量就越多。这应该是密码更改功能中的常量或全局配置值，但随着计算机速度的提高，它应该会随着时间的推移而增加，因此您需要记住每个帐户的值。一些算法有一个单一的数值，其他算法有更多的参数（例如分别调整 CPU 使用率和 RAM 使用率）。
- 盐。由于盐必须是全局唯一的，因此必须为每个帐户存储它。每次更改密码时都应随机生成盐。
- 正确的散列，即散列算法中数学计算的输出。
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 algorithmis a number or a short alphanumeric string encoding the choice of algorithm, parametersis a printable string, and saltand outputare encoded in Base64 without terminating =.
其中algorithm是一个数字或编码算法的选择一个短字母数字串，parameters是可打印字符串，salt并output以Base64而不终止被编码=。
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.
16 字节足以用于盐和输出。（参见例如对 Argon2 的建议。）以 Base64 编码，每个字符为 21 个字符。其他两部分取决于算法和参数，但通常为 20-40 个字符。总共大约有 82 个 ASCII 字符（CHAR(82)，不需要 Unicode），如果您认为以后难以扩大字段，则应为其添加安全余量。
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.
如果您以二进制格式对哈希进行编码，则算法可以将其缩减为 1 个字节，硬度为 1-4 个字节（如果您对某些参数进行了硬编码），盐和输出各为 16 个字节, 总共 37 个字节。说40 个字节( BINARY(40)) 至少有几个备用字节。请注意，这些是 8 位字节，不可打印字符，特别是该字段可以包含空字节。
Note that the length of the hash is completely unrelated to the length of the password.
请注意，散列的长度与密码的长度完全无关。
回答by Dana the Sane
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.
您可能会发现这篇关于加盐的 Wikipedia 文章值得一读。这个想法是添加一组数据来随机化你的哈希值；如果有人未经授权访问密码哈希，这将保护您的密码免受字典攻击。
回答by Treb
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.
作为固定长度的字符串（VARCHAR(n) 或 MySQL 调用它）。散列始终具有固定长度，例如 12 个字符（取决于您使用的散列算法）。因此，20 个字符的密码将减少为 12 个字符的哈希值，而 4 个字符的密码也将生成 12 个字符的哈希值。
回答by bart
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.
TEXT为了向前兼容，您应该使用（存储无限数量的字符）。随着时间的推移，散列算法（需要）变得更强大，因此随着时间的推移，这个数据库字段将需要支持更多的字符。此外，根据您的迁移策略，您可能需要将新旧哈希存储在同一字段中，因此不建议将长度固定为一种类型的哈希。
回答by willasaywhat
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.
这实际上取决于您使用的散列算法。如果我没记错的话，密码的长度与散列的长度几乎没有关系。查看您正在使用的散列算法的规范，运行一些测试，然后在上面截断。
回答by yfeldblum
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.
哈希是位序列（128 位、160 位、256 位等，取决于算法）。如果 MySQL 允许，您的列应该是二进制类型的，而不是文本/字符类型的（SQL Server 数据类型是binary(n)或varbinary(n)）。您还应该对哈希加盐。Salts 可以是文本或二进制，您将需要一个相应的列。
回答by Stephen Walcher
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.
我一直在测试以找到加密字符串的最大字符串长度并将其设置为 VARCHAR 类型的字符长度。根据您将要拥有的记录数量，它确实可以帮助增加数据库的大小。
回答by Hare Srinivasa
for md5 vARCHAR(32) is appropriate. For those using AES better to use varbinary.
对于 md5 vARCHAR(32) 是合适的。对于那些使用 AES 的人来说，最好使用 varbinary。
