A login box
A site asks for your name and looks you up in its database. The code behind it might be this:
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:
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?
Typed into the name box
anaSent 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:
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:
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 idea | Why it is not enough |
|---|---|
| Remove or escape quote marks from input | Easy to get wrong, different for every database, and there are tricks that need no quotes |
| Block words like DROP | Attackers have endless ways to write the same thing |
| Check the input in the browser | The 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
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.
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.
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.
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.