CakeFest 2024: The Official CakePHP Conference

PDO::prepare

(PHP 5 >= 5.1.0, PHP 7, PHP 8, PHP 8,PECL pdo >= 0.1.0)

PDO::prepare 预处理要执行的语句,并返回语句对象

说明

public PDO::prepare(string $query, array $options = []): PDOStatement|false

PDOStatement::execute() 方法预处理待执行的 SQL 语句。 语句模板可以包含零个或多个参数占位标记,格式是命名(:name)或问号(?)的形式,当它执行时将用真实数据取代。 在同一个语句模板里,命名形式和问号形式不能同时使用;只能选择其中一种参数形式。 请用参数形式绑定用户输入的数据,不要直接字符串拼接到查询里。

调用 PDOStatement::execute() 时,每一个值的参数占位标记,名称必须唯一。 除非启用模拟(emulation)模式,同一个语句里无法使用重名的参数。

注意:

参数占位符仅能字面上展示完整的数据。不能是字面的一部分,不能是关键词,不能是标识符,不能是其他任意的范围。 举例说明:不能把多个值绑到单个参数里,然后在 SQL 语句里用 IN() 查询。

如果用不同参数,通过 PDO::prepare()PDOStatement::execute() 多次调用同一个 SQL 语句,将提升应用的性能 —— 驱动可以让客户端/服务器缓存查询和元信息。 同时,调用 PDO::prepare()PDOStatement::execute() 还能阻止 SQL 注入攻击,不需要给参数手动加引号与转义。

如果内置驱动不支持参数,PDO 将模拟出参数的功能;如果驱动仅仅支持其中一种风格(命名参数和问号参数两种),也会自动重写到另外一种风格。

注意: The parser used for emulated prepared statements and for rewriting named or question mark style parameters supports the non standard backslash escapes for single- and double quotes. That means that terminating quotes immediately preceeded by a backslash are not recognized as such, which may result in wrong detection of parameters causing the prepared statement to fail when it is executed. A work-around is to not use emulated prepares for such SQL queries, and to avoid rewriting of parameters by using a parameter style which is natively supported by the driver.

自 PHP 7.4.0 起,可以通过两个问号来转义问号。这意味着 ?? 在发送查询到数据库时会转换成 ?

参数

query

必须是对目标数据库服务器有效的 SQL 语句模板。

options

数组包含一个或多个 key=>value 键值对,为返回的 PDOStatement 对象设置属性。 常见用法是:设置 PDO::ATTR_CURSORPDO::CURSOR_SCROLL,将得到可滚动的光标。 某些驱动有驱动级的选项,在 prepare 时就设置。

返回值

如果数据库服务器已经成功预处理语句,PDO::prepare() 返回 PDOStatement 对象。 如果数据库服务器无法预处理语句,PDO::prepare() 返回 false 或抛出 PDOException (取决于 错误处理)。

注意:

模拟模式下的预处理语句不会和数据库服务器交互,所以 PDO::prepare() 不会检查语句。

错误/异常

如果属性 PDO::ATTR_ERRMODE 设置为 PDO::ERRMODE_WARNING,则发出级别为 E_WARNING 的错误。

如果属性 PDO::ATTR_ERRMODE 设置为 PDO::ERRMODE_EXCEPTION,则抛出 PDOException

示例

示例 #1 命名参数形式的 SQL 语句模板

<?php
/* 传入数组的值,并执行已预处理的语句 */
$sql = 'SELECT name, colour, calories
FROM fruit
WHERE calories < :calories AND colour = :colour'
;
$sth = $dbh->prepare($sql, [PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY]);
$sth->execute(['calories' => 150, 'colour' => 'red']);
$red = $sth->fetchAll();
/* Array keys can be prefixed with colons ":" too (optional) */
$sth->execute([':calories' => 175, ':colour' => 'yellow']);
$yellow = $sth->fetchAll();
?>

示例 #2 问号形式的 SQL 语句模板

<?php
/* 传入数组的值,并执行已预处理的语句 */
$sth = $dbh->prepare('SELECT name, colour, calories
FROM fruit
WHERE calories < ? AND colour = ?'
);
$sth->execute([150, 'red']);
$red = $sth->fetchAll();
$sth->execute([175, 'yellow']);
$yellow = $sth->fetchAll();
?>

示例 #3 问号转义的 SQL 语句模板

<?php
/* note: this is only valid on PostgreSQL databases */
$sth = $dbh->prepare('SELECT * FROM issues WHERE tag::jsonb ?? ?');
$sth->execute(['feature']);
$featureIssues = $sth->fetchAll();
$sth->execute(['performance']);
$performanceIssues = $sth->fetchAll();
?>

参见

add a note

User Contributed Notes 26 notes

up
178
Anonymous
10 years ago
To those wondering why adding quotes to around a placeholder is wrong, and why you can't use placeholders for table or column names:

There is a common misconception about how the placeholders in prepared statements work: they are not simply substituted in as (escaped) strings, and the resulting SQL executed. Instead, a DBMS asked to "prepare" a statement comes up with a complete query plan for how it would execute that query, including which tables and indexes it would use, which will be the same regardless of how you fill in the placeholders.

The plan for "SELECT name FROM my_table WHERE id = :value" will be the same whatever you substitute for ":value", but the seemingly similar "SELECT name FROM :table WHERE id = :value" cannot be planned, because the DBMS has no idea what table you're actually going to select from.

Even when using "emulated prepares", PDO cannot let you use placeholders anywhere, because it would have to work out what you meant: does "Select :foo From some_table" mean ":foo" is going to be a column reference, or a literal string?

When your query is using a dynamic column reference, you should be explicitly white-listing the columns you know to exist on the table, e.g. using a switch statement with an exception thrown in the default: clause.
up
90
Simon Le Pine
11 years ago
Hi All,

First time posting to php.net, a little nervous.

After a bunch of searching I've learned 2 things about prepared statements:
1.) It fails if you enclose in a single quote (')
This fails: "SELECT * FROM users WHERE email=':email'"
This works: "SELECT * FROM users WHERE email=:email"
2.) You cannot search with a prepared statement
This fails: "SELECT * FROM users WHERE :search=:email"
This succeeds: "SELECT * FROM users WHERE $search=:email"

In my case I allow the user to enter their username or email, determine which they've entered and set $search to "username" or "email". As this value is not entered by the user there is no potential for SQL injection and thus safe to use as I have done.

Hope that saves someone else from a lot of searching.
up
43
bg at enativ dot com
10 years ago
if you run queries in a loop, don't include $pdo->prepare() inside the loop, it will save you some resources (and time).

prepare statement inside loop:
for($i=0; $i<1000; $i++) {
$rs = $pdo->prepare("SELECT `id` FROM `admins` WHERE `groupID` = :groupID AND `id` <> :id");
$rs->execute([':groupID' => $group, ':id' => $id]);
}

// took 0.066626071929932 microseconds

prepare statement outside loop:
$rs = $pdo->prepare("SELECT `id` FROM `admins` WHERE `groupID` = :groupID AND `id` <> :id");
for($i=0; $i<1000; $i++) {
$rs->execute([':groupID' => $group, ':id' => $id]);
}

// took 0.064448118209839 microseconds

for 1,000 (simple) queries it took 0.002 microseconds less.
not much, but it worth mention.
up
56
daniel dot egeberg at gmail dot com
14 years ago
You can also pass an array of values to PDOStatement::execute(). This is also secured against SQL injection. You don't necessarily have to use bindParam() or bindValue().
up
40
admin at wdfa dot co dot uk
14 years ago
Note on the SQL injection properties of prepared statements.

Prepared statements only project you from SQL injection IF you use the bindParam or bindValue option.

For example if you have a table called users with two fields, username and email and someone updates their username you might run

UPDATE `users` SET `user`='$var'

where $var would be the user submitted text.

Now if you did
<?php
$a
=new PDO("mysql:host=localhost;dbname=database;","root","");
$b=$a->prepare("UPDATE `users` SET user='$var'");
$b->execute();
?>

and the user had entered User', email='test for a test the injection would occur and the email would be updated to test as well as the user being updated to User.

Using bindParam as follows
<?php
$var
="User', email='test";
$a=new PDO("mysql:host=localhost;dbname=database;","root","");
$b=$a->prepare("UPDATE `users` SET user=:var");
$b->bindParam(":var",$var);
$b->execute();
?>

The sql would be escaped and update the username to User', email='test'
up
5
Mark Simon
6 years ago
Many students are tempted to add single quotes around string place holders in the SQL statement, since that’s what they normally do around strings in SQL and PHP.

I have to explain:

Quotes are not part of the string — they are used to construct a string in the coding language. If you are creating a string literal in SQL or PHP, then it must indeed be quoted. If the string has already been created, and is being passed on, then additional quotes would be wrong at best, and mis-interpreted at worst.

In prepared place holders, think of place holders as variables, which, whether they are strings or other values, are always written without quotes.
up
14
public at grik dot net
12 years ago
With PDO_MYSQL you need to remember about the PDO::ATTR_EMULATE_PREPARES option.

The default value is TRUE, like
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES,true);

This means that no prepared statement is created with $dbh->prepare() call. With exec() call PDO replaces the placeholders with values itself and sends MySQL a generic query string.

The first consequence is that the call $dbh->prepare('garbage');
reports no error. You will get an SQL error during the $dbh->exec() call.
The second one is the SQL injection risk in special cases, like using a placeholder for the table name.

The reason for emulation is a poor performance of MySQL with prepared statements. Emulation works significantly faster.
up
4
Robin
14 years ago
Use prepared statements to ensure integrity of binary data during storage and retrieval. Escaping/quoting by f.e. sqlite_escape_string() or PDO::quote() is NOT suited for binary data - only for strings of text.

A simple test verifies perfect storage and retrieval with prepared statements:

<?php

$num_values
= 10000;

$db = new pdo( 'sqlite::memory:' );

$db->exec( 'CREATE TABLE data (binary BLOB(512));' );

// generate plenty of troublesome, binary data
for( $i = 0; $i < $num_values; $i++ )
{
for(
$val = null, $c = 0; $c < 512/16; $c++ )
$val .= md5( mt_rand(), true );
@
$binary[] = $val;
}

// insert each value by prepared statement
for( $i = 0; $i < $num_values; $i++ )
$db->prepare( 'INSERT INTO data VALUES (?);' )->execute( array($binary[$i]) );

// fetch the entire row
$data = $db->query( 'SELECT binary FROM data;' )->fetchAll( PDO::FETCH_COLUMN );

// compare with original array, noting any mismatch
for( $i = 0; $i < $num_values; $i++ )
if(
$data[$i] != $binary[$i] ) echo "[$i] mismatch\n";

$db = null;

?>
up
4
pbakhuis
10 years ago
Noteworthy in my opinion is that if you prepare a statement but do not bind a value to the markers it will insert null by default. e.g.
<?php
/** @var PDO $db */
$prep = $db->prepare('INSERT INTO item(title, link) VALUES(:title, :link)');
$prep->execute();
?>
Will attempt to insert null, null into the item table.
up
1
machitgarha at outlook dot com
6 years ago
Hello everyone.

I want to note that it doesn't matter where you are using a variable inside the query directly, that is not secure against SQL injections (unless performing a long security operation).

The following example is insecure against SQL injections:

<?php

$statement
= $databaseConnection->prepare("SELECT * FROM `$_POST['table']` WHERE $_POST['search_for']=:search");
$statement->bindParam(":search", $search);
$search = 18; // For example
$statement->execute();

?>

If an attacker pass '1;-- ' as input named 'search_for', he is not a very bad attacker; because he didn't delete your data! In the above example, an attacker can do anything with connected database (unless you have restricted the connected user). Unfortunately, as Simon Le Pine mentioned, you cannot use prepared statements as other parts of a query; just can be used to search in indexes.

Hope this helps from loosing some data.
Sorry for my a bit weak English!
up
2
theking2 at king dot ma
1 year ago
There are restrictions on the placeholder string. In the following code the first execute fails with a SQLSTATE[HY093]. It is not clear exactly what characters are allowed.

<?php declare(strict_types=1);

$db = new \PDO("mysql:hostname=localhost;dbname=minidwh", "minidwh", "Meisterstueck!");
$db->query("SET NAMES 'utf8mb4'");

$db->query("DROP TABLE IF EXISTS `äëïöüß`");
$db->query("CREATE TABLE `äëïöüß` ( `id` INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE = ARIA;");
$db->query("ALTER TABLE `äëïöüß` ADD COLUMN `äëïöüß` TEXT NULL");
try {
$stmt = $db->prepare("INSERT INTO `äëïöüß` (`äëïöüß`) VALUES (:äëïöüß)");
$result = $stmt->execute([':äëïöüß' => 'test1']);
} catch (
\PDOException $e) {
echo
$e->getMessage() . '<BR>';
}

try {
$stmt = $db->prepare("INSERT INTO `äëïöüß` (`äëïöüß`) VALUES (?)");
$result = $stmt->execute(['test2']);
} catch (
\PDOException $e) {
echo
$e->getMessage() . '<BR>';
}

try {
$stmt = $db->prepare("INSERT INTO `äëïöüß` (`äëïöüß`) VALUES (:column)");
$result = $stmt->execute([':column' => 'test3']);
} catch (
\PDOException $e) {
echo
$e->getMessage() . '<BR>';
}

try {
$stmt = $db->prepare("INSERT INTO `äëïöüß` (`äëïöüß`) VALUES (:column)");
$stmt->bindValue(':column', 'test4');
$result = $stmt->execute();
} catch (
\PDOException $e) {
echo
$e->getMessage() . '<BR>';
}
up
1
php dot chaska at xoxy dot net
10 years ago
Note that for Postgres, even though Postgres does support prepared statements, PHP's PDO driver NEVER sends the prepared statement to the Postgres server in advance of the call to PDO::execute().

Therefore, PDO::prepare() will never throw an error for things like faulty SQL syntax.

It also means the server will not parse and plan the SQL until the first time PDO::execute() is called, which may or may not adversely affect your optimization plans.
up
-2
orrd101 at gmail dot com
11 years ago
Don't just automatically use prepare() for all of your queries.

If you are only submitting one query, using PDO::query() with PDO::quote() is much faster (about 3x faster in my test results with MySQL). A prepared query is only faster if you are submitting thousands of identical queries at once (with different data).

If you Google for performance comparisons you will find that this is generally consistently the case, or you can write some code and do your own comparison for your particular configuration and query scenario. But generally PDO::query() will always be faster except when submitting a large number of identical queries. Prepared queries do have the advantage of escaping the data for you, so you have to be sure to use quote() when using query().
up
-2
roth at egotec dot com
17 years ago
Attention using MySQL and prepared statements.
Using a placeholder multiple times inside a statement doesn't work. PDO just translates the first occurance und leaves the second one as is.

select id,name from demo_de where name LIKE :name OR name=:name

You have to use

select id,name from demo_de where name LIKE :name OR name=:name2

and bind name two times. I don't know if other databases (for example Oracle or MSSQL) support multiple occurances. If that's the fact, then the PDO behaviour for MySQL should be changed.
up
-3
Hayley Watson
10 years ago
It is possible to prepare in advance several statements against a single connection. As long as that connection remains open the statements can be executed and fetched from as often as you like in any order; their "prepare-execute-fetch" steps can be interleaved in whichever way is best.

So if you're likely to be using several statements often (perhaps within a loop of transactions), you may like to consider preparing all the statements you'll be using up front.
up
-3
ak_9jsz
15 years ago
Using cursors doesn't work with SQLite 3.5.9. I get an error message when it gets to the execute() method.

Some of you might be saying "duh!" but i was surprised to see TRIGGER support in SQLite, so i had to try. :)

I wanted to use Absolute referencing on a Scrollable cursor and i only wanted one column of data. So i used this instead of a cursor.

<?php

$dbo
= new PDO('sqlite:tdb');
$sql = 'SELECT F1, F2 FROM tblA WHERE F1 <> "A";';
$res = $dbo->prepare($sql);
$res->execute();
$resColumn = $res->fetchAll(PDO::FETCH_COLUMN, 0);

for(
$r=0;$r<=3;$r++)
echo
'Row '. $r . ' returned: ' . $resColumn[$r] . "\n";

$dbo = null;
$res = null;
?>
up
-6
omidbahrami1990 at gmail dot com
6 years ago
This Is A Secure Way To Sign in With pdo::prepare
--------------------------------------------------------
<?php
function secured_signin($username,$password)
{
try
{
$connection = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbusername, $dbpassword);
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$prepared = $connection->prepare("SELECT COUNT(`username`) FROM `users` WHERE `username` = :bp_username AND `password` = :bp_password ; ");
$prepared->bindParam(':bp_username', $username);
$prepared->bindParam(':bp_password', $password);
$prepared->execute();

if (
$prepared->fetchColumn() == 1)
$result=true;

else
$result=false;
}

catch(
PDOException $x) { die("Secured"); }

$prepared = null;
$connection = null;

return
$result;
}
/*
$dbhost ---> DataBase IP Address
$dbusername ---> DataBase Username
$dbpassword ---> DataBase Password
$dbname ---> DataBase Name
*/
?>
up
-6
jesse dot chisholm at gmail dot com
9 years ago
@Simon Le Pine

Be aware that:

$search = "user";
$sth = db->prepare("SELECT * FROM users WHERE $search=:email");

and

$search = "email";
$sth = db->prepare("SELECT * FROM users WHERE $search=:email");

will produce two totally different prepared statements.

Doing this _will not work_:

$search = "user";
$sth = db->prepare("SELECT * FROM users WHERE $search=:email");
$sth->execute(array(email=>"yada"));
$search = "email";
$sth->execute(array(email=>"yada@ya.da"));
up
-4
sgirard at rossprint dot com
14 years ago
Maybe everyone else already knows this but...

If you have a routine that prepares/executes many insert or update statements for a sqlite db then you may want to make use of the pdo transactions.

On some old hardware my query set went from 12 seconds to 1/3-1/2 second.

-sean
up
-9
johniskew
17 years ago
If you need to create variable sql statements in a prepare statement...for example you may need to construct a sql query with zero, one, two, etc numbers of arguments...here is a way to do it without a lot of if/else statements needed to glue the sql together:

<?php

public function matchCriteria($field1=null,$field2=null,$field3=null) {
$db=DB::conn();
$sql=array();
$paramArray=array();
if(!empty(
$field1)) {
$sql[]='field1=?';
$paramArray[]=$field1;
}
if(!empty(
$field2)) {
$sql[]='field2=?';
$paramArray[]=$field2;
}
if(!empty(
$field3)) {
$sql[]='field3=?';
$paramArray[]=$field3;
}
$rs=$db->prepare('SELECT * FROM mytable'.(count($paramArray)>0 ? ' WHERE '.join(' AND ',$sql) : ''));
$result=$rs->execute($paramArray);
if(
$result) {
return
$rs;
}
return
false;
}

?>
up
-9
pascal dot buguet at laposte dot net
13 years ago
PDO::CURSOR_SCROLL is ok with MSS.
You must install SQL Server Driver for PHP 2.0 CTP2 : SQLSRV20.EXE
and the native client "Microsoft SQL Server 2008 R2 Native Client" : sqlncli.msi.
up
-7
william dot clarke at gmail dot com
17 years ago
Surely if you want to use prepared statements that way you should use the syntax in the second example:

eg.

instead of:
select id,name from demo_de where name LIKE :name OR name=:name

use:
select id,name from demo_de where name LIKE ? OR name=?

I believe you are supposed to either use distinct named parameters (name, name1) OR anonymous parameters (?s)
up
-9
richard at codevanilla.com
14 years ago
beware
PDO will emulate prepared statements/bound parameters for drivers that do not natively support them, and can also rewrite named or question mark style parameter markers to something more appropriate, if the driver supports one style but not the other.

This includes mySQL it seems so

<?php
try{
$sth1 = $this->db1->prepare($t1, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));

}
catch(
PDOException $e){
return
$this->pack('dbError', $e->getMessage());
}
?>

does not and so will not throw the exception if your SQL is wrong.

You will need to check that $sth1 is not null.
up
-12
Kjetil H
10 years ago
Please note that the correct internal method signature is:
<?php public function prepare ($statement, $driver_options = array()) ?>

and NOT:
<?php public function prepare ($statement, array $driver_options = array()) ?>.

Redeclaring the method using the latter method signature throws a Stricts Standards error.
up
-14
www.onphp5.com
16 years ago
Please note that the statement regarding driver_options is misleading:

"This array holds one or more key=>value pairs to set attribute values for the PDOStatement object that this method returns. You would most commonly use this to set the PDO::ATTR_CURSOR value to PDO::CURSOR_SCROLL to request a scrollable cursor. Some drivers have driver specific options that may be set at prepare-time"

From this you might think that scrollable cursors work for all databases, but they don't! Check out this bug report:
http://bugs.php.net/bug.php?id=34625
up
-21
chatelain dot cedric dot pro at gmail dot com
9 years ago
you can't use CREATE DATABASE with prepared statement.

$sql = $conn->prepare("DROP DATABASE IF EXISTS :dbname ;",
array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sql->execute(array(':dbname' => $dbname));

This will not work.
Anyone has an explanation ?
To Top