PHP PDO PASSWORD HASH?

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

If you wanted to hash using MD5, you could do the following with the password before constructing the SQL statement:

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

echo $count."<br>";

The idea is the same even if it is another hash function. Hash the password before constructing the SQL statement.

As Fiarr and VoteyDisciple have noted in the comments below, opt for a SHA hash as it is more secure.

sha1()

Answer 2

<?php
try {
  $dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
  $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $stmt = new PDOStatement();

  $hash = sha1($pass . $dataUniqueToEachUser);
  $stmt = $dbh->prepare("INSERT INTO Users(name, email, hash) VALUES (:name, :email, :hash)");
  $stmt->bindParam(':name', $_POST['username'], PDO::PARAM_STR);
  $stmt->bindParam(':email', $_POST['email'], PDO::PARAM_STR);
  $stmt->bindParam(':hash', $hash, PDO::PARAM_STR);
  $stmt->execute();

  if ($stmt->rowCount() == 0) {
   $valid = true;
  }
}
catch (PDOException $e) {
  echo "An error occurred: {$e}";
}
?>

Answer 3

Hi,

It's perhaps more safe if you create your own hash (and perhaps more simple tough). The best sollution, in my oppinion, is to create a hash by joining several fields from the table, like this:

$pass1 = sha1($pass1.$name);

Note: md5, as suggested above, isn't the saffest sollution, since currently there's a lot of databases over the internet with keywords associated to md5 hash variables, making it easier to someone who wants to hack your system.

Answer 4

You're using PDO, so you should be using query placeholders.

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

echo $count."<br>";

As mentioned by others, you should be using something like MD5 or SHA1, preferably SHA1 due to MD5 being not-unreasonable to work around nowadays. You can do this before, or after. Here's how you'd do it before inserting:

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

echo $count."<br>";

And here's how you'd do it during the insert:

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

echo $count."<br>";

There are lots of other things to consider as well, such as salts and other tricks.

Answer 5

Using variable substitution like that makes your app wide open to SQL injection attacks, unless you are escaping $userid and the lot before the code you pasted.

Better to use PDO's substitution abilities (I mean, you're already calling .prepare):

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

I'm not sure if inlining PASSWORD() like that will work -- please correct me if I'm wrong. If you want to take Alan's approach and not rely on MySQL to hash your passwords (wise), you can also do something like this:

$sql = "INSERT INTO contractors (userid, password, name) VALUES (?, ?, ?)";
$query = $dbh->prepare($sql);
$dbh->execute(array($userid, sha1($hashed), $name));

Consider using a salt as well:

$hashed = sha1("SaltedPassword" . $pass1);