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

This article mainly introduces the analysis of the PDO anti-injection principle and the precautions for using PDO. It gives a detailed description of the specific implementation methods for preventing PDO injection, which has good reference value, for more information, see

This article mainly introduces the analysis of the PDO anti-injection principle and the precautions for using PDO. It gives a detailed description of the specific implementation methods for preventing PDO injection, which has good reference value, for more information, see

This article describes in detail the analysis of the PDO anti-injection principle and precautions for using PDO. The specific analysis is as follows:

We all know that, as long as PDO is properly used, it can basically prevent SQL injection. This article mainly answers the following two questions:
Why use PDO instead of mysql_connect?
Why does PDO defend against injection?
What should I pay special attention to when using PDO anti-injection?

1. Why is PDO preferred?

The PHP manual makes it clear:
Prepared statements and stored procedures
Limitations 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 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 ).

That is, the PDO prepare method is used to improve the query performance of the same SQL template and prevent SQL injection.
In addition, the PHP Manual provides warning information.
Prior to PHP 5.3.6, this element was silently ignored. The same behaviour 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.

In PHP 5.3.6 and earlier versions, the charset definition in DSN is not supported. Instead, use PDO: MYSQL_ATTR_INIT_COMMAND to set the initial SQL, which is our commonly used set names gbk command.

I have seen some programs that are still trying to use addslashes for anti-injection purposes. As a result, this is actually more of a problem. For details, please refer
Some other practices: Before executing database queries, clear keywords such as select, union,... in SQL. This is obviously a very wrong way of handling. If the submitted body does contain the students's union, the original content will be tampered with after replacement, killing innocent people and making them unavailable.

Ii. Why does PDO defend against SQL injection?
First read the following PHP code:

The Code is as follows:

<? Php
$ 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 = 'hangsan ';
$ 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

To thoroughly understand the communication details between php and mysql server, I used wireshark packet capture for research. After wireshak is installed, we set the filter condition to tcp. port = 3306, for example:

In this way, only the communication data with mysql port 3306 is displayed to avoid unnecessary interference.
Note that wireshak is based on the wincap driver and does not support listening on the local loopback interface (that is, the method for connecting to local mysql using php cannot be listened on ), connect to MySQL of another machine (a virtual machine that bridges the network can also) for testing.

Then run our PHP program and the listening result is as follows. We found that PHP simply sends SQL directly to the MySQL Server:

In fact, this is similar to using mysql_real_escape_string to escape the string and then concatenate it into an SQL statement (only escape is completed by the PDO local driver ), obviously, this situation may still cause SQL injection. That is to say, in php, mysql_real_escape_string in pdo prepare is called locally to operate on the query. The local single-byte character set is used, when we pass multi-byte encoding variables, it may still cause the SQL Injection Vulnerability (one of the problems with php versions earlier than 5.3.6, which explains why PDO is used, we recommend that you upgrade to php 5.3.6 + and specify the reason for charset In The DSN string.

For php versions earlier than 5.3.6, the following code may still cause SQL Injection problems:

The Code is as follows:

$ 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.

So, how can I disable the local escape of PHP and escape it by the MySQL Server?
PDO has a parameter named PDO: ATTR_EMULATE_PREPARES, indicating whether to simulate prepare locally using PHP. The default value of this parameter is unknown. According to the packet capture analysis results, php 5.3.6 + uses local variables by default and concatenates them into SQL statements and sends them to the MySQL Server. We can set this value to false to try the results, run the following code:

The Code is as follows: