MySQL MySQL密码功能

MySQL password function

提问by Wes

Is it considered good or bad practice to use MySQL's password function to hash passwords used by an application? I can see pros and cons. I'm curious if there is a general consensus on whether it is good or bad.

使用 MySQL 的密码函数来散列应用程序使用的密码是好还是坏的做法？我可以看到优点和缺点。我很好奇是否对它的好坏有普遍的共识。

回答by Bill Karwin

The docs for MySQL's PASSWORD()function states:

MySQL 的PASSWORD()函数的文档说明：

The PASSWORD() function is used by the authentication system in MySQL Server; you should not use it in your own applications.

PASSWORD() 函数由 MySQL Server 中的身份验证系统使用；你不应该在你自己的应用程序中使用它。

Read "You're Probably Storing Passwords Incorrectly" for better advice on hashing and storing passwords.

阅读“您可能不正确地存储密码”以获取有关散列和存储密码的更好建议。

MD5 and SHA-1 are considered to be too weak to use for passwords. The current recommendation is to use SHA-256.

MD5 和 SHA-1 被认为太弱而不能用于密码。当前的建议是使用 SHA-256。

I contributed a patch to MySQL to support a SHA2()function, and the patch was accepted, but since their roadmap has changed it's not clear when it will make it into a released product.

我为 MySQL 贡献了一个补丁来支持一个SHA2()功能，这个补丁被接受了，但是由于他们的路线图已经改变，所以不清楚什么时候它会成为一个发布的产品。

In the meantime, you can use hashing and salting in your programming language, and simply store the result hash digest in the database. If you use PHP, SHA-256 is available in the hash()function.

同时，您可以在您的编程语言中使用散列和加盐，并将结果散列摘要存储在数据库中。如果您使用 PHP，则hash()函数中可以使用 SHA-256 。

update:MySQL 5.5.8 was released in December 2010, and that release contains support for the SHA2()function.

更新：MySQL 5.5.8 于 2010 年 12 月发布，该版本包含对该SHA2()功能的支持。

回答by davek

If you are using a database function to hash passwords then by definition they have to arrive in the database unhashed: I would therefore prefer to do it much nearer the "source" i.e. in the frontend application so you're not passing around exposed information.

如果您使用数据库函数来散列密码，那么根据定义，它们必须未经散列地到达数据库：因此，我更愿意在更靠近“源”的地方进行，即在前端应用程序中，这样您就不会传递暴露的信息。

回答by davethegr8

I believe the actual PASSWORDfunction in MySQL is insecure, and has been broken, but I can't find a link at the moment. I know the older one (OLD_PASSWORDin 5 and up) is definitely insecure.

我相信PASSWORDMySQL 中的实际功能是不安全的，并且已被破坏，但我目前找不到链接。我知道旧的（OLD_PASSWORD5岁及以上）绝对不安全。

Of course, all passwords should always be stored with a salt (for further obscurity). Example:

当然，所有密码都应始终与盐一起存储（为了进一步模糊）。例子：

UPDATE users SET password=MD5(CONCAT('salt', 'user provided value')) WHERE id=54

There is also the MD5function, but with the rise of colossal rainbow tables, it's not 100% reliable as a way of completely obfuscating stored passwords.

还有这个MD5功能，但随着巨大的彩虹表的兴起，作为一种完全混淆存储密码的方式，它并不是 100% 可靠的。

A better method is hashing the password (with a salt) before it reaches the database. Example:

更好的方法是在密码到达数据库之前散列密码（使用盐）。例子：

<?php
$password = sha1(SALT.$_POST["password"]);
$sql = "UPDATE users SET password='".$password."' WHERE id=54";
?>

回答by Kshitiz Bathwal

IntroductionWithout going into too technical and mathematical detail, it might be useful to explain a little bit about the difference between encryption, hashing, and salting.

简介不用太多的技术和数学细节，这可能是解释这之间的区别一点点有用的加密，散列和腌制。

EncryptionEncryption has been around for an awfully long time. The Egyptions used it to create mystery and amusement, and the Romans used it to send secret messages. When you encrypt a password, you apply some sort of algorithm which scrambles it up. Applying the key, unscrambles it.

加密加密已经存在了很长时间。埃及人用它来制造神秘和娱乐，罗马人用它来发送秘密信息。当您加密密码时，您会应用某种算法来对其进行加扰。应用密钥，解读它。

ROT13 is a simple example of an encryption algorithm. It basically replaces each letter with one 13 places away in the alphabet.

ROT13 是加密算法的一个简单示例。它基本上用字母表中的 13 个位置替换每个字母。

Don't drink the wine. = Qba'g qevax gur jvar. ROT13 is obviously quite a weak algorithm, but it's useful to illustrate the key point here - Encrypted data is reversible. It's like that by design. There's no point encrypting a secret message if the person at the other end is unable to decipher it. Therefore, it's useful for things like credit card numbers, or emails. The web browser you're reading this on is also using encryption.

酒不要喝。= Qba'g qevax gur jvar。ROT13 显然是一个相当弱的算法，但它有助于说明这里的关键点——加密数据是可逆的。设计上就是这样。如果另一端的人无法破译它，那么加密一条秘密消息就毫无意义。因此，它对信用卡号或电子邮件等内容很有用。您正在阅读本文的 Web 浏览器也在使用加密。

The server encrypts the data, sends it over a secure SSL connection to your browser, which decrypts it so you can read it.

服务器对数据进行加密，通过安全的 SSL 连接将其发送到您的浏览器，浏览器会对其进行解密，以便您阅读。

HashingHashing is different from encryption in that once the data is encoded, it can't be decoded. Well, at least it's extremely difficult to do so. Unlike encryption, the output is always of a fixed length, depending on the algorithm you use.

散列散列与加密的不同之处在于，一旦数据被编码，就不能被解码。嗯，至少这是非常困难的。与加密不同，输出的长度总是固定的，具体取决于您使用的算法。

Using our phrase from before, and the MD5 algorithm we get ...

使用我们之前的短语，以及我们得到的 MD5 算法......

Don't drink the wine. = b290557177ec5dd7098d1de84616dd04 If we try a longer phrase ...

酒不要喝。= b290557177ec5dd7098d1de84616dd04 如果我们尝试更长的短语......

Please don't drink the wine, it tastes terrible. = fd870b20869d9ae05d84e3d7fbed0c94 You will see that the results are both the same length. This means, that multiple inputs could result in the same output, called a collission.

请不要喝这种酒，它的味道很糟糕。= fd870b20869d9ae05d84e3d7fbed0c94 您将看到结果的长度相同。这意味着，多个输入可能导致相同的输出，称为碰撞。

Hashing is useful when storing things which you don't need to read back, but you need to check. Passwords are the primary example. Instead of storing the clear text, you store the hashed version. Then, when someone types in their password, you apply the same hashing algorithm and compare it with what you have in the database. If they match, then the gates open.

在存储不需要回读但需要检查的内容时，散列很有用。密码是主要的例子。您不是存储明文，而是存储散列版本。然后，当有人输入他们的密码时，您应用相同的散列算法并将其与数据库中的内容进行比较。如果它们匹配，则大门打开。

Hash functions can also be used to test whether information has been tampered with. When sending an email, you first share a secret value that only you and the receiver know of. Before sending the email, you sign it with your secret value and produce the hash value. Then send your clear text email (without the secret value) along with the hash value. Your friend can then do the same process and if the hashes are the same, then they know your message hasn't been tampered with along the way. This technique is called Message Authentication Code or Hash Based Message Authentication Code.

哈希函数还可用于测试信息是否被篡改。发送电子邮件时，您首先共享一个只有您和收件人知道的秘密值。在发送电子邮件之前，您使用您的秘密值对其进行签名并生成哈希值。然后发送您的明文电子邮件（没有秘密值）以及哈希值。然后，您的朋友可以执行相同的过程，如果哈希值相同，则他们知道您的消息在此过程中没有被篡改。这种技术称为消息身份验证代码或基于哈希的消息身份验证代码。

The important factor for hashing algorithms is that they only work one way. The only way to work out the original value, is by brute force. Trying multiple values to see if they produce the same hash.

散列算法的重要因素是它们只能以一种方式工作。计算原始值的唯一方法是使用蛮力。尝试多个值以查看它们是否产生相同的哈希值。

This is particularly problematic with passwords which are generally short and use commonly found words. It wouldn't take a modern computer very long to run through a large dictionary (or use existing rainbow tables) and figure out the hashed result of every common password.

这对于通常较短且使用常见词的密码尤其成问题。现代计算机不需要很长时间就可以浏览一个大字典（或使用现有的彩虹表）并找出每个常用密码的散列结果。

That's where salting comes in.

这就是盐分的用武之地。

SaltingBeside clogging up your arteries, salts can clog up anyone trying to crack a hashed password. They work by adding an extra secret value to the end of the input, extending the length of the original password.

盐渍除了堵塞您的动脉，盐分还会堵塞任何试图破解散列密码的人。它们的工作方式是在输入的末尾添加一个额外的秘密值，从而延长原始密码的长度。

Say your password is rocky and the salt value is i.love.salt. The hash value would be made up from both of these together rockyi.love.salt. This provides some protection for those people who use common words as their password. However, if someone learns of the salt value you use, then they just add it to the end (or start) of each dictionary word they try in their attack.

假设您的密码是 Rocky，而盐值是 i.love.salt。哈希值将由这两者共同组成rockyi.love.salt。这为那些使用常用词作为密码的人提供了一些保护。但是，如果有人知道您使用的盐值，那么他们只需将其添加到他们尝试攻击的每个字典单词的末尾（或开头）。

To make this more difficult you can use random salts, one for each password. It obviously needs to be stored in the database somewhere matched up with the user account, but it does make brute force attacking much more difficult. Finally, you can create a salt from multiple parts, you can use the current date-time, the username, a secret phrase, a random value, or a combination of all of these.

为了使这更困难，您可以使用随机盐，每个密码一个。它显然需要存储在与用户帐户匹配的数据库中，但这确实使暴力攻击变得更加困难。最后，您可以从多个部分创建盐，您可以使用当前日期时间、用户名、秘密短语、随机值或所有这些的组合。