How to prevent SQL injection in php

Problem description:
If the data entered by the user is inserted into a SQL query statement without processing, the application will likely suffer from SQL injection attacks, as in the following example:

$unsafe_variable = $_POST['user_input'];

mysql_query("INSERT INTO ` table` (`column`) VALUES ('" . $unsafe_variable . "')");
Because the user's input may be like this:

value'); DROP TABLE table;--
Then the SQL query will become As follows:

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

What effective methods should be adopted to prevent SQL injection?

Best answer (from Theo):
Use prepared statements and parameterized queries. The prepared statements and parameters are sent to the database server for parsing respectively, 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. 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  
}

PDO
Note that using PDO by default does not let the MySQL database execute real prepared statements (See why below). To solve this problem, you should disable PDO emulation of prepared statements. An example of correctly 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 above example, the error mode (ATTR_ERRMODE) is not necessary, but it is recommended to add it. In this way, when a fatal error (Fatal Error) occurs, the script will not stop running, but gives the programmer an opportunity to catch PDOExceptions so that the error can be properly handled. However, the first setAttribute() call is required, which disables PDO from simulating prepared statements and uses real prepared statements, i.e. MySQL executes prepared statements. This ensures that statements and parameters have not been processed by PHP before being sent to MySQL, which will prevent attackers from injecting malicious SQL. To understand the reason, please refer to this blog post: Analysis of PDO anti-injection principle and precautions for using PDO. Note that in older versions of PHP (<5.3.6), you cannot set the charset through the DSN in PDO's constructor, see: silently ignored the charset parameter.

Parsing
What happens when you send a SQL statement to the database server for preprocessing and parsing? Tell the database engine where you want to filter by specifying a placeholder (a ? or a :name as in the example above). When you call execute, the prepared statement will be combined with the parameter values ​​you specify. The key point is here: the parameter value is combined with the parsed SQL statement, not the SQL string. SQL injection is triggered by scripts that contain malicious strings when constructing SQL statements. So, by separating SQL statements and parameters, you prevent the risk of SQL injection. Any parameter values ​​you send will be treated as ordinary strings and will not be parsed by the database server. Going back to the above example, if the value of the $name variable is 'Sarah'; DELETE FROM employees, then the actual query will be to find records in employees where the name field value is 'Sarah'; DELETE FROM employees. Another benefit of using prepared 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 execution speed a bit. If you want to ask how to do insertion, please look at the following example (using PDO):

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