PDO principle and correct use method

Preface

As the method of database parameterized query becomes more and more common, SQL injection vulnerabilities are greatly reduced compared with before, and PDO is the most typical one in PHP. The pre-compiled query method is becoming more and more widely used.

As we all know, PDO is the best way to prevent SQL injection in PHP, but it is not a 100% way to eliminate SQL injection. The key depends on how to use it.

I learned in an article before that problems such as multi-sentence execution caused by controllable parameters in PDO scenarios (https://xz.aliyun.com/t/3950), so I discussed SQL injection in the PDO scenario has been explored again.

PDO query statements can control existing security issues:

First create a new library and table locally and write something casually.

Then write a test.php and use PDO to perform a simple query:

<?php 
try{
  $db = new PDO(&#39;mysql:host=localhost;dbname=pdotest&#39;,&#39;root&#39;,&#39;&#39;);
} 
catch(Exception $e)
{  echo $e->getMessage();
}if(isset($_GET[&#39;id&#39;]))
{
  $id = $_GET[&#39;id&#39;];
}else{
  $id=1;
}
$query = "select balabala from table1 where 1=?";echo "id:".$id."</br>";
$row = $db->prepare($query);
$row->bindParam(1,$id);
$row->execute();
$result = $row->fetch(PDO::FETCH_ASSOC);if($result)
{  echo "结果为：";
  print_r($result);  echo "</br>";
}

Print the input content and the results obtained on the page:

PDO has the following three main settings related to security issues:

PDO::ATTR_EMULATE_PREPARES
PDO::ATTR_ERRMODE
PDO::MYSQL_ATTR_MULTI_STATEMENTS

are respectively related to simulated pre-compilation, error reporting and multi-sentence execution.

PDO allows multi-sentence execution and simulated precompilation by default. It has been written in many previous articles that when the parameters are controllable, it will lead to stack injection.

For example, if we change the query statement to:

$query = "select balabala from table1 where 1={$id}";
$row = $db->query($query);

, we can perform illegal operations on $query before the $db->query() step is executed. Then PDO is equivalent to Useless:

Security risks in PDO default settings:

If we have no controllable parameters in the query statement, and enter the parameters according to Is there no problem if I write it in prepare->bindParam->execute?

We query according to the following statement:

$query = "select balabala from table1 where 1=?";
$row = $db->prepare($query);
$row->bindParam(1,$_GET[‘id’]);
$row->execute();

We enter a parameter in the URL: ?id=asdasd, and then set SET GLOBAL GENERAL_LOG=ON to monitor in real time from the .log. Take a look at what the sql statement actually executes:

We found that the simulated precompiled request sending method is no different from the previous mysqli, but we noticed that in the original The parameters are not wrapped in single quotes in the query statement, but they are wrapped in single quotes here, so we can try to enter some special characters, such as single quotes:

We found that the single quotes were escaped. At this time, we couldn't help but think about what would happen if gbk encoding was set:

We will find that select * from table1 is executed successfully, although PDO will only return a result, but it is indeed executed.

In other words, even if there are no controllable parameters in the query statement, but only bound parameters such as ? or :id, stack injection can still be performed.

What if you turn off multi-sentence execution?

We set PDO::MYSQL_ATTR_MULTI_STATEMENTS to false and repeat the above operation:

We found that it no longer works.

In fact, only the statement of setting gbk was executed

But is this the end?

Why not try other methods such as union injection?

After trying it, I found that union injection is also possible! No need for multi-sentence execution at all!

实际上，在模拟预编译的情况下，PDO对于SQL注入的防范（PDO::queto()），无非就是将数字型的注入转变为字符型的注入，又用类似mysql_real_escape_string()的方法将单引号、双引号、反斜杠等字符进行了转义。

这种防范方法在GBK编码的情况下便可用宽字节进行绕过，而在非GBK编码的情况下，若存在二次注入的情况，是否能利用呢？

答案是否定的。

二次注入是由于对添加进数据库中的数据没有再次处理和转义而导致的，而预编译对每次查询都进行转义，则不存在二次注入的情况。

上述安全隐患，是由于未正确设置PDO造成的，在PDO的默认设置中，PDO::ATTR_EMULATE_PREPARES和PDO::MYSQL_ATTR_MULTI_STATEMENTS都是true，意味着模拟预编译和多句执行是默认开启的。

而在非模拟预编译的情况下，若语句中没有可控参数，是否还能这样做呢？

答案是否定的。

我们将PDO::ATTR_EMULATE_PREPARES设为false，来看看sql语句到底执行了什么：

它对每一句sql语句都进行了预编译和执行两个操作，在执行select balabala from table1 where 1=?这句时，如果是GBK编码，那么它将会把?绑定的参数转化成16进制，这样无论输入什么样的东西都无法再进行注入了。

如果不是GBK编码，如上面所说，也不存在二次注入的情况，故可以避免SQL注入漏洞。

相同原理的Prepare Statement方法

PDO的原理，与Mysql中prepare语句是一样的。上面PDO所执行的SQL语句，用如下的方式可以等效替代：

Set @x=0x31
Prepare a from “select balabala from table1 where 1=?”
Execute a using @x

我们可以手动将输入的参数设置为@x，并将其转化为16进制，随后预编译，再执行

也就是说，不用PDO也可以仿照其原理手动设置预编译：

$db = new mysqli('localhost','root','','pdotest');if(isset($_GET['id']))
{
	$id = "0x".bin2hex($_GET['id']);
}else{
	$id=1;
}echo "id:".$id."</br>";
$db->query("set names gbk");
$db->query("set @x={$id}");
$db->query("prepare a from &#39;select balabala from table1 where 1=?&#39;");
$row = $db->query("execute a using @x");
$result = $row->fetch_assoc();if($result)
{	echo "结果为：";
	print_r($result);	echo "</br>";
}

得到的结果和使用PDO是一样的：

这样设置不用担心没有合理地设置PDO，或是用了GBK编码等情况。

Prepare Statement在SQL注入中的利用

Prepare语句在防范SQL注入方面起到了非常大的作用，但是对于SQL注入攻击却也提供了新的手段。

Prepare语句最大的特点就是它可以将16进制串转为语句字符串并执行。如果我们发现了一个存在堆叠注入的场景，但过滤非常严格，便可以使用prepare语句进行绕过。

例如我们将createtable table2 like table1转化成16进制，然后执行：

我们发现数据库中已经多了一个表table2。则语句成功执行了。

总结

对于此类问题的防范，主要有以下三个方面：

1. 合理、安全地使用gbk编码。即使采用PDO预编译的方式，如若配置不当，依然可造成宽字节注入

2. 使用PDO时，一定要将模拟预编译设为false

3. 可采用使用Prepare Statement手动预编译，杜绝SQL注入

相关文章教程推荐：网站安全教程

The above is the detailed content of PDO principle and correct use method. For more information, please follow other related articles on the PHP Chinese website!