PHP with PDO anti-SQL injection considerations

In the 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 common set names GBK directive.

Why PDO can prevent SQL injection?
Please read the following PHP code first:

The code is as follows	Copy Code
<?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

The above code, PHP simply send SQL directly to the MySQL Server, in fact, this and we usually use mysql_real_escape_string to escape the string, and then stitching into a SQL statement no difference (only by the PDO local driver to complete the escape), It is clear that this situation is still likely to result in 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 multibyte-encoded variables, It is also possible that a SQL injection Vulnerability (PHP 5.3.6 one of the previous versions) is likely to explain why it is recommended to upgrade to PHP 5.3.6+ when using PDO, and to 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:

The code is as follows	Copy Code
$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?

The

PDO has a parameter, named Pdo::attr_emulate_prepares, that indicates whether the PHP local impersonation prepare is used, and the default value for this parameter is unknown. PHP 5.3.6+ By default or by using local variables, stitching into SQL sent to the MySQL server, we set this value to false, try the effect, such as the following code:

The code is as follows	Copy Code
<?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; Www.111Cn.nEt $name = ' Zhangsan '; $st->bindparam (1, $id); $st->bindparam (2, $name); $st->execute (); $st->fetchall (); ?>

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 you need to specify the CharSet attribute in DSN, such as:

The code is as follows	Copy Code
$pdo = new PDO (' Mysql:host=localhost;dbname=test;charset=utf8 ', ' root ');

Thus, the problem of SQL injection can be eliminated fundamentally.

Considerations for using PDO

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), the specified charset in DSN is invalid, while set names < Execution of the charset> (detailed statement Pdo::mysql_attr_init_command => ' SET NAMES UTF8 ') is essential. (PHP 5.3.6 has already dealt with this issue, either by using local impersonation prepare or by calling MySQL server prepare.) )

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.

The following is an example code that can be used to prevent injection:

The code is as follows

Copy Code

$dbhost = "localhost"; $dbname = "Test"; $dbusr = "root"; $dbpwd = ""; $DBHDL =null; $dbstm =null;   $opt = Array (pdo::mysql_attr_init_command => ' SET NAMES utf8 '); $dsn = ' mysql:host= '. $dbhost. ';d bname= '. $dbname. '; Charset=utf8 '; try {   $DBHDL = new PDO ($DSN, $dbusr, $dbpwd, $opt);//www.111cn.net   $DBHDL =->setattribute (PDO :: Attr_emulate_prepares, false);  //dbhdl->setattribute (pdo::attr_errmode,pdo::errmode_silent);//display none  //dbhdl-> SetAttribute (pdo::attr_errmode,pdo::errmode_warning);//display WARNING   $DBHDL->setattribute (PDO:: attr_errmode,pdo::errmode_exception);//display EXCEPTION } catch (Pdoexceptsddttrtion $e) {//return pdoexception  print "error!:". $e->getmessage (). "<br>";  die (); } $dbhost = "localhost"; $dbname = "Test"; $dbusr = "root"; $dbpwd = ""; $dbhdl =null; $dbstm =null;   $dsn = ' mysql:host= '. $dbhost. ';d bname= '. $dbname. '; Charset=utf8 '; try {   $DBHDL = new PDO ($DSN, $DBUSR, $dbpwd,)   $DBHDL =->setattribute (pdo::attr_emulate_ Prepares, false);  //dbhdl->setattribute (pdo::attr_errmode,pdo::errmode_silent);//display none  //dbhdl-> SetAttribute (pdo::attr_errmode,pdo::errmode_warning);//display WARNING   $DBHDL->setattribute (PDO:: attr_errmode,pdo::errmode_exception);//display EXCEPTION   $dbhdl->query (' SET NAMES GBK '); } catch (Pdoexceptsddttrtion $e) {//return pdoexception  print "error!:". $e->getmessage (). "<br>";  die (); }