Analysis of How to Prevent SQL Injection in PHP

This paper analyzes how to prevent SQL injection in PHP with examples. Share it for your reference. The specific analysis is as follows:

1. Problem description:

If the data entered by the user is inserted into an SQL query statement without processing, the application will probably suffer an SQL injection attack, as in the following example:

$unsafe_variable = $_POST['user_input']; 

 

mysql_query("INSERT INTO `table` (`column`) VALUES ('" . $unsafe_variable . "')");

Because the user's input might look like this:

value'); DROP TABLE table;--

Then the SQL query will look like this:

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

What effective methods should be taken to prevent SQL injection?

2. Solution analysis:

Use preprocessing statements and parameterized queries. Preprocessing statements and parameters are sent to the database server for parsing, and the parameters will be treated as ordinary characters. This approach prevents attackers from injecting malicious SQL. You have two options to implement this method:

1. Using PDO:

$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');

 

$stmt->execute(array('name' => $name));

 

foreach ($stmt as $row) {

    // do something with $row

}

2. Using 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

}

3. PDO

Note that using PDO by default does not cause the MySQL database to execute real preprocessing statements (see below for reasons). To solve this problem, you should prohibit PDO from simulating preprocessing statements. An example of using PDO correctly 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 above example, the error reporting mode (ATTR_ERRMODE) is not required, but it is recommended to add it. In this way, when a fatal error (Fatal Error) occurs, the script does not stop running, but gives the programmer a chance to catch PDOExceptions so that the error can be properly handled. However, the first setAttribute () call is required, which prohibits PDO from simulating preprocessing statements and using real preprocessing statements, that is, MySQL executing preprocessing statements. This ensures that statements and parameters are not processed by PHP before being sent to MySQL, which will prevent attackers from injecting malicious SQL. To understand the reason, please refer to the previous article: Analysis of PDO anti-injection principle and precautions for using PDO. Note that in the old version of PHP ( < 5.3. 6), you cannot set the character set on the DSN of the PDO constructor, refer to: silently ignored the charset parameter.

4. Analyze

What happens when you send the SQL statement to the database server for preprocessing and parsing? Tell the database engine where you want to filter by specifying a placeholder (1? Or 1 named in the above example: name). When you call execute, the preprocessing statement will be combined with the parameter values you specify. Here's the key point: The value of the parameter is combined with the parsed SQL statement into 1, not the SQL string. SQL injection is done by triggering scripts to include malicious strings when constructing SQL statements. So, by separating the SQL statement from the parameters, you prevent the risk of SQL injection. The value of any parameter you send will be treated as an ordinary string and will not be parsed by the database server. Back to the above example, if the value of the $name variable is' Sarah '; DELETE FROM employees, then the actual query would be to find in employees that the name field value is' Sarah '; Record of DELETE FROM employees. Another advantage of using preprocessing statements is that if you execute the same statement many times in the same database connection session, it will only be parsed once, which can improve the execution speed by 1 point. If you want to ask how to insert, please look at the following example (using PDO):

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

 

$preparedStatement->execute(array('column' => $unsafeValue));

I hope this article is helpful to everyone's PHP programming.