A search box shows every customer
A support worker at an online shop types a strange line into the customer search box. Instead of one customer, the page lists all of them.
Nothing was hacked from outside. The shop’s own code trusted what was typed. Two of the most common web attacks work this way.
By the end of this lesson
Note
I can explain how injection and cross-site scripting work.
Key words
| Word | What it means | Example |
|---|---|---|
| Input | anything a user types or sends | |
| Injection | input that changes a command instead of being data | |
| Parameter | a slot in a query that only holds a value | ? |
| Cross-site scripting | input that runs as code in someone’s browser | XSS |
| Escaping | changing special characters so they show as text | < |
The cells students fill in are left blank in this preview.
Injection turns input into a command
Many apps build an SQL query from what you type. A careless app glues your input straight into the query text.
If the input contains a quote mark, it can end the value early. Whatever follows becomes part of the command.
This is SQL injection. Run the query below on a toy table to see it.
Try it: the query that ran
-- The app meant to find one customer by email.
-- Someone typed this into the email box: ' OR '1'='1
-- The app glued it into the query, and this is what ran:
SELECT name, email FROM customers
WHERE email = '' OR '1'='1';
-- Run it. Then change the WHERE line to: WHERE email = '[email protected]';
Parameters keep input as a value
The fix is a parameter. The query has a ? where the value goes, and the input is passed separately.
The database then treats the input only as a value, never as SQL. A quote mark is just a character.
Every common language and database library supports parameters.
Try it: glued and parameter side by side
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE customers (name TEXT, email TEXT)")
db.execute("INSERT INTO customers VALUES ('Priya Shah', '[email protected]')")
db.execute("INSERT INTO customers VALUES ('Tom Reid', '[email protected]')")
typed = "' OR '1'='1"
# Unsafe: the input becomes part of the SQL
unsafe = "SELECT name FROM customers WHERE email = '" + typed + "'"
print("Glued:", db.execute(unsafe).fetchall())
# Safe: the ? is a parameter, so the input is only ever a value
safe = "SELECT name FROM customers WHERE email = ?"
print("Parameter:", db.execute(safe, [typed]).fetchall())
# Change typed to "[email protected]" and run it again.
A common mistake
Warning
Many people think removing quote marks from input makes a query safe. Attackers find other characters and tricks that get round a filter. Use parameters every time input goes into a query, because then input can never become SQL.
Cross-site scripting runs input in a browser
A shop shows customer reviews on its pages. A careless page puts each review in exactly as typed.
If a review contains a <script> tag, every visitor’s browser runs it. That script can read what the visitor sees or send it elsewhere. This is cross-site scripting, or XSS.
The fix is escaping: < becomes <, so the browser shows it and never runs it.
Try it: escape a review
import html
review = "<script>alert('hi')</script> Great bread"
# Unsafe: the review goes into the page as it is
print("<p>" + review + "</p>")
# Safe: escape turns < and > into text the browser only shows
print("<p>" + html.escape(review) + "</p>")
# Try a review with <b>bold</b> in it.