I've been having trouble with storing a password_hash into my MySql database. It's cutting off characters from the beginning and some in between.
I've tried setting the encoding to UTF 8 in the HTML, setting the same in the connect function and also I've changed the collation to every possible type. Yet I am able to paste the correct hash from an echo into the database and that works fine.
$password = $_POST['pno'];
$pno = password_hash($password,PASSWORD_DEFAULT);
MySqlDb::query('INSERT INTO user (name, email, pno, address_line_1, address_line_2, town, county, post_code, phone) VALUES (:name, :email, :pno, :address_line_1, :address_line_2, :town, :county, :post_code, :phone)',
[':name'=>$name, ':email'=>$email, ':pno'=>$pno, ':address_line_1'=>$address_line_1, ':address_line_2'=>$address_line_2, ':town'=>$town, ':county'=>$county, ':post_code'=>$post_code, ':phone'=>$phone]);
echo $pno;
So the echo will give me this: $2y$10$rtlUaDeXsyhtWS5.SS4nuu.xapBdrHXG7V.DpSLCLAAwYqXPJHKWi
But in the database it stores this: y$rtlUaDeXsyhtWS5.SS4nuu.xapBdrHXG7V.DpSLCLAAwYqXPJHKWi
I've also tried the encode and decode functions, pretty much out of ideas.
class MySqlDb {
/**
* @var MySQLi instance
*/
protected static $link;
/**
* @var bool set to true to print query before executing
*/
public static $debug = false;
/**
* Creates a new MySQLi instance use getConnection() to retrieve
* @return none
*/
private function __construct() {
self::$link = @mysqli_connect(DATABASE_HOST, DATABASE_USERNAME, DATABASE_PASSWORD, DATABASE_NAME);
if (mysqli_connect_errno()) {
//die('MySqlDb Error: Could not connect to database ('.mysqli_connect_errno().')');
throw new Exception('MySqlDb::() Could not connect to database (' . mysqli_connect_errno() . ')');
}
}
/**
* Returns the MySQLi connection
* @return MySQLi instance
*/
public static function getConnection() {
if (!self::$link) {
new MySqlDb();
}
return self::$link;
}
/**
* Helper function for mysqli_query if params are given will use quote function
* see below MySqlDb:quote this is to help avoid sql injection attacks and automatically escape slashes etc
*
* @param string $sql
* @param array $params
* @return mysqli_query resource ...
*
* Example:
* MySqlDb::query("drop from user where id=$_GET['id']"); // very bad 'id' could contain "' or 1=1"
* MySqlDb::query("drop from user where id=:id", [':id' => $_GET['id']]); // much better
*/
public static function query($sql, array $params = null) {
$con = self::getConnection();
if (!empty($params)) {
$sql = self::quote($sql, $params);
}
if (self::$debug) {
echo $sql;
}
$res = mysqli_query($con, $sql);
if (!$res) {
//die('MySqlDb Error: query: '.mysqli_error($con));
throw new Exception('MySqlDb::query() ' . mysqli_error($con));
}
return $res;
}
/**
* Helper function to quote sql values similar to pdo::bindvalue using named parameters
* to help avoid sql injection attacks and avoid using stuff like addslashes
*
* @param string $sql sql string to quote
* @param array $params key value pairs of parameters and values to placed into them
* @return string sql quoted string
*
* Example:
* $sql = MySqlDb::quote("select * from users where id=:unique1 or id=:unique2", [':unique1'=>2, ':unique2'=>4])
* TODO:
* MySqlDb::quote("select * from users where id+5 = :id or id = :id",[':id'=>3] fails on number params dont match like pdo
* could convert 'substr_count($sql, ':')' into '$unique_sql_param_count'
* e.g. preg_match_all("/:[\w-_]+\b/i", $test, $matches); $paramcount = count(array_flip($matches[0]));
*/
public static function quote($sql, array $params) {
// check correct number of params
if (substr_count($sql, ':') != count($params)) {
//die('MySqlDb Error: quote: number params do not match');
throw new Exception('MySqlDb::quote() number params do not match');
}
$cnt = 0;
foreach ($params as $param => $value) {
//$sql = str_replace($param, "'".self::escape($value)."'", $sql, $cnt); // was matching sub strings :(
$sql = preg_replace("/$param\b/i", "'" . self::escape($value) . "'", $sql, -1, $cnt);
if ($cnt !== 1) {
//die("MySqlDb Error: quote: param '{$param}' not matched or is duplicate");
throw new Exception("MySqlDb::quote() param '{$param}' not matched or is duplicate");
}
}
return $sql;
}
/**
* Wrapper for mysqli_escape_string
* @param mixed $value
* @return escaped value
*/
public static function escape($value) {
return mysqli_escape_string(self::getConnection(), $value);
}
/**
* Helper function to return all rows in result as an associative array
* @param string $sql
* @return array array of arrays
*/
public static function all($sql, array $params = null) {
return mysqli_fetch_all(self::query($sql, $params), MYSQLI_ASSOC);
}
/**
* Helper function to return first row in result from potential multiple values
* @param string $sql
* @return array single array
*/
public static function first($sql, array $params = null) {
$res = mysqli_fetch_array(self::query($sql, $params), MYSQLI_ASSOC);
return is_array($res) ? $res : [];
}
/**
* Helper function to return first field in result from potential multiple values
* @param string $sql
* @return mixed single variable
*/
public static function scalar($sql, array $params = null) {
$res = mysqli_fetch_array(self::query($sql, $params), MYSQLI_NUM);
return is_array($res) ? $res[0] : null;
}
}
This line in your quote method is the direct cause of the problem.
$sql = preg_replace("/$param\b/i", "'" . self::escape($value) . "'", $sql, -1, $cnt);
The result of self::escape($value) will be your original hash,
$2y$10$rtlUaDeXsyhtWS5.SS4nuu.xapBdrHXG7V.DpSLCLAAwYqXPJHKWi
In the context of preg_replace, the $2 and $10 are meaningful. In the Parameters section of the preg_replace documentation it says
replacement may contain references of the form or $n, with the latter form being the preferred one. Every such reference will be replaced by the text captured by the n'th parenthesized pattern.
You have no parenthesized patterns, so those values are replaced with nothing.
You could fix it by escaping any $ in the replacement parameter, but I would rather recommend replacing your escape/replace-based quote method with a method that creates a prepared statement and binds the parameters before executing it.