PDO anti-injection Principle Analysis and precautions for using PDO, pdo precautions

PDO anti-injection Principle Analysis and precautions for using PDO, pdo precautions

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 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.bkjia.com/article/49205.htm for details
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:
Copy codeThe 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:
Copy codeThe 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:

Copy codeThe Code is as follows: <? Php
$ Pdo = new PDO ("mysql: host = 192.168.0.1; dbname = test;", "root ");
$ Pdo-> setAttribute (PDO: ATTR_EMULATE_PREPARES, false); // The content we just added
 
$ 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 ();
?>
 
Run the program and use wireshark for packet capture 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:
Copy codeThe Code is as follows: $ 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 correct encoding, no need to encode 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.
 
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 ).

I hope this article will help you with PHP programming.

PHP pre-processes PDO to prevent Injection

Pdo can be used for preprocessing to effectively prevent SQL injection to bgim.

Pdo problems in php

This is a bit too big, and security is not just about SQL!
Using PDO also prevents SQL injection. You need to check your user input !!!! Image single quotes
Php can also be used in several ways! I can't tell you all about it. Let's take a look at the materials. This is usually mentioned in the tutorials!