Other Ways to Secure Our Database Passwords

Hiding the password from SQL

Now that we’re using a strong hashing function to encode the password before we store it, and we’re also using a salt to thwart dictionary attacks, we might think it enough to ensure security. But the password still appears in plain text in the SQL expression, which means that it’s readable if an attacker can intercept network packets or if SQL queries are logged, and the log files fall into the wrong hands.

We can protect against this kind of exposure by not putting the plain-text password into the SQL query. Instead, we can compute the hash in our application code, and use only this hash in the SQL query. It is not helpful for an attacker to intercept the hash because they can’t reverse it to get the password.

It is important to remember that we need the salt before we can compute the hash.

The following PHP example uses the PDO extension to get the salt, compute a hash, and run a query to validate the password against the salted hash stored in the database:

<?php
$password = 'xyzzy';
$stmt = $pdo->query(
"SELECT salt
FROM Accounts WHERE account_name = 'bill'");
$row = $stmt->fetch();
$salt = $row[0];
$hash = hash('sha256', $password . $salt);
$stmt = $pdo->query("
SELECT (password_hash = '$hash') AS password_matches; FROM Accounts AS a WHERE a.account_name = 'bill'");
$row = $stmt->fetch();
if ($row === false) { 
  // account 'bill' does not exist 
} else { 
  $password_matches = $row[0];
  if (!$password_matches) { 
    // password given was incorrect 
  } 
}
?>