How to use quotations in SQL statement with MySQLi Prepared Statements in PHP?

Question

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.");

I'm not quite sure why this was down-voted. I did some extensive searching and can't figure it out. If it's a bad question, can someone point me in the right direction? — TheKarateKid, Mar 06 '13 at 05:12
I haven't done the down vote. But the problem in your question is you haven't posted your code, just putted your error. — Yogesh Suthar, Mar 06 '13 at 05:14
I thought I provided enough information as-is, because the line I provided is what's causing the error. I will provide the full code now. — TheKarateKid, Mar 06 '13 at 05:15
Sorry I mean in your first snippet. You are calling `mysqli_stmt_bind_param` with `$email` I can't see what you've set it to... — lc., Mar 06 '13 at 05:27

score 2 · Answer 1 · answered Mar 06 '13 at 06:12

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.

lc. · Answer 2 · 2013-03-06T05:29:34.573

1

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:

The parameter containing a value other than you expect
Or, the data in the database not being what you expect

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 "?".

edited Mar 06 '13 at 05:29

answered Mar 06 '13 at 05:14

lc.

113,939
20
158
187

When I manually type the SQL statement in a MySQL terminal without the tickmarks around the email address, I get a MySQL syntax error because the "@" sign is causing a break. The parameter must be input within quotation marks, but MySQLi is not allowing me to do so. – TheKarateKid Mar 06 '13 at 05:20
@FireAndIce727 When you manually type the statement in a terminal, you are not using a parameter, you are typing a string literal. Therefore you have to enclose the string in single quotation marks (e.g. `SELECT user_id FROM Users WHERE email='someone@example.com'`). – lc. Mar 06 '13 at 05:29
@FireAndIce727 I'm guessing the query "not returning what it is supposed to" is because `$_POST['email']` doesn't contain what you expect. When using parameters, you do not need to enclose a string in quotation marks as it is already a string. – lc. Mar 06 '13 at 05:38
I figured it out, as I was not preparing the result set to output variables after querying the database. Your assistance helped me move on from thinking it was a problem with the SQL syntax. Thank you! – TheKarateKid Mar 06 '13 at 06:15

score 1 · Answer 3 · answered Mar 06 '13 at 05:38

1

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.

answered Mar 06 '13 at 05:38

Mayukh Roy

1,815
3
19
31

How to use quotations in SQL statement with MySQLi Prepared Statements in PHP?

3 Answers3