Shulou(Shulou.com)06/03 Report--

This article mainly introduces "how to understand PDO anti-injection principle analysis and matters needing attention". In daily operation, I believe many people have doubts about how to understand PDO anti-injection principle analysis and matters needing attention. Xiaobian consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "how to understand PDO anti-injection principle analysis and matters needing attention". Next, please follow the editor to study!

The specific analysis is as follows:

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

Why use PDO instead of mysql_connect?

Why can PDO prevent injection?

What should I pay special attention to when using PDO anti-injection?

First, why give priority to using PDO?

The PHP manual says very clearly:

Prepared statements and stored procedures

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 if the prepare method of PDO is used, it is mainly to improve the query performance of the same SQL template and prevent SQL injection.

At the same time, warnings are given in the PHP manual

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.

This means that in PHP 5.3.6 and earlier, the charset definition in DSN is not supported, but the initial SQL, our commonly used set names gbk instruction, should be set using PDO::MYSQL_ATTR_INIT_COMMAND.

I have seen some programs that are still trying to use addslashes to prevent injection, but they do not realize that there are more problems. For more information, please see https://www.jb51.net/article/49205.htm.

There are also some practices: before executing the database query, change select, union,.... in SQL. Get rid of keywords like that. This is obviously a very wrong way to deal with it. If the submitted text does contain the students's union, it will tamper with the original content after replacement, killing innocent people indiscriminately and undesirable.

Second, why can PDO prevent SQL injection?

Take a look at the following PHP code first:

The copy code is as follows:

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 grab package to study it. After installing wireshak, we set the filter condition to tcp.port==3306, as shown below:

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

It is important to note that wireshak is based on wincap driver and does not support the listening of local loopback interfaces (that is, it is impossible to listen by using php to connect to local mysql). Please connect to the MySQL of other machines (virtual machines that bridge the network) for testing.

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

In fact, this is no different from using mysql_real_escape_string to escape strings and then splicing them into SQL statements (only PDO local drivers complete the escape). Obviously, in this case, it is still possible to cause SQL injection, that is, when php locally calls mysql_real_escape_string in pdo prepare to operate query, using the local single-byte character set, and when we pass multi-byte encoded variables It is still possible to cause SQL injection vulnerabilities (one of the problems with php versions prior to 5.3.6, which explains why it is recommended to upgrade to php 5.3.6 vulnerabilities when using PDO and specify charset in the DSN string.

For php versions prior to 5.3.6, the following code can still cause SQL injection problems:

The copy 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 would be to assign a character set to mysql Server and send the variable to MySQL Server to complete the escape according to the character.

So, how can we prohibit the local escape of PHP and leave it to MySQL Server?

PDO has a parameter called 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 packet analysis we just grabbed, php 5.3.6 + defaults to use local variables to transfer and splice SQL to send to MySQL Server. Let's set this value to false to try the effect, such as the following code:

The copy code is as follows:

Run the program, use wireshark to grab the package analysis, and the result is as follows:

You see that? This is the magic. It can be seen that this time PHP sends SQL templates and variables to MySQL twice, and MySQL completes the escape of variables. Since variables and SQL templates are sent twice, there is no problem of SQL injection, but you need to specify charset attributes in DSN, such as:

The copy code is as follows:

$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 email it to zhangxugg@163.com and discuss it together.

Matters needing attention in using PDO

With the above in mind, we can summarize a few considerations for using PDO to eliminate SQL injection:

1. Php is upgraded to 5.3.6pm. It is strongly recommended to upgrade to php 5.3.9+ php 5.4powered php 5.3.8 in production environment. There is a fatal hash collision vulnerability.

two。 If you are using php 5.3.6 attributes, specify the charset attribute in the DSN of PDO

3. If you use PHP 5.3.6 or earlier, and set the PDO::ATTR_EMULATE_PREPARES parameter to false (that is, variables are handled by MySQL), php 5.3.6 or later has dealt with this problem, whether using local emulation prepare or calling mysql server's prepare. Specifying charset in DSN is invalid, and the execution of set names is essential.

4. If you are using PHP 5.3.6 or earlier, because the value of ATTR_EMULATE_PREPARES is not set by default in the Yii framework, specify the value of emulatePrepare as false in the database configuration file.

So, the question is, if you specify charset in DSN, do you still need to execute set names?

No, it can't be saved. Set names actually has two functions:

a. Tell mysql server what code the client (PHP program) submitted to it

b. Tell mysql server what the coding of the result is required by the client

That is, if the datasheet uses the gbk character set and the PHP program uses UTF-8 encoding, we can run set names utf8 before executing the query and tell mysql server to encode correctly without having to convert it in the program. In this way, we submit the query to mysql server in utf-8 code, and the result will also be utf-8 code. Save the conversion coding problem in the program, no doubt, this will not produce garbled code.

So what is the purpose of specifying charset in DSN? Just tell PDO that the local driver uses the specified character set (not the mysql server communication character set) when escaping, sets the mysql server communication character set, and uses the set names instruction.

At this point, the study on "how to understand the analysis of PDO anti-injection principle and matters needing attention" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.