如何在 PHP / MySQL 中对用户进行身份验证？

How do I authenticate a user in PHP / MySQL?

提问by TechTravelThink

So recently I learned how to properly add a username and password to a database. My database is usersys, and the table storing user information is called userdb. The table has two columns - username (primary), password.

所以最近我学会了如何正确地将用户名和密码添加到数据库中。我的数据库是usersys，存放用户信息的表叫userdb。该表有两列 - 用户名（主），密码。

The registration form works great, enters the users input into the database correctly and also checks to see whether the user's username is already in the database or not.

注册表工作得很好，将用户输入正确输入到数据库中，并检查用户的用户名是否已经在数据库中。

With that said, I am asking if anyone could help me create a login script. So far, this is what I have:

话虽如此，我问是否有人可以帮助我创建登录脚本。到目前为止，这就是我所拥有的：

$username = $_POST['username'];
$password = $_POST['password'];
$displayname = $_POST['username'];
$displayname = strtolower($displayname);
$displayname = ucfirst($displayname);           
echo "Your username: " . $displayname . "<br />";

mysql_connect("localhost", "root", "******") or die(mysql_error());
echo "Connected to MySQL<br />";

mysql_select_db("usersys") or die(mysql_error());
echo "Connected to Database <br />";

$lcusername = strtolower($username);
$esclcusername = mysql_real_escape_string($lcusername);
$escpassword = mysql_real_escape_string($password);

$result = mysql_query("SELECT * FROM userdb WHERE username='$esclcusername' AND     password='$escpassword'") or die(mysql_error());
$row = mysql_fetch_array( $result );
$validateUser = $row['username'];
$validatePass = $row['password'];

The POST data is from the previous log in page. I want this script to check the table (userdb) and find the row for the username that the user entered from the previous form and verify that the password entered matches the username's password set in that row, in userdb table.

POST 数据来自上一个登录页面。我希望此脚本检查表 (userdb) 并找到用户从前一个表单输入的用户名所在的行，并验证输入的密码是否与 userdb 表中该行中设置的用户名密码相匹配。

I also want some type of way to check whether or not if the username entered exists, to tell the user that the username entered does not exists if it can not be found in the table.

我还想要某种类型的方法来检查输入的用户名是否存在，如果在表中找不到，则告诉用户输入的用户名不存在。

回答by TechTravelThink

This is not a direct answer to this question but a GOOD value-add. You should use MYSQL SHA1 function to encrypt the password before storing into the database.

这不是这个问题的直接答案，而是一个很好的增值。在将密码存储到数据库之前，您应该使用 MYSQL SHA1 函数对密码进行加密。

$user = $_POST['userid'];
$pwd = $_POST['password'];
$insert_sql = "INSERT into USER(userid, password) VALUES($user, SHA1($pwd))";

$select_sql = "SELECT * FROM USER WHERE userid=$user AND password=SHA1($pwd))";

回答by ólafur Waage

You can use sessions. Sessions are global variables that when set, stay with the user while he is browsing through the site.

您可以使用会话。会话是全局变量，设置后会在用户浏览站点时与用户保持联系。

Since you are learning PHP, try out this tutorialon the official website.

由于您正在学习PHP，请在官方网站上尝试本教程。

But what you would do in theory is when the username and password match, you set a session variable

但是理论上你会做的是当用户名和密码匹配时，你设置一个会话变量

$_SESSION["auth"] = true;
$_SESSION["user_id"] = $row["user_id"];

And then do a check to see if the user is authenticated.

然后检查用户是否通过身份验证。

回答by karim79

One way to do it (DISCLAIMER: not necessarily best-practice):

一种方法（免责声明：不一定是最佳实践）：

$result = mysql_query("SELECT id FROM userdb WHERE username='$esclcusername' AND  password='$escpassword'") or die(mysql_error());
$row = mysql_fetch_array( $result );
$id = (int)$row['id'];
if($id > 0) {
    //log in the user
    session_start();
    $_SESSION['userId'] = $id;
    $_SESSION['username'] = $displayname;
}

... and on pages that require authentication:

...以及需要身份验证的页面：

session_start();
if(!isset($_SESSION['userId'])) {
    die('You need to be logged in!!!');
} else {
    echo 'Welcome ' . $_SESSION['username'];
}

Read more about PHP sessions.

阅读有关 PHP会话的更多信息。

回答by Maki

I like to use both $_SESSIONand MYSQLChecks with any login POST. This should help get a few things started.

我喜欢在任何登录 POST 中使用$_SESSION和MYSQL检查。这应该有助于开始一些事情。

$username = mysql_real_escape_string($_POST[username]);
$password = strip_tags($_POST[password]);
$password = sha1($password);

if(isset($username) && isset($password) && !empty($username) && !empty($password))
{
$sql = mysql_query("SELECT * FROM users_column WHERE username = '$username' AND password = '$password'");

//Check the number of users against database
//with the given criteria.  We're looking for 1 so 
//adding > 0 (greater than zero does the trick). 
$num_rows = mysql_num_rows($sql);
if($num_rows > 0){

//Lets grab and create a variable from the DB to register
//the user's session with.
$gid = mysql_query("SELECT * FROM users_column WHERE username = '$username' AND password = '$password'");
$row = mysql_fetch_assoc($gid);
$uid = $row[userid];

// This is where we register the session.
$_SESSION[valid_user] = $uid;

//Send the user to the member page.  The userid is what the 
//session include runs against.
header('Location: memberpage.php?userid='.$userid);
}

//If it doesn't check out -- throw an error.
else
{
echo 'Invalid Login Information';
}
}

NOTE: You would need to start the page file with session_start()and create a separate Session Check include stating with session_start()and then your progressions e.g. if($_SESSION[valid_user] != $userid)do something.

注意：您需要用 开始页面文件session_start()并创建一个单独的会话检查包括声明session_start()然后您的进程，例如if($_SESSION[valid_user] != $userid)做某事。

回答by Anand Raj

I agree with the idea if using SESSION variables while authenticating the user.

如果在对用户进行身份验证时使用 SESSION 变量，我同意这个想法。

The easy way to authenticate the user is as follows

验证用户的简单方法如下

//connect the mysql_db
$mysql_connect()
$mysql_select_db() 

//reading from mysql table
$res="SELECT * FROM table WHERE name=$username AND password=$password";
$val=mysql_query($res);

//authentication
$count=mysql_num_rows($val);
if($count==1)
//authenticate the user
else
through an error

回答by Razvan Stefanescu

You could use a select statement to retreive from MySQL the password for the specified username. If you have an empty result set, then you do not have the username in the table.

您可以使用 select 语句从 MySQL 检索指定用户名的密码。如果结果集为空，则表中没有用户名。

If you need the user to be authenticated in more than one php page, then one choice whould be using sessions (http://www.php.net/manual/en/function.session-start.php).

如果您需要在多个 php 页面中对用户进行身份验证，则可以选择使用会话 ( http://www.php.net/manual/en/function.session-start.php)。

Also, I think you should think about security, i.e. preventing SQL injection:

另外，我认为您应该考虑安全性，即防止 SQL 注入：

$variable = mysql_real_escape_string($_POST['variable'])

and avoiding to "die" (treating errors and returning user-friendly messages from the script).

并避免“死亡”（处理错误并从脚本返回用户友好的消息）。

回答by Wayne

I would also think about not storing passwords in your database. One way hashes with MD5 or SHA1 are a way of adding a layer of security at the db level.

我也会考虑不在您的数据库中存储密码。使用 MD5 或 SHA1 进行散列的一种方式是在数据库级别添加安全层的一种方式。

See http://php.net/md5or http://php.net/sha1for further information.

有关更多信息，请参阅http://php.net/md5或http://php.net/sha1。