Jump to content
Larry Ullman's Book Forums

Prepared Statements Not Producing Same Results As Standard Sql Query


Recommended Posts

Hello,

 

I have been writing a login script, which was failing and then I got it to work. But i'm having trouble understanding a couple of occurrences. If i register using this script:

 


if($_SERVER['REQUEST_METHOD'] == 'POST'){


// check for first name
// allows letters, a space, full stop, apostrophe, hyphen
if (preg_match ('/^[A-Z \'.-]{2,20}$/i', $_POST['first_name'])) {
$fn = mysqli_real_escape_string ($dbc, $_POST['first_name']);
} else {
$errors['first_name'] = 'Please enter your first name!';
}

// check for last name
if(preg_match('/^[A-Z\'.-]{2,40}$/i', $_POST['last_name'])) {
$ln = mysqli_real_escape_string($dbc, $_POST['last_name']);
} else {
$errors['last_name'] = 'Please enter your last name';
}

// check email address
if(filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)) {
$e = mysqli_real_escape_string($dbc, $_POST['email']);
} else {
$errors['email'] = 'Please enter a valid email address';
}
// check for company
if(preg_match('/^[A-Z\'.-]{2,40}$/i', $_POST['company'])) {
$comp = mysqli_real_escape_string($dbc, $_POST['company']);
} else {
$errors['company'] = 'Please enter your company name or state individual';
}
// check for a password and match against the confirmed password:
if (preg_match ('/^(\w*(?=\w*\d)(?=\w*[a-z])(?=\w*[A-Z])\w*){6,20}$/', $_POST['pass1']) ) {
if ($_POST['pass1'] == $_POST['pass2']) {
$p = mysqli_real_escape_string($dbc, $_POST['pass1']);
} else {
$errors['pass2'] = 'Your password did not match the confirmed password!';
}
} else {
$errors['pass1'] = '6-20 characters long with at least 1 lower-case letter, 1 upper-case letters & 1 number';
}

if(empty($errors)) {
$q = "SELECT `email` FROM `users` WHERE `email`='$e'";
$r = mysqli_query($dbc, $q);
$rows = mysqli_num_rows($r);
if($rows === 0){ // No users exist

$active = md5(uniqid(rand(), true));
$query = "INSERT INTO `users` (`first_name`, `last_name`, `email`, `company`, `pass`, `active`) VALUES ('$fn', '$ln', '$e', '$comp', '" . passwordHash($p) . "', '$active')";
$r = mysqli_query($dbc, $query);

if(mysqli_affected_rows($dbc) == 1) {
echo '<div id="content">
<div class="register">
<h3>Thanks For Registering</h3><p>You have been succesfully registered please check your email account for an activation email.</p>
</div></div>';
// call activation email script
// pass name, email and firstname to script
activationEmail($active, $fn, $e);
//include footer
include('include/footer.inc.php');
echo '</body></html>';
exit();
} else { // query failed
trigger_error('<div id="content"><div class="register"><p class="error">You Could not be registered at this time due to a system error. We apologize for any inconvenience</p></div></div>');
}
} else { // email in use
$row = mysqli_fetch_array($r, MYSQLI_NUM);
if($row[0] == $_POST['email']){
$errors['email'] = ' This email is already registered,<a href="' . BASE_URL . 'forgotten-password"> forgot password</a>';
}
} // end of email registered already
}
}

I am registered just fine and if I try to login using this script I can log in.


$errors = array();

// validate the email address
if(filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)) {
$e = mysqli_real_escape_string($dbc, $_POST['email']);
} else {
$errors['email'] = 'Please enter a valid email address';
}

// validate password
if(!empty($_POST['pass'])) {
$p = mysqli_real_escape_string($dbc, $_POST['pass']);
} else {
$errors['pass'] = 'Please enter your password';
}

// if there are no errors
if(empty($errors)) {
// execute query

$q = "SELECT id, first_name, type FROM users WHERE (email='$e' AND pass='" . passwordHash($p) . "')";
$r = mysqli_query ($dbc, $q);

if (mysqli_num_rows($r) == 1) {

$row = mysqli_fetch_array($r, MYSQLI_NUM);
$_SESSION['user_id'] = $row[0];
$_SESSION['first_name'] = $row[1];


} else { // not valid credentials
$errors['login'] = 'The email and passwords do not match those on file';
}


} // end of login

 

But originally I used prepared statements to register and I noticed that when I use prepared statements it sets the DB password value differently to that of the non-prepared statements query. They are the same password but one appears as:

// This is the prepared statement

b39d2b5c22502dceda406d1f6b169fce37b687d9202d0328765e473d724dd51d

and the other appears as

 

// This is the standard query

b39d2b22502dceda406d1f6b169fce37b687d9202d0328765e473d724dd51dcf

 

My prepared statements script to register (which works fine in that it registers me) is:


if($_SERVER['REQUEST_METHOD'] == 'POST'){

// prepare databse
$query = "INSERT INTO `users` (`first_name`, `last_name`, `email`, `company`, `pass`, `active`) VALUES (?, ?, ?, ?, ?, ?)";
$stmt = mysqli_prepare($dbc, $query);
mysqli_stmt_bind_param ($stmt, 'ssssss', $fn, $ln, $e, $comp, $p, $active);


// check for first name
// allows letters, a space, full stop, apostrophe, hyphen
if (preg_match ('/^[A-Z \'.-]{2,20}$/i', $_POST['first_name'])) {
$fn = mysqli_real_escape_string ($dbc, $_POST['first_name']);
} else {
$errors['first_name'] = 'Please enter your first name!';
}

// check for last name
if(preg_match('/^[A-Z\'.-]{2,40}$/i', $_POST['last_name'])) {
$ln = mysqli_real_escape_string($dbc, $_POST['last_name']);
} else {
$errors['last_name'] = 'Please enter your last name';
}

// check email address
if(filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)) {
$e = mysqli_real_escape_string($dbc, $_POST['email']);
} else {
$errors['email'] = 'Please enter a valid email address';
}
// check for company
if(preg_match('/^[A-Z\'.-]{2,40}$/i', $_POST['company'])) {
$comp = mysqli_real_escape_string($dbc, $_POST['company']);
} else {
$errors['company'] = 'Please enter your company name or state individual';
}
// check for a password and match against the confirmed password:
if (preg_match ('/^(\w*(?=\w*\d)(?=\w*[a-z])(?=\w*[A-Z])\w*){6,20}$/', $_POST['pass1']) ) {
if ($_POST['pass1'] == $_POST['pass2']) {
$p = mysqli_real_escape_string($dbc, $_POST['pass1']);
$p = passwordHash($p);
} else {
$errors['pass2'] = 'Your password did not match the confirmed password!';
}
} else {
$errors['pass1'] = '6-20 characters long with at least 1 lower-case letter, 1 upper-case letters & 1 number';
}

if(empty($errors)) {
$q = "SELECT `email` FROM `users` WHERE `email`='$e'";
$r = mysqli_query($dbc, $q);
$rows = mysqli_num_rows($r);
if($rows === 0){ // No users exist

$active = md5(uniqid(rand(), true));

// execute query
mysqli_stmt_execute($stmt);

if(mysqli_stmt_affected_rows($stmt) == 1) {
echo '<div id="content">
<div class="register">
<h3>Thanks For Registering</h3><p>You have been succesfully registered please check your email account for an activation email.</p>
</div></div>';
// call activation email script
// pass name, email and firstname to script
activationEmail($active, $fn, $e);
//include footer
include('include/footer.inc.php');
echo '</body></html>';
exit();
} else { // query failed
trigger_error('<div id="content"><div class="register"><p class="error">You Could not be registered at this time due to a system error. We apologize for any inconvenience</p></div></div>');
}
} else { // email in use
$row = mysqli_fetch_array($r, MYSQLI_NUM);
if($row[0] == $_POST['email']){
$errors['email'] = ' This email is already registered,<a href="' . BASE_URL . 'forgotten-password"> forgot password</a>';
}
} // end of email registered already
}
// clode statement
mysqli_stmt_close($stmt);

// close connection
mysqli_close($dbc);
} // end of submitted form

And this script I used to login which fails is


$errors = array();

// login query
$q = "SELECT id, first_name, type FROM users WHERE (email=? AND pass=?)";
$stmt = mysqli_prepare($dbc, $q);
mysqli_stmt_bind_param($stmt, 'ss', $e, $p);

// validate the email address
if(filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)) {
$e = mysqli_real_escape_string($dbc, $_POST['email']);
} else {
$errors['email'] = 'Please enter a valid email address';
}

// validate password
if(!empty($_POST['pass'])) {
$p = mysqli_real_escape_string($dbc, $_POST['pass']);
$p = passwordHash($p);
} else {
$errors['pass'] = 'Please enter your password';
}

// if there are no errors
if(empty($errors)) {
// execute query
mysqli_stmt_execute($stmt);

if(mysqli_stmt_num_rows($stmt) == 1){ // query worked
$row = mysqli_fetch_array($r, MYSQLI_NUM);
$_SESSION['user_id'] = $row[0];
$_SESSION['first_name'] = $row[1];


} else { // not valid credentials
$errors['login'] = 'The email and passwords do not match those on file';
}

// close statement
mysqli_stmt_close($stmt);

//close connection
mysqli_close($dbc);

} // end of login

 

I'd like to use prepared statements but I'm at a loss really as to why the prepared statements query inserts a different hashed representation of the password and subsequently why I don't seem to be able to login using prepared statements. I understand why If i mixed and matched between SQL and prepared SQL as the hash is not the same. (I apologise for the length of code too).

Link to comment
Share on other sites

My instinct is that your passwordHash() function, not shown in your code, returns binary data, not a string. In your regular query, the binary data is inserted in the query, and therefore in the database, as binary data. In your prepared statement, you treat the password as a string, not as binary data. Thus the conversion and the disconnect with the login.

Link to comment
Share on other sites

Hi Larry,

 

Thanks, i see, I did wonder about that. My passwordHash() is basically the same as your function

function get_password_hash($password) {

// Need the database connection:
global $dbc;

// Return the escaped password:
return mysqli_real_escape_string ($dbc, hash_hmac('sha256', $password, 'c#haRl891', true));

}

in the ecommerce example but I have a different 'key' within the hash where yours is 'c#haRl891'.

 

I tried:


$query = "INSERT INTO `users` (`first_name`, `last_name`, `email`, `company`, `pass`, `active`) VALUES (?, ?, ?, ?,passwordHash(?), ?)";
$stmt = mysqli_prepare($dbc, $query);
mysqli_stmt_bind_param ($stmt, 'ssssbs', $fn, $ln, $e, $comp, $p, $active); // altered the 's' to 'b' for binary

 

which returns an error saying the number of variables doesn't match, so I tried to concatenate it like (?, ?, ?, ?,' . passwordHash(?) . ', ?)"; and it throws an "Number of variables doesn't match number of parameters in prepared statement error"

 

So i guess my question is how do you write a prepared statement query to accept a function call within a query. I did Google it and someone somewhere basically hashed the string before it got inserted into the DB. Which i suppose works, but it is the source of my errors.

Link to comment
Share on other sites

Okay, so, yes, your function is returning binary data. You can use binary data in a prepared statement, but you have to use the b flag, not s. And, no, you can't call a function as the value for a prepared statement, because the placeholders are bound to variables. And you can't put a PHP function call in the query, even concatenated, as you discovered. The solution is simpler than you think though: bind the password to a variable as you normally would, and before you execute the statement, assign the result of the passwordHash() function call to that variable.

Link to comment
Share on other sites

Hi Larry, thanks for your input, i do appreciate it, I changed my query to


// prepare databse
$query = "INSERT INTO `users` (`first_name`, `last_name`, `email`, `company`, `pass`, `active`) VALUES (?, ?, ?, ?, ?, ?)";
$stmt = mysqli_prepare($dbc, $query);
mysqli_stmt_bind_param ($stmt, 'ssssbs', $fn, $ln, $e, $comp, $p, $active);

 

Retrieved the password as normal using


// check for a password and match against the confirmed password:
if (preg_match ('/^(\w*(?=\w*\d)(?=\w*[a-z])(?=\w*[A-Z])\w*){6,20}$/', $_POST['pass1']) ) {
if ($_POST['pass1'] == $_POST['pass2']) {
$p = mysqli_real_escape_string($dbc, $_POST['pass1']);
} else {
$errors['pass2'] = 'Your password did not match the confirmed password!';
}
} else {
$errors['pass1'] = '6-20 characters long with at least 1 lower-case letter, 1 upper-case letters & 1 number';
}

 

And then executed the query like so:


if(empty($errors)) {
$q = "SELECT `email` FROM `users` WHERE `email`='$e'";
$r = mysqli_query($dbc, $q);
$rows = mysqli_num_rows($r);
if($rows === 0){ // No users exist

$active = md5(uniqid(rand(), true));
$p = passwordHash($p);
// execute query
mysqli_stmt_execute($stmt);

if(mysqli_stmt_affected_rows($stmt) == 1) {
// rest of code

 

It says i've registered but on inspection of the DB the password column is empty. I tried doing this earlier and it was empty then so I just assumed and still do that this wrong.

I echoed the variables out at each stage too, after I first got $p and ran it through mysqli_real_escape_Sting and its value was as expected and then i echoed out $p after the hash and it returned some weird characters which i believe to be a binary representation ��+\"P-��@mk��7��� -(v^G=rM��

Link to comment
Share on other sites

First of all, you don't want to run the password through the escaping function. For one, you don't need to do that when using prepared statements (which is to say, you shouldn't do that when using prepared statements). For two, the hashing function will create a binary hash version of the password with the escapes.

 

What is your password column in the database defined as?

Link to comment
Share on other sites

Hi Larry my password column is called `pass` and is defined as varbinary(32) NOT NULL.

 

From what you've said I've changed the code so that I don't run the value of the password form input through the mysqli_real_escape_string.



if (preg_match ('/^(\w*(?=\w*\d)(?=\w*[a-z])(?=\w*[A-Z])\w*){6,20}$/', $_POST['pass1']) ) {
if ($_POST['pass1'] == $_POST['pass2']) {
$p = $_POST['pass1'];
} else {
$errors['pass2'] = 'Your password did not match the confirmed password!';
}
} else {
$errors['pass1'] = '6-20 characters long with at least 1 lower-case letter, 1 upper-case letters & 1 number';
}

 

I shall also remove / alter the rest of my registration form validation so that I don't run the $_POST variables through the mysqli_real_escape_string.

 

I also take it I should alter my password function so that it looks like:


function passwordHash($password) {

// the database connection
global $dbc;

// return the escaped password
return hash_hmac('sha256', $password, 'c#haRl891', true);

} // end of passwordHash() function

 

Is that what you wanted me to do?

 

I still then hashed the $_POST['pass1'] before executing the query though. As i thought that it was the right thing to do, but the password field was still blank upon registering

Link to comment
Share on other sites

This is the SQL structure for my table in case anyone wanted to know


CREATE TABLE IF NOT EXISTS `users` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `type` varchar(6) NOT NULL DEFAULT 'member',
 `company` varchar(50) NOT NULL,
 `email` varchar(80) NOT NULL,
 `pass` varbinary(32) NOT NULL,
 `first_name` varchar(20) NOT NULL,
 `last_name` varchar(40) NOT NULL,
 `active` char(32) DEFAULT NULL,
 `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `date_modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 PRIMARY KEY (`id`),
 UNIQUE KEY `email` (`email`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

 

And the code for my registration script is:


// validate registration
// did you submit form
if($_SERVER['REQUEST_METHOD'] == 'POST'){

// prepare databse to insert
$query = "INSERT INTO `users` (`first_name`, `last_name`, `email`, `company`, `pass`, `active`) VALUES (?, ?, ?, ?, ?, ?)";
$stmt = mysqli_prepare($dbc, $query);
mysqli_stmt_bind_param ($stmt, 'ssssbs', $fn, $ln, $e, $comp, $p, $active);

// check for first name
// allows letters, a space, full stop, apostrophe, hyphen
if (preg_match ('/^[A-Z \'.-]{2,20}$/i', $_POST['first_name'])) {
$fn = $_POST['first_name'];
} else {
$errors['first_name'] = 'Please enter your first name!';
}

// check for last name
if(preg_match('/^[A-Z\'.-]{2,40}$/i', $_POST['last_name'])) {
$ln = $_POST['last_name'];
} else {
$errors['last_name'] = 'Please enter your last name';
}

// check email address
if(filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)) {
$e = $_POST['email'];
} else {
$errors['email'] = 'Please enter a valid email address';
}
// check for company
if(preg_match('/^[A-Z\'.-]{2,40}$/i', $_POST['company'])) {
$comp = $_POST['company'];
} else {
$errors['company'] = 'Please enter your company name or state individual';
}
// check for a password and match against the confirmed password:
if (preg_match ('/^(\w*(?=\w*\d)(?=\w*[a-z])(?=\w*[A-Z])\w*){6,20}$/', $_POST['pass1']) ) {
if ($_POST['pass1'] == $_POST['pass2']) {
$p = passwordHash($_POST['pass1']);
} else {
$errors['pass2'] = 'Your password did not match the confirmed password!';
}
} else {
$errors['pass1'] = '6-20 characters long with at least 1 lower-case letter, 1 upper-case letters & 1 number';
}

if(empty($errors)) {

// prepare emal query if email is in DB
$q = "SELECT `email` FROM `users` WHERE `email`='$e'";
$r = mysqli_query($dbc, $q);

if (mysqli_num_rows($r) === 0) { // no user exists

$active = md5(uniqid(rand(), true)); // create activation code

mysqli_stmt_execute($stmt);

if(mysqli_stmt_affected_rows($stmt) === 1) {
echo '<div id="content">
<div class="register">
<h3>Thanks For Registering</h3><p>You have been succesfully registered please check your email account for an activation email.</p>
</div></div>';
// call activation email script
// pass name, email and firstname to script
activationEmail($active, $fn, $e);
//include footer
include('include/footer.inc.php');
echo '</body></html>';
exit();
} else { // query failed
trigger_error('<div id="content"><div class="register"><p class="error">You Could not be registered at this time due to a system error. We apologize for any inconvenience</p></div></div>');
}
} else { // email in use
$row = mysqli_fetch_array($r, MYSQLI_NUM);
if($row[0] == $_POST['email']){
$errors['email'] = ' This email is already registered,<a href="' . BASE_URL . 'forgotten-password"> forgot password</a>';
}
} // end of email registered already
}
// clode statement
mysqli_stmt_close($stmt);

// close connection
mysqli_close($dbc);
} // end of submitted form

?>
//***THE FROM IS THIS TOO******//

<form action="<?php echo BASE_URL; ?>register" method="post" accept-charset="utf-8">
<p><label>First Name:</label><input type="text" name="first_name" id="first_name" value="<?php if(isset($_POST['first_name'])) { echo $_POST['first_name']; } ?>" />
<span><?php if (isset($errors['first_name'])) { echo $errors['first_name']; } ?></span></p>
<p><label>Last Name:</label><input type="text" name="last_name" id="last_name" value="<?php if(isset($_POST['last_name'])) { echo $_POST['last_name']; } ?>" />
	 <span><?php if (isset($errors['last_name'])) { echo $errors['last_name']; } ?></span></p>
<p><label>Email:</label><input type="text" name="email" id="email" value="<?php if(isset($_POST['email'])) { echo $_POST['email']; } ?>"/>
	 <span><?php if (isset($errors['email'])) { echo $errors['email']; } ?></span></p>
<p><label>Company:</label><input type="text" name="company" id="company" value="<?php if(isset($_POST['company'])) { echo $_POST['company']; } ?>"/>
	 <span><?php if (isset($errors['company'])) { echo $errors['company']; } ?></span></p>
<p><label>Password:</label><input type="password" name="pass1" id="pass" />
	 <span><?php if(isset($errors['pass1'])) { echo $errors['pass1']; } ?></span></p>
<p><label>Confirm Password:</label><input type="password" name="pass2" id="pass2" />
	 <span><?php if(isset($errors['pass2'])) { echo $errors['pass2']; } ?></span></p>	
<p><input type="submit" name="submit" value="Register" /></p>	
</form>

 

I just looked at it again and still no password gets submitted. I echoed out the value of $p also just before the statement is executed and it had a value of "��+\"P-��@mk��7��� -(v^G=rM��" so at the point of execution it does have a value. I'm really stumped.

Link to comment
Share on other sites

Hi Antonio,

Yeah I know 'b' is for blob, but the passwordHash I'm using returns binary data. It inserts a hex hash when I use a standard SQL query, which i suppose is a fail safe, but I want to use prepared statements. This is the password hash by the way, I think its posted earlier on too


function passwordHash($password) {

// the database connection
global $dbc;

// return the escaped password
return hash_hmac('sha256', $password, 'c#haRl891', true);

} // end of passwordHash() function

Link to comment
Share on other sites

I echoed out strlen($p) after I has $p and it returned 32 as the length, I then altered the varbinary field to (1000) in length and still the same result. :wacko:

 

To Update: The only way I seem to be able to insert data into the pword field is by setting the data-type to 's' not 'b'. I compared inserting a password like this against a standard query and the result is that the prepared statement hash of the exact same password is 2 characters shorter.

 

To further update: I have used the prepared statement above (in the full code) but changed the 'blob' to a 'string' this allowed a password to be registered in the field. Which suggests to me that standard SQL inserts binary as a string. Because looking at the my old database for Larry's ecommerce 1 example the passwords are stored as 32 character long hexadecimal hashes. Now I'm not sure whether that means that the prepared statements insertion should be stored this way, but I don't really understand why the standard vs the prepared way should differ in how they are stored??

 

But what I did then was this short basic code, which is documented so you can follow:


// inserted password hash from registering, taken from DB
$inserted = 'd1ca230e50b724f50c9eb8e2e8bf17e0431dd51c6f277c784ea081d513d927ab';

echo $inserted;
echo '<br />';

// hardcode the password
$p = 'passworD5';
// run the password through the hash
$p = passwordHash($p);
// what length is returned
echo 'The password is ' . strlen($p) . ' long';
echo '<br />';
// convert $p from binary to hex representational to compare visually
echo bin2hex($p);
// convert $p to be hex representation
$p = bin2hex($p);
echo '<br />';

// check if inserted value is implicitly equal to $p value that is generated in the  script
if($inserted === $p) {
echo 'Yes - we are equal';
} else {
echo 'No - we aren\'t equal';
}

echo '<br />';
if((strcmp($inserted, $p) === 0)) {
echo 'Case sensitive match';
} else {
echo 'Case sensitive fail';
}

 

Now that returned:

d1ca230e50b724f50c9eb8e2e8bf17e0431dd51c6f277c784ea081d513d927ab

The password is 32 long

d1ca230e50b724f50c9eb8e2e8bf17e0431dd51c6f277c784ea081d513d927ab

Yes - we are equal

Case sensitive match

Link to comment
Share on other sites

I'm not sure what the problem is here, but one reliable solution would be to change the function so it returns a string, change the database so that it returns a string, and then use the s flag in the statement. There's a minor performance benefit to using binary data here, but that's it, really.

Link to comment
Share on other sites

I didn't realise you'd commented :lol: , you may be able to tell from my previous post that this is the route I am gearing towards. so if I change the function to

 

function passwordHash($password) {


// the database connection
global $dbc;

// return the escaped password
return hash_hmac('sha256', $password, 'c#haRl891');

} // end of passwordHash() function

 

Change the 's' flag as mentioned above.

 

Change the database field of `pass` to a VARCHAR and what length would you advise (64)?? Is that the maximum it can return?

 

I should be all good?

Link to comment
Share on other sites

That's no problem, I obviously spent quite a lot time looking up prepared statements and data types for MYSQL and I never found anything at all. Maybe there's some little quirk in my XAMPP or something. But thanks for your help, good to get it sorted (I hope) as I like prepared statements :)

Link to comment
Share on other sites

 Share

×
×
  • Create New...