[chino]

Hey,

I am trying to use the MySQL UPDATE query to update the database. It is not updating the table as required and I am getting the error: "Number of variables doesn't match number of parameters in prepared statement in...". Are there any examples of how it should be written? I have the following code:

[MySQL client version: 5.1.63]
[PHP version: 5.2.17]

	$con = connect();
	if($stmt = mysqli_prepare($con, "UPDATE login SET email = '?' WHERE email = '?'")){
		mysqli_stmt_bind_param($stmt, 'ss', $email1, $_SESSION['email']);
		mysqli_stmt_execute($stmt);
		disconnect();
		exit;
	} else {
		header("location: ../profile.php?m=04");
	}

Where connect(), disconnect(), $email1 are set elsewhere.

Thanks!

[Atli]

Hey.

When you bind a parameter, it must not be quoted. If you write: WHERE email = '?', then MySQLi will read that as if you want to compare the email with the letter ?. If you want it to replace the ? with a value, leave it unquoted: WHERE email = ?.

When you use prepared statements, the values you are passing into the query will never actually go into the query. You send them along with the query but separately. So there is no need for strings to be quoted at any point, as they won't ever be inside the query string.

P.S.
I've moved this over to the PHP forum.

This post has been edited by Atli: 05 August 2012 - 02:16 PM

[chino]

You legend, that works now and I have spent ages trying to get this to work! One more question then, what if the value is a string and it contains single quotes? Can I escape the quotes at any point?

Thanks!

[CTphpnwb]

The point of prepared statements is that data is always treated as data so there is no need to escape anything.

[Atli]

You don't need to. That's the beauty of prepared statements: the values are never a part of the SQL query. Because of that, any sort of corruption the value would otherwise cause as a part of the query is avoided completely. You don't have to worry about single-quotes or security threats via SQL injection. Those are a thing of the past

[chino]

Brilliant, I suddenly have a whole new respect for Prepared Statements. Cheers guys!