Specialty · BackendLesson 3 of 6Free to read

Databases from code · Backend Development

How does a backend read and write its database safely?

Why you need it

Almost every backend is a thin layer over a database. Two habits separate solid code from dangerous code: parameterised queries, which make SQL injection impossible, and transactions, which make sure a group of changes happens completely or not at all.

The intuition

Python's built-in sqlite3 works like every database library:

db = sqlite3.connect("shop.db")
rows = db.execute("SELECT id, name FROM users WHERE name = ?", ("Ada",)).fetchall()

execute(sql, params) runs one statement. The ? marks are filled in by the database, as data, never as SQL. That's what stops injection: whatever the user typed can't change the query.
fetchone() gives one row (a tuple) or None; fetchall() gives a list of rows.
cursor.lastrowid is the id of the row you just inserted.
Breaking a rule (UNIQUE, NOT NULL, CHECK) raises sqlite3.IntegrityError: catch it and answer 409 or 400.

Transactions. Moving money is two updates. If the second one fails, the first must be undone. with db: does that: it commits if the block finishes, and rolls back if an exception escapes.

Definition

Parameterised query

SQL with ? placeholders, and the values passed separately: execute(sql, params). The values can never become SQL.

Transaction

A group of changes that is saved completely (commit) or not at all (rollback).

Examples

Query with placeholders

Try it yourself

Rows come back as tuples.

import sqlite3

def make_db():
    db = sqlite3.connect(":memory:")
    db.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE)")
    db.executemany("INSERT INTO users (name, email) VALUES (?, ?)",
                   [("Ada", "ada@example.com"), ("Linus", "linus@example.com")])
    return db

db = make_db()
row = db.execute("SELECT id, email FROM users WHERE name = ?", ("Linus",)).fetchone()
print(row)
print(db.execute("SELECT name FROM users WHERE name = ?", ("Zed",)).fetchone())
print(db.execute("SELECT COUNT(*) FROM users").fetchone()[0])

Output

(2, 'linus@example.com')
None
2

Step by step

L10A fresh in-memory database with two users.
L11The tuple fills the ?. Note the comma: ("Linus",) is a one-item tuple.
L13No such row: fetchone() returns None.None

Insert and handle a duplicate

Try it yourself

lastrowid gives the new id; a UNIQUE clash raises IntegrityError.

import sqlite3

def make_db():
    db = sqlite3.connect(":memory:")
    db.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE)")
    db.executemany("INSERT INTO users (name, email) VALUES (?, ?)",
                   [("Ada", "ada@example.com"), ("Linus", "linus@example.com")])
    return db

def add_user(db, name, email):
    try:
        cur = db.execute("INSERT INTO users (name, email) VALUES (?, ?)", (name, email))
        return 201, {"id": cur.lastrowid}
    except sqlite3.IntegrityError:
        return 409, {"error": "email already used"}

db = make_db()
print(add_user(db, "Grace", "grace@example.com"))
print(add_user(db, "Ada 2", "ada@example.com"))

Output

(201, {'id': 3})
(409, {'error': 'email already used'})

Step by step

L13The new row's id: 3.
L14ada@example.com already exists, so the UNIQUE rule fails.
L15409 Conflict: the request clashes with existing data.

All or nothing

Try it yourself

with db: rolls back both updates when the second one breaks a rule.

import sqlite3

def make_bank():
    db = sqlite3.connect(":memory:")
    db.execute("CREATE TABLE accounts (id INTEGER PRIMARY KEY, owner TEXT, balance INTEGER CHECK (balance >= 0))")
    db.executemany("INSERT INTO accounts (owner, balance) VALUES (?, ?)", [("Ada", 100), ("Bo", 20)])
    db.commit()
    return db

def balances(db):
    return [row[0] for row in db.execute("SELECT balance FROM accounts ORDER BY id")]

db = make_bank()
try:
    with db:
        db.execute("UPDATE accounts SET balance = balance + 50 WHERE id = 2")
        db.execute("UPDATE accounts SET balance = balance - 50 WHERE id = 1")
        db.execute("UPDATE accounts SET balance = balance - 50 WHERE id = 1")
        db.execute("UPDATE accounts SET balance = balance - 50 WHERE id = 1")
except sqlite3.IntegrityError:
    print("rolled back")
print(balances(db))

Output

rolled back
[100, 20]

Step by step

L15Start a transaction.
L19Ada would go to -50: the CHECK rule fails and raises.
L21The exception left the with block, so every update in it was undone.
L22Exactly as before.[100, 20]

Common mistakes

Building SQL with f-strings

f"... WHERE name = '{name}'" lets a user type ' OR '1'='1 and change your query. Always use ? placeholders.

(value) is not a tuple

Parameters must be a tuple or list: ("Ada",) with a comma. ("Ada") is just a string, and each letter becomes a parameter.

Half-finished changes

Two related updates without a transaction can leave the data broken if the program stops between them. Group them in with db:.

Quick quiz

1. Why are ? placeholders safe from SQL injection?

AThe database receives the values separately, as data, never as SQL
BThey remove quotes from the input
CThey only allow letters and digits

Show the answer

Answer: A

The query's structure is fixed before any value is seen.

2. What does this print?

import sqlite3

def make_db():
    db = sqlite3.connect(":memory:")
    db.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE)")
    db.executemany("INSERT INTO users (name, email) VALUES (?, ?)",
                   [("Ada", "ada@example.com"), ("Linus", "linus@example.com")])
    return db

db = make_db()
rows = db.execute("SELECT name FROM users ORDER BY name DESC").fetchall()
print(rows)

A
```
[('Linus',), ('Ada',)]
```
B
```
['Linus', 'Ada']
```
C
```
[('Ada',), ('Linus',)]
```

Show the answer

Answer: A

fetchall gives a list of tuples, even with one column.

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.

PredictInsert and count
Type exactly what this prints.
ModifyClose the injection hole
find_user builds SQL from the input, so find_user(db, "x' OR '1'='1") returns a user it shouldn't. Rewrite the query with a ? placeholder.
WriteTransfer money
Write transfer(db, from_id, to_id, amount) for the accounts table (make_bank creates it).
- move amount from one account to the other inside a transaction (with db:)
- if the sender doesn't have enough money, the table's CHECK (balance >= 0) rule raises sqlite3.IntegrityError: catch it, make sure nothing changed, and return False
- return True when it worked

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

Term	Chinese	Definition
sqlite3	sqlite3	Python's built-in library for SQLite databases.
Parameterised query	参数化查询	SQL with ? placeholders; values are passed separately.
Transaction	事务	Changes saved all together or not at all.
IntegrityError	IntegrityError	Raised when a change breaks a table rule such as UNIQUE or CHECK.