"Recommended" PDO anti-SQL injection attack principle analysis and considerations for using PDO

We all know that as long as the proper use of PDO, can basically prevent the generation of SQL injection, this article mainly answers the following questions:

• Why use PDO instead of mysql_connect?
• Why does PDO prevent injection?
• What should I pay special attention to when using PDO anti-injection?

First, why should I use PDO first?

The PHP manual says it clearly:

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

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

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

Also, a warning message is given in the PHP manual

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 driver option, as the following Example shows. Warning The method in the below example can is only is used with character sets that share the same lower 7 bit representation as ASC II, such as Iso-8859-1 and UTF-8. Users using character sets that has 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 PHP 5.3.6 and previous versions, the CharSet definition in DSN is not supported, but the initial SQL should be set using Pdo::mysql_attr_init_command , which is our usual set names GBK instructions.

I see some programs, still trying to use addslashes () to achieve the purpose of anti-injection, but this is actually more problems, see the details Http://www.lorui.com/addslashes-mysql_escape_string-mysql_ Real_eascape_string.html

There are other practices: Before you execute a database query, Select, Union, ... in SQL. Sort of keywords to clean out. This practice is obviously a very wrong way of handling, if the body of the submission does contain the students's union, the replacement will tamper with the original content, killing innocents and undesirable.

Second, why PDO can prevent SQL injection?

Please look at the following PHP code first:

<?php$pdo = new PDO ("Mysql:host=192.168.0.1;dbname=test;charset=utf8", "root", "root"), $st = $pdo->prepare (" SELECT * FROM info where id =? and name =? "); $id =: $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 specifically used the Wireshark grab bag for research, after installing Wireshak, we set the filter condition to tcp.port==3306, as shown in:

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

It is important to note that 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 can also) of the MySQL test.

Then run our PHP program and listen to the results below, and we found that PHP simply sent SQL directly to MySQL Server:

In fact, this and we usually use mysql_real_escape_string () to escape the string, and then stitching into SQL statements no difference (only by the PDO local driver to complete the escape), it is clear that this situation is still likely to cause SQL injection, That is, in PHP local call PDO prepare in the mysql_real_escape_string () to operate query, using a local single-byte character set, and we pass the multibyte-encoded variable, it is possible to create a SQL injection Vulnerability (PHP 5.3.6 one of the previous versions of the issue, which explains why it is recommended to upgrade to PHP 5.3.6+ when using PDO, and to specify the reason for CharSet in the DSN string.

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

$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 based on the character.

So, how can you prohibitphp Local Escapeand handed over toMySQL Server EscapeIt?

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

<?php$pdo = new PDO ("Mysql:host=192.168.0.1;dbname=test;", "Root", "root"), $pdo->setattribute (pdo::attr_ Emulate_prepares, false); $st = $pdo->prepare ("select * from info where id =? and name =? "); $id =: $name = ' Zhangsan '; $st->bindparam (1, $id); $st->bindparam (2, $name); $st->execute (); $st->fetchall ();

Red Line is what we just joined, run the following program, using the Wireshark capture packet analysis, the results are as follows:

Did you see it? This is the magic, it can be seen that PHP is the SQL template and variables are sent to MySQL two times, by the completion of the MySQL variable escape processing , since the variables and SQL template is sent two times, then there is no s QL injects the problem, but you need to specify the CharSet attribute in the DSN, such as:

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

In this way, the problem of SQL injection can be eliminated fundamentally.

Iii. Considerations for using PDO

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

1. PHP upgrade to 5.3.6+, production environment is highly recommended to upgrade to PHP 5.3.9+ PHP 5.4+,php 5.3.8 There is a fatal hash collision vulnerability.

2. If you are using php5.3.6+, specify the charset attribute in the PDO DSN.

3. If PHP 5.3.6 and previous versions are used, set the pdo::attr_emulate_prepares parameter to False (that is, the variable is handled by MySQL), PHP 5.3.6 or later has dealt with this issue, whether using Local impersonation Prepar E or prepare that call MySQL server are available. Specifying CharSet in DSN is not valid, and the execution of set names <charset> is essential.

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

There is a problem, then, if CharSet is specified in the DSN, do you still need to performSet names <charset>It?

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

1. Tell MySQL Server What the encoding is for the client (PHP program) to submit to it;
2. Tell MySQL Server what the encoding of the results the client needs is;

In other words, if the data table uses the GBK character set, and the PHP program uses UTF-8 encoding, we run the set names UTF8 before executing the query, telling MySQL Server to encode correctly, without having to encode the conversion in the program. This way we submit the query to MySQL Server with Utf-8 encoding, and the result will be UTF-8 encoding. Eliminates the conversion coding problem in the program, do not have the doubt, this does not produce garbled.

What is the role of specifying CharSet in DSN?

Just tell PDO that the local drive escapes using the specified character set (not setting the MySQL server communication character set), setting the MySQL server communication character set, and using the set names <charset> directive.

Stop trying to write your own SQL Injection filter library (cumbersome and prone to unknown vulnerabilities).

Reference:

http://zhangxugg-163-com.iteye.com/blog/1835721

The Pdo::attr_emulate_prepares property is set to false as a result of a massacre

"Recommended" PDO anti-SQL injection attack principle analysis and considerations for using PDO