Chapter 9 — Data and databases | Bachelor's Threat intelligence and OSINT

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"]
  N1["9.1 Why a database"]
  N2["9.2 Table design"]
  N3["Lab — querying the store"]
  N4["9.3 Reading the result"]
  N5["9.4 Indicator ageing"]
  N6["9.5 Parameterised queries"]
  N7["9.6 Keeping history"]
  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 Why a database"]
  N2["9.2 Table design"]
  N3["Lab — querying the store"]
  N4["9.3 Reading the result"]
  N5["9.4 Indicator ageing"]
  N6["9.5 Parameterised queries"]
  N7["9.6 Keeping history"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6 --> N7 --> Q["EX025"]

[Learning outcomes]
    |
    v
[9.1 Why a database]
    |
    v
[9.2 Table design]
    |
    v
[Lab — querying the store]
    |
    v
[9.3 Reading the result]
    |
    v
[9.4 Indicator ageing]
    |
    v
[9.5 Parameterised queries]
    |
    v
[9.6 Keeping history]
    |
    v
[Worked example] --> [Exercise EX025]

Learning outcomes

You will (1) design a small indicator table; (2) query it with SQL through Python's sqlite3; (3) find stale indicators; (4) store handling labels alongside data.

9.1 Why a database

A spreadsheet works until two analysts edit it at once. A small SQLite database gives one source of truth, queries instead of manual filtering, and a record of what was known when.

Amara moves her indicator list from a shared spreadsheet into SQLite after two analysts overwrote each other's notes. The database file is backed up with the team's other records, and every change is made through her import script or a reviewed query, so it is clear who changed what and when.

9.2 Table design

Columns: value, type, source, TLP label, first_seen, last_seen and status. Storing TLP with each row means queries can filter what may be shared before anything leaves HCHT.

Status records whether an indicator is active, under review or expired. Amara adds a constraint so the TLP column accepts only the five valid labels, which stops a typo from creating a label that queries would miss. First and last seen dates let the team show how long an indicator has been relevant, which matters for ageing decisions.

Lab — querying the store

"""Lab 2 (ch 09): store synthetic indicators in an in-memory SQLite database and query by TLP and age."""
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("create table ioc(value text primary key, type text, tlp text, last_seen text)")
db.executemany("insert into ioc values(?,?,?,?)", [
    ("portal-login.example", "domain", "TLP:GREEN", "2026-10-01"),
    ("203.0.113.50", "ipv4", "TLP:AMBER", "2026-07-15"),
    ("files.example", "domain", "TLP:CLEAR", "2026-10-08"),
    ("198.51.100.7", "ipv4", "TLP:GREEN", "2026-05-02"),
])
q = "select value, tlp from ioc where tlp in ('TLP:CLEAR','TLP:GREEN') and last_seen >= ? order by value"
for row in db.execute(q, ("2026-09-01",)):
    print(row)
print("stale:", db.execute("select count(*) from ioc where last_seen < '2026-09-01'").fetchone()[0])

Run with python3 lab_sql.py. Verified output:

('files.example', 'TLP:CLEAR')
('portal-login.example', 'TLP:GREEN')
stale: 2

9.3 Reading the result

The query returns two domains with their TLP labels: files.example at TLP:CLEAR and portal-login.example at TLP:GREEN. A second query counts two indicators whose last_seen date is older than the review threshold — candidates for expiry.

The query filters by TLP before returning rows, so a list prepared for sharing cannot include restricted entries by mistake. Amara reviews the two expiry candidates with the supervisor before changing their status, since one may still be active according to a recent HSSC update.

9.4 Indicator ageing

Addresses change hands; domains are re-registered. An indicator from last year may now belong to an innocent party. Ageing rules — review after 90 days for addresses, longer for hashes — keep blocklists accurate. Bianco's Pyramid of Pain explains why: low-level indicators are cheap for adversaries to change.

Amara applies the rules through a scheduled query that lists indicators due for review. Each review asks whether the indicator still appears in recent reports or logs. If not, it is moved to monitoring or expired, and the reason is recorded. Hashes age more slowly because a file's hash never changes, although the file may stop being used.

9.5 Parameterised queries

Always pass values as parameters, never by string concatenation. This prevents malformed values from corrupting queries.

Amara's scripts use parameters for every query that includes a value from a feed, even when the value has already been validated. Feeds sometimes contain quotes or semicolons inside domain names that were defanged by hand, and a concatenated query could fail or change in unexpected ways. Parameters remove the risk entirely and make the code easier to read and review. It is a simple, permanent habit.

9.6 Keeping history

When an indicator is retired, set its status to "expired" rather than deleting the row. A later investigation may ask whether HCHT was blocking that address on a particular day, and only a history of status changes with dates can answer the question honestly.

Amara adds a small history table that records each status change with the date and the analyst. That makes it possible to answer such questions with dates rather than memory, and it lets the team measure how long indicators usually stay active before they are retired.

Worked example

Before sending a weekly summary to HSSC members, Amara filters for rows labelled TLP:CLEAR or TLP:GREEN only. A TLP:AMBER row from a partner is automatically excluded. She also exports the stale list to the network team for review rather than deleting rows herself.

Synthetic case. All rows are invented.

Errors and limits

A database inherits the quality of its inputs. Labels must be entered correctly at import.

Exercises

EX025 — Lab output. What did the domain query return?
Show marking criteria
Rubric: 3 = files.example CLEAR and portal-login.example GREEN; 2 = one; 1 = count only; 0 = none.
EX026 — Why store TLP. Why keep the TLP label in each row?
Show marking criteria
Rubric: 3 = filter shareable data by query before release; 2 = partial; 1 = decoration; 0 = none.
EX027 — Stale handling. What should happen to the two stale indicators?
Show marking criteria
Rubric: 3 = review with owners; expire not delete silently; 2 = delete; 1 = keep forever; 0 = none.

Synthesis

Model indicators with labels and dates, query safely, age indicators and keep history.

Continue studying — chapter 09 of 40

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