Jump to content



Photo

Chapter 13 Prepared Statements


  • Please log in to reply
10 replies to this topic

#1 hbphoto

hbphoto

    Member

  • Members
  • PipPip
  • 24 posts

Posted 27 August 2012 - 8:44 AM

Hello:

Would these statements be correct in inserting a hashed password using a bind variable?

$q = "INSERT INTO client (first_name, last_name, address, city, state, zip, phone, email, [b][color=#ff0000]pass[/color][/b], date_created)
		 VALUES (?, ?, ?, ?, ?, ?, ?, ?, [b][color=#ff0000]get_password_hash(?)[/color][/b], NOW() )";
 
$stmt = mysqli_prepare($dbc, $q);
 
mysqli_stmt_bind_param($stmt, 'sssssiis[color=#ff0000][b]s[/b][/color]', $fn, $ln, '$sa', '$c', '$st', '$z', '$ph', '$e', [b][color=#ff0000]'$p'[/color][/b]);
 
mysqli_stmt_execute($stmt);

Thanks for the help.
  • 0

#2 rob

rob

    Advanced Member

  • Members
  • PipPipPip
  • 133 posts

Posted 27 August 2012 - 11:02 AM

You need to remove the styling you've put in your code above, it's messed up.

Did you run this code?
  • 0

#3 Larry

Larry

    Administrator/Writer

  • Administrators
  • 3,540 posts
  • LocationState College, PA (USA)

Posted 27 August 2012 - 11:22 AM

Also depends upon the format of the hashed password: string or binary.
  • 0

#4 hbphoto

hbphoto

    Member

  • Members
  • PipPip
  • 24 posts

Posted 28 August 2012 - 11:00 AM

Hello:

My password field in the table is set as varbinary.

I removed the formmating from the code. The error message I received stated that only variables could be bound.

$q = "INSERT INTO client (first_name, last_name, address, city, state, zip, phone, email, pass, date_created)
		 VALUES (?, ?, ?, ?, ?, ?, ?, ?, get_password_hash(?), NOW() )";
 
$stmt = mysqli_prepare($dbc, $q);
 
mysqli_stmt_bind_param($stmt, 'sssssiiss', $fn, $ln, '$sa', '$c', '$st', '$z', '$ph', '$e', '$p');
 
mysqli_stmt_execute($stmt);


  • 0

#5 rob

rob

    Advanced Member

  • Members
  • PipPipPip
  • 133 posts

Posted 28 August 2012 - 11:15 AM

You're placing values in the prepared statement, remove these and replace with parameter markers.

Remove the single quotes around your variables where you're binding the parameter markers to your application variables.

Ensure you have the same number of application variables for the number of parameter markers.
  • 0

#6 hbphoto

hbphoto

    Member

  • Members
  • PipPip
  • 24 posts

Posted 28 August 2012 - 12:41 PM

I will give it a try.

As for the date, if I use NOW() in my VALUES of the insert statement, I don't include it as a bind variable. Would that be correct? Otherwise, how do I insert a date?

And, for the password, if I place a ? in the VALUES, in the bind statement how do I hash the password to be inserted?
  • 0

#7 rob

rob

    Advanced Member

  • Members
  • PipPipPip
  • 133 posts

Posted 28 August 2012 - 1:52 PM

Yeah, you can pass NOW() into the prepared statement as a non-bound parameter.

For the password, I would make this bound and assign the result of get_password_hash to a variable, adding it via mysqli_stmt_bind_param.
  • 0

#8 hbphoto

hbphoto

    Member

  • Members
  • PipPip
  • 24 posts

Posted 28 August 2012 - 2:08 PM

Ok, I'll give this a try.
  • 0

#9 hbphoto

hbphoto

    Member

  • Members
  • PipPip
  • 24 posts

Posted 29 August 2012 - 8:50 AM

I made some modifications to my code and when I run the script I'm receiving the following error message:

Fatal error: Only variables can be passed by reference in add_client.php on line 105.

Here is the code:
line 102:   $q = "INSERT INTO client (first_name, last_name, address, city, state, zip, phone, email, pass, date_created)
line 103: 		 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW() )";
line 104:   $stmt = mysqli_prepare($dbc, $q);
line 105:   mysqli_stmt_bind_param($stmt, 'sssssiiss', $fn, $ln, $sa, $c, $st, $z, $ph, $e, '"  .  get_password_hash($p) .  "');
line 106:   mysqli_stmt_execute($stmt);

Can someone help?
Thank you!
  • 0

#10 Antonio Conte

Antonio Conte

    Advanced Member

  • Members
  • PipPipPip
  • 1,007 posts
  • LocationOslo, Norway

Posted 29 August 2012 - 9:07 AM

Assign the result of get_password_hash($p) to a new variable before the query. Right now, you are just passing in a string.

$hash = get_password_hash($p);

....
mysqli_stmt_bind_param($stmt, 'sssssiiss', $fn, $ln, $sa, $c, $st, $z, $ph, $e, $hash);

  • 0

#11 hbphoto

hbphoto

    Member

  • Members
  • PipPip
  • 24 posts

Posted 29 August 2012 - 9:08 AM

I fixed the problem. Here's what I did.

I createda variable $pwd which hashes the password first. Then I referenced this new variable in the bind_param statement.

$pwd = get_password_hash($p);
	  
  $q = "INSERT INTO client (first_name, last_name, address, city, state, zip, phone, email, pass, date_created)
		 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW() )";
  $stmt = mysqli_prepare($dbc, $q);
  mysqli_stmt_bind_param($stmt, 'sssssiiss', $fn, $ln, $sa, $c, $st, $z, $ph, $e, $pwd);
  mysqli_stmt_execute($stmt);

Thanks to everyone for the help!
  • 0