Prevent SQL injection in PHP

If user input is not processed correctly and inserted into a SQL query, a SQL injection vulnerability occurs. To understand this risk, consider the following example:

$unsafe_variable = $_POST['user_input'];

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

In this scenario, if the user maliciously enters a value similar to value'); DROP TABLE table;--, the query will become:

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

This opens the door to malicious attacks on the database.

Mixing Techniques:

Recommended security practices to prevent SQL injection are to separate data from SQL, regardless of which database you are using. This means ensuring that the data is treated as data and never interpreted as a command by the SQL parser. The most efficient way to achieve this is to use preprocessing statements and parameterized queries.

Preprocessing statements and parameterized queries:

]

The preprocessing statement involves sending SQL queries and parameters to the database server separately, allowing the database to process their combination. This prevents malicious SQL injection attempts by ensuring that the data is not parsed by PHP before transfer.

Implementation options:

There are two main methods to implement preprocessing statements:

1. PDO (PHP data object):

   ]

   This is a common approach that works with all supported database drivers. Here is an example of its usage:

   $stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');
   $stmt->execute([ 'name' => $name ]);
   
   foreach ($stmt as $row) {
       // 处理行
   }

2. MySQLi (MySQL Improved Extension):

   ] For MySQL databases, you can use MySQLi. Starting in PHP 8.2, you can use the execute_query()

   method to prepare, bind parameters, and execute SQL statements in one step:

   $result = $db->execute_query('SELECT * FROM employees WHERE name = ?', [$name]);
   while ($row = $result->fetch_assoc()) {
       // 处理行
   }

   For PHP 8.1 and below:

   $stmt = $db->prepare('SELECT * FROM employees WHERE name = ?');
   $stmt->bind_param('s', $name); // 's' 表示'字符串'变量类型
   $stmt->execute();
   $result = $stmt->get_result();
   while ($row = $result->fetch_assoc()) {
       // 处理行
   }

If you use a database other than MySQL, driver-specific alternatives exist, such as pg_prepare() and pg_execute()

] of PostgreSQL.

Correct connection settings:

]

When establishing a connection, it is important to disable simulation of preprocessing statements for improved performance and security.

PDO Connection:

$dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8mb4', 'user', 'password');

$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

MySQLi connection:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // 错误报告
$dbConnection = new mysqli('127.0.0.1', 'username', 'password', 'test');
$dbConnection->set_charset('utf8mb4'); // 字符集

in conclusion:

By implementing preprocessing statements and setting up the connection correctly, you can effectively prevent SQL injection attacks and ensure the security and integrity of your database applications.