带有预先准备语句的登录系统mysqli

Question

我很难把我的登录系统转换成准备好的语句。我已经成功地转换了我的整个Crud系统，但只是混淆了最后一部分。任何帮助都将不胜感激。

这是没有准备好的声明的原始登录：

if(isset($_POST['submit'])) {
    $user = mysqli_real_escape_string($con, $_POST['username']);
    $password = mysqli_real_escape_string($con, $_POST['password']);
 
    if($user == "" || $password == "") {
        echo "Either username or password field is empty.";
        echo "<br/>";
        echo "<a href='login.php'>Go back</a>";
    } else {
        $result = mysqli_query($con, "SELECT * FROM login WHERE username='$user' AND password=md5('$password')")
        or die("Could not execute the select query.");
        
        $row = mysqli_fetch_assoc($result);
        
        if(is_array($row) && !empty($row)) {
            $validuser = $row['username'];
            $_SESSION['valid'] = $validuser;
            $_SESSION['name'] = $row['name'];
            $_SESSION['id'] = $row['id'];
        } else {
            echo "Invalid username or password.";
            echo "<br/>";
            echo "<a href='login.php'>Go back</a>";
        }
 
        if(isset($_SESSION['valid'])) {
            header('Location: index.php');            
        }
    }
} else {
?>
    <div class="row">
    <div class="col-md-6 col-md-offset-3">
        <div class="box">
            <h3>Login</h3> 
            <form name="form1" action="" method="POST" enctype="multipart/form-data">
                <input type="text"  name="username" id="username" placeholder="Username" class="form-control"><br>
                <input type="password"  name="password" id="password" placeholder="Password" class="form-control"><br>
                <button type="submit" name="submit" class="btn btn-success button">Login</button>
            </form>
        </div>
    </div>
<?php
}
?>
</body>
</html>

然后我的index.php页面

<?php
    if(isset($_SESSION['valid'])) {            
        include("connect.php");                    
        $result = mysqli_query($con, "SELECT * FROM login");
    ?>                
        Welcome <?php echo $_SESSION['name'] ?> ! <a href='logout.php'>Logout</a><br/>
        <br/>
        <a href='view.php'><?php echo ("<div class='alert alert-success'>Click here to View and Add Products!</div>")?></a>
        <a href='viewuser.php'><?php echo ("<div class='alert alert-success'>Click here to Add Users!</div>")?></a>
        <br/><br/>
    <?php    
    } else {
        echo ("<div class='alert alert-danger'>You must be logged in to access CMS site.</div>"); 
        echo "<a href='login.php' class='btn btn-primary'>Login</a>";
    }
    ?>

这就是我到目前为止所做的：

if(isset($_POST['submit'])) {
    $user = $_POST['username'];
    $password = md5(['password']);
 
            $sql = $con->prepare("SELECT * FROM login WHERE username = ? AND password = ?");
            $sql->bind_param("ss", $user, $password);
            $sql->execute();
            $result = $sql->get_result();
            while($row = $result->fetch_assoc()) {
            

        
        $row = mysqli_fetch_assoc($result);  
        
        if(is_array($row) && !empty($row)) {
            $validuser = $row['username'];
            $_SESSION['valid'] = $validuser;
            $_SESSION['name'] = $row['name'];
            $_SESSION['id'] = $row['id'];
        } else {
            echo "Invalid username or password.";
            echo "<br/>";
            echo "<a href='login.php'>Go back</a>";
        }
 
        if(isset($_SESSION['valid'])) {
            header('Location: index.php');            
        }
    }
}
?>
    <div class="row">
    <div class="col-md-6 col-md-offset-3">
        <div class="box">
            <h3>Login</h3> 
            <form name="form1" action="" method="POST" enctype="multipart/form-data">
                <input type="text"  name="username" id="username" placeholder="Username" class="form-control"><br>
                <input type="password"  name="password" id="password" placeholder="Password" class="form-control"><br>
                <button type="submit" name="submit" class="btn btn-success button">Login</button>
            </form>
        </div>
    </div>
</body>
</html>

在这个问题上，我很困惑如何用index.php实现它，不管我在网上搜索了什么，我只是不明白这部分：

$row = mysqli_fetch_assoc($result);
        
        if(is_array($row) && !empty($row)) {
            $validuser = $row['username'];
            $_SESSION['valid'] = $validuser;
            $_SESSION['name'] = $row['name'];
            $_SESSION['id'] = $row['id'];
        } else {
            echo "Invalid username or password.";
            echo "<br/>";
            echo "<a href='login.php'>Go back</a>";
        }
 
        if(isset($_SESSION['valid'])) {
            header('Location: index.php');            
        }
    }
}

Answer 1

正如注释已经指出的，应该使用像散列()这样的适当函数，并将散列存储在varchar(255)类型的字段中。

// Hash a new password for storing in the database.
// The function automatically generates a cryptographically safe salt.
$hashToStoreInDb = password_hash($password, PASSWORD_DEFAULT);

在登录表单中，我们不能直接使用SQL验证密码，也不能搜索密码，因为存储的散列是咸的，单个散列的计算需要很长时间。相反我们..。

1. 必须从数据库中读取密码哈希，并按用户id进行搜索。
2. 然后，可以使用核实()函数根据找到的哈希检查登录密码。

下面可以找到mysqli的代码示例，演示如何使用mysqli连接进行密码验证。代码没有错误检查以使其可读性：

/**
 * mysqli example for a login with a stored password-hash
 */
$mysqli = new mysqli($dbHost, $dbUser, $dbPassword, $dbName);
$mysqli->set_charset('utf8');

// Find the stored password hash in the db, searching by username or email
$sql = 'SELECT password FROM users WHERE username = ?';
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('s', $_POST['username']); // it is safe to pass the user input unescaped
$stmt->execute();

// If this user exists, fetch the password-hash and check it
$isPasswordCorrect = false;
$stmt->bind_result($hashFromDb);
if ($stmt->fetch() === true)
{
  // Check whether the entered password matches the stored hash.
  // The salt and the cost factor will be extracted from $hashFromDb.
  $isPasswordCorrect = password_verify($_POST['password'], $hashFromDb);
}