To effectively prevent SQL injection attacks, regardless of the database you are using, it is essential to keep data separate from SQL commands. This ensures that data is treated strictly as data and not interpreted as executable commands by the SQL parser. While it is possible to construct SQL statements with properly formatted data, if you are not completely familiar with the intricacies involved, it is best to utilize prepared statements and parameterized queries. These techniques involve sending SQL statements to the database server separately from any parameters, making it impossible for an attacker to inject harmful SQL code.
You basically have two options to achieve this:
1. Using PDO (for any supported database driver):
$stmt = $pdo->prepare('SELECT * FROM users WHERE name = :name');
$stmt->execute([ 'name' => $name ]);
foreach ($stmt as $row) {
// Do something with $row
}
2. Using MySQLi (for MySQL):
Since PHP 8.2+ we can make use of execute_query() which prepares, binds parameters, and executes SQL statement in one method:
$result = $db->execute_query('SELECT * FROM users WHERE name = ?', [$name]);
while ($row = $result->fetch_assoc()) {
// Do something with $row
}
Up to PHP8.1:
$stmt = $db->prepare('SELECT * FROM employees WHERE name = ?');
$stmt->bind_param('s', $name); // 's' specifies variable type 'string'
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
// Do something with $row
}
If you’re connecting to a database other than MySQL, there is a driver-specific second option that you can refer to (for example, pg_prepare() and pg_execute() for PostgreSQL). PDO is the universal option.