I am using prepared statements to perform a SELECT query to my database, however the nature of the SQL syntax is causing a problem with MySQLi.
When I attempt to prepare:
SELECT user_id FROM Users WHERE email='?';
I am getting an error
Warning: mysqli_stmt_bind_param(): Number of variables doesn't match number of parameters in prepared statement
I know the error is being caused by putting quotation marks around the ? but the problem is that these quote marks are required for the SQL syntax to be correct. If I remove the quotes, the prepared statement works, but the database doesn't return what it is supposed to.
Is there something I can do, or is this a limitation of the interface? If it's a limitation, what can I do to perform this query with the safety of prepared statements?
Full Code:
$email = $_POST["email"];
$sql = "SELECT user_id,fname,city,state,zip FROM Users WHERE email='?';";
$types = 's';
$stmt = $db_obj->prepare($sql);
if (!mysqli_stmt_bind_param($stmt, $types, $email)) {
echo "SQL Bind Parameter error.<br/>";
exit;
}
if (!mysqli_stmt_execute($stmt)) {
echo "SQL Statement Execute error.<br/>";
exit;
}
if (!isset($row[0]))
exit ("No such email registered.");
The correct syntax is SELECT user_id FROM Users WHERE email=?. Your issue of the prepared statement not returning the "correct" results must either result from:
Note that in your query SELECT user_id FROM Users WHERE email='?', the ? is not a parameter. It is the string literal "?". This query will search for any rows in the Users table where the email column contains the value "?".
Please remove the quotes on ? mark.
$stmt = $mysqli->prepare("SELECT user_id,fname,city,state,zip FROM Users WHERE email=?");
/* bind parameters for markers */
$stmt->bind_param("s", $email);
/* execute query */
$stmt->execute();
Try this.
As @lc posted above, the prepared statement is always passing the email in as a string instead of a parameter to MySQL, so I removed the quote marks knowing that they are not needed to solve my problem.
It turns out that I did not bind the output variables after the statement execution, so even when the prepared statement was being done correctly, I was not reading the response correctly.
I added:
mysqli_stmt_bind_result($stmt, $user_id, $fname, $city, $state, $zip);
mysqli_stmt_fetch($stmt);
if (empty($user_id))
exit ("No such email registered.");
Since the database results were properly being bound to the variable now, the variables such as $userid were now holding the data from the database that was expected.