Prepared Statements

The MySQL database supports prepared statements. A prepared statement or a parameterized statement is used to execute the same statement repeatedly with high efficiency.

Basic workflow

The prepared statement execution consists of two stages: prepare and execute. At the prepare stage a statement template is send to the database server. The server performs a syntax check and initializes server internal resources for later use.

The MySQL server supports using anonymous, positional placeholder with ?.

Пример #1 First stage: prepare


<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno)
 echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;

/* Non-prepared statement */
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
    !$mysqli->query("CREATE TABLE test(id INT)"))
    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;

/* Prepared statement, stage 1: prepare */
if (!($stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?)")))
 echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
?>

Prepare is followed by execute. During execute the client binds parameter values and sends them to the server. The server creates a statement from the statement template and the bound values to execute it using the previously created internal resources.

Пример #2 Second stage: bind and execute


<?php
/* Prepared statement, stage 2: bind and execute */
$id = 1;
if (!$stmt->bind_param("i", $id))
 echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;

if (!$stmt->execute())
 echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
?>

Repeated execution

A prepared statement can be executed repeatedly. Upon every execution the current value of the bound variable is evaluated and send to the server. The statement is not parsed again. The statement template is not transferred to the server again.

Пример #3 INSERT prepared once, executed multiple times


<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno)
 echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;

/* Non-prepared statement */
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
    !$mysqli->query("CREATE TABLE test(id INT)"))
    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;

/* Prepared statement, stage 1: prepare */
if (!($stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?)")))
 echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;

/* Prepared statement, stage 2: bind and execute */
$id = 1;
if (!$stmt->bind_param("i", $id))
 echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;

if (!$stmt->execute())
 echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;

/* Prepared statement: repeated execution, only data transferred from client to server */
for ($id = 2; $id < 5; $id++)
  if (!$stmt->execute())
    echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;

/* explicit close recommended */
$stmt->close();

/* Non-prepared statement */
$res = $mysqli->query("SELECT id FROM test");
var_dump($res->fetch_all());
?>

Результат выполнения данного примера:

array(4) {
  [0]=>
  array(1) {
    [0]=>
    string(1) "1"
  }
  [1]=>
  array(1) {
    [0]=>
    string(1) "2"
  }
  [2]=>
  array(1) {
    [0]=>
    string(1) "3"
  }
  [3]=>
  array(1) {
    [0]=>
    string(1) "4"
  }
}

Every prepared statement occupies server resources. Statements should be closed explicitly immediately after use. If not done explicitly, the statement will be closed when the statement handle is freed by PHP.

Using a prepared statement is not always the most efficient way of executing a statement. A prepared statement executed only once causes more client-server round-trips than a non-prepared statement. This is why the SELECT is not run as a prepared statement above.

Also, consider the use of the MySQL multi-INSERT SQL syntax for INSERTs. For the example, belows multi-INSERT requires less round-trips between the server and client than the prepared statement shown above.

Пример #4 Less round trips using multi-INSERT SQL


<?php
if (!$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3), (4)"))
 echo "Multi-INSERT failed: (" . $mysqli->errno . ") " . $mysqli->error;
?>

Result set values data types

The MySQL Client Server Protocol defines a different data transfer protocol for prepared statements and non-prepared statements. Prepared statements are using the so called binary protocol. The MySQL server sends result set data "as is" in binary format. Results are not serialized into strings before sending. The client libraries do not receive strings only. Instead, they will receive binary data and try to convert the values into appropriate PHP data types. For example, results from an SQL INT column will be provided as PHP integer variables.

Пример #5 Native datatypes


<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno)
 echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;

if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
    !$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))") ||
    !$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a')"))
    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;

$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");
$stmt->execute();
$res = $stmt->get_result();
$row = $res->fetch_assoc();

printf("id = %s (%s)\n", $row['id'], gettype($row['id']));
printf("label = %s (%s)\n", $row['label'], gettype($row['label']));
?>

Результат выполнения данного примера:

id = 1 (integer)
label = a (string)

This behaviour differes from non-prepared statements. By default, non-prepared statements return all results as strings. This default can be changed using a connection option. If the connection option is used, there are no differences.

Fetching results using bound variables

Results from prepared statements can either be retrieved by binding output variables or by requesting a mysqli_result object.

Output variables must be bound after statement execution. One variable must be bound for every column of the statements result set.

Пример #6 Output variable binding


<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno)
 echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;

if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
    !$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))") ||
    !$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a')"))
    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;

if (!($stmt = $mysqli->prepare("SELECT id, label FROM test")))
 echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;

if (!$stmt->execute())
  echo "Execute failed: (" . $mysqli->errno . ") " . $mysqli->error;

$out_id = NULL;
$out_label = NULL;
if (!$stmt->bind_result($out_id, $out_label))
 echo "Binding output parameters failed: (" . $stmt->errno . ") " . $stmt->error;

while ($stmt->fetch())
 printf("id = %s (%s), label = %s (%s)\n",
   $out_id, gettype($out_id),
   $out_label, gettype($out_label));
?>

Результат выполнения данного примера:

id = 1 (integer), label = a (string)

Prepared statements return unbuffered result sets by default. The results of the statement are not implicitly fetched and transferred from the server to the client for client-side buffering. The result set takes server resources until all results have been fetched by the client. Thus it is recommended to consume results timely. If a client fails to fetch all results or the client closes the statement before having fetched all data, the data has to be fetched implicitly by mysqli.

It is also possible to buffer the results of a prepared statement using mysqli_stmt_store_result().

Fetching results using mysqli_result interface

Instead of using bound results, results can also be retrieved through the mysqli_result interface. mysqli_stmt_get_result() returns a buffered result set.

Пример #7 Using mysqli_result to fetch results


<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno)
 echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;

if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
    !$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))") ||
    !$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a')"))
    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;

if (!($stmt = $mysqli->prepare("SELECT id, label FROM test ORDER BY id ASC")))
 echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;

if (!$stmt->execute())
  echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;

if (!($res = $stmt->get_result()))
 echo "Getting result set failed: (" . $stmt->errno . ") " . $stmt->error;

var_dump($res->fetch_all());
?>

Результат выполнения данного примера:

array(1) {
  [0]=>
  array(2) {
    [0]=>
    int(1)
    [1]=>
    string(1) "a"
  }
}

Using the mysqli_result interface this has the additional benefit of flexible client-side result set navigation.

Пример #8 Buffered result set for flexible read out


<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno)
 echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;

if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
    !$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))") ||
    !$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a'), (2, 'b'), (3, 'c')"))
    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;

if (!($stmt = $mysqli->prepare("SELECT id, label FROM test")))
 echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;

if (!$stmt->execute())
  echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;

if (!($res = $stmt->get_result()))
 echo "Getting result set failed: (" . $stmt->errno . ") " . $stmt->error;

for ($row_no = ($res->num_rows - 1); $row_no >= 0; $row_no--) {
  $res->data_seek($row_no);
  var_dump($res->fetch_assoc());
}
$res->close();
?>

Результат выполнения данного примера:

array(2) {
  ["id"]=>
  int(3)
  ["label"]=>
  string(1) "c"
}
array(2) {
  ["id"]=>
  int(2)
  ["label"]=>
  string(1) "b"
}
array(2) {
  ["id"]=>
  int(1)
  ["label"]=>
  string(1) "a"
}

Escaping and SQL injection

Bound variables will be escaped automatically by the server. The server inserts their escaped values at the appropriate places into the statement template before execution. Users must hint the server about the type of the bound variable for appropriate conversion, see mysqli_stmt_bind_param().

The automatic escaping of values within the server is sometimes considered as a security feature to prevent SQL injection. The same degree of security can be achieved with non-prepared statements, if input values are escaped correctly.

Client-side prepared statement emulation

The API does not include a client-side prepared statement emulation.

Quick prepared - non-prepared statement comparison

The below table gives a quick comparison on server-side prepared and non-prepared statements.

	Prepared Statement	Non-prepared statement
Client-server round trips, SELECT, single execution	2	1
Statement string transferred from client to server	1	1
Client-server round trips, SELECT, repeated (n) execution	1 + n	n
Statement string transferred from client to server	1 template, n times bound parameter, if any	n times together with parameter, if any
Input parameter binding API	yes, automatic input escaping	no, manual input escaping
Output variable binding API	yes	no
Supports use of mysqli_result API	yes, use mysqli_stmt_get_result()	yes
Buffered result sets	yes, use mysqli_stmt_get_result()() or binding with mysqli_stmt_store_result()	yes, default of mysqli_query()
Unbuffered result sets	yes, use output binding API	yes, use mysqli_real_query() with mysqli_use_result()
MySQL Client Server protocol data transfer flavour	binary protocol	text protocol
Result set values SQL data types	preserved when fetching	converted to string or preserved when fetching
Supports all SQL statements	Recent MySQL versions support most but not all	yes

See also