php绑定变量,PHP使用SQL绑定变量

什么是SQL绑定变量？

百度百科的解释是：在sql语句的条件中使用变量而不是常量。比如shared pool里有两条sql语句：

select * from tab1 where col1=1;

select * from tab1 where col1=2;

对oracle数据库来说，这是两条完全不同的SQL，对这两条语句都需要进行hard parse。因为oracle会根据sql语句的文本去计算每个字符在内存里的hash值，因此虽然上述两条SQL只有一个字符不一样，oracle根据hash算法在内存中得到的hash地址就不一样，所以oracle就会认为这是两条完全不同的语句。而如果将上述SQL改写成：

select * from tab1 where col1=:var1;

然后通过对变量var1的赋值去查询，那么oracle对这条语句第一次会进行hardparse，以后就只进行soft parse。假设某条语句被重复执行了几十万次，那么使用bind var带来的好处是巨大的。一个应用程序如果bind var使用不充分，那么几乎一定会伴随着严重的性能问题。

SQL绑定变量可以提高效率、防止SQL注入、易于分析优化等作用，以下分别作说明。

SQL绑定变量作用：提高效率

使用SQL绑定变量后SQL语句是静态的，第一次执行后数据库不需要再次解析SQL语句，从而提高SQL语句的执行效率。

测试场景：

Oracle数据库，CM_USER表有USER_ID、USER_NAME两个字段，表记录数为0，测试执行通过USER_ID查询USER_NAME，非绑定变量及绑定变量方式各执行10000次

测试结果：

非绑定变量耗时   9.919 秒 　　绑定变量耗时　   1.358 秒

测试代码：

$count = 10000;

//非绑定变量

for ($i = 1; $i <= $count; $i++)

{

$sql = sprintf("select USER_NAME from CM_USER where USER_ID = %d", $i);

oraQuery($sql);

}

//绑定变量

for ($i = 1; $i <= $count; $i++)

{

$sql = "select USER_NAME from CM_USER where USER_ID = :USER_ID";

oraQuery($sql, array("USER_ID" => $i));

}

SQL绑定变量作用：防止SQL注入

非绑定变量方式，如没经过字符串转义把用户输入内容直接拼接进SQL语句，则存在被SQL注入攻击风险：

select * from CM_USER

where USER_NAME = 'test' and USER_PASSWORD = '123';

select * from CM_USER

where USER_NAME = 'test' and USER_PASSWORD = '123' or USER_NAME = 'test';

绑定变量则把用户输入的数据作为变量值传入数据库，从而避免SQL注入风险：

select * from CM_USER

where USER_NAME = :USER_NAME and USER_PASSWORD = :USER_PASSWORD

SQL绑定变量作用：易于分析优化

非绑定变量动态拼接SQL语句，任何不同条件值均会导致SQL语句不一样，无法进行统计分析：

select * from CM_USER where USER_ID = 1111

select * from CM_USER where USER_ID = 2222

select * from CM_USER where USER_ID = 3333

绑定变量方式是静态SQL，SQL语句是恒定不变的，可以通过日志分析获得SQL语句的平均执行时间等指标，快速定位有问题的SQL以优化系统：

select * from CM_USER where USER_ID = :USER_ID

PHP SQL绑定变量应用现状

PHP官方最初对SQL绑定变量并不重视，mysql模块不支持绑定变量，不得不提供mysql_escape_string函数转义特殊字符以避免输入特殊字符、防止SQL注入，从PHP 5开始提供支持绑定变量的mysqli模块，相应地，早期的官方框架Zend Framework 1也是不支持SQL绑定变量的，估计另一个流行框架YII Framework 1也不支持，但在官方网站上找不到在用版本验证。以下是部分PHP主流框架对SQL绑定变量的支持情况(表中Swoole示例语句来自iteye报导，其余均来自各自官方网站)：

框架

示例语句

绑定变量

Zend Framework 1

$select->where('favorite_color = ?', 'yellow');

不支持

Zend Framework 2

$select->where(array('id' => 2));

支持

YII Framework 2

$query->where(['name' => null, 'age' => 20]);

支持

ThinkPHP

$where['name'] = ':name';

$list = $Model->where($where)

->bind(':name',I('name'))->select();

支持

CodeIgniter

$sql = "select * from T_USER where ID = ?";

$this->db->query($sql, array(3));

不支持

YAF

未提供

Swoole

$gets['where'][] = 'category=10';

$data = $model->gets($gets);

不支持

访问Oracle数据库实现

PHP访问数据库功能由各数据库提供的扩展模块支持，不同数据库、不同版本的用法有很大差异，我使用的是php_oci8_11g模块，实现SQL绑定变量访问比较简单，以下代码对访问Oracle数据库功能进行了简单函数封装，以使得更方便使用：

//通用查询函数，支持绑定变量，使用关联数组$params传递绑定的变量值，如为普通数组则参数必须以数字顺序命名，如：:0, :1

//示例：oraQuery("select sysdate from dual where DUMMY = :DUMMY", array("DUMMY" => "X"));

function oraQuery($sql, $params = array())

{

$stid = oci_parse($GLOBALS["conn"], $sql);

if (count($params) > 0)

{

foreach($params as $key => &$value)

{

$result = oci_bind_by_name($stid, ":{$key}", $value);

if (!$result) return $result;

}

}

$result = oci_execute($stid, $GLOBALS["oraExecMode"]);

if (!$result) return $result;

$result = array();

while (($row = oci_fetch_assoc($stid)) != FALSE)

$result[] = $row;

return $result;

}

//通用执行函数，支持update、insert、delete等，支持绑定变量

function oraExecute($sql, $params = array())

{

$stid = oci_parse($GLOBALS["conn"], $sql);

if (count($params) > 0)

{

foreach($params as $key => &$value)

{

$result = oci_bind_by_name($stid, ":{$key}", $value);

if (!$result) return $result;

}

}

$result = oci_execute($stid, $GLOBALS["oraExecMode"]);

if (!$result) return $result;

return $result;

}

//易用性封装

function oraSelect($table, $cond, $fields = "*")

{

$where = "";

foreach($cond as $key => $value)

{

if (!empty($where)) $where .= " and ";

$where .= "{$key} = :{$key}";

}

$sql = sprintf("select %s from %s where %s", $fields, $table, $where);

$result = oraQuery($sql, $cond);

return $result;

}

function oraInsert($table, $data)

function oraInsertList($table, $list)

function oraDelete($table, $data)

function oraDeleteList($table, $list)

function oraUpdate($table, $data, $cond)

注：本文改编自本人在珠三角技术沙龙上的演讲PPT，PPT下载地址：

http://techparty.org/techparty/2014/08/19/barcamp-slides/