How PDO prevents SQL Injection

How PDO prevents SQL Injection

When I mention how to prevent SQL injection, I always think of using PDO to bind parameters or using mysql_real_eascape_string () (although the old functions such as mysql_XXX are not recommended ). But how does PDO prevent injection?

In the manual, there is a paragraph like this:

Prepared statements and stored procedures

Limitations 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 it's 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 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 ).

Prepared statements are so useful that they are the only feature that PDO will emulate for drivers that don't support them. this ensures that an application will be able to use the same data access paradigm regardless of the capabilities of the database.

The approximate translation is:

Many more mature databases support the concept of pre-processing statements. What are these? It can be considered as a mechanism to run SQL statements by compiling SQL statement templates. Preprocessing statements can bring two benefits:

The query only needs to be parsed (or compiled) once, but can be executed multiple times through the same or different parameters. After the query is processed, the database will analyze, compile, and optimize its plan to execute the query. The process of complex queries may take enough time, which significantly slows down the application. If necessary, you can use different parameters to repeat the same query multiple times. Avoid repeating the [analysis/compilation/optimization] cycle by using the application that processes the statements. This means that the pre-processing statement uses fewer resources and runs faster. No quotation marks are required for the bound parameters. The driver automatically processes the parameters. If the application uses preprocessing statements, developers can ensure that SQL injection is not performed (however, if the rest of the query uses unescaped input, SQL injection is still possible ).

Preprocessing statements are very useful. PDO can use a local simulation method to provide this function for database systems without preprocessing. This ensures that an application can access the database in a unified way.

The use of PDO can bring two very good results, pre-compilation improves the query speed, and variable binding can prevent SQL injection, in fact, the mechanism of PDO to prevent SQL injection is similar to using mysql_real_escape_string for escape. PDO has two escape mechanisms. The first is local escape, this escape method uses the single-byte character set (PHP <5.3.6) to escape the input (single-byte and multi-byte), but this escape method has some potential risks. Major Risks: when PHP version is earlier than 5.3.6, local escape can only convert single-byte character sets. Versions later than 5.3.6 will be escaped Based on the charset specified in the PDO connection. The official PHP manual is described as follows:

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.

Therefore, PDO of different versions is different in the behavior of local escape.

The second method is PDO. First, the SQL statement template is sent to the Mysql Server, and then the bound character variables are sent to the Mysql server. The escape here is done on the Mysql Server, it is converted according to the encoding format specified in charset when you connect to PDO. This escape method is more robust, and you can reuse templates to improve program performance in business scenarios where repeated queries are made. If you want to set Mysql Server to escape, you must first execute:

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

The following figure shows the PDO Query Process of the packets captured by wireshark:

Bound variables:

If $ pdo-> setAttribute (PDO: ATTR_EMULATE_PREPARES, false) is not executed, PDO only assembles the inserted parameters with the SQL template after being escaped locally, and then send it to the Mysql Server. This is actually similar to filtering with mysql_real_escape_string () and then assembling this method.

You can choose one of the following two methods to consider database security:

A. Add (php versions earlier than 5.3.6): $ pdo-> setAttribute (PDO: ATTR_EMULATE_PREPARES, false );

B. Upgrade to php 5.3.6 (you do not need to set PDO: ATTR_EMULATE_PREPARES)

For program portability and unified security, we recommend that you use the $ pdo-> setAttribute (PDO: ATTR_EMULATE_PREPARES, false) method.