A shop search that shows an error
A small shop has a search box for its stock list. A visitor types a word with an apostrophe in it, such as Baker's apron.
The page answers with a line of strange words from the machine behind it. The owner calls it a small bug. It is a sign that the page lets visitors write part of its questions.
The one rule
Warning
This lab is a shop that is insecure on purpose. Attack your own private copy and nothing else. It has no way out to the internet. Never try these things on a site that is not yours: in the UK that 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 asks a database | SELECT name FROM products |
| SQL injection | typed text that changes the query a program sends | |
| Parameter | a value sent beside a query and never inside it | ? |
| String | a piece of text in a program | "pen" |
| 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 make a query by joining a fixed start to what the visitor typed. The database receives one long string and cannot tell which part came from the programmer and which from the visitor.
If the visitor's text contains a quote mark, it can end the programmer's text early. What follows is then read as part of the query.
The query the program builds
# Change term to "Notebook" and press Run. Then try "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 receives | What happens |
|---|---|---|
| pen | ... LIKE '%pen%' | Works as intended |
| Baker's | ... LIKE '%Baker's%' | The quote ends the text early, so the query is broken |
| x' AND 1=2 -- | ... LIKE '%x' AND 1=2 --%' | The visitor has added a condition of their own |
The cells students fill in are left blank in this preview.
Look at the shop in your lab
Your lab has a real shop with this weakness. Start the lab, then run these three. They only look. The first is an ordinary search. The second is a name with an apostrophe, which the shop cannot handle. The third prints the line of the shop's code that builds the query.
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.
The line that builds the query
docker exec app grep -n "rows = 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.
What the three answers tell you
The first prints Pen 1.2, the name and the price. The second prints a line that starts database error. The shop passed your apostrophe straight into its query.
The third prints a line number and one long line of code. Find the + signs in it. Each one joins typed text into the query.
A parameter keeps text out of the query
A parameter fixes this. The program sends the query with a ? where the value goes. It sends the typed text separately.
The database reads the query first and fixes its shape. The text arrives later 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 typed text is enough. It is not. Filters miss cases, and a missed case is a hole. A parameter works because it never mixes text into the query, however clever the text is. Use a parameter for every value.
Some parts cannot be parameters
A parameter can stand for a value. It cannot stand for a column name or a sort order. A page that lets visitors sort by price or by name still has to put that word into the query.
The safe way is a short list of allowed words. If the visitor's choice is not on the list, use a default.
Only words from the list
ALLOWED = ["name", "price"]
# Change choice to "price", then to "stock; drop", and press Run.
choice = "name"
if choice in ALLOWED:
column = choice
else:
column = "name"
print("ORDER BY " + column)