Skip to content
PURPLEDUEL

SQL injection explained: what it is and how to prevent it

Updated · 3 min read

SQL injection is a web application vulnerability that appears when text typed by a user is pasted into an SQL query without precautions, so the database runs it as part of the command instead of as plain data. For years it has been among the most widespread and damaging flaws; the good news is that the defence is simple and well known.

Why it happens

A relational database receives instructions written in SQL, for example "give me the row of the users table with this name". Many applications build these instructions by gluing together pieces of text, including what the user typed in a form, in a URL parameter or in a cookie. The problem is mixing code (the structure of the query) with data (what the user typed): if the database cannot tell them apart, whoever writes the data can change the command.

A worked example, at the concept level

Here is a deliberately vulnerable fragment, in pseudocode, from a login form:

name = request.field("name")
query = "SELECT * FROM users WHERE name = '" + name + "'"
result = database.run(query)

If the user types anna, the query is the intended one. But the program cannot tell a name from a piece of an instruction: if quotes and SQL keywords appear in the field, the structure of the query changes, and with it the logic of the site. The possible consequences are reading rows that should not be shown, skipping an access check or altering data. The flaw is not in the database: it is in gluing code and data together.

Trying these techniques on a real site without written permission is a crime. To practise, only use environments built for it, such as labs and deliberately vulnerable applications that you run locally.

How to defend

  1. Parameterised queries (prepared statements). The query with placeholders travels separately from the values, and the database treats the values purely as data, whatever characters they contain.
  2. ORMs and modern libraries, which parameterise by default. Watch out for the places where raw SQL is written by hand.
  3. Validate input: expected type, length and format (a numeric identifier must be a number). It is an extra check, not a replacement for parameterised queries.
  4. Least privilege for the database user: the application must not be able to drop tables or read what it does not need.
  5. Generic errors for users, details only in the logs: overly rich error messages help attackers work out how the query is built.
  6. A web application firewall as a safety net, not as the solution: it filters the most basic attempts but does not fix the code.
  7. Code review and authorised security testing, with scanners and periodic checks, to find the places where a query is still built by hand.

The corrected version of the earlier fragment looks like this: the structure is fixed and the value travels separately.

query = "SELECT * FROM users WHERE name = ?"
result = database.run(query, [name])

Why such a frequent mistake

Concatenating text seems the quickest route, it works in tests with normal data, and the flaw stays invisible until someone tries unusual input. That is why the most effective team rule is simple: no query is ever built by joining text that comes from outside.

Keep reading