Pointer Walk

SQL Injection

A name typed into a login box becomes part of the database command. One quote mark turns data into code, and one habit prevents it.

SecurityBeginner8 min read

A login box

A site asks for your name and looks you up in its database. The code behind it might be this:

python
name = request.form["name"]
query = "SELECT * FROM users WHERE name = '" + name + "'"
database.execute(query)

It builds a piece of SQL by gluing your input between two quote marks. Type ana and the database receives:

sql
SELECT * FROM users WHERE name = 'ana'

That works. It is also one of the oldest and most damaging security holes there is.

When data becomes code

The database does not receive "a query" and "an input" as two things. It receives one string of text and reads it as a command. It has no way to know which characters the programmer wrote and which came from a stranger.

So what if the stranger types characters that mean something in SQL?

Input glued into the queryInteractive
Typed in
The code

Typed into the name box

ana

Sent to the database

SELECT * FROM users WHERE name = 'ana'

What happens

1 row returned: ana

How the database reads it: Find the user whose name is ana.

Try all three inputs with the code set to "Builds the query as text".

Example

One quote mark

The input is ' OR '1'='1. Glued in, the query becomes:

sql
SELECT * FROM users WHERE name = '' OR '1'='1'

The first quote in the input closed the string the programmer had opened. Everything after it is no longer a name. It is SQL.

'1'='1' is always true, so the condition is true for every row, and the whole table comes back. If this were a login check, the attacker would be logged in as the first user, often the administrator.

This is SQL injection: input that was meant as data is run as part of the command. The third input in the demo goes further and adds a whole second command.

An attacker who finds this can typically read every table, change or delete data, and sometimes take over the server. It has been behind a long list of real-world data breaches.

The fix: never glue

The cure is to keep the command and the data separate all the way to the database.

Write the query with a placeholder where the value goes, and pass the value alongside:

python
name = request.form["name"]
database.execute("SELECT * FROM users WHERE name = ?", [name])

This is a parameterized query, also called a prepared statement.

Switch the demo to "Uses a parameter" and try the attacks again.

The database now gets the command first, with a gap in it. It works out what the command means. Only then does it receive the value, and it treats that value as a value and nothing else. The quote marks in ' OR '1'='1 are just characters in a rather odd name. No user has that name, so nothing is returned.

The input can no longer change the meaning of the command, because the meaning was fixed before the input arrived.

Things that are not the fix

Tempting ideaWhy it is not enough
Remove or escape quote marks from inputEasy to get wrong, different for every database, and there are tricks that need no quotes
Block words like DROPAttackers have endless ways to write the same thing
Check the input in the browserThe attacker does not have to use your page. They can send any request they like
"Our users would never do that"Automated scanners test every public form on the internet, constantly

Checking that input looks sensible is still worth doing. It is not what protects you from injection. Parameterized queries are.

Using it properly

  1. Use placeholders for every value, every time

    Including values that "cannot" come from a user. Data that was stored safely last week can still be dangerous when glued into a query today.

  2. Let your library do it

    Query builders and ORMs produce parameterized queries by default. The risk returns when you drop down to writing raw SQL strings.

  3. Do not build table or column names from input

    Placeholders work for values, not for names. If a user chooses a sort column, pick it from a fixed list in your code.

  4. Give the application's database account only the rights it needs

    A web app that only reads and writes rows does not need permission to drop tables. If something slips through, the damage is limited.

The same mistake elsewhere

SQL injection is one member of a family. Any time input is glued into text that something else will interpret, the same thing can happen.

  • Command injection: input glued into a shell command.
  • Cross-site scripting: input glued into a web page, where the browser runs it as script.
  • Prompt injection: input glued into instructions for a language model.

The lesson is the same in each: keep instructions and data apart, and never let one be read as the other.

Key takeaways

  • SQL injection happens when user input is glued into the text of a database command.
  • A quote mark in the input can end the intended value, and the rest is run as SQL.
  • An attacker can then read, change or delete data.
  • Parameterized queries send the command and the values separately, so input is only ever data.
  • Filtering dangerous characters is not a reliable defence. Separation is.

Your progress

Check your progress

0%

  1. Read the page50% of this page

    When you have gone through the page and its animations, tick it off.

  2. Take the quiz50% of this page

    7 questions, from easy to hard. Take it as often as you like: the questions change, and only your best score counts.

Saved on this device only. See all your progress