Prepared Statements
Prepared Statements
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 sent 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 ?.
Example #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.
Example #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 sent to the server. The statement is not parsed
again. The statement template is not transferred to the server
again.
Example #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());
?>The above example will output:
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, multi-INSERT requires less
round-trips between the server and client than the prepared
statement shown above.
Example #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.
Example #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']));
?>The above example will output:
id = 1 (integer) label = a (string)
This behavior differs 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.
Example #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));
}
?>The above example will output:
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.
Example #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());
?>The above example will output:
array(1) {
[0]=>
array(2) {
[0]=>
int(1)
[1]=>
string(1) "a"
}
}
Using the mysqli_result
interface offers the additional benefit of flexible
client-side result set navigation.
Example #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();
?>The above example will output:
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 are sent to the server separately
from the query and thus cannot interfere with it. The server uses
these values directly at the point of execution, after the
statement template is parsed. Bound parameters do not need to be
escaped as they are never substituted into the query string
directly. A hint must be provided to the server for the type of
bound variable, to create an appropriate conversion. See the
mysqli_stmt_bind_param() function for more
information.
Such a separation sometimes considered as the only
security feature to prevent SQL injection, but the same degree of
security can be achieved with non-prepared statements, if all the
values are formatted correctly. It should be noted that correct
formatting is not the same as escaping and involves more logic than
simple escaping. Thus, prepared statements are simply a more
convenient and less error-prone approach to this element of
database security.
Client-side prepared statement
emulation
The API does not include emulation for client-side
prepared statement emulation.
Quick prepared – non-prepared
statement comparison
The table below compares 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 flavor | 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