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

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 withPDO: MYSQL_ATTR_INIT_COMMANDDriver 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)MustUseCharsetOption 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.PDO: MYSQL_ATTR_INIT_COMMAND sets the initial SQL, which is our commonly used set names gbk command.

 

I see some programs, still trying to use addslashes to achieve the purpose of anti-injection, but this is actually more problems, please refer to the http://www.lorui.com/addslashes-mysql_escape_string-mysql_real_eascape_string.html

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:

<? 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:

 

 

Actually, 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:

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

<? Php

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

 

$ 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 packets for analysis. The result is as follows:

 

 

Have you seen it? This is amazing. We can see that this PHP sends the SQL template and variables to MySQL twice, and MySQL completes variable escape processing, since the variables and the SQL template are sent twice, there is no SQL Injection problem, but you need to specify the charset attribute in the DSN, such:

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

 

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

 

3. Precautions for using PDO

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

1. php is upgraded to 5.3.6 +. We strongly recommend that you upgrade to php 5.3.9 + php 5.4 + in the production environment. php 5.3.8 has a fatal hash Collision Vulnerability.

 

2. If php 5.3.6 + is used, specify the charset attribute in the DSN of PDO.

3. if PHP 5.3.6 and earlier versions are used, set the PDO: ATTR_EMULATE_PREPARES parameter to false (that is, MySQL processes the variables). php 5.3.6 and later versions have already handled this problem, you can either simulate prepare locally or call the prepare of mysql server. Specifying charset in DSN is invalid, and execution of set names <charset> is essential.

 

4. If PHP 5.3.6 and earlier versions are used, because the Yii framework does not set the ATTR_EMULATE_PREPARES value by default, set emulatePrepare to false in the database configuration file.

 

So there is a problem. If charset is specified in DSN, do you still need to execute set names <charset>?

Yes, it cannot save. Set names <charset> has two functions:

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

B. Tell mysql server what is the result code required by the client

That is to say, 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 to be correctly encoded without coding the conversion in the program. In this way, we submit the query to mysql server in UTF-8 encoding, and the result will also be UTF-8 encoding. This saves the trouble of converting the code in the program. do not have any questions.

 

So what is the role of charset specified in DSN? It only tells PDO that the specified character set (not the mysql server communication Character set) is used for escape of the local driver, and the set names <charset> command must be used to set the mysql server communication character set.

 

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

 

I can't figure it out. Why do I use the traditional mysql_XXX function library instead of PDO for some new projects? If PDO is used correctly, SQL injection can be eliminated fundamentally. I strongly recommend that the Technical Directors and front-line technical developers of various companies pay attention to this issue, use PDO whenever possible to speed up project progress and security quality.

 

Do not write the SQL Injection filter function library by yourself (tedious and easy to create unknown vulnerabilities ).