Everyone Can Learn Program
中文 Start learning
Specialty · SecurityLesson 5 of 9Free to read

SQL injection · Cyber Security Basics

How can typing into a login box give someone admin access?

Course contents

Why you need it

SQL injection has been one of the most damaging web vulnerabilities for over twenty years, and it still appears in new code. It comes from one habit: building a database query by gluing user input into a string. The fix is simple once you see the problem.

The intuition

Imagine a form letter: "Dear ____, your order has shipped." If someone writes their name as Bob. Also, refund everything., a careless clerk reads the whole thing as an instruction.

When you paste user input into SQL, the user gets to write part of your program. A quote character ' ends your string early, and whatever follows becomes SQL. ' OR '1'='1 turns a password check into "or true".

A parameterised query sends the SQL and the values separately: ? marks where a value goes, and the database treats it only as data, never as code. No amount of quotes can change the query.

(The examples keep passwords as plain text only to stay short. Lesson 3 showed how to hash them.)

Definition

SQL injection

An attack where input placed into a SQL string changes the meaning of the query, for example to skip a password check or read other tables.

Parameterised query

A query with placeholders such as `?`, whose values are passed separately: `db.execute(sql, (a, b))`. Values can never become SQL code.

Examples

A login that can be fooled

Try it yourself

The function prints the SQL it builds, so you can see what the database receives.

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE users (name TEXT, password TEXT)")
db.execute("INSERT INTO users VALUES ('admin', 's3cret!')")

def login(name, password):
    sql = "SELECT name FROM users WHERE name = '" + name + "' AND password = '" + password + "'"
    print(sql)
    return db.execute(sql).fetchone() is not None

print(login("admin", "guess"))
print(login("admin", "' OR '1'='1"))
Step by step
  1. L2An in-memory database with one user.
  2. L7The input is glued straight into the SQL text.
  3. L11Wrong password: no row, so False.
  4. L12The quote closes the string and OR '1'='1' is always true: logged in without the password.

The fix: placeholders

Try it yourself

Same database, same attack. The ? placeholders keep the input as data.

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE users (name TEXT, password TEXT)")
db.execute("INSERT INTO users VALUES ('admin', 's3cret!')")

def login(name, password):
    sql = "SELECT name FROM users WHERE name = ? AND password = ?"
    return db.execute(sql, (name, password)).fetchone() is not None

print(login("admin", "' OR '1'='1"))
print(login("admin", "s3cret!"))
Step by step
  1. L7The SQL never changes; only ? marks the spots.
  2. L8Values travel separately as a tuple.
  3. L10The attack is now just a strange password that doesn't match.False
  4. L11The real password still works.True

Common mistakes

Escaping quotes by hand

Replacing ' with '' yourself misses cases (other quote styles, encodings, numbers without quotes). Placeholders are simpler and always right.

Placeholders for names

? works for values only, not for table or column names. If users choose a column (for sorting, say), check it against an allow-list such as {"name", "price"}.

Quick quiz

1. What does this print?

name = "x' OR 1=1 --"
sql = "SELECT * FROM users WHERE name = '" + name + "'"
print(sql)
  1. A
    SELECT * FROM users WHERE name = 'x' OR 1=1 --'
  2. B
    SELECT * FROM users WHERE name = 'x OR 1=1 --'
  3. C
    SELECT * FROM users WHERE name = ?
Show the answer

Answer: A

The quote inside the input closes the string. -- starts a SQL comment, which hides the leftover quote.

2. Which line is safe from SQL injection?

  1. A`db.execute(f"SELECT * FROM t WHERE id = {uid}")`
  2. B`db.execute("SELECT * FROM t WHERE id = " + str(uid))`
  3. C`db.execute("SELECT * FROM t WHERE id = ?", (uid,))`
Show the answer

Answer: C

f-strings and + both put the input into the SQL text. Only the placeholder keeps it separate.

Practice

3 exercises in three steps: predict what a program prints, change a program to meet a new requirement, then write one from scratch. Your code runs in the browser and is checked the moment you submit.

  1. PredictWhat does the database see?

    Type exactly what this program prints.

  2. ModifyFix the lookup

    find_user(name) builds its SQL with an f-string, so the attack at the bottom returns a user. Rewrite the query with a ? placeholder so that:

    • find_user("bob") still returns ('bob', 'bob@example.com')
    • the attack returns None
  3. WriteA safe search

    Write search(term) that returns a list of product names whose name contains term, sorted by name. Use LIKE with a placeholder: the pattern is "%" + term + "%", passed as a value, not pasted into the SQL.

    An attack such as search("' OR 1=1 --") must return [].

Practise this lesson

Open the interactive lesson to edit and run these examples and solve the exercises with instant feedback. Free to start, no sign-up.

Open the interactive lesson Course map

Interactive lessons open in order. If you have not done the earlier ones yet, you start at the course map.

Terms in this lesson

TermChineseDefinition
SQL injectionSQL 注入Input that changes the meaning of a SQL query because it was pasted into the SQL text.
Placeholder (?)占位符（?）A marker in a SQL query where a value is filled in safely by the database driver.
Allow-list允许清单A list of the only values that are accepted; everything else is rejected.