PDO参数化查询

参数化查询（Parameterized Query或Parameterized Statement）是指在设计与数据库链接并访问数据时，在需要填入数值或数据的地方，使用参数（Parameter）来给值，这个方法目前已被视为最有效可预防SQL注入攻击的攻击手法的防御方式。

除了安全因素，相比起拼接字符串的SQL语句，参数化的查询往往有性能优势。因为参数化的查询能让不同的数据通过参数到达数据库，从而公用同一条SQL语句。大多数数据库会缓存解释SQL语句产生的字节码而省下重复解析的开销。如果采取拼接字符串的SQL语句，则会由于操作数据是SQL语句的一部分而非参数的一部分，而反复大量解释SQL语句产生不必要的开销。

原理

在使用参数化查询的情况下，数据库服务器不会将参数的内容视为SQL指令的一部分来处理，而是在数据库完成SQL指令的编译后，才应用参数运行，因此就算参数中含有具破坏性的指令，也不会被数据库所运行。

PDO

PDO用于PHP之内。在使用PDO驱动时，参数查询的使用方法一般为：

// 实例化数据抽象层对象
$db = new PDO('pgsql:host=127.0.0.1;port=5432;dbname=testdb');
// 对SQL语句执行prepare，得到PDOStatement对象
$stmt = $db->prepare('SELECT * FROM "myTable" WHERE "id" = :id AND "is_valid" = :is_valid');
// 绑定参数
$stmt->bindValue(':id', $id);
$stmt->bindValue(':is_valid', true);
// 查询
$stmt->execute();
// 获取数据
foreach($stmt as $row) {
    var_dump($row);
}

对于MySQL的特定驱动，也可以这样使用：

$db = new mysqli("localhost", "user", "pass", "database");
$stmt = $db -> prepare("SELECT priv FROM testUsers WHERE username=? AND password=?");
$stmt -> bind_param("ss", $user, $pass);
$stmt -> execute();

值得注意的是，以下方式虽然能有效防止SQL注入（归功于mysql_real_escape_string函数的转义），但并不是真正的参数化查询。其本质仍然是拼接字符串的SQL语句。

$query = sprintf("SELECT * FROM Users where UserName='%s' and Password='%s'", 
                  mysql_real_escape_string($Username), 
                  mysql_real_escape_string($Password));
mysql_query($query);

使用事例

1、准备语句

重复执行一个SQL查询，通过每次迭代使用不同的参数，这种情况使用预处理语句运行效率最高。使用预处理语句，首先需要在数据库服务器中先准备好“一个SQL语句”，但并不需要马上执行。PDO支持使用“占位符”语法，将变量绑定到这个预处理的SQL语句中。对于一个准备好的SQL语句，如果在每次执行时都要改变一些列值，这种情况必须使用“占位符号”而不是具体的列值。在PDO中有两种使用占位符的语法：“命名参数”和“问号参数”，使用哪一种语法要看个人的喜好。

使用命名参数作为占位符的INSERT插入语句：

复制代码代码如下:

$dbh->prepare(“insert into contactinfo(name,address,phone) values(:name,:address,:phone)”);

需要自定义一个字符串作为“命名参数”，每个命名参数需要冒号(：)开始，参数的命名一定要有意义，最好和对应的字段名称相同。
使用问号(?)参数作为占位符的INSERT插入语句：

复制代码代码如下:

$dbh->prepare(“insert into contactinfo(name,address,phone) values(?,?,?)”);

问号参数一定要和字段的位置顺序对应。不管是使用哪一种参数作为占位符构成的查询，或是语句中没有用到占位符，都需要使用PDO对象中的prepare()方法，去准备这个将要用于迭代执行的查询，并返回PDOStatement类对象。

2、绑定参数

当SQL语句通过PDO对象中的prepare()方法在数据库服务器端准备好了以后，如果使用了占位符，就需要在每次执行时替换输入的参数。可以通过PDOStatement对象中的bindParam()方法，把参数变量绑定到准备好的占位符上(位置或名字要对应)。方法bindParame()的原型如下所示：

复制代码代码如下:

bool PDOStatement::bindParam ( mixed $parameter , mixed &$variable [, int $data_type = PDO::PARAM_STR [, int $length [, mixed $driver_options ]]] )

第一个参数parameter是必选项，如果在准备好的查询中占位符语法使用名字参数，那么将名字参数字符串作为bindParam()方法的第一个参数提供。如果占位符语法使用问号参数，那么将准备好的查询中列值占位符的索引偏移量，作为该方法的第一个参数。

第二个参数variable也是可选项，提供供给第一个参数所指定占位符的值。因为该参数是按引用传递的，所以只能提供变量作为参数，不能直接提供数值。

第三个参数data_type是可选项，为当前被绑定的参数设置数据类型。可以为以下值。

PDO::PARAM_BOOL 代表boolean数据类型。
PDO::PARAM_NULL 代表SQL中的NULL类型。
PDO::PARAM_INT 代表SQL中的INTEGER数据类型。
PDO::PARAM_STR 代表SQL中的CHAR、VARCHAR和其他字符串数据类型。
PDO::PARAM_LOB 代表SQL中大对象数据类型。

第四个参数length是可选项，用于指定数据类型的长度。

第五个参数driver_options是可选项，通过该参数提供任何数据库驱动程序特定的选项。
使用命名参数作为占位符的参数绑定示例：

复制代码代码如下:

<?php
//...省略PDO连接数据库代码
$query = "insert into contactinfo (name,address,phone) values(:name,:address,:phone)";
$stmt = $dbh->prepare($query);          //调用PDO对象中的prepare()方法
 
$stmt->blinparam(':name',$name);        //将变量$name的引用绑定到准备好的查询名字参数":name"中
$stmt->blinparam(':address',$address);
$stmt->blinparam(':phone',phone);
//...
?>

使用问号（？）作为占位符的参数绑定示例：

复制代码代码如下:

<?php
//...省略PDO连接数据库代码
$query = "insert into contactinfo (name,address,phone) values(?,?,?)";
$stmt = $dbh->prepare($query);          //调用PDO对象中的prepare()方法
 
$stmt->blinparam(1,$name,PDO::PARAM_STR);        //将变量$name的引用绑定到准备好的查询名字参数":name"中
$stmt->blinparam(2,$address,PDO::PARAM_STR);
$stmt->blinparam(3,phone,PDO::PARAM_STR,20);
//...
?>

3、执行准备语句

当准备语句完成，并绑定了相应的参数后，就可以通过调用PDOStatement类对象中的execute()方法，反复执行在数据库缓存区准备好的语句了。在下面的示例中，向前面提供的contactinfo表中，使用预处理方式连续执行同一个INSERT语句，通过改变不同的参数添加两条记录。如下所示：

复制代码代码如下:

<?php 
try {
     $dbh = new PDO('mysql:dbname=testdb;host=localhost', $username, $passwd);
}catch (PDOException $e){
    echo '数据库连接失败：'.$e->getMessage();
    exit;
}
 
$query = "insert into contactinfo (name,address,phone) values(?,?,?)";
$stmt = $dbh->prepare($query);
 
$stmt->blinparam(1,$name);      
$stmt->blinparam(2,$address);
$stmt->blinparam(3,phone);
 
$name = "赵某某";
$address = "海淀区中关村";
$phone = "15801688348";
 
$stmt->execute();           //执行参数被绑定后的准备语句
?>

如果你只是要传递输入参数，并且有许多这样的参数要传递，那么你会觉得下面所示的快捷方式语法非常有帮助。是通过在execute()方法中提供一个可选参数，该参数是由准备查询中的命名参数占位符组成的数组，这是第二种为预处理查询在执行中替换输入参数的方式。此语法使你能够省去对$stmt->bindParam()的调用。将上面的示例做如下修改：

复制代码代码如下:

<?php 
//...省略PDO连接数据库代码
$query = "insert into contactinfo (name,address,phone) values(?,?,?)";
$stmt = $dbh->prepare($query); 
 
//传递一个数组为预处理查询中的命名参数绑定值，并执行一次。
$stmt->execute(array("赵某某","海淀区","15801688348"));
?>

另外，如果执行的是INSERT语句，并且数据表中有自动增长的ID字段，可以使用PDO对象中的lastinsertId()方法获取最后插入数据表中的记录ID。如果需要查看其他DML语句是否执行成功，可以通过PDOStatement类对象中的rowCount()方法获取影响记录的行数。