Skip to content

Lesson 1 of 7

SQL injection

Listen to an AI summary of SQL injection

0:00 / 0:34

A note with a gap

A coach is away and leaves a note for the stand-in coach. The note has a gap for the name of one player who may leave early. A player writes in the gap, and the stand-in coach does what the finished note says.

  • The player who may leave at 2pm is ____.The note the coach wrote, with a gap.
  • The player who may leave at 2pm is Sam.The first player wrote Sam in the gap.
  • The player who may leave at 2pm is Sam. Everyone else may go home now.The second player wrote "Sam. Everyone else may go home now" in the gap.
One note, filled in by two players.

The stand-in coach follows the finished note and sends everyone else home. A real stand-in coach might notice the second sentence, but a database never does.

In code, the note is a query that a programmer wrote. The gap is where the code joins in text from a visitor. The stand-in coach is the database, which does what the finished query says.

The search in the sports shop

A visitor types a product name, such as hoodie, into the search box of the sports shop. The browser sends the name to the server in a request. The server runs five lines of Python. Look at how line 3 builds the query.

  1. def search():
  2. name = request.args["name"]# reads the name from the request
  3. query = "SELECT * FROM products WHERE name = '" + name + "'"# joins the name into the query
  4. rows = database.execute(query)# sends the query to the database
  5. return rows
Line 3 is the note with the gap.

Line 3 joins three strings with +. The first string is the start of a query and ends with ', a single quotation mark. The second is the variable name, which holds the text from the visitor. The third string is one more '.

The finished query

For the name hoodie, line 3 builds the query in the picture.

SELECT * FROM products WHERE name = 'hoodie'

  1. SELECT * FROM products WHERE name = 'the programmer wrote
  2. hoodiefrom the request
  3. 'the programmer wrote
The finished query for a customer who typed hoodie.

In SQL, a pair of ' characters marks the start and the end of a piece of text.

An attacker types ' OR '1'='1 into the search box. Line 3 joins the attacker's text into the query in the same way.

SELECT * FROM products WHERE name = '' OR '1'='1'

  1. SELECT * FROM products WHERE name = 'the programmer wrote
  2. ' OR '1'='1from the request
  3. 'the programmer wrote
Part of the attacker's text is now SQL.

The first ' from the attacker ends the piece of text that the programmer started. The words OR '1'='1 are now outside the piece of text, so the database reads the words as SQL. The last ' from the programmer closes the attacker's '1.

The finished query has two tests with OR between the tests. The test name = '' is false for every product. The test '1'='1' is true for every row, so the database returns every row in the table.

What is SQL injection

SQL injection (CWE-89) is untrusted data joined into a SQL query, so that the database runs SQL an attacker wrote.

The same weakness in a query on the table of orders returns the name and address on every order. An attacker can also type SQL that changes or deletes rows.

The diff is the change that gave the sports shop its search. The removed line ran a fixed query. Line 9 joins name into the query, and line 10 sends the query to the database.

shop/search.py+3-1

  1. def search():
  2. - rows = database.execute("SELECT * FROM products")
  3. + name = request.args["name"]
  4. + query = "SELECT * FROM products WHERE name = '" + name + "'"# the weakness
  5. + rows = database.execute(query)
  6. return show_products(rows)
A change to the search of the sports shop.

What is a parameterised query

A safer note is a printed form with a separate box for the name. The stand-in coach reads the box as a name and nothing else.

A parameterised query does the same job in code. The code sends the SQL and the values to the database separately, so that the database never reads a value as SQL.

A placeholder is a mark in a query, such as a question mark, that shows where a value goes. The safer search changes lines 3 and 4. Look at the question mark on line 3 and the list on line 4:

    query = "SELECT * FROM products WHERE name = ?"
    rows = database.execute(query, [name])

Line 3 is now fixed text with a placeholder and no +. Line 4 passes two things to the database, the query and a list that holds name.

  1. SELECT * FROM products WHERE name = ?-- The query. The database reads this text as SQL.
  2. ' OR '1'='1-- The value. The database reads this text as a name and nothing else.
The database receives the query and the value separately.

The database looks for a product whose name is the whole text ' OR '1'='1. No product has that name, so the database returns no rows.

For every query, a reviewer asks whether any value from a request reaches the query without a placeholder.

Why validation is not enough

Validation of the characters of a product name stops some attacks. A real product name such as Supporters' hoodie has a ' in the name, so a check cannot refuse every '.

What a placeholder cannot stand for

A placeholder stands for a value, such as a name or a number. A placeholder cannot stand for the name of a table or a column, or for a SQL word such as SELECT.

The sports shop lets a visitor sort the products by name or by price. The visitor's choice arrives in the request as the text name or price. In SQL, ORDER BY price sorts the rows by the column price. The code joins the visitor's choice into the query:

sort = request.args["sort"]
query = "SELECT * FROM products ORDER BY " + sort

An attacker can send SQL in place of price. The fix is an allowlist, and any value not on the list is refused. Look at lines 1, 3 and 4 of the safer code:

COLUMNS = ["name", "price"]
sort = request.args["sort"]
if sort not in COLUMNS:
    sort = "name"
query = "SELECT * FROM products ORDER BY " + sort

Line 1 is the allowlist, with the two column names the programmer chose. Line 3 tests whether the text from the request is on the list. Line 4 refuses any other text and uses name in its place. Only text that the programmer wrote can reach the query on line 5.

Examples of what to look for

  • A new line that builds a SQL query with +, an f-string or a template string from a value that came from a request.
  • A column name, a table name or a SQL word from a request joined into a query with no allowlist.
  • A check on the length or the characters of the input, with the value still joined into the query.

Check your understanding

  1. State what a placeholder is.

    Show the answer

    A placeholder is a mark in a query, such as a question mark, that shows where a value goes.

  2. A visitor types ' OR '1'='1 into the search box of the sports shop. Give the finished query that line 3 builds.

    Show the answer

    The finished query is SELECT * FROM products WHERE name = '' OR '1'='1'.

  3. Give one reason why validation alone does not fix SQL injection.

    Show the answer

    A real product name such as Supporters' hoodie has a ' in the name, so a check cannot refuse every '.

Practise it

Challenges with a weakness this lesson teaches.