Parameters keep typed text out of a query
SQL injection is an attack where typed text changes the database query a program sends. The fix is a parameter: a value sent beside the query and never inside it.
A library catalogue lets staff search by title. One search for a title with an apostrophe in it breaks the page. Another lists books that should be hidden. Both come from the same habit of building queries from text.
By the end of this lesson
Note
I can write a database query so that typed text cannot change it.
Key words
| Word | What it means | Example |
|---|---|---|
| Query | a question a program asks a database | SELECT title FROM books |
| SQL injection | typed text that changes the query a program sends | |
| Parameter | a value sent beside a query and never inside it | |
| Placeholder | the mark in a query where a parameter goes | ? |
| Escaping | putting a backslash or a second quote before a risky character | |
| Allow list | a list of the only words accepted |
The cells students fill in are left blank in this preview.
The one rule
Warning
The lab is a shop that is insecure on purpose. Attack and fix your own private copy and nothing else. It has no way out to the internet. Never test a site that is not yours. In the UK that is a crime under the Computer Misuse Act 1990.
A query built from text can be rewritten
If a program joins typed text into a query string, the database receives one string. It cannot tell which part the programmer wrote and which part a visitor typed.
A quote mark in the typed text ends the programmer's text early. Whatever follows is read as part of the query. The visitor has become a second author.
The same word, joined and as a parameter
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE books (title TEXT, on_loan INTEGER)")
db.executemany("INSERT INTO books VALUES (?, ?)", [("Dune", 0), ("Emma", 0), ("Secret ledger", 1)])
# Change word to "Dune" and press Run. Then put the first version back.
word = "zzz' OR on_loan = 1 -- "
joined = "SELECT title FROM books WHERE on_loan = 0 AND title = '" + word + "'"
print(joined)
print(db.execute(joined).fetchall())
safe = "SELECT title FROM books WHERE on_loan = 0 AND title = ?"
print(db.execute(safe, (word,)).fetchall())
A parameter is only ever a value
With a parameter, the program sends the query with a placeholder, ?, where the value goes. It sends the typed text separately.
The database reads and fixes the query first. The text arrives afterwards and can only be a value. No quote mark in it can end anything. Use a parameter for every value, every time.
Worked example: which tool for which part
| Part of the query | Can it be a parameter? | What to do |
|---|---|---|
| A title or a name to match | Yes | Use a placeholder |
| A number such as an id | Yes | Use a placeholder |
| A column name | No | Pick it from an allow list |
| A sort direction | No | Accept only asc or desc |
| The whole query | No | Write it in the program, never from input |
The cells students fill in are left blank in this preview.
A common mistake
Warning
Many people think a parameter can stand in for any part of a query. It cannot. It stands for a value only. A column name or a sort word must be chosen from an allow list in your own code, because it cannot be a placeholder.
Escaping is a weaker second choice
Escaping changes risky characters so they lose their meaning, such as doubling a quote mark. It can work, but the rules differ between databases, and one missed place is a hole.
It also does nothing where there is no quote to double, such as a number. A parameter does not depend on any of that. Prefer the parameter whenever the database offers one.
Escaping does nothing for a number
def escape(text):
return text.replace("'", "''")
# Change number to "5" and press Run. Then try "5 OR 1=1" again.
number = "5 OR 1=1"
print("'" + escape("O'Brien") + "'")
print("SELECT * FROM orders WHERE id = " + escape(number))
Count where the shop talks to its database
Your lab shop is written in Python. Six lines of its program call the database. The next two lines count them, then show the two that already use placeholders. You only look. Finding the weak one is the lab's job.
How many lines call the database?
docker exec app grep -c "db.execute" /tmp/app/app.pyShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Which lines already use placeholders?
docker exec app grep -n "VALUES (?" /tmp/app/app.pyShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Five lines are safe and one is not
The first prints 6. The second prints two lines, numbered 15 and 20. Each has a ? where the values go, and the values are sent separately.
Three more of the six use fixed text with nothing typed in. That leaves one line out of six. It is the only one that joins text from a visitor into the query.
A search with several filters still uses placeholders
Some pages let a visitor filter by several columns. The program builds the condition list in a loop. Each column comes from an allow list. Each value goes into a list of parameters.
The query text is made only from your own words. The values never touch it. If no filter is chosen, leave the WHERE part out.
Building a filter with an allow list
ALLOWED = ["category", "in_stock"]
# Add a filter of your own, such as "in_stock": 0, then press Run.
# Unknown columns are skipped here. In the assignment you refuse them.
filters = {"category": "pens", "in_stock": 1, "colour; DROP": "red"}
conditions = []
params = []
for column in filters:
if column in ALLOWED:
conditions.append(column + " = ?")
params.append(filters[column])
print("SELECT name FROM products WHERE " + " AND ".join(conditions))
print(params)