How to remove salted password from database and auth user?
This is my first trial to implement a member site with salted passwords all stored in a DB (MySQL). Everything works, except for an error on the Member Login page.
Error:
The login page accepts any entry on the membership site and for some reason passes my check to$result === false
This is the code to check if an item exists, let me know what the problem is:
$servername = 'localhost';
$username = 'root';
$pwd = '';
$dbname = 'lp001';
$connect = new mysqli($servername,$username,$pwd,$dbname);
if ($connect->connect_error){
die('connection failed, reason: '.$connect->connect_error);
}
$name = mysqli_real_escape_string($connect, $_POST['name']);
$password = mysqli_real_escape_string($connect, $_POST['password']);
$saltQuery = "SELECT salt FROM users WHERE name = '$name';";
$result = mysqli_query($connect, $saltQuery);
if ($result === false){
die(mysqli_error());
}
$row = mysqli_fetch_assoc($result);
$salt = $row['salt'];
$saltedPW = $password.$salt;
$hashedPW = hash('sha256', $saltedPW);
$sqlQuery = "SELECT * FROM users WHERE name = '$name' AND password = '$hashedPW'";
if (mysqli_query($connect, $sqlQuery)){
echo '<h1>Welcome to the member site '.$name.'</h1>';
}else{
echo 'error adding the query: '.$sql_q.'<br> Reason: '.mysqli_error($connect);
}
source to share
Often, developers struggle with login password validation because they are not sure how to handle the stored password hash. They know that the password must be hashed with an appropriate function like password_hash () and stored in a field varchar(255)
:
// 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 from SQL, nor can we search for it, because the stored hashes are salty. Instead, we ...
- need to read password hash from database, search by user id
- and then can verify the login password with the found hash using password_verify () .
Below you can find sample code showing how to perform password validation using mysqli . The code is error-free 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 this example uses prepared statements to avoid SQL injection, in which case no escaping is required. An example equivalent to reading from a pdo connection might look like:
/**
* 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);
}
source to share