Why PDO connection to mysql can prevent injection

  $stmt = $pdo->prepare('select * from user where id=?');
    $id = 1;
    $stmt->bindParam(1,$id);
    $stmt - >execute();

<span style="font-size:14px;" data-filtered="filtered"></span>

In this case, PHP simply sends the sql statement to the mysql server,This is different from our usual There is no difference in using mysql_real_escape_string to escape the string and then splicing it into a SQL statement (it is only escaped by the PDO local driver). Obviously, SQL injection is still possible in this case, that is to say, calling pdo prepare locally in PHP Mysql_real_escape_string in to operate the query uses the local single-byte character set, and when we pass multi-byte encoded variables, it may still cause SQL injection vulnerabilities (one of the problems in versions before PHP 5.3.6, this is also This explains why when using PDO, it is recommended to upgrade to php 5.3.6 and specify the charset in the DSN string.

The correct escape should be Specify the character set for mysql Server and send the variables to MySQL Server to complete character escaping.

So, how can you ban PHP How about local escaping and leaving it to MySQL Server for escaping?

PDO has a parameter named PDO::ATTR_EMULATE_PREPARES, which indicates whether to use PHP to simulate prepare locally. The default value of this parameter is unknown . And according to the packet capture analysis results we just saw, PHP 5.3.6 still uses local variables by default, splicing them into SQL and sending it to MySQL Server. We set this value to false,

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);$stmt = $pdo->prepare('select * from user where id=?');
$id = 1;
$stmt->bindParam(1,$id);
$stmt - >execute();

This time PHP sends the SQL template and variables to MySQL in two times, and MySQL completes the escaping of the variables. Since the variables and SQL template are sent in two times, there is no SQL injection. Problem, but you need to specify the charset attribute in the DSN, such as:

$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root');

So, there is a question, if charset is specified in the DSN, is it still needed? What about executing set names ?

Yes, it cannot be omitted.set names actually has two functions ：

A. Tell mysql server what encoding the client (PHP program) submitted to it

B. Tell mysql server, what is the encoding of the result required by the client

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 and tell the mysql server to encode it correctly. There is no need to In-program encoding conversion. In this way, we submit the query to mysql server in utf-8 encoding, and the results obtained will also be in utf-8 encoding. This eliminates the problem of conversion encoding in the program. Don't have any doubts. This will not produce garbled code.

So what is the function of specifying charset in DSN? It just tells PDO that when the local driver escapes To use the specified character set (not to set the mysql server communication character set), to set the mysql server communication character set, you must also use the set names command.

Related recommendations:

ThinkPHP framework based on PDO method to connect database operation example

The above is the detailed content of Why PDO connection to mysql can prevent injection. For more information, please follow other related articles on the PHP Chinese website!