How to prevent SQL injection in PHP

To prevent SQL injection attacks, it is essential to keep the data separate from the SQL language so that each is handled correctly. This ensures that data stays data and is never interpreted as commands by the SQL parser, whatever database you use.

While you can build an SQL statement with properly formatted data parts, it's advisable to use prepared statements and parameterized queries if you're not fully on top of the details. These SQL statements are sent to the database server and parsed without any parameters. As a result, an attacker cannot inject malicious SQL code. Going with this method is therefore an effective way to prevent SQL injection attacks.

To do this, you have two options:

1. Use PDO (for any supported database driver):

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

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

2. Use MySQLi (for MySQL):

Since PHP 8.2+, we can use execute_query(), which prepares the SQL query, binds the parameters and executes it in a single method:

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

Up to PHP 8.1:

$stmt = $dbConnection->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
}

Explanation

Using prepared statements is a safe way to prevent SQL injection attacks. When you pass an SQL statement to the prepare function, it is parsed and compiled by the database server. By specifying parameters as ?, or as a named parameter such as :name, you tell the database engine what to filter. When you call execute, the prepared statement is combined with the parameter values you specified.

It's important to note that the parameter values are combined with the compiled statement, not with an SQL string. This limits the risk of SQL injection by preventing malicious strings from being added to the SQL code sent to the database. All the parameters sent will be treated as strings, but the database engine can also optimize their format for faster execution.

So, with prepared statements, if your $name variable contains "Jeanne' ; DELETE FROM employees", it will be treated as a string and won't delete the table. What's more, when you run the same statement several times, it is parsed and compiled only once, which speeds things up.

In conclusion, using prepared statements is a recommended way to keep your SQL queries secure and prevent SQL injection attacks.

An example (using PDO):

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