Analysis of the principle of using PDO to prevent SQL injection

Preface

This article uses pdo's preprocessing method to avoid sql injection. Let’s take a look at the detailed introduction.

In the PHP manual under 'PDO -- Prepared Statements and Stored Procedures':

• Many of the more mature databases support the concept of prepared statements. What are prepared statements? Think of it as a compiled template of the SQL you want to run, which can be customized using variable parameters. Prepared statements can bring two major benefits:
• The query needs to be parsed (or preprocessed) only once, but can be executed multiple times with the same or different parameters. When a query is prepared, the database analyzes, compiles, and optimizes the plan for executing the query. For complex queries, this process can take a long time and can significantly slow down your application if the same query needs to be repeated multiple times with different parameters. By using prepared statements, you can avoid repeated parse/compile/optimize cycles. In short, prepared statements use fewer resources and therefore run faster.
• Parameters supplied to prepared statements do not need to be enclosed in quotes; the driver handles this automatically. If your application uses only prepared statements, you can ensure that SQL injection cannot occur. (However, there is still a risk of SQL injection if other parts of the query are constructed from unescaped input).
• Prepared statements are so useful that their only feature is that PDO will emulate them if the driver does not support them. This ensures that applications can use the same data access patterns regardless of whether the database has such capabilities.

The following are the two benefits mentioned above:

1. First, let's talk about MySQL's stored procedures. MySQL5 introduced the stored procedure feature. When the stored procedure was created, the database had already parsed and optimized it. Secondly, once a stored procedure is executed, a copy of the stored procedure will be kept in the memory, so that the next time the same stored procedure is executed, it can be read directly from the memory. For the use of MySQL stored procedures, please refer to: https://www.jb51.net/article/7032.htm

For PDO, the principle is the same, except that PDO supports EMULATE_PREPARES (simulated preprocessing), which is completed locally by the PDO driver. At the same time, you can also not use local simulated preprocessing and let MySQL complete it. These two situations will be explained below.

2. To prevent SQL injection, I used tcpdump and wireshark to capture the packet for analysis.

Execute a piece of code on the virtual machine to initiate a request to the remote MySQL:

<?php

$pdo = new PDO("mysql:host=10.121.95.81;dbname=thor_cms;charset=utf8", "root","qihoo@360@qihoo");

$st = $pdo->prepare("select * from share where id =? and uid = ?");

$id = 6;
$uid = 521;

$st->bindParam(1, $id);
$st->bindParam(2, $uid);

$st->execute();
$ret = $st->fetchAll();

print_r($ret);

Generate files by capturing packets through tcpdump:

tcpdump -ieth0 -A -s 3000 port 3306 -w ./mysql.dump

sz mysql.dump

Open the file via wireshark:

You can see the whole process: 3-way handshake--Login Request--Request Query--Request Quit

Looking at the Request Query package, you can see:

Huh? Isn't this also concatenating SQL statements?

In fact, this is no different from how we usually use mysql_real_escape_string to escape the string and then concatenate it into SQL statements. It is just that the PDO local driver completes the escape (EMULATE_PREPARES)

In this case, SQL injection is still possible, that is, when mysql_real_escape_string in pdo prepare is called locally in PHP to operate the query, the local single-byte character set is used, and when we pass multi-byte encoded variables, SQL injection vulnerabilities may still be caused (one of the problems in versions before PHP 5.3.6, which explains why it is recommended to upgrade to PHP 5.3.6+ and specify the charset in the DSN string when using PDO).

For versions prior to PHP 5.3.6, the following code may still cause SQL injection issues:

$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 correct escape should be to specify the character set for MySQL Server and send the variable to MySQL Server to complete the character escape.

So, how can we disable PHP local escaping and let MySQL Server do the escaping?

PDO has a parameter called PDO::ATTR_EMULATE_PREPARES, which indicates whether to use PHP local simulated prepare. This parameter defaults to true. Let's change it to false and then capture the packet.

Add after the first line of code

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

Use tcpdump to capture the packet again. Through wireshark we can see:

PHP uses the prepare--execute method to send SQL statements

This time the variable escape processing is performed by MySQL server.

Since the variables and SQL template are sent twice, there is no SQL injection problem, but there will obviously be one more transmission, which is not necessary after php5.3.6.

Notes on using PDO

1. Upgrade PHP to 5.3.6+. It is strongly recommended to upgrade to PHP 5.3.9+ or PHP 5.4+ for production environments. PHP 5.3.8 has a fatal hash collision vulnerability.

2. If you are using PHP 5.3.6+, please specify the charset attribute in the PDO DSN. Less than 5.3.6: $dbh = new PDO($dsn,$user,$pass,array(PDO::MYSQL_ATTR_INIT_COMMAND => "set names utf8"));

3. If you are using PHP 5.3.6 or earlier, set the PDO::ATTR_EMULATE_PREPARES parameter to false (that is, let the MySQL server handle the variables). PHP 5.3.6 and later versions have already handled this problem, whether you use local simulated prepare or call MySQL server's prepare.

4. If you are using PHP 5.3.6 or earlier, since 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.

Note:

1. Why do I need to execute set names <charset> if I specify charset in DSN?

In fact, set names <charset> has two functions:

Tell MySQL server what encoding the client (PHP program) submitted to it

Tell MySQL server what encoding the client needs for the result

That is to say, 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 to tell the MySQL server to use the correct encoding without having to convert the encoding in the program. In this way, when we submit queries to MySQL server in UTF-8 encoding, the results will also be in UTF-8 encoding. This eliminates the need for encoding conversion issues in the program. Don't be doubtful, this will not produce garbled characters.

So what is the purpose of specifying charset in DSN? It just tells PDO that the local driver should use 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 also need to use the set names <charset> command.

2. The murder caused by setting the PDO::ATTR_EMULATE_PREPARES attribute to false: http://my.oschina.net/u/437615/blog/369481

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.