SQL Injection: What It Is and How to Prevent It

cover

SQL injection is one of the oldest and best-understood web vulnerabilities, yet it still appears regularly in security reports, mostly in old code and quick custom scripts. Here is what it is and how to make your code immune to it.

What SQL injection is

SQL injection happens when user input is treated as part of a SQL command instead of as data. Look at this vulnerable PHP code:

$id = $_GET['id'];
$result = $db->query("SELECT * FROM users WHERE id = $id");

If someone visits ?id=1 OR 1=1, the query becomes SELECT * FROM users WHERE id = 1 OR 1=1 and returns every user. Worse inputs can read other tables, bypass logins or change data.

The fix: prepared statements

With prepared statements, the query structure and the data are sent to the database separately. The database never interprets the data as SQL.

$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?');
$stmt->execute([$_GET['id']]);
$user = $stmt->fetch();

Named placeholders work too:

$stmt = $pdo->prepare('SELECT * FROM orders WHERE customer_id = :cid AND status = :status');
$stmt->execute(['cid' => $customerId, 'status' => 'paid']);

In WordPress, use $wpdb->prepare(). In Laravel, the query builder and Eloquent use bound parameters by default.

What prepared statements don't cover

Placeholders work for values, not for table names, column names or sort directions. If users can choose a sort column, check it against a whitelist:

$allowed = ['name', 'created_at', 'price'];
$sort = in_array($_GET['sort'] ?? '', $allowed, true) ? $_GET['sort'] : 'created_at';

Defense in depth

  • Least privilege: the database user for the website shouldn't have permission to drop tables or read other databases.
  • Hide errors in production: detailed SQL error messages help attackers.
  • Validate input types: if an ID must be an integer, cast it or reject anything else.
  • Keep software updated: many injection attacks target known holes in old plugins and CMS versions.
  • Use a web application firewall as an extra layer, not as a replacement for fixing code.

What doesn't work

Escaping functions like addslashes(), blacklists of "dangerous" words, and client-side JavaScript validation are not protection. Prepared statements are the only reliable fix.

Comments