Principle analysis of PDO anti-injection and _php skills of precautions

As we all know, as long as the rational and correct use of PDO, can basically prevent the production of SQL injection, this article mainly answers the following two questions:

Why use PDO instead of mysql_connect?

Why PDO can prevent injection?

What should you pay special attention to when using PDO to prevent injection?

Why should priority be given to the use of PDO?

The PHP manual says very clearly:

Copy Code code as follows:

Prepared statements and stored procedures
Many of the more mature databases support the concept of prepared statements. What are they? They can is thought of as a kind of compiled template for the SQL this application wants to run, which can be customized Using variable parameters. Prepared statements offer two major benefits:

The query only needs to is parsed (or prepared) once, but can be executed multiple times with the same or different Ters. When the "query is" prepared, the database would analyze, compile and optimize its's plan for executing the query. For complex queries This process can take up enough time that it'll noticeably slow down a application if there is a NE Ed 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 is prepared statements use fewer 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 is sure that no SQL injection would occur (however , if other portions of the query are being built up with unescaped input, SQL injection is still possible).

Even with PDO's prepare approach, the main thing is to improve query performance for the same SQL template, prevent SQL injection

Also, a warning message is given in the PHP manual

Copy Code code as follows:

Prior to PHP 5.3.6, this element is silently ignored. The same behaviour can be partly replicated with the Pdo::mysql_attr_init_command option, as the driver following Shows.
Warning
The method in the below example can is used with character sets that share the same lower 7 bit representation as ASC II, such as Iso-8859-1 and UTF-8. The Users using character sets that have different representations (such as UTF-16 or BIG5) must use the CharSet option Provid Ed in PHP 5.3.6 and later versions.

This means that in the PHP 5.3.6 and previous versions, the CharSet definition in DSN is not supported, and the initial SQL should be set using Pdo::mysql_attr_init_command, which is our common set names GBK directive.

I see some programs, still trying to use addslashes to achieve the purpose of prevention, but this actually more problems, please see the details http://www.lorui.com/addslashes-mysql_escape_string-mysql_ Real_eascape_string.html

There are also ways to: SELECT, Union, ... in SQL before executing a database query. Sort of key words to clean out. This approach is obviously a very wrong way of handling, if the submitted body does contain the students ' s union, the replacement will tamper with the original content, indiscriminate killing of innocent, undesirable.

Second, why PDO can prevent SQL injection?
Please read the following PHP code first:

Copy Code code 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 = ' 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 fully understand the details of PHP and MySQL server communication, I particularly used the Wireshark grab bag for research, after the installation of Wireshak, we set the filter conditions for tcp.port==3306, the following figure:

This shows only communication data with the MySQL 3306 port, avoiding unnecessary interference.

In particular, Wireshak is based on the Wincap driver, does not support the local loopback interface listening (that is, using PHP to connect to the local MySQL method is not listening), please connect other machines (Bridge network virtual machine also can) of MySQL test.

Then we ran our PHP program and listened to the results as follows, and we found that PHP simply sent SQL directly to MySQL Server:

In fact, this is not the same as our usual use of mysql_real_escape_string to escape the string, and then stitching into the SQL statement is no difference (only by the PDO local driver to complete the escape), it is obvious that this case is still likely to cause SQL injection, That is, in PHP, local calls to the mysql_real_escape_string in PDO prepare to manipulate query, using a local single-byte character set, and when we pass multibyte-encoded variables, it is possible to create SQL injection vulnerabilities (PHP 5.3.6 one of the previous versions of the issue, which explains why when using PDO, it is recommended that you upgrade to PHP 5.3.6+ and specify the cause of CharSet in the DSN string.

For previous versions of PHP 5.3.6, the following code can still cause SQL injection problems:

Copy Code code 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 the escape by character.

So how do you prevent PHP from escaping locally and being escaped by MySQL server?

PDO has a parameter, named Pdo::attr_emulate_prepares, that indicates whether to use the PHP local impersonation prepare, the default value of this parameter is unknown. And according to what we just grabbed the analysis results, the PHP 5.3.6+ by default or using local variables, splicing into SQL sent to the MySQL server, we set this value to false, try the effect, such as the following code:

Copy Code code as follows:

<?php
$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 what we just added, run the following program, using the Wireshark grab analysis, the results are as follows:

Did you see it? This is where the magic is, see this time PHP is the SQL template and variables are sent to MySQL two times, by the MySQL complete variable escape processing, since the variable and SQL template is sent two times, then there is no problem with SQL injection, but need to specify the CharSet attribute in DSN, Such as:

Copy Code code as follows:

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

Thus, the problem of SQL injection can be eliminated fundamentally. If you are not very clear about this, you can send an email to zhangxugg@163.com and discuss it together.

Third, the use of PDO considerations

Once you know the above points, we can summarize several considerations for using PDO to eliminate SQL injection:

1. PHP upgrade to 5.3.6+, the production environment is strongly recommended to upgrade to PHP 5.3.9+ PHP 5.4+,php 5.3.8 A fatal hash collision vulnerability.

2. If you use PHP 5.3.6+, specify the CharSet attribute in the DSN of PDO

3. If you use the PHP 5.3.6 and previous versions, set the Pdo::attr_emulate_prepares parameter to False (that is, the variable processing by MySQL), PHP 5.3.6 has already dealt with this issue, Either using the local impersonation prepare or calling the MySQL server prepare is available. Specifying CharSet in DSN is not valid, while set names <charset> execution is essential.

4. If the PHP 5.3.6 and previous versions are used and the Attr_emulate_prepares value is not set by default for the YII framework, specify the Emulateprepare value in the database configuration file as false.

So, there's a problem, if CharSet is specified in DSN, is it necessary to execute set names <charset>?

Yes, it can't be saved. Set names <charset> actually has two functions:

A. Tell MySQL server what the client (PHP program) is submitting the code to it

B. Tell the MySQL server what the client needs to encode the results

That is, if the datasheet uses the GBK character set, and the PHP program uses UTF-8 encoding, we run the set names UTF8 before executing the query, telling the MySQL server to encode correctly, without encoding the conversion in the program. So we submit query to MySQL server with Utf-8 encoding, and the result will be UTF-8 encoding. Eliminates the program The conversion coding problem, do not have the question, this does not produce the garbled code.

So what is the role of specifying CharSet in DSN? Simply tells PDO that the local driver escapes with the specified character set (not the MySQL server communication character set), sets the MySQL server communication character set, and also uses the set names <charset> instructions.

I really can't figure out, some new projects, why not use the PDO and use the traditional mysql_xxx function library? If the correct use of PDO, you can fundamentally eliminate SQL injection, I strongly recommend that the company's technical leaders, front-line technology research and development personnel, to the issue of attention, as far as possible to use PDO to speed up project progress and safety quality.

Stop trying to write your own SQL Injection filter library (it's cumbersome and easy to create an unknown vulnerability).

The above is the entire content of this article, I hope that the small partners can read well, very practical.