Chapter 13 — Data systems | Engineering Digital forensics and malware analysis

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 Application databases"]
  N2["13.2 Deletion in databases"]
  N3["13.3 Case management schema"]
  N4["13.4 Integrity of records"]
  N5["13.5 Queries as method"]
  N6["13.6 Time storage"]
  N7["13.7 Access control"]
  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 Application databases"]
  N2["13.2 Deletion in databases"]
  N3["13.3 Case management schema"]
  N4["13.4 Integrity of records"]
  N5["13.5 Queries as method"]
  N6["13.6 Time storage"]
  N7["13.7 Access control"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6 --> N7 --> Q["EX037"]

[Learning outcomes]
    |
    v
[13.1 Application databases]
    |
    v
[13.2 Deletion in databases]
    |
    v
[13.3 Case management schema]
    |
    v
[13.4 Integrity of records]
    |
    v
[13.5 Queries as method]
    |
    v
[13.6 Time storage]
    |
    v
[13.7 Access control]
    |
    v
[Worked example] --> [Exercise EX037]

Learning outcomes

You will (1) explain how databases store and delete records; (2) recover evidence from application databases; (3) design a case-data schema; (4) protect integrity of stored findings.

13.1 Application databases

Many apps — browsers, messengers, phone apps — store data in SQLite databases. Tables hold messages, visits and contacts with timestamps.

SQLite stores a whole database in one file with a write-ahead log alongside it, and recent changes may sit only in that log until a checkpoint copies them across. Copying just the main file can therefore miss the latest messages. Priya preserves the database, its WAL and its shared-memory file together, and opens copies read-only, because simply opening a live database in a viewer can trigger a checkpoint that changes the evidence.

13.2 Deletion in databases

Deleting a row often marks space free rather than erasing it. Until overwritten or vacuumed, fragments may remain in free pages or write-ahead logs. Examiners use validated tools to view them and report them as recovered, not current.

A freelist page might still hold most of a deleted message, while the record that linked it to a conversation has gone. Recovered fragments therefore often lack context, such as which chat they belonged to or when they were sent. Priya's reports separate fully recovered records from partial fragments and say what each one can and cannot establish, so a fragment is not presented with more certainty than it has.

13.3 Case management schema

Priya designs tables for Northfield: cases, items, acquisitions (item, tool, hash, examiner, time), findings (item, description, source artefact). Foreign keys enforce that every finding links to an acquired item.

Each acquisition row stores the hash algorithm and value, the tool name and version, the examiner and the start and end times. A finding row cannot be inserted unless its item has at least one acquisition, which the foreign key and a check constraint enforce together. Priya draws the schema and walks Hugo through one synthetic case from intake to finding, checking that every link the report needs exists.

13.4 Integrity of records

Records are append-only: corrections add a new row referencing the old one. Nothing is silently overwritten.

If an acquisition time was entered wrongly, the correction row holds the new time, the reason, the person making the correction and when. Queries normally show the latest values, but the full history is always available. Database permissions stop update and delete statements on these tables for ordinary users, and an administrator who changes those permissions is recorded in a separate audit log. Reports quote the version that was current when they were issued.

13.5 Queries as method

Queries used to produce findings are saved with the case so another examiner can rerun them.

A saved query records the exact SQL, the database it ran against and the time it ran. If a finding says 'forty-two messages between accounts A and B in March', another examiner can run the same query against the preserved copy and should get forty-two. If they get a different number, the difference points to a changed copy, a different tool or an error in the original query, and each of those is worth knowing.

13.6 Time storage

Store timestamps in UTC with the original value and its source format, so conversions can be checked.

Storing the original value and its format, for example 'Unix seconds, 1692576949' or 'Windows FILETIME, 133370505499107328', lets anyone redo the conversion. The UTC column makes sorting and comparison simple. If a conversion bug is found later, the original values allow every affected record to be recomputed, instead of trying to reverse an error from already converted numbers. A source-zone column records whether the original was UTC, local time with a known offset, or local time with no zone at all, so ambiguous values stay visibly flagged and are never silently treated as UTC.

13.7 Access control

Examiners see only their cases; administrators' actions are themselves logged.

Case access follows assignment: an examiner sees items for cases they are assigned to, and a reviewer gets read-only access for the duration of the review. Administrators can change assignments but cannot read case contents without a logged, approved request. A supervisor reviews the access log each month, and unexplained access is investigated like any other incident. Break-glass access for emergencies exists, but each use triggers an alert to two supervisors and needs a written reason within a day. Students on placement never get production accounts; they work on a separate synthetic copy of the system.

Exporting data

When findings leave the case system, exports include a manifest: which queries ran, when, by whom, and the hash of each output file. The recipient can confirm the export matches the record, and the lab can answer later questions about exactly what was disclosed. Priya tests the export on a synthetic case and has a classmate verify every hash independently before she accepts the feature as complete.

Worked example

A synthetic messenger database has 120 live messages and fragments of 14 more in the write-ahead log. Priya's report lists the 14 separately as "recovered from WAL; may not represent the final state of the conversation".

Synthetic case. The database is invented.

Errors and limits

Recovered fragments may be incomplete or out of order; they support, not replace, live records.

Exercises

EX037 — Deleted rows. Why can deleted database rows sometimes be recovered? Rubric: 3 = space marked free; remains until overwritten/vacuumed, also WAL; 2 = partial; 1 = backups; 0 = none.
EX038 — Append-only. How are corrections handled in the case schema? Rubric: 3 = new row referencing old, no overwrite; 2 = partial; 1 = edit in place; 0 = none.
EX039 — WAL wording. How should the 14 recovered messages be reported? Rubric: 3 = separately, labelled recovered, may not be final; 2 = partial; 1 = merged; 0 = none.

Synthesis

Databases keep traces after deletion; case systems must keep findings linked, append-only and reproducible.

Continue studying — chapter 13 of 50

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