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:

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:



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:

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

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);//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:

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, MySQL performs variable processing). PHP 5.3.6 and above versions have already dealt with this problem, whether it is You can use local simulation 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

In other words, 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 and tell the mysql server to encode it correctly. There is no need to encode it in the program. Convert. 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 < ;charset> 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.

Stop trying to write your own SQL injection filtering library (it’s tedious and can easily lead to unknown vulnerabilities).

I hope this article will be helpful to everyone’s PHP programming design.

PHP uses preprocessed PDO to prevent injection

You can use pdo for preprocessing to effectively prevent sql injection into bgim

pdo problem in php

That’s a bit broad. Security is not just something SQL can do!
You must also prevent SQL injection when using PDO. You must check your user’s input! ! ! ! Like single quotes
, there are several methods in PHP that can also be used! I can’t tell you everything, just take a look at the information, tutorials usually talk about this!

http://www.bkjia.com/PHPjc/898286.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/898286.htmlTechArticleAnalysis of PDO anti-injection principle and summary of precautions for using PDO, pdo precautions This article details the principle of PDO anti-injection Analysis and precautions for using PDO are shared with everyone for your reference...