Analysis of PDO anti-injection principle and precautions for using PDO

We all know that as long as PDO is used reasonably and correctly, SQL injection can basically be prevented. This article mainly answers the following two questions:

Why use PDO instead of mysql_connect?

Why is PDO anti-injection?

What should you pay special attention to when using PDO to prevent injection?

1. Why should you use PDO first?

The PHP manual makes it very clear:

Prepared statements and procedures stored
Many of the more mature databases support the concept of prepared statements. What are they? They can be thought of as a kind of compiled template for the SQL that an application wants to run, that can be customized using variable parameters. Prepared statements offer two major benefits:
The query only needs to be parsed (or prepared) once, but can be executed multiple times with the same or different parameters. When the query is prepared, the database will analyze, compile and optimize its plan for executing the query. For complex queries this process can take up enough time that it will noticeably slow down an application if there is a need to repeat the same query many times with different parameters. By using a prepared statement the application avoids repeating the analyze/compile/optimize cycle. This means that prepared statements use fewer resources and thus run faster. The parameters to prepared statements don't need to be quoted; the driver automatically handles this. If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur(however, if other portions of the query are being built up with unescaped input, SQL injection is still possible).

Even using the prepare method of PDO, the main purpose is to

improve the performance of the same SQL template query and prevent SQL injection
At the same time, PHP manual A warning message was given in

Prior to PHP 5.3.6, this element was silently ignored. The same behavior can be partly replicated with the

PDO::MYSQL_ATTR_INIT_COMMAND driver option, as the following example shows.

Warning

The method in the below example can only be used with character sets that share the same lower 7 bit representation as ASCII, such as ISO-8859-1 and UTF-8. Users using character sets that have different representations (such as UTF-16 or Big5) must

use the

charset option provided in PHP 5.3.6 and later versions.

It means that in PHP 5.3.6 and previous versions, charset definition in DSN is not supported, but PDO::MYSQL_ATTR_INIT_COMMAND should be used to set the initial SQL, which is our commonly used set names gbk command.

I saw some programs still trying to use addslashes to prevent injection, but I didn’t know that this actually caused more problems. For details, please see http://www.lorui.com/addslashes-mysql_escape_string-mysql_real_eascape_string.html

Also There are some methods: before executing the database query, clean up keywords such as select, union, .... in SQL. This approach is obviously a very wrong way to handle it. If the submitted text does contain the students's union, the replacement will tamper with the original content, killing innocent people indiscriminately, and is not advisable.

2. Why can PDO prevent SQL injection? Please look at the following PHP code first:

$pdo = new PDO("mysql:host=192.168.0.1;dbname=test;charset=utf8","root");

$st = $pdo->prepare("select * from info where id =? and name = ?");

$id = 21;

$name = 'zhangsan ';

$st->bindParam(1,$id);

$st->bindParam(2,$name);

$st->execute() ;

$st->fetchAll();

?>

The environment is as follows:

PHP 5.4.7

Mysql protocol version 10

MySQL Server 5.5.27

In order to thoroughly understand the details of the communication between PHP and MySQL server, I specially used wireshark to capture packets for research. After installing wireshak, we set the filter condition to tcp.port==3306, as shown below:

This way, only the communication data with mysql 3306 port is displayed to avoid unnecessary interference.

It is important to note that wireshak is based on the wincap driver and does not support listening on the local loopback interface (even if you use php to connect to the local mysql method, it cannot be listened). Please connect to other machines (virtual machines with bridged networks also Can be tested with MySQL.

Then run our PHP program, the listening results are as follows, we found that PHP simply sends SQL directly to MySQL Server:

In fact, this is related to We usually use mysql_real_escape_string to escape the string, and then splice it into a SQL statement. There is no difference (it is only escaped by the PDO local driver). Obviously, in this case, it is still possible to cause SQL injection, that is to say, calling it locally in php Mysql_real_escape_string in pdo prepare operates query, using the local single-byte character set, and when we pass multi-byte encoded variables, it may still cause SQL injection vulnerabilities (one of the problems in versions before PHP 5.3.6, This also explains why when using PDO, it is recommended to upgrade to php 5.3.6+ and specify charset in the DSN string

For versions before php 5.3.6, the following code may still cause SQL injection. Question:

$pdo->query('SET NAMES GBK');

$var = chr(0xbf) . chr(0x27) . "OR 1=1 /*";

$query = "SELECT * FROM info WHERE name = ?";

$stmt = $pdo->prepare($query);

$stmt->execute(array($var));

The reason is consistent with the above analysis.

The correct escape should be to specify the character set for mysql Server and send the variable to MySQL Server to complete the character escape.

Then, How to disable PHP local escaping and let MySQL Server escape?

PDO has a parameter named PDO::ATTR_EMULATE_PREPARES , which indicates whether to use PHP to simulate prepare locally. The default value of this parameter is unknown. . And according to the results of the packet capture analysis just now, PHP 5.3.6+ still uses local variables to convert and splice them into SQL to send to MySQL Server. We set this value to false and try the effect, as shown in the following code:

$pdo = new PDO("mysql:host=192.168.0.1;dbname=test;","root");

$pdo->setAttribute(PDO:: ATTR_EMULATE_PREPARES, false);

$st = $pdo->prepare("select * from info where id =? and name = ?");

$id = 21;

$name = 'zhangsan';

$st->bindParam(1,$id);

$st->bindParam(2,$name);

$st->execute();

$ st->fetchAll();

?>

The red line is the content we just added. Run the following program and use wireshark to capture and analyze the packets. The results are as follows:

Did you see it? This is the magic. It can be seen that this time PHP sends the SQL template and variables to MySQL in two times, and MySQL completes the escaping of the variables. Since the variables and SQL template are sent in two times, then there is no SQL injection is a problem, but you need to specify the charset attribute in the DSN, such as:

$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root' );

In this way, the problem of SQL injection can be fundamentally eliminated. If you are not very clear about this, you can send an email to zhangxugg@163.com, to discuss it together.

3. Precautions for using PDO

After knowing the above points, we can summarize several precautions for using PDO to prevent SQL injection:

1. Upgrade php to 5.3.6+, For production environments, it is strongly recommended to upgrade to php 5.3.9+ php 5.4+. PHP 5.3.8 has a fatal hash collision vulnerability.

2. If you use PHP 5.3.6+, please specify the charset attribute in the DSN of PDO3. If you use PHP 5.3.6 and previous versions, set the PDO::ATTR_EMULATE_PREPARES parameter is false (that is, variable processing is performed by MySQL), PHP 5.3.6 or above has already dealt with this problem, whether using local simulation prepare or calling prepare of mysql server. Specifying a charset in a DSN is invalid, and the execution of set names is essential.

4. If you use PHP 5.3.6 and previous versions, because the Yii framework does not set the value of ATTR_EMULATE_PREPARES by default, please set it in the database configuration file Specify The value of emulatePrepare is false.

So, there is a question. If charset is specified in the DSN, do I still need to execute set names ?

Yes, you can’t save it. set names actually has two functions:

A. Tell the mysql server what encoding the client (PHP program) submitted to it

B. Tell mysql server, what is the encoding of the results that the client needs? In other words, if the data table uses the gbk character set and the PHP program uses UTF-8 encoding, we run set names utf8 before executing the query, telling Mysql server only needs to encode it correctly, and there is no need to convert the encoding in the program. In this way, we submit the query to mysql server in utf-8 encoding, and the results obtained will also be in utf-8 encoding. This eliminates the problem of conversion encoding in the program. Don't have any doubts. This will not produce garbled code.

So what is the role of specifying charset in DSN? It just tells PDO that the local driver uses the specified character set when escaping (it does not set the mysql server communication character set), and sets the mysql server communication character set. You must also use the set names command.

If the picture is lost, you can send an email to zhangxugg@163.com,
to request a PDF version.

I really can’t figure out why some new projects don’t use PDO but use the traditional mysql_XXX function library? If PDO is used correctly, SQL injection can be fundamentally eliminated. I strongly recommend that the technical leaders and front-line technical R&D personnel of each company pay attention to this issue and use PDO as much as possible to speed up project progress and safety quality.

Don’t try to write your own SQL injection filtering function library (it’s cumbersome and can easily create unknown vulnerabilities).

The above introduces the analysis of the anti-injection principle of PDO and the precautions for using PDO, including the content. I hope it will be helpful to friends who are interested in PHP tutorials.