How PHP prevents SQL injection (2)

: This article describes how to prevent SQL injection in PHP (2). If you are interested in the PHP Tutorial, refer to it. If you input a query directly inserted into an SQL statement, the application will be vulnerable to SQL injection. for example:

$unsafe_variable = $_POST['user_input'];mysql_query("INSERT INTO table (column) VALUES ('" . $unsafe_variable . "')");

This is because you can enter a VALUE similar to "); drop table;-to convert the query:

INSERTINTOtable (column) VALUES('VALUE');DROPTABLEtable;'

How should we prevent this situation? Let's take a look at Theo's answer.
Use predefine statements and parameterized queries. SQL statements with any parameters will be sent to the database server and parsed! It is impossible for attackers to maliciously inject SQL statements!
There are basically two options to achieve this goal:

1. use PDO (PHP Data Objects)

$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');$stmt->execute(array(':name' => $name));foreach ($stmtas$row) {    // do something with $row}

2. use mysqli

$stmt=$dbConnection->prepare('SELECT * FROM employees WHERE name = ?');$stmt->bind_param('s', $name);$stmt->execute();$result=$stmt->get_result();while ($row=$result->fetch_assoc()) {    // do something with $row}

PDO (PHP DataObject)
Note that when you use PDO to access the MySQL database, the true pre-definition statements are not used by default! To solve this problem, you must disable the statements prepared by simulation. An example of using PDO to create a connection is as follows:

$dbConnection=new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'pass');$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_Exception);

In the preceding example, the error mode ERRMODE is not strictly required, but we recommend that you add it. This method does not stop when an error occurs and a fatal error occurs. And give developers the opportunity to capture any errors (when PDO is thrownExceptionException ).
The setAttribute () line is mandatory. it tells PDO to disable the simulation of the pre-definition statement and use the real pre-definition statement. This ensures that the statements and values are not parsed by PHP before being sent to the MySQL database server (attackers have no chance to inject malicious SQL statements ).
Of course, you can set character set parameters in the constructor options. Note that the 'old' PHP version (5.3.6) ignores character set parameters in DSN.

Explanation)
What happens when the SQL prepared statements you pass are parsed and compiled by the database server? Use the specified character (in the preceding example, it is like? Or: name) tells the database engine what you want to filter, and then calls execute to execute the prepared statement combined with the parameter value you specified.
The most important thing here is that this parameter value is combined with a pre-compiled statement, rather than an SQL string. the operating principle of SQL injection is that SQL scripts created by spoofing methods include malicious strings sent to the database. therefore, by sending actual separate SQL parameters, you will reduce the risk. when using prepared statements, any parameters you send will only be treated as strings (although the database engine may optimize some parameters, of course, it may eventually be numbers ). in the preceding example, if the variable $ name contains 'Sara'; DELETE * FROM employees, the result will only be a search string "'Sara'; DELETE * FROM employees ", you will not get an empty table.
Another benefit of using prepared statements is that if you execute the same statement multiple times in the same session, this will only be parsed and compiled once, giving you some speed increase.
Oh, since you asked how to insert, here is an example (using PDO ):

$preparedStatement=$db->prepare('INSERT INTO table (column) VALUES (:column)');$preparedStatement->execute(array(':column'=>$unsafeValue));

Http://www.jb51.net/article/36042.htm

The above introduces the PHP method to prevent SQL injection (2), including some content, hope to be helpful to friends who are interested in PHP tutorials.