Chapter 9 — Data and databases | Bachelor's 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 9 — Data and databases

Lesson map

Lesson 09 published. Map, example, EX025.

flowchart TD
  N0["Learning outcomes"]
  N1["9.1 Application databases"]
  N2["9.2 SQL basics"]
  N3["Lab — querying browser history"]
  N4["9.3 Reading the result"]
  N5["9.4 Real browser schemas"]
  N6["9.5 Caution"]
  N7["9.6 Corroboration"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6 --> N7
  N7 --> EX["Worked example"]
  EX --> Q["Exercise EX025"]

Logical drawing

flowchart LR
  N0["Learning outcomes"]
  N1["9.1 Application databases"]
  N2["9.2 SQL basics"]
  N3["Lab — querying browser history"]
  N4["9.3 Reading the result"]
  N5["9.4 Real browser schemas"]
  N6["9.5 Caution"]
  N7["9.6 Corroboration"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6 --> N7 --> Q["EX025"]

[Learning outcomes]
    |
    v
[9.1 Application databases]
    |
    v
[9.2 SQL basics]
    |
    v
[Lab — querying browser history]
    |
    v
[9.3 Reading the result]
    |
    v
[9.4 Real browser schemas]
    |
    v
[9.5 Caution]
    |
    v
[9.6 Corroboration]
    |
    v
[Worked example] --> [Exercise EX025]

Learning outcomes

You will (1) query an application database with SQL; (2) understand browser history structure; (3) filter by time windows; (4) interpret query results cautiously.

9.1 Application databases

Browsers, chat apps and phones store records in SQLite databases. Each table has columns such as URL, title and visit time. Examiners work on copies, never the original file, because opening a database can change it (for example by applying a write-ahead log).

A write-ahead log holds recent changes not yet merged into the main database file. Opening the database with a normal tool can merge them and change the file, so the copy's hash would no longer match. Examiners copy the database together with its companion files and work only on the copies, keeping a hash of each.

9.2 SQL basics

SELECT chooses columns, WHERE filters rows, ORDER BY sorts. Queries are saved with the case so others can rerun them.

The lab's query combines all three: it selects time and URL, filters to 09:10–09:20 UTC, and sorts by time. Saving the exact query text means a reviewer can see the window used and rerun it; changing the window to 09:00–09:30 would also return the timetable entry at 09:02, which is not relevant to the phishing event. A wider window is not wrong, but the report must say which one was used and why.

Lab — querying browser history

"""Query a synthetic browser-history database (sqlite3, in memory)."""
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("create table visits(url text, title text, ts text)")
db.executemany("insert into visits values(?,?,?)", [
    ("https://intranet.example/timetable", "Timetable", "2026-02-10T09:02:00Z"),
    ("https://login-portal.example.net/verify", "Verify your account", "2026-02-10T09:14:30Z"),
    ("https://mail.example/inbox", "Inbox", "2026-02-10T09:13:55Z"),
    ("https://intranet.example/library", "Library", "2026-02-10T10:40:00Z")])
for row in db.execute("select ts, url from visits where ts between '2026-02-10T09:10' and '2026-02-10T09:20' order by ts"):
    print(*row)
print("total visits:", db.execute("select count(*) from visits").fetchone()[0])

Run with python3 lab_history.py. Verified output:

2026-02-10T09:13:55Z https://mail.example/inbox
2026-02-10T09:14:30Z https://login-portal.example.net/verify
total visits: 4

9.3 Reading the result

The synthetic history has four visits. Filtering 09:10–09:20 UTC returns two: the webmail inbox at 09:13:55 and the fake "Verify your account" page at 09:14:30. The sequence — inbox, then a verification page 35 seconds later — is consistent with following a link from an email.

Note the ordering: the rows were inserted out of order, but ORDER BY returns them by time. Without it, the database may return rows in storage order, and a reader could mistake that order for the sequence of events. The comparison works on text here because the ISO format sorts correctly as text when every entry uses the same time zone and precision, which the synthetic data guarantee.

9.4 Real browser schemas

Real browsers store times in their own formats (for example microseconds since an epoch) and split data across tables. Validated tools decode them; Leah's synthetic table uses ISO text for teaching.

Decoding such times by hand is error-prone: confusing microseconds with milliseconds, or choosing the wrong starting date, shifts a time by years or decades. Validated tools have been tested against known values, which is why examiners rely on them for casework. Leah checks one decoded time against an independent source, such as the proxy log, before trusting the rest.

9.5 Caution

History can be cleared or incomplete (private browsing, synchronisation). Absence of a visit does not prove it did not happen.

For ws-114, a missing entry might mean the user browsed privately, cleared history, or used a different browser. Synchronisation can also bring in visits made on other devices, so an entry may not have happened on ws-114 at all. Leah states which browser and profile she examined and treats the proxy log as the independent check on what left the machine.

9.6 Corroboration

The 09:14:30 entry matches the proxy POST time, from an independent source.

The proxy is a separate system with its own clock and storage, so its agreement is meaningful. If the two times had differed by a fixed amount, the clock offsets recorded earlier would explain it; if they differed irregularly, Leah would question whether both describe the same visit. Here the browser time, after correcting ws-114's 47-second clock offset measured at seizure, matches the proxy exactly, and she reports it with both sources named. Each source alone would be weaker; together they show the page was both visited and submitted to.

9.7 Documentation

Record database path, copy hash, query text and result count.

These four items let a reviewer repeat the work exactly: find the same database, confirm the copy is unchanged by its hash, run the same query and expect the same count. The worked example's note does this in one sentence. Without the query text, "two rows" cannot be checked, because a different window could return a different number. Leah keeps these items in a fixed order in her notes, so missing ones are easy to spot.

Joins

Some questions need two tables: visits and URLs are often stored separately and linked by an ID. A JOIN combines them. Leah checks each join by counting rows before and after, so duplicates or losses are noticed.

Worked example

Leah records: "Copy of History database (SHA-256 recorded) queried for 09:10–09:20 UTC; 2 rows returned; the fake verification page visit at 09:14:30 matches the proxy log POST at the same second."

Synthetic case. The database is created by the lab script.

Errors and limits

String comparison of ISO times works only when all times share format and zone; normalise first.

Exercises

EX025 — Window result. Which visits fall in the 09:10–09:20 window? Rubric: 3 = inbox 09:13:55, fake page 09:14:30; 2 = one; 1 = wrong; 0 = none.
EX026 — Work on copy. Why query a copy of the database? Rubric: 3 = opening can change it (WAL); 2 = partial; 1 = speed; 0 = none.
EX027 — Missing visit. What does absence of a visit in history show? Rubric: 3 = not that it did not happen; clearing/private mode; 2 = partial; 1 = proves no visit; 0 = none.

Synthesis

SQL on copied databases, saved queries and corroboration make application data reliable evidence.

Continue studying — chapter 09 of 40

← Chapter 08 (draft) · Course chapter index · Chapter 10 (draft) →