zend framework防止sql注入方法

一、问题描述:

public function ly($account)

{

$sql = “SELECT TO_CHAR(QUESTIONDATETIME,’YYYY-mm-dd hh24:mi:ss’) AS QUESTIONDATETIME, QUESTION, ANSWER, REPLYER

FROM BL_CUSTOMERQUESTION

WHERE CUSTOMERID = ‘$account’

AND DATATYPE = ‘0’ ORDER BY QUESTIONDATETIME DESC”;

$result = $this->_db->fetchAll($sql);

return $result;

}

以上代码存在sql注入的问题,使用?的方式,可以有效的防止的SQL的注入

public function ly($account)

{

$sql = “SELECT TO_CHAR(QUESTIONDATETIME,’YYYY-mm-dd hh24:mi:ss’) AS QUESTIONDATETIME, QUESTION, ANSWER, REPLYER

FROM BL_CUSTOMERQUESTION

WHERE CUSTOMERID = ?

AND DATATYPE = ‘0’ ORDER BY QUESTIONDATETIME DESC”;

$result = $this->_db->fetchAll($sql, array($account));

return $result;

}

 

二、Sql注入的例子:

       $account = “123456′ or 1=’1”;

        $sql = “SELECT *

                 FROM BL_CUSTOMERQUESTION

                 WHERE CUSTOMERNAME = $account

               ORDER BY QUESTIONDATETIME DESC”;

        $result = $this->_db->fetchAll($sql);

 

上面的例子会把所有的记录查询出来

以下书写可以防止sql注入:

       $account = “123456′ or 1=’1”;

        $sql = “SELECT *

                 FROM BL_CUSTOMERQUESTION

                 WHERE CUSTOMERNAME = ?

               ORDER BY QUESTIONDATETIME DESC”;

        $result = $this->_db->fetchAll($sql, array($account));

 

三、防止sql注入总结:

1. 接收参数时过滤

Post, get方式接收参数的时候,通过Zend_Filter::filterStatic进行过滤,例如:

  • 过滤数字

$gender = Zend_Filter::filterStatic($this->_getParam(“GENDER”),’Digits’);

 

  • 过滤字母数字

$papcode = Zend_Filter::filterStatic($this->_getParam(“PAPCODE”),’Alnum’);

 

  • 过滤html标签

$username = Zend_Filter::filterStatic($this->_getParam(“USERNAME”),’HtmlEntities’);

 

  • 还有很多,大家可以参考zend framework手册

2. 执行sql语句的时候过滤

Sql语句中所有有参数的地方都用?替代,然后在$this->_db->fetchAll或$this->_db->fetchRow等操作的时候把变量带上,例如:

       $account = “123456′ or 1=’1”;

        $sql = “SELECT *

                 FROM BL_CUSTOMERQUESTION

                 WHERE CUSTOMERNAME = ?

               ORDER BY QUESTIONDATETIME DESC”;

        $result = $this->_db->fetchAll($sql, array($account));

 

Leave a Reply

Your email address will not be published. Required fields are marked *