是什么数据类型使用散列密码字段长度呢?What data type to use for hashed password field and what length?
我不确定密码散列是如何工作的(稍后将实现它),但现在需要创建数据库模式。 我在考虑限制密码20字符,但据我所知后加密散列将不同长度的字符串。 所以,如何在数据库中存储这些密码?
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?
(原文：Also see Openwall's PHP password hashing framework (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 and sits as a judge in the Password Hashing Competition. So he knows a thing or two about attacks on passwords.)
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.
(原文：@Hippo: Please, don't use the username as the salt. Generate a random salt per user.)Bill Karwin的回复:是的,还有# 39;没有理由不将它存储在相同的行。即使攻击者获得访问您的数据库,虽然# 39;d来构建他们的彩虹表基于盐。和这# 39;年代一样工作,只是猜测密码。
(原文：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的回复:@SgtPooki:你需要另一列来存储明文的盐。然后你可以散列用户# 39;年代相同的密码时盐类型,并比较结果哈希摘要存储在表中。
(原文：@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.)fijiaaron的回复:如果你# 39;再保险在同一个表中存储盐(或任何其他位置相同的访问权限)机场# 39;没有理由不使用用户名作为盐,因为它将独特的每个用户。然而,任何已知的盐使散列密码地弱于如果没有已知的盐。盐只会增加价值,如果它还未知。
(原文：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.)mattstuehler的回复:
(原文：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?)
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.
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.
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.
(原文：That is indeed very worthwhile (+1), but it doesn't answer the question! (-1))Treb的回复:是的,但是绝对有关在这种背景下(+ 1)
(原文：Yes, but definitely relevant in this context (+1))
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
varbinary(n)). You should also salt the hashes. Salts may be text or binary, and you will need a corresponding column.
(原文：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.)yfeldblum的回复:盐并不是秘密。唯一的秘密是密码。确保每个新密码获得新盐。每次用户更改他的密码,系统应该生成一个新的密码盐。盐应该是漫长的,随机的,如16字节的密码安全PRNG生成。
(原文：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.)
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.
for md5 vARCHAR(32) is appropriate. For those using AES better to use varbinary.
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.
- 什么是适当的交叉表的SQL查询语法吗?What is the proper syntax for a cross-table SQL query?
- 在MySQL中变量限制条款Variable LIMIT Clause in MySQL
- 从MySQL在Java中检索记录Retrieving records from MySQL in Java
- mysql导入脚本mysql import script
- 有什么问题这个create table语句(复制)What is wrong with this create table statement [duplicate]
- 是什么数据类型使用散列密码字段长度呢?What data type to use for hashed password field and what length?
- 这是什么时间格式?(不是UNIX,UTC,没有什么)What time format is this? (not UNIX, not UTC, nothing)
- 通过cli从远程mysql导入sql服务器mysql import sql via cli from remote server
- 最好的货币MySQL数据类型是什么?What is the best datatype for currencies in MySQL?
- 编写一个单元测试框架,测试SQL存储过程Writing a unit testing framework for testing SQL stored procedures
- 我怎么能在MySQL在两列布尔逻辑吗?How can I do boolean logic on two columns in MySQL?