How to change this part of code as PHP prepared statement?

Question

Statement

In login_check.php, it worked but I would like to change it into the prepared statement.

login.php

<body>
      <div class="container">
          <h1>Please Log In to the System</h1>
            <form method="post" action="login_check.php">
                  <input type="text" name="username" placeholder="Username" required>
                  <input type="password" name="password" placeholder="Password" autocomplete="off" required> 
                  <button type="submit" name="login" value="Log In">Log In</button>
            </form>
    </div>
</body>

login_check.php

<body>
<?php
    //Establish connection
    include 'connection.php';
//-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    $sql = "SELECT * FROM admins WHERE admin_username = '".mysqli_real_escape_string($conn, $_POST['username'])."' and admin_password = '".mysqli_real_escape_string($conn, $_POST['password'])."'";
    $query = mysqli_query($conn, $sql);
    $result = mysqli_fetch_array($query);
//-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    if(!$result) //Username or Password is invalid!
    {
        ?>
        <div class="container">
            <h1>Username or Password is invalid!</h1>
           <form method="post" action="login.php">
            <button type="submit">Back</button>
           </form>
        </div>
        <?php
    }
    else //Username and Password are valid!
    {
        $_SESSION["admin_id"] = $result["admin_id"];
        $_SESSION["admin_username"] = $result["admin_username"];
        session_write_close();
        header("location:front.php");
    }
    $conn->close();
?>
</body>

**WARNING**: Writing your own access control layer is not easy and there are many opportunities to get it severely wrong. Please, do not write your own authentication system when any modern [development framework](http://codegeekz.com/best-php-frameworks-for-developers/) like [Laravel](http://laravel.com/) comes with a robust [authentication system](https://laravel.com/docs/master/authentication) built-in. At the absolute least follow [recommended security best practices](http://www.phptherightway.com/#security) and **never store passwords as plain-text** or a weak hash like **SHA1 or MD5**. — tadman, Apr 20 '19 at 05:23

Nick · Accepted Answer · 2019-04-20T03:59:54.490

3

To change to prepared statements, you just need to

replace variables in your query with a ?
prepare the query
bind variables to the parameters
execute the statement

For your code that would look like this:

$sql = "SELECT * FROM admins WHERE admin_username = ? and admin_password = ?";
$stmt = $conn->prepare($sql) or die($conn->error);
$stmt->bind_value("ss", $_POST['username'], $_POST['password']);
$stmt->execute() or die($stmt->error);
$result = $stmt->get_result();

Note that you should not be storing passwords in plain text in your database. Please look into PHPs password_hash and password_verify functions to properly handle your passwords. You would use password_hash when storing the password in the database, and then your code for verifying the user would look something like:

$sql = "SELECT * FROM admins WHERE admin_username = ?";
$stmt = $conn->prepare($sql) or die($conn->error);
$stmt->bind_value("s", $_POST['username']);
$stmt->execute() or die($stmt->error);
$result = $stmt->get_result() or die($stmt->error);
$row = $result->fetch_array();
if (!$row|| !password_verify($_POST['password'], $row['admin_password']) {
    // invalid username or password

edited Apr 20 '19 at 03:59

answered Apr 20 '19 at 03:45

Nick

138,499
22
57
95

From my code, how do I use `password_hash()` and `password_verify()` ? Where should I put them in? – Oat V Apr 20 '19 at 03:53
1

@OatV I suggest taking a look at [this question](https://stackoverflow.com/questions/30279321/how-to-use-password-hash) as well as the manual pages. I've also made an edit to help on the way. – Nick Apr 20 '19 at 04:00
From your `if statement`, it always returns as `username or password is invalid`, even I put the valid ID and Password. – Oat V Apr 20 '19 at 04:08
@OatV have you hashed the password in the database? – Nick Apr 20 '19 at 04:16
1

I haven't hashed it yet or should I do sth like this -> `$hash = password_hash($_POST['password'], PASSWORD_DEFAULT); ` ?? – Oat V Apr 20 '19 at 04:20
You will need to recreate all the password fields in the database with hashed passwords to make this work. Something along the lines of `$result = mysqli_query('SELECT admin_id, admin_password FROM admins'); while ($row = $result->fetch_array()) { $password = hash_password($row['admin_password']); mysqli_query("UPDATE admins SET admin_password = '$password' WHERE id='{$row['admin_id']}"); }` – Nick Apr 20 '19 at 04:23

Oat V · Answer 2 · 2019-04-20T04:49:24.483

login_check.php

<?php

    //Establish connection
    include 'connection.php';

    $sql = "SELECT * FROM admins WHERE admin_username = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("s",$_POST['username']);
    $stmt->execute();
    $result = $stmt->get_result();

    while ($row = $result->fetch_array())
    {
        $hash = password_hash($_POST['password'],PASSWORD_DEFAULT);
        mysqli_query($conn,"UPDATE admins SET admin_password = '$hash' WHERE admin_id='{$row['admin_id']}");
    }

    if(!$row || !password_verify($_POST['password'],$row['admin_password']))
    {
        ?>
        <div class="container">
            <h1>Username or Password is invalid!</h1>
           <form method="post" action="login.php">
                <button style="font-size: 30px" type="submit" class="btn btn-dark"><b>Back</b> <i class="fas fa-arrow-alt-circle-left"></i></button>
           </form>
        </div>
        <?php
    }
    else
    {
        $_SESSION["admin_id"] = $result["admin_id"];
        $_SESSION["admin_username"] = $result["admin_username"];
        session_write_close();
        header("location:front.php");
    }
    $stmt->close();
    $conn->close();
?>
</body>

There is no syntax error shown up but I think there are some mistakes in

=> mysqli_query($conn,"UPDATE admins SET admin_password = '$hash' WHERE admin_id ='{$row['admin_id']}");

=> if(!$row || !password_verify($_POST['password'],$row['admin_password']))

Form this code, the result is it always returns as username or password is invalid, even I put the valid ID and Password.

You should only update the passwords in the table once. You need to remove that code. Then you should just use the code I posted in my answer. — Nick, Apr 20 '19 at 05:10

How to change this part of code as PHP prepared statement?

2 Answers2