php 如何检查用户名和密码是否与数据库值匹配

How to check username and password matches the database values

提问by Jananath Banuka

I'm really sorry if the question looks silly. But I've been trying for days to check my usernameand passwordin the database matches what I'm typing in the htmlpage... This is my Login form...

如果问题看起来很愚蠢，我真的很抱歉。但是我已经尝试了几天来检查我的username和password数据库中的匹配我在html页面中输入的内容......这是我的登录表单......

<form method="POST" action="Dashboard/Dashboard.php">

    <div class="form-group md-form">
        <!--<input type="email" class="form-control" id="email" value="" placeholder="Enter email address">-->
        <i class="fa fa-user prefix grey-text"></i>
        <input name="username" id="username" type="text" class="form-control" required>
        <label for="defaultForm-email">Username</label>
    </div>
    <div class="form-group md-form">
        <!--<input type="password" class="form-control" id="password" value="" placeholder="Enter password">-->
        <i class="fa fa-lock prefix grey-text"></i>
        <input name="password" id="password" type="password"  class="form-control" required>
        <label for="defaultForm-pass">Your password</label>
    </div>
    <div class="text-center">
        <button type="reset" class="btn btn-amber btn-sm"><strong>Reset</strong></button>
        <input type="submit" name="submit" id="submit" class="btn btn-green btn-sm" value="Sign in">                                           
    </div>

</form>

And this is the code(php) I'm using in Dashboard.php

这是php我正在使用的代码（）Dashboard.php

<?php
    $servername = "localhost";
    $username = "root";
    $password = "";
    $databaseName = "test";

    $conn = mysqli_connect($servername, $username, $password, $databaseName);

    $un = $_POST['username'];
    $pw = $_POST['password'];
    print $pass . "_" . $email;

    $query = mysqli_query($conn, "SELECT log_username,log_password FROM login WHERE log_username='$un' AND log_password='$pw'");

    $result_can = mysqli_query($conn, $query);


    while ($row = mysql_fetch_assoc($result_can)) {


        $check_username = $row['username'];
        $check_password = $row['password'];
    }
    if ($un == $check_username && $pw == $check_password) {
        $message = "ok";
        echo "<script type='text/javascript'>alert('$message');</script>";
        header("Location: Doctors.php");
    } else {
        $message = "No";
        echo "<script type='text/javascript'>alert('$message');</script>";
        header("Location: Doctors.php");
    }
    ?>

I really tried like thousands of times, but couldn't figure out where I went wrong... Can anyone please help me?

我真的尝试了数千次，但无法弄清楚我哪里出错了......任何人都可以帮助我吗？

I know my code is open to SQL injection, but I don't care about it as this is a example I needed to show to my friendsSo neglect that part.

我知道我的代码对 SQL 注入是开放的，但我并不关心它，因为这是我需要向我的朋友展示的一个例子所以忽略那部分。

回答by O. Jones

Stack Overflow is for "professional and enthusiast programmers." With respect, you've shown us code in your question that isn't even close to being worthy of either name.

Stack Overflow 是为“专业和狂热的程序员”准备的。恕我直言，您在问题中向我们展示了与这两个名字都名不符实的代码。

StackOverflow people don't have much of a sense of humor about bad security code. You get strong reactions to code like yours because, well, Equifax, and Ashley Madison, and Adobe, and all the rest of the places that have been cracked by cybercriminals. Why do we jump on you? Because we don't like cybercriminals and we don't want to make life easy for them. Friends don't let friends do bad password security. Friends don't show friends grossly insecure password-validation code.

StackOverflow 的人对糟糕的安全代码没有多少幽默感。您对像您这样的代码反应强烈，因为 Equifax、Ashley Madison、Adobe 以及其他所有已被网络犯罪分子破解的地方。我们为什么要跳到你身上？因为我们不喜欢网络犯罪分子，我们不想让他们的生活变得轻松。朋友不要让朋友做坏密码安全。朋友不会向朋友展示非常不安全的密码验证代码。

What's wrong with your code? You're storing passwords as plain text, and you're vulnerable to SQL injection. I will address the first of these issues.

你的代码有什么问题？您将密码存储为纯文本，并且容易受到 SQL 注入的影响。我将解决这些问题中的第一个。

Fortunately, php has facilities to do good password security. Read about them here. http://php.net/manual/en/faq.passwords.phpUse them. How do you handle passwords?

幸运的是，php 具有很好的密码安全性。在此处阅读有关它们的信息。 http://php.net/manual/en/faq.passwords.php使用它们。你如何处理密码？

1. When a user registers on your site and first presents a password, you hash it, something like this.

1. 当用户在您的网站上注册并首先提供密码时，您会对其进行哈希处理，就像这样。

  $usersPassword = $_POST['password']);
  $hash = password_hash( $usersPassword , PASSWORD_DEFAULT );
  // you then store the username and the hash in your dbms. 
  // the column holding the hash should be VARCHAR(255) for future-proofing
  // NEVER! store the plain text (unhashed) password in your database

1. When a user tries to log in, you do a query like this:

   SELECT log_password FROM log_user WHERE log_username = TheUsernameGiven
   

   You then put the retrieved password into a variable named $hash.

   You then use php's password_verify()functionto check whether the password your would-be user just gave you matches the password in your database.

   Finally, you check whether the user's password needs to be rehashed, because the method you used previously to hash it has become obsolete.

2. 当用户尝试登录时，您会执行如下查询：

   SELECT log_password FROM log_user WHERE log_username = TheUsernameGiven
   

   然后将检索到的密码放入名为 的变量中$hash。

   然后您使用php 的password_verify()函数来检查您的潜在用户刚刚给您的密码是否与您的数据库中的密码匹配。

   最后，您检查用户的密码是否需要重新散列，因为您之前用于对其进行散列的方法已过时。

 $usersPassword = $_POST['password']);
 $valid = password_verify ( $usersPassword, $hash );
 if ( $valid ) {
   if ( password_needs_rehash ( $hash, PASSWORD_DEFAULT ) ) {
     $newHash = password_hash( $usersPassword, PASSWORD_DEFAULT );
     /* UPDATE the user's row in `log_user` to store $newHash */
   }
   /* log the user in, have fun! */
 }
 else {
  /* tell the would-be user the username/password combo is invalid */
 }

This sequence is futureproof, because it can rehash passwords later if the old hashing method gets too easy for cybercreeps to crack.

这个序列是面向未来的，因为如果旧的散列方法太容易被网络爬虫破解，它可以在以后重新散列密码。

回答by Bill Karwin

Several problems, some were mentioned by comments above.

几个问题，上面的评论提到了一些问题。

Mixing mysql_* vs. mysqli_* API

混合使用 mysql_* 与 mysqli_* API

You call the query with mysqli_query()but you try to fetch results with mysql_fetch_assoc(). You can't mix these different APIs. The mysql_*functions will not use the connection you opened with mysqli_connect(), and vice-versa. Pick one MySQL extension and stick with it.

您使用 调用查询，mysqli_query()但尝试使用 获取结果mysql_fetch_assoc()。您不能混合使用这些不同的 API。这些mysql_*函数不会使用您用 开启的连接mysqli_connect()，反之亦然。选择一个 MySQL 扩展并坚持使用。

Hint: Don't use mysql_*at all. It's deprecated, and has been removed from PHP 7.0+

提示：完全不要使用mysql_*。它已弃用，并已从 PHP 7.0+ 中删除

Querying with conditions for bothusername and password

与条件查询两个用户名和密码

Just search for the username, and fetch the password. If you search for both, then the search will return zero rows, unless the correct password was used.

只需搜索用户名，并获取密码。如果您同时搜索both，则搜索将返回零行，除非使用了正确的密码。

You don't want that. You want to avoid putting the plaintext password in the SQL query. Just search on the username, and fetch the stored password and then compare what you fetch to the user input password.

你不想那样。您希望避免将明文密码放入 SQL 查询中。只需搜索用户名，获取存储的密码，然后将获取的内容与用户输入的密码进行比较。

Uninitialized variables

未初始化的变量

If you fetch zero rows from your query, then $check_usernameand $check_passwordare never set. Then you compare those variables in your if statement. Not a fatal error, but bad style.

如果您从您的查询，然后取零行$check_username和$check_password永远不会置。然后在 if 语句中比较这些变量。不是致命的错误，而是糟糕的风格。

No password hashing

没有密码散列

You appear to be comparing the user input, which I assume is plaintext, directly to what's stored in the database. You're Probably Storing Passwords Incorrectly.

您似乎正在将用户输入（我认为是纯文本）直接与数据库中存储的内容进行比较。您可能未正确存储密码。

Instead, when you store your password, use password_hash()first.

相反，当您存储密码时，请先使用password_hash()。

No query parameters

没有查询参数

I know you said you don't care about your SQL injection vulnerability, but this is like being an electrician and saying you don't care that your electrical panel is stuffed with oily rags. Be sure to post your disregard for safety on your LinkedIn profile, so employers know who to avoid.

我知道你说过你不在乎 SQL 注入漏洞，但这就像是一个电工，说你不在乎你的电气面板塞满了油污的破布。请务必在您的 LinkedIn 个人资料上发布您对安全的漠视，以便雇主知道该避开谁。

Recommended implementation

推荐实施

mysqli_report(MYSQLI_REPORT_STRICT); // enable exceptions

$conn = new mysqli($servername, $mysql_username, $mysql_password, $databaseName);

$log_username = $_POST['username'];
$log_password = $_POST['password'];

$sql = "SELECT log_username, log_password_hash FROM login WHERE log_username=?";
$stmt = $conn->prepare($sql);
$stmt->bind_param('s', $log_username);
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    if (password_verify($log_password, $row['log_password_hash'])) {
        $message = "ok";
        // header must be called before any other output
        header("Location: Doctors.php");
        exit();
    }
}
$message = "No";
// header must be called before any other output
header("Location: Doctors.php");

回答by rickdenhaan

There are several problems here, both in your code and in the thought process. Let's work our way down:

这里有几个问题，无论是在你的代码中还是在思考过程中。让我们往下走：

$un = $_POST['username'];
$pw = $_POST['password'];
print $pass . "_" . $email;

That printline shouldbe giving you a warning. The variables $passand $emaildo not exist. You should remove that line, unless what you were tryingto do is to print $unand $pwinstead.

那条print线应该给你一个警告。变量$pass和$email不存在。您应该删除该行，除非您尝试做的是打印$un而$pw不是打印。

$query = mysqli_query($conn, "SELECT log_username,log_password FROM login WHERE log_username='$un' AND log_password='$pw'");

There's no need to select both the username and password column. If there is a match, they will always be the same as $unand $pw, which you already have. You're only checking whether the username and password are correct or not, so selecting a single column is good enough. Preferably the user id, but only the username will be sufficient.

无需同时选择用户名和密码列。如果有匹配项，它们将始终$un与$pw您已经拥有的和相同。您只是检查用户名和密码是否正确，因此选择单列就足够了。最好是用户 ID，但只有用户名就足够了。

Keep in mind that -- assuming the query executes successfully -- $querywill contain a mysqli_resultobject.

请记住——假设查询成功执行——$query将包含一个mysqli_result对象。

$result_can = mysqli_query($conn, $query);

This line needs to be removed. You have already executed your query and $queryis its result, what you're doing here makes no sense and should be giving you a warning, or perhaps even a fatal error.

需要删除此行。你已经执行了你的查询并且$query是它的结果，你在这里做的事情没有意义，应该给你一个警告，甚至可能是一个致命的错误。

while ($row = mysql_fetch_assoc($result_can)) {
    $check_username = $row['username'];
    $check_password = $row['password'];
}
if ($un == $check_username && $pw == $check_password) {
    $message = "ok";
    echo "<script type='text/javascript'>alert('$message');</script>";
    header("Location: Doctors.php");
} else {
    $message = "No";
    echo "<script type='text/javascript'>alert('$message');</script>";
    header("Location: Doctors.php");
}

You cannotmix mysql_*and mysqli_*functions. Using mysql_fetch_assoc()here should give you a fatal error. You should use mysqli_fetch_assoc()instead (on $queryinstead of $result_can), however:

你不能混用mysql_*和mysqli_*功能。mysql_fetch_assoc()在这里使用应该会给你一个致命的错误。您应该mysqli_fetch_assoc()改用（on$query而不是$result_can），但是：

Since you're only interested in whether or not there was any result at all, this whole section can be changed to:

既然你只在是否有任何结果有兴趣可言，这整段可以改为：

if (mysqli_num_rows($query) > 0) {
    $message = "ok";
    echo "<script type='text/javascript'>alert('$message');</script>";
    header("Location: Doctors.php");
} else {
    $message = "No";
    echo "<script type='text/javascript'>alert('$message');</script>";
    header("Location: Doctors.php");
}

This will pose other problems, because you cannot use header()to redirect the user after echo'ing your <script>tag (you will get a "headers already sent" error). If you want the Javascript alert, perform the redirect with Javascript as well. Also, that $messagevariable is rather useless, you might as well put the message directly into the alert:

这会带来其他问题，因为您不能header()在回显您的<script>标签后使用重定向用户（您将收到“标题已发送”错误）。如果您需要 Javascript 警报，也可以使用 Javascript 执行重定向。此外，该$message变量相当无用，您不妨将消息直接放入警报中：

if (mysqli_num_rows($query) > 0) {
    echo "<script type='text/javascript'>alert('ok'); window.location.href='Doctors.php';</script>";
} else {
    echo "<script type='text/javascript'>alert('No'); window.location.href='Doctors.php';</script>";
}

Once you fix allof these issues, you still have some thinking to do.

一旦你解决了所有这些问题，你还有一些想法要做。

• You should neverstore passwords in plain text in your database.
• You may not care about SQL injection right now, but with your current query I can log in as any valid user (e.g. "admin") by typing their username as admin' AND 1 --, or if I just want access I can use a username of any' OR 1 --and be logged in as the first user in your table. Look into prepared statements and how they work.
• You have no error handling at all. You should add checks to see if the database connection was opened successfully, the query executed properly, the form was posted and the username/password fields were filled in and think about how you want to present a useful error message to the user.

• 您永远不应该在数据库中以纯文本形式存储密码。
• 您现在可能不关心 SQL 注入，但是根据您当前的查询，我可以通过输入任何有效用户（例如“admin”）的用户名作为 登录admin' AND 1 --，或者如果我只想访问我可以使用用户名any' OR 1 --并被登录作为您表中的第一个用户。查看准备好的语句及其工作原理。
• 您根本没有错误处理。您应该添加检查以查看数据库连接是否已成功打开、查询是否正确执行、表单是否已发布以及用户名/密码字段是否已填写，并考虑如何向用户呈现有用的错误消息。

The main lesson here should be: when you're developing and it doesn't work, always check the error logs to see if it contains any hints and turn on PHP's error reporting features so you can see what you did wrong right in your browser.

这里的主要教训应该是：当你正在开发并且它不起作用时，总是检查错误日志以查看它是否包含任何提示并打开 PHP 的错误报告功能，这样你就可以在浏览器中看到你做错了什么.