51

I am dissecting some code and came across this, 

$sql = 'SELECT page.*, author.name AS author, updator.name AS updator '
     . 'FROM '.TABLE_PREFIX.'page AS page '
     . 'LEFT JOIN '.TABLE_PREFIX.'user AS author ON author.id = page.created_by_id '
     . 'LEFT JOIN '.TABLE_PREFIX.'user AS updator ON updator.id = page.updated_by_id '
     . 'WHERE slug = ? AND parent_id = ? AND (status_id='.Page::STATUS_REVIEWED.' OR status_id='.Page::STATUS_PUBLISHED.' OR status_id='.Page::STATUS_HIDDEN.')';

I am wondering what the "?" does in the WHERE statement. Is it some sort of parameter holder?

Ciro Santilli OurBigBook.com
  • 347,512
  • 102
  • 1,199
  • 985
Levi
  • 12,214
  • 14
  • 43
  • 47
  • IMHO, Question lacks context / insufficient research effort. Almost certainly, could have been answered by OP, by observing where `$sql` was used - presumably it was eventually used in a prepared statement - then looking up doc for prepared statement would have answered the question. – ToolmakerSteve Aug 27 '20 at 20:09
  • Does this answer your question? [What does a question mark represent in SQL queries?](https://stackoverflow.com/questions/3727688/what-does-a-question-mark-represent-in-sql-queries) – Mark Rotteveel May 13 '22 at 09:07

4 Answers4

50

Prepared statments use the '?' in MySQL to allow for binding params to the statement. Highly regarded as more secure against SQL injections if used properly. This also allows for quicker SQL queries as the request only has to be compiled once and can be reused.

Jayrox
  • 4,335
  • 4
  • 40
  • 43
  • How would one use them? By that I mean how do you set the question mark to take on a parameter? – Levi Mar 23 '09 at 20:16
  • 1
    There are multiple ways to do prepared statements, PDO and MySQLi support them. http://us3.php.net/mysqli http://us3.php.net/manual/en/mysqli.prepare.php and a quick google search brought me to http://www.petefreitag.com/item/356.cfm – Jayrox Mar 23 '09 at 20:23
37

The question mark represents a parameter that will later be replaced. Using parameterized queries is more secure than embedding the parameters right into the query.

SQL Server calls this parameterize queries, and Oracle calls it bind variables.

The usage varies with the language that you are executing the query from.

Here is an example of how it is used from PHP.

assuming that $mysqli is a database connection and people is a table with 4 columns.

$stmt = $mysqli->prepare("INSERT INTO People VALUES (?, ?, ?, ?)");

$stmt->bind_param('sssd', $firstName, $lastName, $email, $age);

The 'sssd' is a flag identifying the rest of the parameters, where s represents string and d represents digits.

Manolo
  • 24,020
  • 20
  • 85
  • 130
Steve Stedman
  • 713
  • 2
  • 7
  • 10
7

? has no special meaning in MySQL WHERE = statements, only in prepared statements

The most common case where we see it is due to special meaning given to ? by several web frameworks like PHP and Rails.

? is just a syntax error at:

CREATE TABLE t (s CHAR(1));
SELECT * FROM t WHERE s = ?;

because it is unquoted, and in:

INSERT INTO t VALUES ('a');
INSERT INTO t VALUES ("?");
SELECT * FROM t WHERE s = '?';

it returns:

s
?

thus apparently without special meaning.

MySQL 5.0 prepared statements

MySQL 5.0 added the prepared statement feature which has similar semantics to the question mark in web frameworks.

Example from the docs:

PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
SET @a = 3;
SET @b = 4;
EXECUTE stmt1 USING @a, @b;

Output:

hypotenuse
5

These also escape special characters as expected:

PREPARE stmt1 FROM 'SELECT ? AS s';
SET @a = "'";
EXECUTE stmt1 USING @a;

Output:

s
'

Rails example

In Rails for example, the question mark is replaced by an argument given by a variable of the library's programming language (Ruby), e.g.:

Table.where("column = ?", "value")

and it automatically quotes arguments to avoid bugs and SQL injection, generating a statement like:

SELECT * FROM Table WHERE column = 'value';

The quoting would save us in case of something like:

Table.where("column = ?", "; INJECTION")
Ciro Santilli OurBigBook.com
  • 347,512
  • 102
  • 1,199
  • 985
  • 1
    Even after editing in 2018, I consider this answer misleading. It isn't until the "prepared statements" section that you find out what was really going on in OP's situation. First section makes it sound like it was a coding error [unlikely in OP's case], second section talks about Rails [not relevant to the question]. Honestly, I consider this answer a net-negative for the discussion, given that it was added years later, that the accepted answer already covers the topic of prepared statements, and that the other two sections are off-purpose. – ToolmakerSteve Aug 27 '20 at 19:59
1

These are prepared statements ,prepared statements offer two major benefits:

The query only needs to be parsed (or prepared) once, but can be executed multiple times with the same or different parameters. When the query is prepared, the database will analyze, compile and optimize its plan for executing the query. For complex queries this process can take up enough time that it will noticeably slow down an application if there is a need to repeat the same query many times with different parameters. By using a prepared statement the application avoids repeating the analyze/compile/optimize cycle. This means that prepared statements use fewer resources and thus run faster.

The parameters to prepared statements don't need to be quoted; the driver automatically handles this. If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible).

http://php.net/manual/en/pdo.prepared-statements.php

Raheel
  • 4,953
  • 4
  • 34
  • 40