PDO anti-injection principle analysis and precautions, pdo injection

Analysis of PDO anti-injection principle and precautions, pdo injection

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 give priority to using PDO?

The PHP manual says it very clearly:

Copy code The code is as follows:

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 using PDO’s prepare method, it mainly improves the query performance of the same SQL template and prevents SQL injection

At the same time, a warning message is given in the PHP manual

Copy code The code is as follows:

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, the charset definition in DSN is not supported. Instead, 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

There are also 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 deal with it. If the submitted text does contain the students' 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:

Copy code The code is as follows:

$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 will only display the communication data with mysql 3306 port 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 can also be used) ) MySQL for testing.

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

Actually, this is no different from our usual use of mysql_real_escape_string to escape strings and then splice them into SQL statements (it is only escaped by the PDO local driver). Obviously, in this case, SQL injection is still possible. That is to say, when php locally calls mysql_real_escape_string in pdo prepare to operate the query, the local single-byte character set is used, and when we pass multi-byte encoded variables, it may still cause SQL injection vulnerabilities (php 5.3.6 One of the problems with previous versions, which explains why when using PDO, it is recommended to upgrade to php 5.3.6+ and specify charset in the DSN string.

For PHP versions prior to 5.3.6, the following code may still cause SQL injection problems:

Copy code 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 to complete character escaping.

So, how to disable PHP local escaping and let MySQL Server escape it?

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 packet capture and analysis results we just captured, PHP 5.3.6+ still uses local variables by default, splicing them into SQL and sending it to MySQL Server. We set this value to false and try the effect, such as the following code:

Copy code The code is as follows:

$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 The problem of SQL injection is solved, but the charset attribute needs to be specified in the DSN, such as:

Copy code The 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 send an email to zhangxugg@163.com and discuss it together.

3. Things to note when 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+ and php 5.4+. PHP 5.3.8 has a fatal hash collision vulnerability.

2. If using php 5.3.6+, please specify the charset attribute in the DSN of PDO

3. If you use PHP 5.3.6 and previous versions, set the PDO::ATTR_EMULATE_PREPARES parameter to false (that is, variable processing is performed by MySQL). PHP 5.3.6 and above versions have already handled this problem, whether using local simulation Either prepare or call prepare of mysql server. Specifying a charset in a DSN is invalid, and the execution of set names is essential.

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

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 mysql server what encoding the client (PHP program) submitted to it

B. Tell mysql server, what is the encoding of the result 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 can run set names utf8 before executing the query to tell the mysql server to encode it correctly without encoding conversion 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). To set the mysql server communication character set, you must use set names directive.

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 is the entire content of this article. I hope you guys can read it carefully. It is very practical.