Prevents SQL injection attacks during PHP development

Prevents SQL injection attacks during PHP development

SQL Injection attacks (SQL Injection) means to obtain the access permission of the website database through SQL Injection, and then obtain all the data in the website database, malicious hackers can use SQL injection to tamper with the data in the database and even destroy the data in the database. As a web developer, it is necessary to understand the principles of SQL injection and learn how to protect your website database through code.

The following describes how to prevent SQL injection attacks in PHP. If your input data is inserted into an SQL query statement without being processed, the application may be vulnerable to SQL injection attacks, as shown in the following example:

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

There is no filtering for POST parameters, so the user input may be like this:

value'); DROP TABLE table;--

Then the entire SQL query will become as follows:

INSERT INTO `table` (`column`) VALUES('value'); DROP TABLE table;--')

After such a query is executed, our database will be very dangerous, and the data table will be deleted maliciously. Which effective methods should be taken to prevent SQL injection? In actual development, we can prevent this type of Injection by filtering input parameters. Of course, we can also use preprocessing statements and parameterized queries. The pre-processing statements and parameters are sent to the database server for resolution. The parameters are processed as common characters. This method prevents attackers from injecting malicious SQL statements. You have two options to implement this method: 1. Use PDO:

$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');$stmt->execute(array('name' => $name));foreach ($stmt as $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}

Note that the use of PDO by default does not allow the MySQL database to execute the true pre-processing statement (the reason is described below ). To solve this problem, you should disable PDO to simulate preprocessing statements. An example of using PDO to create a database 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 reporting mode (ATTR_ERRMODE) is not required, but we recommend that you add it. In this way, when a Fatal Error occurs, the script does not stop running, but gives the programmer a chance to capture PDOExceptions to properly handle the Error. However, the first setAttribute () call is required. It prohibits PDO from simulating preprocessing statements. Instead, it uses a true preprocessing statement, that is, MySQL executes preprocessing statements. This ensures that the statements and parameters have not been processed by PHP before being sent to MySQL, which prevents attackers from injecting malicious SQL statements. For the reason, refer to this blog post: Analysis of the PDO anti-injection principle and precautions for using PDO. Note that in earlier versions of PHP (<5.3.6), you cannot set the character set on the DSN of the PDO constructor.

What happens when you send SQL statements to the database server for preprocessing and parsing? By specifying a placeholder (? Or name: name) in the preceding example to tell the database engine where you want to filter. When you call execute, the pre-processing statement will be combined with the parameter value you specified. The key point is here: the parameter value is combined with the parsed SQL statement, rather than the SQL string. SQL Injection contains malicious strings when constructing SQL statements by triggering scripts. Therefore, separating SQL statements from parameters prevents the risk of SQL injection. Any parameter value you send will be treated as a normal string and will not be parsed by the database server. Return to the example above. If the value of the $ name variable is 'sara'; delete from employees, the actual query is to find that the value of the name field in employees is 'sara '; DELETE records FROM employees. Another advantage of using pre-processing statements is that if you execute the same statement multiple times in the same database connection session, it will be parsed only once, which improves the execution speed. If you want to know how to insert data, see the following example (using PDO ):

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