A search box that breaks
A small shop has a search box for its stock. A customer types a name with an apostrophe, such as Baker's apron.
Instead of a result, the page shows a line of strange words from the machine behind it. That line is a warning: the page lets visitors write part of its own questions.
The one rule
Warning
Only test your own copy of the lab, and only with permission. It is a shop made weak on purpose, with no way out to the internet. We learn this to fix it. Doing it to a site that is not yours is a crime under the Computer Misuse Act 1990.
By the end of this lesson
Note
I can fix a query so that typed text cannot change it.
Key words
| Word | What it means | Example |
|---|---|---|
| Database | an organised store of records a program can ask questions of | |
| Query | a question a program sends to a database | SELECT name FROM products |
| SQL injection | typed text that changes the query a program sends | |
| Parameter | a value sent beside a query, never inside it | ? |
| Input | anything a visitor types or sends to a page |
The cells students fill in are left blank in this preview.
A query built from text can be changed
Many programs build a query by joining a fixed start to what the visitor typed. The database gets one long string. It cannot tell which part came from the programmer and which from the visitor.
If the typed text has a quote mark in it, the quote can end the programmer's text early. Whatever follows is then read as part of the query.
The query the program builds
# Change term, then press Run. Try "Notebook", then "Baker's apron".
term = "pen"
start = "SELECT name, price FROM products WHERE name LIKE '%"
query = start + term + "%'"
print(query)
Worked example: three searches
| Visitor types | Query the database gets | What happens |
|---|---|---|
| pen | ... LIKE '%pen%' | Works as intended |
| Baker's | ... LIKE '%Baker's%' | The quote ends the text early, so the query breaks |
| x' OR 1=1 -- | ... LIKE '%x' OR 1=1 --%' | The visitor has added a condition of their own |
The cells students fill in are left blank in this preview.
Before you start
Note
Press Start the lab on the bar at the top. Your lab is a small shop with this exact weakness. The two Runs only search it. They change nothing and hand over nothing hidden.
An ordinary search
curl -s -G --data-urlencode "q=pen" http://app:8000/searchShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
A name with an apostrophe
curl -s -G --data-urlencode "q=Baker's" http://app:8000/searchShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
What the two answers tell you
The first prints Pen 1.2, the name and the price. An ordinary search works.
The second prints a line that starts database error. The shop passed your apostrophe straight into its query, and the query broke. That is the sign the shop is open to injection.
A parameter keeps text out of the query
A parameter fixes this. The program sends the query with a ? where the value goes, and sends the typed text separately.
The database reads the query first and fixes its shape. The text arrives after and can only ever be a value. No quote mark in it can end anything.
The same search, with a parameter
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE products (name TEXT, price REAL)")
db.execute("INSERT INTO products VALUES (?, ?)", ("Baker's apron", 14.0))
db.execute("INSERT INTO products VALUES (?, ?)", ("Pen", 1.2))
# Change term and press Run. Nothing you type can end the query early.
term = "Baker's"
rows = db.execute("SELECT name, price FROM products WHERE name LIKE ?", ("%" + term + "%",)).fetchall()
print(rows)
A common mistake
Warning
Many people think removing the apostrophe from the text is enough. It is not. A filter misses cases, and a missed case is a hole. A parameter is safe because it never mixes text into the query. Use one for every value.
Going further
A page lets visitors sort by name or price. A sort column cannot be a parameter, so the choice goes into the query as text.
Write down how you would make that safe. The answer is a short list of allowed words, and a default if the choice is not on it.