PHP - Manual: Prepared Statements
2024-11-23
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 and protect against SQL injections.
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 ?.
Prepare is followed by execute. During execute the client binds parameter values and sends them to the server. The server executes the statement with the bound values using the previously created internal resources.
示例 #1 Prepared statement
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
/* Non-prepared statement */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
/* Prepared statement, stage 1: prepare */
$stmt = $mysqli->prepare("INSERT INTO test(id, label) VALUES (?, ?)");
/* Prepared statement, stage 2: bind and execute */
$id = 1;
$label = 'PHP';
$stmt->bind_param("is", $id, $label); // "is" means that $id is bound as an integer and $label as a string
$stmt->execute();
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.
示例 #2 INSERT prepared once, executed multiple times
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
/* Non-prepared statement */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
/* Prepared statement, stage 1: prepare */
$stmt = $mysqli->prepare("INSERT INTO test(id, label) VALUES (?, ?)");
/* Prepared statement, stage 2: bind and execute */
$stmt->bind_param("is", $id, $label); // "is" means that $id is bound as an integer and $label as a string
$data = [
1 => 'PHP',
2 => 'Java',
3 => 'C++'
];
foreach ($data as $id => $label) {
$stmt->execute();
}
$result = $mysqli->query('SELECT id, label FROM test');
var_dump($result->fetch_all(MYSQLI_ASSOC));
以上例程会输出：
array(3) {
[0]=>
array(2) {
["id"]=>
string(1) "1"
["label"]=>
string(3) "PHP"
}
[1]=>
array(2) {
["id"]=>
string(1) "2"
["label"]=>
string(4) "Java"
}
[2]=>
array(2) {
["id"]=>
string(1) "3"
["label"]=>
string(3) "C++"
}
}
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 fewer round-trips between the server and client than the prepared statement shown above.
示例 #3 Less round trips using multi-INSERT SQL
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");
$values = [1, 2, 3, 4];
$stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?), (?), (?), (?)");
$stmt->bind_param('iiii', ...$values);
$stmt->execute();
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. Client libraries 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.
示例 #4 Native datatypes
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
/* Non-prepared statement */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP')");
$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");
$stmt->execute();
$result = $stmt->get_result();
$row = $result->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 = PHP (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.
示例 #5 Output variable binding
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
/* Non-prepared statement */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP')");
$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");
$stmt->execute();
$stmt->bind_result($out_id, $out_label);
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 = PHP (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.
示例 #6 Using mysqli_result to fetch results
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
/* Non-prepared statement */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP')");
$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");
$stmt->execute();
$result = $stmt->get_result();
var_dump($result->fetch_all(MYSQLI_ASSOC));
以上例程会输出：
array(1) {
[0]=>
array(2) {
["id"]=>
int(1)
["label"]=>
string(3) "PHP"
}
}
Using the mysqli_result interface offers the additional benefit of flexible client-side result set navigation.
示例 #7 Buffered result set for flexible read out
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
/* Non-prepared statement */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP'), (2, 'Java'), (3, 'C++')");
$stmt = $mysqli->prepare("SELECT id, label FROM test");
$stmt->execute();
$result = $stmt->get_result();
for ($row_no = $result->num_rows - 1; $row_no >= 0; $row_no--) {
$result->data_seek($row_no);
var_dump($result->fetch_assoc());
}
以上例程会输出：
array(2) {
["id"]=>
int(3)
["label"]=>
string(3) "C++"
}
array(2) {
["id"]=>
int(2)
["label"]=>
string(4) "Java"
}
array(2) {
["id"]=>
int(1)
["label"]=>
string(3) "PHP"
}
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 comparison of prepared and non-prepared statements
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 and parsed every time |
| Input parameter binding API | Yes | 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
add a note
User Contributed Notes
There are no user contributed notes for this page.
官方地址：https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php
IT
PHP
编程语言
Linux
开发编程
科技
Elasticsearch
HTML/CSS/XML
网络
JAVA
NoSQL
数据库
面试
C/C++
Golang
Git
算法
操作系统
正则表达式
Redis
互联网
MySql
JavaScript
运维
软件
国际
架构设计
Mac OS
TCP/IP
Excel
Vim
Windows
Socket
Oracle
VR
MongoDB
Python
运营
MemCache
商业
硬件
电子
娱乐
设计
nginx
摄影
游戏
WordPress
HTTP
团建
数码电器
广告
--
php7 安装fileinfo扩展
preg_split — 通过一个正则表达式分隔字符串
php位值,解决 PHP 中 usort 在值相同时改变原始位置的问题
[PHP] inet_pton/inet_ntop IP地址转换函数
array_multisort() 多字段排序
PHPMailer设置utf8 PHPMailer字符集CharSet
ADORecordSet对象
[鸟哥]PHP_INT_MIN 和 -9223372036854775808
PHP 8.1
adodb手册
opcache预加载
Composer的Packagist资源
mysql面试题
支持php7的性能采集扩展
php扩展开发
PHP7 mcrypt替代方案
Packagist / Composer 中国全量镜像
PHP json解析（json_decode）页面工具
如何在Composer中为GitLab实例设置访问令牌
PHP mkdir()写出来的权限与mode值不符合