How Can Prepared Statements Prevent SQL Injection in PHP?

Safeguarding Against SQL Injection in PHP

When integrating user input into SQL queries without appropriate precautions, code vulnerability to SQL injection arises. Consider the following vulnerable code snippet:

$unsafe_variable = $_POST['user_input']; 

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

This scenario can be exploited via user input string, such as value'); DROP TABLE table;--, leading to a malicious query:

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

Prevention Strategies

To avert such attacks, it is imperative to segregate data from SQL, ensuring data is treated as data and not interpreted as commands by the SQL parser. Prepared statements with parameterized queries offer a robust solution, separating the SQL statement and parameter values. The database server parses and compiles the SQL statement, treating parameters as strings, effectively blocking malicious SQL injection attempts.

Implementing Prepared Statements

PDO

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

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

MySQLi for MySQL (PHP 8.2 )

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

MySQLi for MySQL (Up to PHP 8.1)

$stmt = $db->prepare('SELECT * FROM employees WHERE name = ?');
$stmt->bind_param('s', $name); // 's' specifies the variable type => 'string'
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    // Do something with $row
}

Additional Precautions for PDO and MySQLi

PDO

By default, PDO uses emulated prepared statements. To disable emulation and enforce true prepared statements for MySQL, set:

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

MySQLi

Similarly, set:

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

Understanding Prepared Statements

Prepared statements are parsed and compiled by the database server upon execution. Parameters inform the database engine where to apply filters. When executing the prepared statement with values, the compiled statement is combined with those values, not an SQL string. This prevents the injection of malicious strings that could lead to unintended SQL execution.

Caveats

Prepared statements are not suitable for dynamic queries where the query structure is altered. In such cases, a whitelist filter restricting possible values should be employed.

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