Chapter 13 — Data systems
Lesson map
Lesson 13 published. Map, example, EX037.
flowchart TD
N0["Learning outcomes"]
N1["13.1 Data design"]
N2["13.2 Queries and injection"]
N3["Lab — Lab — parameterised queries"]
N4["13.3 Lab result"]
N5["13.4 Access to data"]
N6["13.5 Backups and retention"]
N7["Encryption at rest"]
N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6 --> N7
N7 --> EX["Worked example"]
EX --> Q["Exercise EX037"]
Logical drawing
flowchart LR
N0["Learning outcomes"]
N1["13.1 Data design"]
N2["13.2 Queries and injection"]
N3["Lab — Lab — parameterised queries"]
N4["13.3 Lab result"]
N5["13.4 Access to data"]
N6["13.5 Backups and retention"]
N7["Encryption at rest"]
N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6 --> N7 --> Q["EX037"]
[Learning outcomes]
|
v
[13.1 Data design]
|
v
[13.2 Queries and injection]
|
v
[Lab — Lab — parameterised queries]
|
v
[13.3 Lab result]
|
v
[13.4 Access to data]
|
v
[13.5 Backups and retention]
|
v
[Encryption at rest]
|
v
[Worked example] --> [Exercise EX037]
Learning outcomes
You will (1) design data stores with security in mind; (2) use parameterised queries to keep input as data; (3) protect sensitive fields; (4) control access to data.
13.1 Data design
ticket-web stores riders, tickets and payments. Card numbers are not stored; the payment provider holds them, and ticket-web keeps only a token and the last four digits.
Data that is not stored cannot be stolen from Coastline's database. The payment token is useful only to Coastline's payment provider account, and the last four digits let riders recognise which card they used without exposing the full number. Each field is checked against a purpose: if no feature needs it, it is not collected. This keeps the impact of any breach smaller.
13.2 Queries and injection
When code builds database queries by joining strings with user input, characters in the input can change the query's meaning. This weakness, SQL injection (CWE-89), is part of OWASP Top 10:2021 A03 Injection. The defence is parameterised queries, which send the query and the data separately.
Parameterised queries work because the database receives the query structure first and the values afterwards, so a value can never become part of the structure. Escaping by hand is fragile, since each database has its own rules and one missed case is enough. Kwame's team uses the database library's placeholders everywhere, including in internal reports and scripts.
Lab — Lab — parameterised queries
"""Parameterised queries keep input as data; string building breaks on an ordinary apostrophe."""
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE riders (name TEXT, zone TEXT)")
db.executemany("INSERT INTO riders VALUES (?, ?)", [("Ann O'Neil", "A"), ("Ben Cole", "B")])
name = "Ann O'Neil"
print("parameterised:", db.execute("SELECT zone FROM riders WHERE name = ?", (name,)).fetchall())
try:
db.execute("SELECT zone FROM riders WHERE name = '" + name + "'").fetchall()
except sqlite3.OperationalError as e:
print("string-built query failed:", e)
Run with python3 lab_param.py. Verified output:
parameterised: [('A',)]
string-built query failed: near "Neil": syntax error
13.3 Lab result
With a parameterised query, looking up "Ann O'Neil" returns her zone A. The string-built version fails with a syntax error because the apostrophe in her name ends the text early. The same mechanism that breaks for an honest name is what makes string-built queries dangerous; parameterisation removes it.
Ann's name contains nothing malicious, yet it broke the string-built query, which shows the flaw is in the code, not in her input. An attacker would use the same gap deliberately, adding text that changes the query instead of merely ending it. The fetchall result is a list of tuples, so the single tuple containing A confirms one matching rider.
13.4 Access to data
Staff access goes through roles: support sees tickets but not payment tokens; finance sees refunds. Database accounts used by the app have only the permissions it needs.
Roles reduce the harm a single compromised staff account can do, and they make access easy to review: listing a role's members shows who can see what. The app's database account cannot drop tables or read the audit log, so an injection flaw in ticket-web could not erase its tracks there. Role membership is reviewed each quarter and when staff change jobs.
13.5 Backups and retention
Backups are encrypted and tested; rider data is deleted after the legal retention period.
An untested backup may fail exactly when needed, so restores are practised on a schedule. Encryption protects copies stored away from the main system. Keeping rider data longer than the law requires adds risk without benefit, and deletion must reach backups too, which is why older backups expire on the same timetable as the live data they contain. Restore tests use a separate environment, so a practice restore never overwrites real rider records or exposes them to staff who would not normally see them.
Encryption at rest
The database disk and backups are encrypted, and keys are held in a separate key service. Staff who manage backups cannot read the key service, so a stolen backup alone is useless. Kwame tests a restore every quarter to prove the backups and keys work together.
Worked example
Code review finds one report query built with string formatting for a date filter. Although the date comes from a dropdown, Kwame changes it to a parameter, explaining that inputs can be altered before reaching the server.
Synthetic case. Data and code are synthetic.
Errors and limits
Parameterisation does not help if table or column names come from users; use allowlists for those.
Exercises
- EX037 — Card data. What card data does ticket-web keep? Rubric: 3 = token and last four digits only; 2 = partial; 1 = vague; 0 = none.
- EX038 — Lab contrast. What did the lab show? Rubric: 3 = parameterised works; string-built fails on apostrophe; 2 = partial; 1 = vague; 0 = none.
- EX039 — Dropdown reasoning. Why parameterise a dropdown value? Rubric: 3 = inputs can be altered before server; 2 = partial; 1 = vague; 0 = none.
Synthesis
Keep input as data, store less sensitive data and limit access by role.
Continue studying — chapter 13 of 50
← Chapter 12 (draft) · Course chapter index · Chapter 14 (draft) →