MySQL phpMyAdmin 中的散列

Hashing in phpMyAdmin

提问by codeinprogress

I have a mySQL database and I am using phpMyAdmin to access it. The database has table employees with fields like name, address, email and password.

我有一个 mySQL 数据库，我正在使用 phpMyAdmin 访问它。数据库有包含姓名、地址、电子邮件和密码等字段的员工表。

Initially the password field was just VARCHAR (20). But now I want to hash my password with SHA-256 hashing technique.

最初密码字段只是VARCHAR (20). 但现在我想用 SHA-256 散列技术散列我的密码。

I do not have much experience with databases so I want to know is -

我对数据库没有太多经验，所以我想知道 -

1. can I hash all my current employees passwords without affecting the other fields or the entire table?

2. In future when I am entering data in the database (from a web application), where do I write the hashing function to hash the password? i.e. does the hashing occurs at the front end and then the hashed password is stored in the DB or the password goes to the DB where it is hashed and then stored.

1. 我可以在不影响其他字段或整个表的情况下散列所有当前员工的密码吗？

2. 将来当我在数据库中输入数据（来自 Web 应用程序）时，我应该在哪里编写散列函数来散列密码？即散列是否发生在前端，然后散列的密码存储在数据库中，或者密码进入数据库，在那里散列然后存储。

Solution and Suggestions are appreciated.

解决方案和建议表示赞赏。

回答by John Woo

Q1: Can I hash all my current employees passwords without affecting the other fields or the entire table?

Q1：我可以在不影响其他字段或整个表的情况下对所有当前员工密码进行哈希处理吗？

A:Yes. But you need to alter the size of your column of the password by 40-42. You will use the PASSWORD( )built-in function to encrypt your password

答：是的。但是您需要将密码列的大小更改为 40-42。您将使用PASSWORD( )内置函数来加密您的密码

ALTER TABLE tableName MODIFY `password` VARCHAR(42); 

after that you can now update the password column

之后，您现在可以更新密码列

UPDATE tablename
SET     `password` = PASSWORD(`password`);

ex.)

前任。）

abcde12345 => *20B30AFAF441808B50273EDA287132EC25B02DE2

Q2: In future when I am entering data in the database (from a web application), where do I write the hashing function to hash the password?

Q2：将来当我在数据库中输入数据时（来自 Web 应用程序），我在哪里编写散列函数来散列密码？

A:In your INSERT query

答：在你INSERT query

INSERT INTO tableName (name, address, email, password) 
VALUES ('aa','bb',''cc,PASSWORD('abcde12345'))

when you want to search for the password, encrypt first the text:

当您要搜索密码时，请先加密文本：

SELECT *
FROM   tableName
WHERE `password` = PASSWORD('abcde12345')

one more thing, don't forget to escape your Passwordcolumn with backticksince it is a MySQL Reserved Word.

还有一件事，不要忘记转义您的Password列，backtick因为它是MySQL 保留字。

回答by matteomattei

You can hash the password in php and then store it in the DB:

您可以在 php 中散列密码，然后将其存储在数据库中：

$pwd = hash('sha256',$_POST['password']);

MySQL does not support sha256 function so you need to hash by code and then store/update your password table. Otherwise you can consider this http://stuge.se/mysql-sha256/

MySQL 不支持 sha256 函数，因此您需要通过代码进行散列，然后存储/更新您的密码表。否则你可以考虑这个http://stuge.se/mysql-sha256/

回答by Arseny

can I hash all my current employees passwords without affecting the other fields or the entire table?

我可以在不影响其他字段或整个表的情况下散列所有当前员工的密码吗？

Yes. For example, if you're going to use the SHA-1hashing function, you can add the corresponding column and hash all your passwords with one query:

是的。例如，如果您打算使用SHA-1散列函数，您可以添加相应的列并使用一个查询散列所有密码：

alter table employee add column password_hash varchar(40);
update employee set password_hash = sha1(password);

It is assumed that your plain text password column is called “password”. You can drop the original column after you have the hashes, of course (and, most likely, this is exactly what you want to do next).

假设您的纯文本密码列称为“密码”。当然，您可以在获得散列后删除原始列（而且，很可能，这正是您接下来想要执行的操作）。

However, I strongly advice you to read more on hashing algorithmsand pick something better. For example, you may want to use a different hashing function and/or add salt.

但是，我强烈建议您阅读有关哈希算法的更多信息并选择更好的方法。例如，您可能想要使用不同的散列函数和/或添加salt。

In future when I am entering data in the database (from a web application), where do I write the hashing function to hash the password? i.e. does the hashing occurs at the front end and then the hashed password is stored in the DB or the password goes to the DB where it is hashed and then stored.

将来当我在数据库中输入数据（来自 Web 应用程序）时，我应该在哪里编写散列函数来散列密码？即散列是否发生在前端，然后散列的密码存储在数据库中，或者密码进入数据库，在那里散列然后存储。

Most commonly, the hashing occurs on the server side each time a user logs in. Then an authentication session is created and the session ID is stored in the user's cookies (so you never store the password or it's hash on the client side, however, you transmit it to the server when the user logs in, and this is why it is good to use SSL at least for authentication).

最常见的是，每次用户登录时，都会在服务器端进行散列。然后创建一个身份验证会话，并将会话 ID 存储在用户的 cookie 中（因此您永远不会将密码或它的散列存储在客户端，但是，您在用户登录时将其传输到服务器，这就是为什么至少使用 SSL 进行身份验证是好的原因）。

In some cases, you may want to even build a separate authentication backend which only accepts password hashing requests (so even if someone cracks into your system, the exact hashing schema would be still secret until they crack the hashing backend as well, which can be a lot harder if it's built carefully enough). However, you would only need something like this in case you really care a lot about the security and it is really important. Otherwise the typical server side hashing will be enough.

在某些情况下，您甚至可能想要构建一个单独的身份验证后端，它只接受密码散列请求（因此即使有人破解了您的系统，确切的散列模式仍然是秘密的，直到他们也破解了散列后端，这可以是如果建造得足够仔细，那就难多了）。但是，如果您真的非常关心安全性并且它非常重要，您只需要这样的东西。否则典型的服务器端散列就足够了。