How to retract a salted password from the Database and auth user?

Often developers struggle with the verification of a login password, because they are not sure how to handle the stored password hash. They know that the password should be hashed with an appropriate function like password_hash(), and store them in a varchar(255) field:

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

In the login form, we cannot verify the password directly with SQL, nor can we search for it, because the stored hashes are salted. Instead we…

1. have to read the password-hash from the database, searching by the user id
2. and afterwards can check the login password against the found hash with the password_verify() function.

Below you can find some example code, showing how to do password verification with an mysqli connection. The code has no error checking to make it readable:

/**
 * 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
$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);
}

Note that the example uses prepared statements to avoid SQL-injection, escaping is not necessary in this case. An equivalent example to read from a pdo connection could look like this:

/**
 * pdo example for a login with a stored password-hash
 */
$dsn = "mysql:host=$dbHost;dbname=$dbName;charset=utf8";
$pdo = new PDO($dsn, $dbUser, $dbPassword);

// Find the stored password hash in the db, searching by username
$sql="SELECT password FROM users WHERE username = ?";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(1, $_POST['username'], PDO::PARAM_STR); // it is safe to pass the user input unescaped
$stmt->execute();

// If this user exists, fetch the password hash and check it
$isPasswordCorrect = false;
if (($row = $stmt->fetch(PDO::FETCH_ASSOC)) !== false)
{
  $hashFromDb = $row['password'];

  // 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);
}