How to use password verify on the login form php

Question

I used a login form where I was open to SQL injection and I am trying to fix it using prepared statements. I have no idea what is the problem with the code. I have never used a login fform with prepared statements before.

This is what I tried at first.

$query = "SELECT * FROM users WHERE username='$username' AND password='$password'";
$stmt = mysqli_prepare($db, $query);
mysqli_stmt_bind_param($stmt, 'ss', $username, $password);
$stmt->execute();
$stmt->bind_result($username, $password);

$hash = $password;
if (password_verify($password,$hash)) {
    $_SESSION['username'] = $username;
    exit(header('location: indexclient.php'));
}

and this is what I am trying now.

$stmt = $db->prepare("SELECT * FROM users WHERE username = ?");
$stmt->bind_param("s", $_POST['username']);
$stmt->execute();
$stmt->bind_result($hash);
$stmt->fetch();

if (password_verify($_POST['password'], $hash)) {
      $_SESSION['username'] = $username;
      exit(header('location: indexclient.php'));
} else {
    echo "Invalid login";
}
$stmt->close();

The error message I get with the last code is "Number of bind variables doesn't match number of fields in prepared statement".

Answer 1

You can't use the password in the WHERE clause (so your second approach is correct), as the hash will be different for each time you use it. Furthermore, you should always select the specific number of columns you need, as you need to define each and every one of them with bind_result(). It can work with SELECT *, but then you rely on bind_result() to cover all possible values - which will break if you suddenly add another column to your table.

$stmt = $db->prepare("SELECT * FROM users WHERE username = ?");
$stmt->bind_param("s", $_POST['username']);
$stmt->execute();
$result = $stmt->get_result();
$user = $result->fetch_assoc();

if ($user && password_verify($_POST['password'], $user['password'])) {
    $_SESSION['user_id'] = $user['id'];
    header('location: indexclient.php');
    exit;
} else {
  echo "Invalid login";
}
$stmt->close();