Insert password hash using PDO Prepared Statements

Question

In a basic mysql insert you are able to set a password variable 'PASSWORD($password)' but this breaks a PDO statement.

How do you hash the password while using pdo::prepare and pdo::execute?

$sql= "INSERT INTO contractors (userid, password, name) VALUES ('$userid', '$pass1', '$name')";
$result = $dbh->prepare($sql);
$count = $result->execute();

Echo $count."<br>";

I am such a n00b, a simple registration page has taken me two days. Kindergarten answers are welcomed.

thanks,

Answer 1

Note

This answer originally recommended an unsalted hash. That's silly nowadays, so it's been rewritten to bring it into the modern era. Watch out for similar now-bad answers in older content here on SO.

You're using PDO, so you should be using parameterized queries with placeholders:

$sql= "INSERT INTO contractors (userid, password, name) VALUES (?, ?, ?)";
$result = $dbh->prepare($sql);
$count = $result->execute(array($userid, $pass1, $name));

echo $count."<br>";

In the modern era, you should us Blowfish/bcrypt instead of MD5 or SHA1. As of PHP 5.3, you can use crypt with the $2y$ prefix. As of PHP 5.5, you'll be able to use password_hash instead. You can use ircmaxell's password_compat library in the mean time.

Here's a demo using crypt and a very low difficulty value. Note that we're also storing the salt. While I've hard-coded the salt here in this demo, you should use a unique salt per user.

$salt = 'saltysaltsaltsalt'; 
$password_hash = crypt($pass1, '$2a$07$' . $salt);
$sql= "INSERT INTO contractors (userid, password, salt, name) VALUES (?, ?, ?, ?)";
$result = $dbh->prepare($sql);
$count = $result->execute(array($userid, $password_hash, $salt, $name));

echo $count."<br>";

Verifying the password is as easy as reconstructing the hash using the same parameters.

$sth = $dbh->prepare('SELECT password, salt FROM contractors WHERE userid = ?');
$sth->execute(array($userid));
list($existing_hash, $salt) = $sth->fetch(PDO::FETCH_NUM);
unset($sth);

$new_hash = crypt($pass1, '$2a$07$' . $salt);
if($new_hash === $existing_hash) {
    echo "Password matched.";
} else {
    echo "Password did not match.";
}