php 如何最好地存储用户信息和用户登录名和密码

How to best store user information and user login and password

提问by Tim

I'm using Mysql and I was assuming it was better to separate out a users personal information and their login and password into two different tables and then just reference them between the two.

我正在使用 Mysql 并且我假设最好将用户的个人信息及其登录名和密码分离到两个不同的表中，然后在两者之间引用它们。

Note: To clarify my post, I understand the techniques of securing the password (hash, salt, etc). I just know that if I'm following practices from other parts of my life (investing, data backup, even personal storage) that in the worst case scenario (comprised table or fire) that having information split among tables provides the potential to protect your additional data.

注意：为了澄清我的帖子，我了解保护密码的技术（散列、盐等）。我只知道，如果我遵循我生活其他部分（投资、数据备份，甚至个人存储）的做法，那么在最坏的情况下（包括表或火灾），在表之间拆分信息可以保护您的附加数据。

回答by Michael Petrotta

Don't store passwords. If it's ever sitting on a disk, it can be stolen. Instead, store password hashes. Use the right hashing algorithm, like bcrypt (which includes a salt).

不要存储密码。如果它曾经坐在磁盘上，它可能会被盗。相反，存储密码哈希。 使用正确的散列算法，如 bcrypt（包括盐）。

EDIT: The OP has responded that he understands the above issue.

编辑：OP 回应说他了解上述问题。

There's no need to store the password in a physically different table from the login. If one database table is compromised, it's not a large leap to access another table in that same database.

无需将密码存储在与登录名不同的物理表中。如果一个数据库表遭到破坏，访问同一个数据库中的另一个表并不是一个大的飞跃。

If you're sufficiently concerned about security and security-in-depth, you might consider storing the user credentials in a completely separate data store from your domain data. One approach, commonly done, is to store credentials in an LDAP directory server. This might also help with any single-sign-on work you do later.

如果您非常关心安全性和深度安全性，您可能会考虑将用户凭据存储在与域数据完全独立的数据存储中。通常采用的一种方法是将凭据存储在 LDAP 目录服务器中。这也可能有助于您以后进行的任何单点登录工作。

回答by Rob

The passwords should be stored as a cryptographic hash, which is a non-reversible operation that prevents reading the plain text. When authenticating users, the password input is subjected to the same hashing process and the hashes compared.

密码应存储为加密哈希，这是一种不可逆转的操作，可防止读取纯文本。在对用户进行身份验证时，密码输入会经过相同的散列过程并比较散列值。

Avoid the use of a fast and cheap hash such as MD5 or SHA1; the objective is to make it expensive for an attacker to compute rainbow tables (based on hash collisions); a fast hash counteracts this. Use of an expensive hash is not a problem for authentication scenarios, since it will have no effect on a single run of the hash.

避免使用快速且廉价的哈希，例如 MD5 或 SHA1；目标是使攻击者计算彩虹表（基于哈希冲突）变得昂贵；快速哈希抵消了这一点。对于身份验证场景，使用昂贵的散列不是问题，因为它不会对散列的单次运行产生影响。

In addition to hashing, salt the hash with a randomly generated value; a nonce, which is then stored in the database and concatenated with the data prior to hashing. This increases the number of possible combinations which have to be generated when computing collisions, and thus increases the overall time complexity of generating rainbow tables.

除了散列之外，还可以使用随机生成的值对散列进行加盐；一个随机数，然后将其存储在数据库中并在散列之前与数据连接。这增加了计算碰撞时必须生成的可能组合的数量，从而增加了生成彩虹表的整体时间复杂度。

Your password hash column can be a fixed length; your cryptographic hash should output values which can be encoded into a fixed length, which will be the same for all hashes.

您的密码哈希列可以是固定长度；您的加密哈希应该输出可以编码为固定长度的值，所有哈希都相同。

Wherever possible, avoid rolling your own password authentication mechanism; use an existing solution, such as bcrypt.

尽可能避免使用自己的密码认证机制；使用现有的解决方案，例如bcrypt.

An excellent explanation of how to handle passwords, and what you need to concern yourself with, can be found at http://www.matasano.com/log/958/enough-with-the-rainbow-tables-what-you-need-to-know-about-secure-password-schemes.

可以在http://www.matasano.com/log/958/enough-with-the-rainbow-tables-what-you-上找到有关如何处理密码以及您需要关注的内容的出色解释需要了解安全密码方案。

As a final note, please remember that if an attacker obtains access to your database, then your immediate concern should probably be with any sensitive or personally-identifying information they may have access to, and any damage they may have done.

最后一点，请记住，如果攻击者获得了对您数据库的访问权限，那么您应该立即关注他们可能访问的任何敏感或个人身份信息，以及他们可能造成的任何损害。

回答by Sasha Chedygov

There's nothing wrong with putting them in the same table. In fact, it would be much faster, so I'd highly recommend it. I don't know why you'd want to split it up.

把它们放在同一张桌子上没有错。事实上，它会更快，所以我强烈推荐它。我不知道你为什么要分开它。

回答by Steve Wortham

I'll attempt to answer your original question. Having it all in one table is fine unless you just have a lot of personal information to gather. In that case it may make sense to split it up. That decision should be made based on the amount of personal information you're dealing with and how often it needs to be accessed.

我会尝试回答你最初的问题。将所有内容放在一张桌子上很好，除非您只需要收集大量个人信息。在这种情况下，将其拆分可能是有意义的。该决定应根据您处理的个人信息量以及需要访问的频率来做出。

I'd say most of the time I'd do something like this in a single table:

我会说大多数时候我会在一个表中做这样的事情：

UserID, FirstName, LastName, Email, Password, TempPassword

But... if you're gathering much more than that. Say you're gathering phone, fax, birth date, biography, etc, etc. And if most of that information is rarely accessed then I'd probably put that in its own table and connect it with a one-to-one relationship. After all, the fewer columns you have on a table, the faster your queries against that table will be. And sometimes it makes sense to simplify the tables that are most accessed. There is a performance hit with the JOIN though whenever you do need to access that personal information, so that's something you'll have to consider.

但是......如果你收集的远不止这些。假设您正在收集电话、传真、出生日期、传记等。如果大部分信息很少被访问，那么我可能会将它们放在自己的表中，并将其与一对一的关系联系起来。毕竟，表中的列越少，对该表的查询就越快。有时简化最常访问的表是有意义的。使用 JOIN 会降低性能，但无论何时您确实需要访问该个人信息，因此您必须考虑这一点。

EDIT -- You know what, I just thought of something. If you create an index on the username or email field (whichever you prefer), it'll almost completely eliminate the performance drawback of creating so many columns in a user table. I say that because whenever you login the WHERE clause will actually be extremely quick to find the username if it has an index and it won't matter if you have 100 columns in that table. So I've changed my opinion. I'd put it all in one table. ;)

编辑——你知道吗，我只是想到了一些东西。如果您在用户名或电子邮件字段（无论您喜欢哪个）上创建索引，它几乎可以完全消除在用户表中创建如此多列的性能缺陷。我这样说是因为每当您登录时，WHERE 子句实际上会非常快速地找到具有索引的用户名，并且该表中是否有 100 列也无关紧要。 所以我改变了我的看法。我会把它放在一张桌子上。;)

In either case, since security seems to be a popular topic, the password should be a hash value. I'd suggest SHA1 (or SHA256 if you're really concerned about it). TempPassword should also use a hash and it's only there for the forgot password functionality. Obviously with a hash you can't decrypt and send the user their original password. So instead you generate a temporary password they can login with, and then force them to change their password again after login.

在任何一种情况下，由于安全性似乎是一个热门话题，因此密码应该是一个哈希值。我建议使用 SHA1（或 SHA256，如果你真的很担心的话）。TempPassword 也应该使用散列，它仅用于忘记密码功能。显然，使用散列，您无法解密并向用户发送其原始密码。因此，您可以生成一个他们可以登录的临时密码，然后强制他们在登录后再次更改密码。

回答by dkaylor

Will all of this data always have a 1:1 relationship with the user? If you can forsee allowing users to have multiple addresses, phone numbers, etc, then you may want to break out the personal info into a separate table.

所有这些数据都会与用户保持 1:1 的关系吗？如果您可以预见允许用户拥有多个地址、电话号码等，那么您可能希望将个人信息分解到一个单独的表中。

回答by Lawrence Dol

First, to state the (hopefully) obvious, if you can in any way at all avoid storing usernames and passwords do so; it's a big responsibility and if your credential store is breached it may provide access to many other places for the same users (due to password sharing).

首先，声明（希望）显而易见，如果您可以以任何方式避免存储用户名和密码，请这样做；这是一项重大责任，如果您的凭证存储被破坏，它可能会为相同的用户提供对许多其他地方的访问（由于密码共享）。

If you must store credentials:

如果您必须存储凭据：

• Don't store a reversible form; store a hash using a recognized algorithm like SHA-256. Use cryptographic software from a reputable trustworthy source - DO NOT ATTEMPT TO ROLL YOUR OWN, YOU WILL LIKELY GET IT WRONG.
• For each credential set, store a salt along with the hashed data; this is used to "prime" the hash such that two identical passwords do not produce the same hash - since that gives away that the passwords are the same.
• Use a secure random generator. Weak randomness is the number one cause of encryption related security failures, not cipher algorithms.

• 不要存储可逆形式；使用公认的算法（如 SHA-256）存储哈希。使用来自信誉良好、值得信赖的来源的加密软件 - 不要试图自行推出，您很可能会弄错。
• 对于每个凭证集，将盐与散列数据一起存储；这用于“准备”散列，以便两个相同的密码不会产生相同的散列 - 因为这表明密码是相同的。
• 使用安全的随机生成器。弱随机性是加密相关安全失败的第一大原因，而不是密码算法。

If you must store reversiblecredentials:

如果您必须存储可逆凭证：

• Choose a good encryption algorithm - AES-256, 3DES (dated), or a public key cipher. Use cryptographic software from a reputable trustworthy source - DO NOT ATTEMPT TO ROLL YOUR OWN, YOU WILL LIKELY GET IT WRONG.
• For each credential set, store a salt (unencrypted) along with the encrypted data; this is used to "prime" the encryption cipher such that two identical passwords do not produce the same cipher text - since that gives away that the passwords are the same.
• Use a secure random generator. Weak randomness is the number one cause of encryption related security failures, not cipher algorithms.
• Store the encryption/decryption key(s) separately from your database, in an O/S secured file, accessible only to your applications runtime profile. That way, if your DB is breached (e.g. through SQL injection) your key is not automatically vulnerable, since that would require access to to the HDD in general. If your O/S supports file encryption tied to a profile, use it - it can only help and it's generally transparent (e.g. NTFS encryption).
• If practical, store the keys themselves encrypted with a primary password. This usually means your app. will need that password keyed in at startup - it does no good to supply it in a parameter from a script since if your HDD is breached you must assume that both the key file and the script can be viewed.
• If the username is not necessary to locate the account record encrypt both the username and password.

• 选择一个好的加密算法 - AES-256、3DES（过时）或公钥密码。使用来自信誉良好、值得信赖的来源的加密软件 - 不要试图自行推出，您很可能会弄错。
• 对于每个凭证集，将盐（未加密）与加密数据一起存储；这用于“准备”加密密码，以便两个相同的密码不会产生相同的密文 - 因为这会泄露密码是相同的。
• 使用安全的随机生成器。弱随机性是加密相关安全失败的第一大原因，而不是密码算法。
• 将加密/解密密钥与您的数据库分开存储在 O/S 安全文件中，只能由您的应用程序运行时配置文件访问。这样，如果您的数据库遭到破坏（例如通过 SQL 注入），您的密钥不会自动受到攻击，因为这通常需要访问 HDD。如果您的操作系统支持绑定到配置文件的文件加密，请使用它 - 它只会有所帮助并且它通常是透明的（例如 NTFS 加密）。
• 如果可行，请存储使用主密码加密的密钥本身。这通常意味着您的应用程序。将需要在启动时输入该密码 - 在脚本的参数中提供它没有好处，因为如果您的 HDD 被破坏，您必须假设可以查看密钥文件和脚本。
• 如果用户名不是定位帐户记录所必需的，则加密用户名和密码。

回答by Jason

In my personal experience, storing the personal information and the login information in individual databases is the best practice in this case. The reason being should an SQL injection take place, it is limited (unless the infiltrator knows the inner layout of your database(s)) to the table that the data pertains to, as opposed to providing access to the whole conglomerate of data.

根据我的个人经验，将个人信息和登录信息存储在单个数据库中是这种情况下的最佳做法。原因是应该发生 SQL 注入，它仅限于（除非渗透者知道您的数据库的内部布局）与数据相关的表，而不是提供对整个数据集团的访问。

However, do note that this may come at the expense of needing to perform more queries, hence a performance hit.

但是，请注意，这可能以需要执行更多查询为代价，因此会影响性能。

回答by dreadwail

You ought to store them in the same table, and use one-way encryption. MD5 will work, but is weak, so you might consider something like SHA1 or another method. There's no benefit to storing the 2 items in seperate tables.

您应该将它们存储在同一张表中，并使用单向加密。MD5 可以工作，但很弱，因此您可以考虑使用 SHA1 或其他方法。将 2 个项目存储在单独的表中没有任何好处。