How to Effectively Prevent SQL Injection in PHP Applications?

How to Prevent SQL Injection in PHP

Directly inserting user input into an SQL query without any modifications leaves an application vulnerable to SQL injection attacks. To prevent this, it's crucial to separate data from SQL to ensure that data remains as data and is never interpreted as commands by the SQL parser.

The Most Effective Solution

The recommended approach for avoiding SQL injection attacks, regardless of the database being used, is to employ prepared statements and parameterized queries. These are SQL statements that are independently parsed by the database server along with the parameters. This approach makes it impossible for attackers to inject malicious SQL.

Implementation Using PDO or MySQLi

PDO provides a universal solution for all supported database drivers, while MySQLi is specific to MySQL.

Using PDO

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

foreach ($stmt as $row) {
    // Do something with $row
}

Using MySQLi (PHP 8.2 with execute_query())

$result = $db->execute_query('SELECT * FROM employees WHERE name = ?', [$name]);

while ($row = $result->fetch_assoc()) {
    // Do something with $row
}

Using MySQLi (PHP 8.1 and earlier)

$stmt = $db->prepare('SELECT * FROM employees WHERE name = ?');
$stmt->bind_param('s', $name); // 's' specifies string type
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    // Do something with $row
}

Correct Connection Setup

When using PDO for MySQL, ensure that real prepared statements are used by disabling emulation.

$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);

For MySQLi, use the following:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // error reporting
$dbConnection = new mysqli('127.0.0.1', 'username', 'password', 'test');
$dbConnection->set_charset('utf8mb4'); // charset

Explanation

Prepared statements are parsed and compiled by the database server, separating the SQL statement from the parameters. When the parameters are passed, they are combined with the compiled statement as strings, eliminating the risk of SQL injection.

Dynamic Queries

While prepared statements can be used for parameters in dynamic queries, the dynamic query structure itself cannot be parametrized. In these cases, it's advisable to employ a whitelist filter.

The above is the detailed content of How to Effectively Prevent SQL Injection in PHP Applications?. For more information, please follow other related articles on the PHP Chinese website!