Chapter 9 — Data and databases | Bachelor's Incident response and cyber resilience

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

Lesson map

Lesson 09 published. Map, example, EX025.

flowchart TD
  N0["Learning outcomes and connection"]
  N1["9.1 Schemas and keys"]
  N2["9.2 Transactions"]
  N3["9.3 Backups and integrity"]
  N4["9.4 Query bias"]
  N5["Chapter synthesis"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5
  N5 --> EX["Worked example"]
  EX --> Q["Exercise EX025"]

Logical drawing

flowchart LR
  N0["Learning outcomes and connection"]
  N1["9.1 Schemas and keys"]
  N2["9.2 Transactions"]
  N3["9.3 Backups and integrity"]
  N4["9.4 Query bias"]
  N5["Chapter synthesis"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> Q["EX025"]

[Learning outcomes and connection]
    |
    v
[9.1 Schemas and keys]
    |
    v
[9.2 Transactions]
    |
    v
[9.3 Backups and integrity]
    |
    v
[9.4 Query bias]
    |
    v
[Chapter synthesis]
    |
    v
[Worked example] --> [Exercise EX025]

Learning outcomes and connection

You will be able to (1) read a relational schema and identify keys; (2) explain transactions and why they matter for evidence and restoration; (3) join tables and detect ambiguous matches; (4) recognize how queries can bias results; and (5) propose integrity checks before restoring data. Chapter 8 handled files; most organizational state lives in databases. Chapter 26 uses these ideas to choose restore points.

9.1 Schemas and keys

A schema defines tables, columns and types. A primary key uniquely identifies a row; a foreign key refers to a row in another table. In incident data, keys are often inconsistent: one system calls a case HC-0101, another 101, a third uses an internal number. Before joining, write down how identifiers map, and whether the mapping is one-to-one.

Lab 09 shows the cost of skipping that step: events reference cases as HC-0101, 101 and 10, and only half match by exact key. Writing the mapping first would have exposed that 10 fits two cases before any total was computed.

9.2 Transactions

A transaction groups changes so that they all apply or none does (atomicity) and leaves the database consistent. Databases record committed changes in a write-ahead or transaction log. For responders this matters twice: the log can show what changed when, and a restore must land at a transaction boundary, not in the middle of a business operation (for example, an appointment created without its patient link).

A business operation that spans two transactions can still leave an inconsistent state if it fails between them, so ask how the application groups its writes, not only how the database works.

9.3 Backups and integrity

A backup is a copy at a point in time; a point-in-time recovery combines a base backup with replayed logs. Integrity checks before restoring include: row counts per table compared with expectations, referential checks (every foreign key resolves), checksums where the platform supports them, and business checks (no appointments in the past created after the incident started). A backup taken after an attacker's change faithfully preserves that change.

Run integrity checks on a copy of the backup, not the production database, so a failed check costs nothing. Record each check's result with the backup's timestamp; if the restored system later shows problems, that record shows what was verified and what was assumed.

9.4 Query bias

A query embodies decisions. WHERE status = 'closed' excludes open cases; an inner join drops rows with no match, silently; GROUP BY on a free-text field splits "Phishing" and "phishing". When a number comes from a query, the query is part of the method and must be reported.

In Lab 09 the inner join reports four events, and a reader would not know that four more existed unless the query and the total were shown together. Report the population, the filter and the count excluded by each step, so a reviewer can see what the number leaves out.

9.5 Worked example and lab (synthetic): joining cases and events

Save as lab09.py. It uses Python's built-in sqlite3 module.

# Lab 09 - joins, ambiguous matches and integrity checks on a synthetic incident database (stdlib only)
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE cases(case_id TEXT PRIMARY KEY, opened TEXT, severity INTEGER);
CREATE TABLE events(ev_id INTEGER PRIMARY KEY, case_ref TEXT, kind TEXT);
INSERT INTO cases VALUES ('HC-0101','2026-03-02',2),('HC-0102','2026-03-02',3),
                         ('HC-0103','2026-03-03',2),('HC-0110','2026-03-04',4),
                         ('HC-0010','2026-02-20',1);
INSERT INTO events(case_ref,kind) VALUES ('HC-0101','email'),('101','signin'),
  ('HC-0102','device'),('HC-0110','signin'),('HC-0110','file'),('110','file'),
  ('HC-0199','signin'),('10','signin');
""")
inner = db.execute("SELECT COUNT(*) FROM events e JOIN cases c ON e.case_ref=c.case_id").fetchone()[0]
total = db.execute("SELECT COUNT(*) FROM events").fetchone()[0]
print("events:", total, "matched by exact key:", inner)

def candidates(ref):
    digits = ref.split("-")[-1].lstrip("0")
    return [c for (c,) in db.execute("SELECT case_id FROM cases")
            if c.split("-")[-1].lstrip("0").endswith(digits)]

for ref, kind in db.execute("SELECT case_ref, kind FROM events WHERE case_ref NOT IN (SELECT case_id FROM cases)"):
    m = candidates(ref)
    status = "unique" if len(m) == 1 else ("ambiguous" if m else "orphan")
    print(f"{ref:8} {kind:7} -> {status} {m}")

no_events = db.execute("SELECT case_id FROM cases WHERE case_id NOT IN (SELECT case_ref FROM events)").fetchall()
print("cases with no exact-key events:", [c for (c,) in no_events])

Expected output:

events: 8 matched by exact key: 4
101      signin  -> unique ['HC-0101']
110      file    -> unique ['HC-0110']
HC-0199  signin  -> orphan []
10       signin  -> ambiguous ['HC-0010', 'HC-0110']
cases with no exact-key events: ['HC-0010', 'HC-0103']

Interpretation: an inner join counts only four of eight events and silently drops the other half. A suffix rule recovers two plausible matches (101 → HC-0101, 110 → HC-0110), labels HC-0199 an orphan (an event for a case that does not exist here — perhaps a deleted case, perhaps a typing error) and exposes 10 as ambiguous: it fits both HC-0010 and HC-0110. Note also that the loose rule is itself a hypothesis: 110 would also have matched a case HC-1110 had one existed. The lab reports each decision instead of hiding it. HC-0010 and HC-0103 have no exact-key events; whether they have events under other identifiers is unknown.

What to do with the result: keep the exact-key join as the conservative view, present the suffix matches as proposed links for a human to confirm against source systems, and never fold ambiguous rows into totals. Before any restoration from this database, the integrity checks would include: every events.case_ref resolves (currently 4 of 8 fail), no case without its opening event, and the count of events per day compared with the source system.

9.6 Common errors and limits

Inner joins that silently drop unmatched rows; reporting the result as "all events".
Fuzzy matching without labelling matches as proposed.
Restoring a backup without checking whether it already contains the attacker's changes.
Forgetting that a database's own logs may have limited retention; check before relying on them.
Matching identifiers by suffix and folding the results into totals without human confirmation.
Counting cases with no matching events as cases with no events; HC-0103 may have events under another identifier.
Running exploratory queries on the production database during an incident instead of on a copy.

Exercises — chapter 9

Attempt each exercise before opening the worked results. Every case, organization, person, address and number in an exercise is synthetic unless a cited public source is named.

EX025 — Run and report. Steps: run Lab 09; then add an event with case_ref 'HC-0103 ' (trailing space) and report how it is classified and why. Rubric: 3 = correct classification (orphan) with the whitespace reasoning checked by running; 2 = run without reasoning; 1 = predicted only; 0 = none.

EX026 — Integrity checklist. Inputs: a booking database to be restored from last night's backup after suspicious changes this morning. Steps: list five checks to run on the restored copy before it serves users, each with its pass criterion. Rubric: 3 = five concrete checks with criteria (counts, referential integrity, known-good sample, absence of known-bad changes, application smoke test); 2 = three; 1 = vague; 0 = none.

EX027 — Query as method. Inputs: "Average time to close in March was 2.1 days" computed with WHERE status='closed' AND opened >= '2026-03-01'. Steps: explain two biases and rewrite the claim honestly. Rubric: 3 = excludes still-open (likely longer) cases; cases opened in late March have had less time to close; honest rewrite with counts; 2 = one bias; 1 = none named; 0 = none.

Chapter synthesis

Know your keys, report every join's losses, label fuzzy matches as proposals, treat queries as part of the method and verify a backup's integrity — and its innocence — before restoring it.