AirJD 焦点
AirJD

没有录音文件
00:00/00:00
加收藏

PHP使用SQL绑定变量 by 梁宇

发布者 phper
发布于 1431648021265  浏览 2832 关键词 PHP 
分享到

第1页

PHP使用SQL绑定变量
梁宇
http://blog.csdn.net/rilyu

第2页

什么是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对这条语句第一次会进行hard parse,以后就只进行soft parse。假设某条语句被重复执行了几十万次,那么使用bind var带来的好处是巨大的。一个应用程序如果bind var使用不充分,那么几乎一定会伴随着严重的性能问题。
注:引用自百度百科

第3页

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));
}

第4页

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语句,则存在被SQL注入攻击风险
绑定变量则把用户输入的数据作为变量值传入数据库,不可能有SQL注入
select * from CM_USER
where USER_NAME = :USER_NAME and USER_PASSWORD = :USER_PASSWORD

第5页

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

第6页

官方态度
 PHP官方在最初对SQL绑定变量并不重视,mysql模块不支持绑定变量

 提供mysql_escape_string函数转义特殊字符

 PHP 5开始提供mysqli模块,支持绑定变量

第7页

主流框架现状
注:Swoole示例语句来自iteye报导,其余均来自各自官方网站

第8页

Oracle访问函数: oraQuery
//通用查询函数,支持绑定变量,使用关联数组$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;
}

第9页

Oracle访问函数: oraExecute
//通用执行函数,支持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;
}

第10页

Oracle访问函数: 易用性封装
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)

第11页

Thanks
支持文件格式:*.ppt, *.pptx, *.pdf
上传最后阶段需要进行在线转换,可能需要1~2分钟,请耐心等待。