The mechanism of PDO to prevent SQL injection

When using PDO to access the MySQL database, the real real prepared statements is not used by default.

To solve the problem, you must disable the emulation effect of the 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 use the real parepared statements. This ensures that SQL statements and corresponding values are not parsed by PHP until they are passed to MySQLServer (all possible malicious SQL injection attacks are prohibited).

Although you can configure the properties of the character set in the file (Charset=utf8), it is important to note that the older version of PHP (< 5.3.6) ignores the character parameters in the 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- >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 database server, where there is only a placeholder? Send in the past, no real user submitted data. When called to execute (). The values submitted by the user are sent to the database, they are sent separately, and the SQL attackers do not have a single chance.

But there are a few things we need 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 allow placeholders to replace data table or column names. Such as:

The code is as follows:

SELECT * FROM blog ORDER by?;

3. You can't let the placeholder replace whatever other SQL syntax you have. 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