简单的 PHP SQL 登录疑难解答 [英] Simple PHP SQL login troubleshooting
问题描述
我试图通过从数据库中检索注册的用户名/密码来创建一个非常基本的 PHP 登录.这永远不会上线，我知道有零输入验证.我要做的就是在登录时从数据库中选择数据.
I am trying to create a very basic PHP login by retrieving a registered username/password from a database. This is not ever going live and I am aware there is zero input validation. All I'm trying to do is select data from a database in a login.
这是 index.html 上的登录表单:
Here is the login form on index.html:
<table width="250" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<form name="form1" method="post" action="checklogin.php">
<td>
<table width="100%" border="0" cellpadding="3" cellspacing="1" bgcolor="#FFFFFF">
<tr>
<td colspan="3"><strong>Member Login </strong></td>
</tr>
<tr>
<td width="78">Username</td>
<td width="6">:</td>
<td width="294"><input name="Username" type="text" id="Username"></td>
</tr>
<tr>
<td>Password</td>
<td>:</td>
<td><input name="Password" type="text" id="Password"></td>
</tr>
<tr>
<td> </td>
<td> </td>
<td><input type="submit" name="Submit" value="Login"></td>
</tr>
</table>
</td>
</form>
</tr>
</table>
这里是 PHP checklogin.php:
And here is the PHP checklogin.php:
<?php
$sql_connection = mysqli_connect ("localhost:8889","root","root","derek_website_tmp");
if (mysqli_connect_errno())
{
echo "failed to connect" . mysqli_connect_error();
}
$Username=$_POST['Username'];
$Password=$_POST['Password'];
$sql = "SELECT * FROM $Members WHERE Username = '$Username' and Password = '$Password'"
$result=mysqli_query($sql);
$count = mysql_num_rows($result);
if ($count==1) {
$_SESSION['Username'] = $Username;
$_SESSION['Password'] = $Password;
header('location:login_success.php');
}
else {
echo 'Wrong Username or Password';
}
if (!mysqli_query($sql_connection))
{
die('Error : ' . mysqli_error($sql_connection));
}
mysqli_close ($sql_connection);
?>
当我尝试这个时，我在检索 checklogin.php 时遇到错误任何帮助将不胜感激.
When I try this I get an error retrieving checklogin.php Any help would be greatly appreciated.
推荐答案
首先，在开发过程中处理错误非常重要，所以我们检查我们的帖子是否存在，我们检查我们是否连接到数据库，我们检查我们的查询通过并且可以运行，我们检查我们提供给查询的参数，然后我们最终执行查询.
Firstly, handling the errors during the development is very important so we check if our post are present, we check if we connected to the database, we check if our query passed and is OK to run, we check the parameters we are giving to the query and we finally execute the query.
之后，您可以使用 bind_result 命名一个变量以接收查询中的字段，就像我所做的那样.
After that you can use bind_result to name a variable to receive the fields from your query, like I have done.
注意我的查询如何使用?这是我们使用 bind_param 定义的准备好的语句，这是为了避免 SQL 注入，在您当前的代码中，SQL 注入仍然是可能的，因为您没有清理变量.
Notice how on my query I am using ? that is a prepared statement that we define using the bind_param this is to avoid SQL injection, in your current code, SQL Injection is still possible since you're not sanitizing your variables.
我相信您正在犯的另一个错误是将密码存储为纯文本，这是非常非常错误的，您应该始终加密密码以保护您的用户和您自己.这就是为什么我不在我的 MySQL 查询中包含密码的原因，我首先只使用用户，如果找到用户，我然后使用他发布的密码来匹配从数据库中检索到的密码，在这种情况下我使用 bcrypt 来完成任务，这是一个非常安全的加密库.
Another mistake I believe you're doing is storing passwords as plain text that is VERY VERY WRONG, you should always encrypt the password to protect your users and yourself. That's why I do not include the password on my MySQL query, I first use only the user, if the user is found I then use the password he posted to match the retrieved password from the database, in this case I am using bcrypt to do the task which is a very secure encryption library.
只有在我看到密码有效后，我才会将数据放入会话并重定向用户.
Only after I see that the password is valid I am then placing the data into the session and redirecting the user.
除了我在答案底部指出的所有错误之外，以下是我编写代码的方式.
<?php
session_start();
include_once('bcrypt.php');
// Your database info
$db_host = '';
$db_user = '';
$db_pass = '';
$db_name = '';
if (!isset($_POST['Username']))
{
echo 'Fill in the username...';
exit;
}
if (!isset($_POST['Password']))
{
echo 'Fill in your password...';
exit;
}
$con = new mysqli($db_host, $db_user, $db_pass, $db_name);
if ($con->connect_error)
{
die('Connect Error (' . $con->connect_errno . ') ' . $con->connect_error);
}
$sql = "SELECT Username, Password FROM `Members` WHERE Username = ?";
if (!$result = $con->prepare($sql))
{
die('Query failed: (' . $con->errno . ') ' . $con->error);
}
if (!$result->bind_param('s', $_POST['Username']))
{
die('Binding parameters failed: (' . $result->errno . ') ' . $result->error);
}
if (!$result->execute())
{
die('Execute failed: (' . $result->errno . ') ' . $result->error);
}
$result->store_result();
if ($result->num_rows == 0)
{
die('No username found...');
}
$result->bind_result($db_username, $db_password);
$result->fetch();
$result->close();
$con->close();
$bcrypt = new Bcrypt(15);
if ($bcrypt->verify($password, $db_password))
{
$_SESSION['Username'] = $db_username;
header('location:login_success.php');
exit;
}
else
{
echo 'Wrong Username or Password';
}
注意:上面的代码只是一个例子，没有经过测试，如果你发现它有任何错误，请告诉我.
我在您发布的代码中注意到的一些错误:
Some of the errors I have noticed on the code you have posted:
你在这里错过了结束的 ;:
You're missing the closing ; over here:
$sql = "SELECT * FROM $Members WHERE Username = '$Username' and Password = '$Password'"
同样在您的查询中，您有 $Members 但您没有在代码中的任何位置定义 $Members 变量，您可能想说 Members代替，如:
Also on your query you have $Members but you have no $Members variable defined anywhere in your code, did you perhaps meant to say Members instead, as in:
$sql = "SELECT * FROM `Members` WHERE Username = '$Username' and Password = '$Password'";
不应该这样
$count = mysql_num_rows($result);
成为
$count = mysqli_num_rows($result);
还有
$result=mysqli_query($sql);
成为
$result=mysqli_query($sql_connection, $sql);
您对mysqli_query
if (!mysqli_query($sql_connection))
这篇关于简单的 PHP SQL 登录疑难解答的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
