PHP login form returning error

问题:

I have created a login script below, however I have been told to use prepared statements

<?php
require '../php/connect.php';
if(isset($_POST['login'])){
    $username = mysqli_real_escape_string($con, $_POST['username']);
    $password = mysqli_real_escape_string($con, $_POST['password']);    
    $select_user = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
    $run_user = mysqli_query($con, $select_user);
    $check_user = mysqli_num_rows($run_user);
    if($check_user>0){
        header('location:../dashboard.php');
    }else{
        header('location:/pages/loginerror.php');
    }
}
?>

After learning about prepared statements for security reasons i started reading prepared statements however i just do not seem to be able to get it to work below. the error i get is "Parse error: syntax error, unexpected 'mysqli_stmt_bind_param' (T_STRING) in /usr/local/www/apache24/data/timed/php/login.php on line 9"

<?php

require '../php/connect.php';
if(isset($_POST['login'])){
    $username = mysqli_real_escape_string($con, $_POST['username']);
    $password = mysqli_real_escape_string($con, $_POST['password']);

    $stmt = mysqli_prepare($con, "SELECT * FROM users WHERE username = ? AND password = ?")
    $stmt->mysqli_stmt_bind_param($stmt, "ss", '$username', '$password');
    mysqli_stmt_execute($stmt);

    $run_user = mysqli_query($con, $stmt);
    $check_user = mysqli_num_rows($run_user);


    if($check_user>0){
        header('location:../dashboard.php');
    }else{
        header('location:/pages/loginerror.php');
    }
}
?>

回答1:

Since you're learning, instead of just telling you what you're doing wrong I'll add some explanations about why it's wrong:

$username = mysqli_real_escape_string($con, $_POST['username']);
$password = mysqli_real_escape_string($con, $_POST['password']);

If you're using prepared statements, you must not use the _escape_string() functions. In a prepared statement you bind your parameters as a particular type (string, integer, etc.). If the variables you bind have been passed through the _escape_string() functions you risk PHP double-escaping certain characters and corrupting your values. You don't want that.

$stmt = mysqli_prepare($con, "SELECT * FROM users WHERE username = ? AND password = ?")

This line is missing a ; at the end.

$stmt->mysqli_stmt_bind_param($stmt, "ss", '$username', '$password');

There are two problems here:

Instead of passing the actual variables $username and $password, you're passing the string '$username' and '$password'. That means that your database will look for literally "$username" and "$password" instead of whatever the content of those variables is. You need to lose the quotation marks.

The second (and more important) problem is that you're mixing procedural and object-oriented styles here. You can call the bind_param function in two ways:

mysqli_stmt_bind_param($stmt, "ss", $username, $password);
// or:
$stmt->bind_param("ss", $username, $password);

You need to choose one of these ways. In terms of what they do it really makes no difference. It's just a matter of personal preference, the outcome is exactly the same. If you're using the first option you need to tell the function which prepared statement you want to bind the variables to, using the second option you're already calling the function on the statement itself, so there's no need to add that argument.

mysqli_stmt_execute($stmt);

Good.

$run_user = mysqli_query($con, $stmt);

Bad.

mysqli_query() is used when you want to perform a literal SQL query. You cannot use that with a prepared statement. mysqli_stmt_excute() has already executed the query on your database. You need to remove this call to mysqli_query().

Keep in mind that executing your prepared statement can fail. If, for some reason, your prepared statement fails to execute (maybe there's a typo in your SQL query, the database connection has dropped, etc.) mysqli_stmt_execute() will return false. It is always wise to check for this, to make debugging things a heck of a lot easier.

$check_user = mysqli_num_rows($run_user);

This is almost correct, except that you need to retrieve the number of rows from the statement itself, since that is your query's interaction with the database. You need to call mysqli_num_rows($stmt) instead. Edit: As you rightly pointed out, mysqli_num_rows() doesn't work on statements. You need to use mysqli_stmt_num_rows($stmt) instead.

If you've been typing along, you should have ended up with something similar to:

<?php

require '../php/connect.php';
if(isset($_POST['login'])){
    $username = $_POST['username'];
    $password = $_POST['password'];

    $stmt = mysqli_prepare($con, "SELECT * FROM users WHERE username = ? AND password = ?");
    mysqli_stmt_bind_param($stmt, "ss", $username, $password);
    $success = mysqli_stmt_execute($stmt);

    if (!$success) {
        echo "Query failed: " . mysqli_stmt_error($stmt);
        die();
    }

    $check_user = mysqli_stmt_num_rows($stmt);

    if($check_user>0){
        header('location:../dashboard.php');
    }else{
        header('location:/pages/loginerror.php');
    }
}
?>

回答2:

<?php

require '../php/connect.php';
if(isset($_POST['login'])){

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

    $sql = "SELECT * FROM users WHERE username = ? AND password = ?";

    $stmt = mysqli_prepare($con, $sql);
    mysqli_stmt_bind_param($stmt, "ss", $username, $password);
    $success = mysqli_stmt_execute($stmt);

    if (!$success) {
        echo "Query failed: " . mysqli_stmt_error($stmt);
        die();
    }

    mysqli_stmt_store_result($stmt);
    $check_user = mysqli_stmt_num_rows($stmt);

    if($check_user>0){
        header('location:../dashboard.php');
    }else{
        echo 'Login Failed';
    }
}
?>