Chapter 13 — Data systems | Engineering Security architecture and engineering

Open, free learning for anyone who wants to protect people and technology. No account is required to read this text or its worked answers.

Chapter 13 — Data systems

Lesson map

Lesson 13 published. Map, example, EX037.

flowchart TD
  N0["Learning outcomes"]
  N1["13.1 Classifying data"]
  N2["13.2 Parameterised queries"]
  N3["Lab — Concatenated versus parameterised query"]
  N4["13.3 Retention and minimisation"]
  N5["Synthesis"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5
  N5 --> EX["Worked example"]
  EX --> Q["Exercise EX037"]

Logical drawing

flowchart LR
  N0["Learning outcomes"]
  N1["13.1 Classifying data"]
  N2["13.2 Parameterised queries"]
  N3["Lab — Concatenated versus parameterised query"]
  N4["13.3 Retention and minimisation"]
  N5["Synthesis"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> Q["EX037"]

[Learning outcomes]
    |
    v
[13.1 Classifying data]
    |
    v
[13.2 Parameterised queries]
    |
    v
[Lab — Concatenated versus parameterised query]
    |
    v
[13.3 Retention and minimisation]
    |
    v
[Synthesis]
    |
    v
[Worked example] --> [Exercise EX037]

Learning outcomes

You will (1) classify data in a transit organisation; (2) prevent injection with parameterised queries; (3) design data retention and minimisation.

13.1 Classifying data

Corvia holds several kinds of data: card identifiers and journey histories, payment tokens, staff records, timetable data and vehicle telemetry. Sofia helps classify them. Journey histories are personal data revealing where people live and work; payment tokens are sensitive financial data; timetables are public. Classification drives architecture: personal and payment data need encryption, access controls and logging, while public data needs integrity but not confidentiality. Daniel stresses that classification must be done before design, because retrofitting protection to a database full of mixed data is expensive and often incomplete.

13.2 Parameterised queries

Sofia writes a query to look up journeys by card identifier. Her first version builds the SQL string by concatenating user input. Daniel shows her that input such as a quote followed by OR 1=1 changes the query's meaning and returns every journey, an injection attack. The fix is a parameterised query, where the database driver passes the input as data, never as code. The lab below demonstrates both with Python's built-in sqlite3 module on a synthetic table. The concatenated query leaks all rows; the parameterised one returns none for the malicious input.

Lab — Concatenated versus parameterised query

# Injection vs parameterised query with stdlib sqlite3 on a synthetic table.
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE journeys (card TEXT, stop TEXT)")
db.executemany("INSERT INTO journeys VALUES (?, ?)", [("C100", "Harbour"), ("C100", "Market"), ("C200", "Depot"), ("C300", "Airport")])
evil = "X' OR '1'='1"
rows_bad = db.execute(f"SELECT * FROM journeys WHERE card = '{evil}'").fetchall()   # never do this
rows_ok = db.execute("SELECT * FROM journeys WHERE card = ?", (evil,)).fetchall()
print("concatenated query rows:", len(rows_bad))
print("parameterised query rows:", len(rows_ok))
print("legit lookup C100:", len(db.execute("SELECT * FROM journeys WHERE card = ?", ("C100",)).fetchall()))

Run with python3 lab_sqli.py. Verified output:

concatenated query rows: 4
parameterised query rows: 0
legit lookup C100: 2

13.3 Retention and minimisation

Storing data forever increases the damage of any breach. Sofia proposes a retention design: detailed journey histories kept for ninety days for customer service and fraud investigation, then aggregated into anonymous counts for planning. Payment tokens are kept only as long as the payment provider requires. ISO/IEC 27002:2022 includes controls on information deletion and data masking. She documents each retention period with its purpose. Minimisation is an architectural control: data that is not stored cannot be stolen, and a smaller data set is easier to protect well.

Worked example

Sofia classifies five Corvia data types, demonstrates injection and its fix in sqlite3 and proposes a ninety-day retention design for journey histories.

Synthetic case. Data, tables and retention periods are synthetic; ISO/IEC 27002:2022 is real.

Errors and limits

Aggregation can still allow re-identification in small groups. Retention rules depend on law and must be checked with counsel.

Exercises

EX037 — Journey sensitivity. Why are journey histories personal data needing protection? Rubric: 3 = they reveal where people live, work and travel;; 2 = location;; 1 = public;; 0 = none.
EX038 — Lab injection result. What did the lab return for the malicious input under each query style? Rubric: 3 = concatenated returned all 4 rows; parameterised returned 0;; 2 = both;; 1 = same;; 0 = none.
EX039 — Minimisation logic. Why is data minimisation a security control? Rubric: 3 = data not stored cannot be stolen and smaller sets are easier to protect;; 2 = not stolen;; 1 = not a control;; 0 = none.

Synthesis

Data architecture classifies before designing, keeps input as data, and stores only what is needed for as long as needed.

Continue studying — chapter 13 of 50

← Chapter 12 (draft) · Course chapter index · Chapter 14 (draft) →