The mechanism of PDO to prevent SQL injection

When using PDO to access the MySQL database, real real prepared statements is not used by default. To solve this problem, you must disable the emulation effect of prepared statements. Here's an example of creating a link using PDO:

The code is as follows:

$DBH = new PDO (' Mysql:dbname=dbtest;host=127.0.0.1;charset=utf8 ', ' user ', ' Pass '); $DBH->setattribute (pdo::attr_ Emulate_prepares, false);

SetAttribute () This line is mandatory, it tells PDO to disable the impersonation preprocessing statement, and uses the real parepared statements. This ensures that SQL statements and corresponding values are not parsed by PHP until they are passed to the MySQL server (all possible malicious SQL injection attacks are prohibited). Although you can set the properties of the character set in the config file (Charset=utf8), it is important to note that older versions of PHP (< 5.3.6) Ignore character parameters in DSN.

Let's take a look at a complete code usage example:

The code is as follows:

$DBH = new PDO ("Mysql:host=localhost; Dbname=dbtest "," User "," pass "), $dbh->setattribute (Pdo::attr_emulate_prepares, false); Disables the emulation effect of the prepared statements $dbh->exec ("Set names ' UTF8 '"); $sql = "SELECT * from test where name =? and password =? "; $stmt = $dbh->prepare ($sql), $exeres = $stmt->execute (Array ($testname, $pass)), if ($exeres) {while ($row = $stmt-&G T;fetch (PDO::FETCH_ASSOC)) {     print_r ($row);}} $DBH = null;

The above code will prevent SQL injection. Why is it?

When prepare () is called, the query statement has been sent to the database server with only placeholders at this time? Send in the past, no user submitted data, when called to execute (), the user submitted values will be sent to the database, they are separate transmission, the two independent, SQL attackers do not have a chance.

But here are a few things to keep in mind that PDO doesn't help you prevent SQL injection

1. You can't make a placeholder? Instead of a set of values, such as:

The code is as follows:

SELECT * FROM blog WHERE userid in (?);

2. You cannot have placeholders in place of data table or column names, such as:

The code is as follows:

SELECT * FROM blog ORDER by?;

3. You cannot allow placeholders to replace any other SQL syntax, such as:
The code is as follows:

SELECT EXTRACT (? From Datetime_column) as variable_datetime_element from blog;

The mechanism of PDO to prevent SQL injection