php mysql 中的用户名和密码验证
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7267685/
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
username and password validation in php mysql
提问by Mohan Kumar
What I want to be able to do is: When a user enters their username and password in the form on the index.html page, if they match what is in the DB, then they get sent to the next page; userlogin.php. If their username or password is incorrect then they are asked to re-enter their details on the index.html page, and displaying an error like, "Your username is Incorrect" or "Your password is Incorrect" above the form text box. I can paste this code if required.
我希望能够做的是：当用户在 index.html 页面上的表单中输入他们的用户名和密码时，如果它们匹配数据库中的内容，那么它们将被发送到下一页；用户登录.php。如果他们的用户名或密码不正确，则要求他们在 index.html 页面上重新输入他们的详细信息，并在表单文本框上方显示“您的用户名不正确”或“您的密码不正确”等错误信息。如果需要，我可以粘贴此代码。
Can I change this text font color as well, to red for example?
我也可以将此文本字体颜色更改为红色吗？
This is the code I currently have for the userlogin.php page
这是我目前拥有的 userlogin.php 页面的代码
<?php
mysql_connect("Server", "root", "Gen") or die("Couldn't select database.");
mysql_select_db("generator") or die("Couldn't select database.");
$username = $_POST['username'];
$password = $_POST['password'];
$sql = "SELECT * FROM users WHERE Username = '$username' AND Password = '$password' ";
$result = mysql_query($sql) or die(mysql_error());
$numrows = mysql_num_rows($result);
if($numrows > 0)
{
echo 'Your in';
}
else
{
echo 'Your not in';
}
?>
回答by Johan
There as sooo many things wrong with this code:
这段代码有很多问题：
1- you have an SQL injection hole.
1- 你有一个 SQL 注入漏洞。
If I enter ' or 1=1 LIMIT 1 --as a username, I will always get access, no matter what.
Change your code into.
如果我' or 1=1 LIMIT 1 --作为用户名输入，无论如何我都可以访问。
将您的代码更改为。
$username = mysql_real_escape_string($_POST['username']);
$password = mysql_real_escape_string($_POST['password']);
See: How does the SQL injection from the "Bobby Tables" XKCD comic work?
请参阅：“Bobby Tables”XKCD 漫画中的 SQL 注入是如何工作的？
2- you are storing the password in the clear
This is a huge no no. Combined with the SQL-injection hole, it will take a hacker 5 minutes to get a list of all usernames and passwords on your site.
2- 您以明文形式存储密码
这是一个巨大的禁忌。结合 SQL 注入漏洞，黑客需要 5 分钟才能获得您网站上所有用户名和密码的列表。
Store the password as a salted hash.
将密码存储为加盐哈希。
I like to use the username as the salt.
我喜欢使用用户名作为盐。
You store the password hash using:
您使用以下方法存储密码哈希：
INSERT INTO users (username, passhash)
VALUES ('$username', SHA2(CONCAT('$password','$username'),512))
And you test the user credentials using:
并且您使用以下方法测试用户凭据：
SELECT * FROM users
WHERE username = '$username' AND
passhash = SHA2(CONCAT('$password','$username'),512)
See: Secure hash and salt for PHP passwords
And: What is "salt" when relating to MYSQL sha1?
请参阅：PHP 密码的 Secure hash 和 salt
以及：与 MYSQL sha1 相关的“salt”是什么？
BTW, use SHA2 with a 512 keylength, SHA1 is no longer secure, and MD5 is even more broken.
顺便说一句，使用密钥长度为 512 的 SHA2，SHA1 不再安全，而 MD5 甚至更坏。
3- A login can only ever match against 1 user
This code:
3- 一次登录只能匹配 1 个用户
此代码：
if($numrows > 0)
Makes no sense, if you get 2 rows out of the database, that's a clear sign someone has hacked your system. The test should be:
没有任何意义，如果您从数据库中取出 2 行，这清楚地表明有人入侵了您的系统。测试应该是：
if($numrows > 1) { //send email to sysadmin that my site has been hacked }
else if ($numrows = 0) { echo "wrong username or password" }
else { echo "welcome dr. Falken" }
4- Don't die if there's an error, call a routine to restart the connection or something
4-如果有错误不要死，调用例程重新启动连接什么的
This code:
这段代码：
$result = mysql_query($sql) or die(mysql_error());
Is fine in testing, but in production you should do something like
在测试中很好，但在生产中你应该做类似的事情
$result = mysql_query($sql);
if ($result) {
//do the deed
} else {
//call error recovery routine
}
The error recovery routine should reconnect to the server, log a error in the logbook. Is the error cannot be fixed, it should send an email to the sysadmin and only then die the server.
错误恢复例程应该重新连接到服务器，在日志中记录错误。如果错误无法修复，它应该向系统管理员发送电子邮件，然后才关闭服务器。
回答by Rijk
First of all, your code is vulnerable to SQL injection. Use PDO and prepared statementsto fix this. Second of all, you're appearantly storing usernames unencrypted. This is very unsafe. Use a hashing functionto encrypt the passwords, and encrypt the submitted password before running the query to get a match. Coloring the output is simple:
首先，您的代码容易受到 SQL 注入攻击。使用 PDO 和准备好的语句来解决这个问题。其次，您似乎在存储未加密的用户名。这是非常不安全的。使用散列函数对密码进行加密，并在运行查询以获取匹配之前对提交的密码进行加密。着色输出很简单：
echo '<span style="color:red">Your not in</span>';
And use sessionsto actually log the user in. After successfully querying the user table for the username/password combination, store the returned user_id in the $_SESSIONvariable. On each page that needs to be secured, just check for the existence of $_SESSION['user_id']; if it isn't there, your user needs to login so redirect him to the login form.
并使用session来实际登录用户。成功查询user表的用户名/密码组合后，将返回的user_id存储在$_SESSION变量中。在需要保护的每个页面上，只需检查是否存在$_SESSION['user_id']; 如果它不存在，您的用户需要登录，因此将他重定向到登录表单。
That should about do the trick for ya ;)
那应该可以为您解决问题；)
