SQL injection – how it works and how to prevent it

The examples work with the sample database from the SQL basics series. Only try the attacks on your own test database.

Vulnerable code

A product page takes a number from the address and puts it directly into the query:

// WRONG – a value from the address directly in the query
$id = $_GET["id"];
foreach($pdo->query("SELECT name, price FROM products WHERE id = ".$id) as $row)
  {
    echo htmlspecialchars($row["name"]." – ".$row["price"])."<br>\n";
  }

For the address ?id=3, it lists one product. But anyone can write anything else into the address:

• ?id=0 OR 1=1 – the condition is true for every row and the page lists all products.
• ?id=0 UNION SELECT email, name FROM customers – data from a completely different table is appended to the result. The product page now lists the e-mails and names of customers:

jane@example.com – Jane Smith
peter@example.com – Peter Brown
– Chloé Martin
thomas@example.com – Thomas Clark
– Lucy Turner

In the same way, an attacker could list passwords, orders or anything else that the database user of the website can access. Depending on how the code is written and what privileges the user has, the attacker may even be able to change or delete data.

Escaping is not enough

// WRONG – escaping does not protect a value that is not in quotes
$id = addslashes($_GET["id"]);

Escaping functions (addslashes, mysqli_real_escape_string) only add a backslash before apostrophes, double quotes and a few other characters. The attack from the previous example contains no such character, so it passes through unchanged. Escaping protects only a value inside quotes and only when the character set of the connection is set correctly – and forgetting it in a single place is enough.

Filtering “dangerous” words is not enough

// WRONG – removing keywords can be bypassed
$id = str_ireplace(["union", "select", " or "], "", $_GET["id"]);

The attacker writes ?id=0 UNUNIONION SELSELECTECT email, name FROM customers. The filter removes “UNION” from the word “UNUNIONION” and “SELECT” from the word “SELSELECTECT” – and what remains is exactly the query it was supposed to stop. Filters of forbidden words can always be bypassed somehow, and they also damage ordinary text, such as an article that contains the word “select”.

The right way: a prepared statement

$stmt = $pdo->prepare("SELECT name, price FROM products WHERE id = ?");
$stmt->execute([(int)($_GET["id"] ?? 0)]);
foreach($stmt as $row)
  {
    echo htmlspecialchars($row["name"]." – ".$row["price"])."<br>\n";
  }

The value now goes to the database separately from the query and stays a value, whatever it contains. Converting to an integer with (int) is a good extra habit for numbers: otherwise the database would convert text such as “3 UNION SELECT…” to the number 3 when comparing it with a numeric column and return product 3. That is not an attack, but a value from the address should not get through when it makes no sense.

What not to forget

• Use parameters for all values, not just those from forms – also for values from $_SERVER (the browser of the user, the IP address), from cookies, from the database or from external services.
• Watch out for logging and statistics queries. They are easy to overlook (“it is just a log”), but they can be abused just the same.
• Table and column names cannot be passed as parameters – pick them from a list of allowed values (see the article on prepared statements).
• The database user of the website should have only the privileges it needs. A website that only reads data does not need the right to drop tables.
• Do not display database error messages on a live website – they reveal to the attacker what the queries look like.

Related articles:
Prepared statements in PDO
Storing passwords with password_hash
Protecting forms against CSRF