Analysis of PDO anti-injection principle and summary of precautions when using PDO, pdo precautions_PHP tutorial

Analysis of PDO anti-injection principle and summary of precautions for using PDO, pdo precautions

This article details the analysis of the anti-injection principle of PDO and the precautions for using PDO, and shares it with everyone for your reference. The specific analysis is as follows:

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

This means that in PHP 5.3.6 and previous versions, 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 have seen some programs that are still trying to use addslashes to prevent injection, but they actually have more problems. For details, please see http://www.bkjia.com/article/49205.htm
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's union, the original content will be tampered with after replacement, which is indiscriminate and undesirable.

2. Why can PDO prevent SQL injection?
Please look at the following PHP code first:

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:



In this way, only the communication data with mysql 3306 port is displayed 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 local mysql, it cannot be listened). Please connect to MySQL on other machines (virtual machines on bridged networks are also acceptable). To test.

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



In fact, 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 calling mysql_real_escape_string in pdo prepare locally in PHP 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 (before PHP 5.3.6) One of the problems, this explains why when using PDO, it is recommended to upgrade to php 5.3.6+ and specify charset in the DSN string.
For versions prior to PHP 5.3.6, the following code may still cause SQL injection problems:

$pdo->query('SET NAMES GBK');
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 can we prohibit 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:

$pdo = new PDO("mysql:host=192.168.0.1;dbname=test;","root");
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);//This is what we just added

$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();
?>

Run the 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:

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