PDO：预处理语句（参数化查询）

@(PDO（PHP data object/PHP数据对象）)[PDO|预处理语句|参数化查询]

PDO Tutorial for MySQL Developers
Using Prepared Statements to Stop Injection Attacks

The database library called PHP Data Objects or PDO for short can use drivers for many different database types, and supports a very important feature known as prepared statements, sometimes also known as parametrized queries.

PDO::prepare

Paste_Image.png

在执行之前，对一条语句进行预处理，并返回一个语句对象。

预处理一条 SQL 语句，以便 PDOStatement::execute() 方法执行。该 SQL 语句可以包含 0 或更多个命名参数（:name）或问号参数（?），这些参数的真实值在语句执行的时候会被替换掉。使用这些参数绑定所有的用户输入的数据，不要在查询中直接包含用户输入的数据。

返回值：

如果数据库服务器成功地预处理了该语句，PDO::prepare() 将会返回一个 PDOStatement 对象；否则，返回 false 或 抛出 PDOException（依 error handling 而定）。

模拟的预处理语句并没有与数据库服务器进行通信，所以PDO::prepare()并没有检查该语句。

PDOStatement::bindParam

Paste_Image.png

原来 PDO 官方手册的简要描述的描述顺序有点怪怪的，并且后面的详细描述也不一致。所以这里把简要描述跟详细描述中的描述顺序统一一下。

Binds the specified variable name to a parameter.
绑定 指定的变量名（只能是 $name 的形式）到 一个参数（:name 或 ?参数 ，可以是 :name 或 从1 开始的索引 的形式）。

绑定 一个 PHP 变量 到 预处理语句中对应的命名占位符或问号占位符。

与 PDOStatement::bindValue() 不同的是：PDOStatement::bindParam() 中的变量是作为引用而绑定的，并且只有在调用 PDOStatement::execute() 的时候才会读取这个变量的值。

Note we used bindValue and not bindParam. Trying to bind a parameter by reference will generate a Fatal Error and this cannot be caught by PDOException either.
但如果需要循环执行预处理语句，最好使用bindParam，具体原因见对应的章节：Executing prepared statements in a loop。

返回值：

成功则返回 true，失败则返回 false

例如：

/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindParam(':colour', $colour, PDO::PARAM_STR, 12);
$sth->execute();

/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < ? AND colour = ?');
$sth->bindParam(1, $calories, PDO::PARAM_INT);
$sth->bindParam(2, $colour, PDO::PARAM_STR, 12);
$sth->execute();

PDOStatement::bindValue

Paste_Image.png

Binds a value to a parameter.

绑定 一个值（可以是 $name 或 'Jack' 的形式） 到 一个参数（:name 或 ?参数 ，可以是 :name 或 从1 开始的索引 的形式）。

绑定 一个值 到 预处理语句中对应的命名占位符或问号占位符。

返回值：

成功则返回 true，失败则返回 false

例如：

$stm->bindValue(':name',$name);  
$stm->bindValue(':name','Jack');  

PDOStatement::execute

Paste_Image.png

Executes a prepared statement.

执行一条 经过预处理的语句。

如果预处理语句中包含占位符，则必须执行以下两点之一：

• 调用PDOStatement::bindParam() 或 PDOStatement::bindValue() 把变量或值绑定到占位符上。
• 或 传入一个数组

1. 参数：

$input_parameters：一个数组。数组的元素数量 应该与 需要执行的 SQL 语句中占位符数量 相等。

• 所有的值作为 PDO::PARAM_STR 处理。

• 不能绑定多个值到一个单独的参数；比如，不能绑定两个值到 IN()子句中一个单独的命名占位符。

• 绑定值的数量不能超过指定的数量。如果在 $input_parameters 的键名数量 比 PDO::prepare() 中的 SQL 语句中指定的参数的数量还要多，则该语句将会失败并发出一个错误。

• $input_parameters 中的键名 必须和 SQL 中声明的 相匹配。在 PHP 5.2.0 之前，这是被忽略的。

2. 返回值：

成功则返回 true，失败则返回 false

例如：

/* Execute a prepared statement by passing an array of insert values */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->execute(array(':calories' => $calories, ':colour' => $colour));

/* Execute a prepared statement by passing an array of insert values */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < ? AND colour = ?');
$sth->execute(array($calories, $colour));

Preparing Statements using SQL functions

You may ask how do you use SQL functions with prepared statements. I've seen people try to bind functions into placeholders like so:

//THIS WILL NOT WORK!
$time = 'NOW()';
$name = 'BOB';
$stmt = $db->prepare("INSERT INTO table(`time`, `name`) VALUES(?, ?)");
$stmt->execute(array($time, $name));

This does not work, you need to put the function in the query as normal:

$name = 'BOB';
$stmt = $db->prepare("INSERT INTO table(`time`, `name`) VALUES(NOW(), ?)");
$stmt->execute(array($name));

You can bind arguments into SQL functions however:

$name = 'BOB';
$password = 'badpass';
$stmt = $db->prepare("INSERT INTO table(`hexvalue`, `password`) VALUES(HEX(?), PASSWORD(?))");
$stmt->execute(array($name, $password));

---

Also note that this does NOT work for LIKE statements:

//THIS DOES NOT WORK
$stmt = $db->prepare("SELECT field FROM table WHERE field LIKE %?%");
$stmt->bindParam(1, $search, PDO::PARAM_STR);
$stmt->execute();

So do this instead:

$stmt = $db->prepare("SELECT field FROM table WHERE field LIKE ?");
$stmt->bindValue(1, "%$search%", PDO::PARAM_STR);
$stmt->execute();

---

Note we used bindValue and not bindParam. Trying to bind a parameter by reference will generate a Fatal Error and this cannot be caught by PDOException either.
但如果需要循环执行预处理语句，最好使用bindParam，具体原因见对应的章节：Executing prepared statements in a loop。

Executing prepared statements in a loop

Prepared statements excel in being called multiple times in a row with different values.

Because the sql statement gets compiled first, it can be called multiple times in a row with different arguments, and you'll get a big speed increase vs calling mysql_query over and over again!

Typically this is done by binding parameters with bindParam. bindParam is much like bindValue except instead of binding the value of a variable, it binds the variable itself, so that if the variable changes, it will be read at the time of execute.

$values = array('bob', 'alice', 'lisa', 'john');
$name = '';
$stmt = $db->prepare("INSERT INTO table(`name`) VALUES(:name)");
$stmt->bindParam(':name', $name, PDO::PARAM_STR);
foreach($values as $name) {
   $stmt->execute();
}

Transactions

Here's an example of using transactions in PDO: (note that calling beginTransaction() turns off auto commit automatically):

try {
    $db->beginTransaction();
 
    $db->exec("SOME QUERY");
 
    $stmt = $db->prepare("SOME OTHER QUERY?");
    $stmt->execute(array($value));
 
    $stmt = $db->prepare("YET ANOTHER QUERY??");
    $stmt->execute(array($value2, $value3));
 
    $db->commit();
} catch(PDOException $ex) {
    //Something went wrong rollback!
    $db->rollBack();
    echo $ex->getMessage();
}