How to apply password_hash for SELECT, INSERT and UPDATE?

Question

I know the password_hash works like this:

string password_hash ( string $password , integer $algo [, array $options ] )

1 - But I wonder how I can apply password_hash in such cases:

$check = mysql_query("SELECT `id` FROM `database`.`user` 
WHERE `password` = '".$password."'") or die(mysql_error());

$insert = mysql_query("INSERT INTO `database`.`user`(`username`,`password`,`email`)
VALUES ('".$username."','".$password."','".$email."')") or die(mysql_error());

$update = mysql_query("UPDATE `database`.`user` SET `password` = '".$newpassword."' 
WHERE `password` = '".$oldpassword."'") or die(mysql_error());

2 - After applying password_hash I’m still gonna need mysql_real_escape_string?

$username = mysql_real_escape_string($_POST['username']);
$password = mysql_real_escape_string($_POST['password']);
$email = mysql_real_escape_string($_POST['email']);

Answer 1

First of all, I recommend reading this question:

How to hash passwords securely?

Here are two examples of password_hash using mysqli and bind_param, dispensing with the need to do any kind of escape manually.

The bind param already automatically escapes the values:

Example password storage with password_hash:

$mysqli = new mysqli( 'enderecodoservidor', 'usuario', 'senha', 'basededados' );

$usuario = $_POST['usuario'];
$hash    = password_hash( $_POST['senha'], PASSWORD_DEFAULT );

$query = 'INSERT INTO usuarios ( nome, hash) VALUES ( ?, ? )';
$stmt = $mysqli->prepare( $query );
$stmt->bind_param("ss", $usuario, $hash );
$stmt->execute();

Example password update with password_hash:

$mysqli = new mysqli( 'enderecodoservidor', 'usuario', 'senha', 'basededados' );

$usuario = $_session['usuario']; // ou idusuario, depende como voce mantem o login
$hash    = password_hash( $_POST['novaSenha'], PASSWORD_DEFAULT );

$query = 'UPDATE usuarios SET hash= ? WHERE nome = ? '; // ou WHERE id = ?
$stmt = $mysqli->prepare( $query );
$stmt->bind_param("ss", $hash , $usuario ); // ou ("si", $hash, $idUsuario)
$stmt->execute();

Example password verification with password_hash:

$mysqli = new mysqli( 'enderecodoservidor', 'usuario', 'senha', 'basededados' );

$usuario = $_POST['usuario'];
$idUsuario = 0; // Isso se trabalhar com ID numérico, o que pode ser interessante

$query = 'SELECT id, hash FROM usuarios WHERE nome = ?';
$stmt = $mysqli->prepare( $query );
$stmt->bind_param("s", $usuario );
$stmt->execute();

$stmt->bind_result( $idUsuario, $hash ); // aqui tem que bater com os campos do select
$stmt->fetch();

if ( password_verify( $_POST['senha'], $hash ) ) {
   echo 'Logado';
} else {
   echo 'Usuario e/ou senha invalidos';
}

Leave a varchar() field wide enough for the password hash, not to run the risk of truncating the data.

See in this post how to update the hash of passwords automatically with new versions of PHP:

/a/147319/70

Answer 2

The password_hash who comes in the PHP 5 >= 5.5.0, could be used like this:

$password = password_hash($password);
$check = mysql_query("SELECT `id` FROM `database`.`user` 
WHERE `password` = '".$password."'") or die(mysql_error());

$password = password_hash($password);
$insert = mysql_query("INSERT INTO `database`.`user`(`username`,`password`,`email`)
VALUES ('".$username."','".$password."','".$email."')") or die(mysql_error());

$password = password_hash($password);
$oldpassword = password_hash($oldpassword);
$update = mysql_query("UPDATE `database`.`user` SET `password` = '".$newpassword."' 
WHERE `password` = '".$oldpassword."'") or die(mysql_error());

Observing: In my opinion I would use filter_vars or filter_input in place of those mysql_real_escape_string and also instead of using the deprecate mysql_*, use the PDO or Mysqli, because it will be discontinued in the newer versions of PHP, for several reasons one of them is security !!!

In that reply has a good explanation also on the part of Mysql.