PDO's mechanism to prevent sql injection

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

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 and will tell PDO to disable impersonation Prepare statements and use real parepared statements. This ensures that the SQL statement and corresponding values ​​are not parsed by PHP before being passed to the mysql server (disabling all possible malicious SQL injection attacks). Although you can set the character set attribute (charset=utf8) in the configuration file, 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); //禁用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 paragraph The code can prevent sql injection. why?

When prepare() is called, the query statement has been sent to the database server. At this time, only the placeholder? is sent, and there is no data submitted by the user; When execute() is called, the values ​​submitted by the user will be transmitted to the database. They are transmitted separately. The two are independent, and SQL attackers have no chance.

But what we need to pay attention to is the following situations. PDO cannot help you prevent SQL injection

1. You cannot let placeholders? replace a set of values , such as:

The code is as follows:

SELECT * FROM blog WHERE userid IN ( ? );

2. You cannot let placeholders replace the data table name or column name, such as:

The code is as follows:

SELECT * FROM blog ORDER BY ?;

3. You cannot let the placeholder ? replace any other SQL syntax, such as:

The code is as follows:

SELECT EXTRACT( ? FROM datetime_column) AS variable_datetime_element FROM blog;

The above is the content of PDO’s mechanism to prevent SQL injection. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!