MySQL and PHP
Copyright 1997-2021 the PHP Documentation Group.
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 3.11 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 3.12 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.13 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 3.14 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 3.15 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 3.16 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 3.17 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 3.18 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.
Table 3.2 Comparison of 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
mysqli::__construct
|
mysqli::query
|
mysqli::prepare
|
mysqli_stmt::prepare
|
mysqli_stmt::execute
|
mysqli_stmt::bind_param
|
mysqli_stmt::bind_result
|