Chapter 9 — Data and databases | Bachelor's Critical infrastructure and sector security

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, EX017.

flowchart TD
  N0["Learning outcomes and connection"]
  N1["9.1 Why relational data"]
  N2["9.2 SQL in four clauses"]
  N3["9.3 Injection"]
  N4["9.5 Practical activity"]
  N5["Chapter synthesis"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5
  N5 --> EX["Worked example"]
  EX --> Q["Exercise EX017"]

Logical drawing

flowchart LR
  N0["Learning outcomes and connection"]
  N1["9.1 Why relational data"]
  N2["9.2 SQL in four clauses"]
  N3["9.3 Injection"]
  N4["9.5 Practical activity"]
  N5["Chapter synthesis"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> Q["EX017"]

[Learning outcomes and connection]
    |
    v
[9.1 Why relational data]
    |
    v
[9.2 SQL in four clauses]
    |
    v
[9.3 Injection]
    |
    v
[9.5 Practical activity]
    |
    v
[Chapter synthesis]
    |
    v
[Worked example] --> [Exercise EX017]

Learning outcomes and connection

After this chapter you can (1) design a small relational schema with keys; (2) write SQL queries that filter, join and order; (3) explain why data types matter, using version numbers as the example; and (4) prevent SQL injection with parameterised queries. It builds on Chapters 3 and 8 and prepares Chapter 14 (privacy), Chapter 15 (measurement) and Chapter 22 (dependencies).

9.1 Why relational data

Security questions combine facts held in different places: which assets exist, what software they run, which advisories affect that software, who owns each asset, which ones face the internet. A relational database stores each kind of fact once, in a table of rows and columns, and connects tables through keys (Codd, 1970). A primary key identifies a row uniquely (an asset ID); a foreign key refers to a row in another table (an asset's product name, matching an advisory's product). A join combines rows that match. Storing each fact once avoids the familiar failure of spreadsheets: two copies that disagree.

9.2 SQL in four clauses

SELECT names the columns you want; FROM ... JOIN ... ON names the tables and how they match; WHERE filters rows; ORDER BY sorts. GROUP BY with COUNT summarises. Python's standard library includes sqlite3, a complete embedded database, so you can practise without a server. Two cautions. First, a join silently drops rows with no match: an asset whose product name is misspelled simply disappears from a vulnerability report. Use a LEFT JOIN and look for nulls when completeness matters. Second, types decide comparisons. Version strings compared as text put "3.10" before "3.4", because the character "1" sorts before "4".

9.3 Injection

If a program builds SQL by pasting user input into a string — "... WHERE id = '" + user_input + "'" — an input such as x' OR '1'='1 changes the query's meaning. This is SQL injection, persistently among the most dangerous software weaknesses (CWE Top 25). The fix is structural: parameterised queries (db.execute("... WHERE id = ?", (user_input,))) send data separately from code, so input can never become SQL.

In Lab 5 every query with an input, such as looking up one asset by ID, should use a ? placeholder.

9.4 Worked example and Lab 5: CPN advisory matching (synthetic)

This case is synthetic. Calder Power Networks keeps an asset table and imports vendor advisories. The question: which assets run a version older than the fixed version, ordered so that known-exploited and internet-exposed items come first? A small custom function compares versions numerically.

# Lab 5 - asset and vulnerability joins in SQLite (synthetic Calder Power data, offline)
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE asset(id TEXT PRIMARY KEY, site TEXT, role TEXT, product TEXT, version TEXT, internet_exposed INTEGER);
CREATE TABLE advisory(product TEXT, fixed_in TEXT, severity TEXT, known_exploited INTEGER);
INSERT INTO asset VALUES
 ('RTU-11','Substation North','feeder RTU','RTU-K','3.2',0),
 ('RTU-12','Substation North','feeder RTU','RTU-K','3.4',0),
 ('HMI-01','Control room','operator HMI','HMI-S','7.0',0),
 ('VPN-01','Head office','remote access gateway','VPN-G','9.1',1),
 ('HIS-01','DMZ','historian replica','HIST-P','5.5',0);
INSERT INTO advisory VALUES
 ('RTU-K','3.4','high',0), ('VPN-G','9.3','critical',1), ('HIST-P','5.1','medium',0);
""")
def vkey(v): return tuple(int(x) for x in v.split("."))
db.create_function("vlt", 2, lambda a, b: int(vkey(a) < vkey(b)))
q = """SELECT a.id, a.role, a.version, v.fixed_in, v.severity, v.known_exploited, a.internet_exposed
       FROM asset a JOIN advisory v ON a.product = v.product
       WHERE vlt(a.version, v.fixed_in)
       ORDER BY v.known_exploited DESC, a.internet_exposed DESC, v.severity"""
for row in db.execute(q):
    print(row)
print("string comparison '3.10' < '3.4':", "3.10" < "3.4", "| numeric:", vkey("3.10") < vkey("3.4"))

Expected output:

('VPN-01', 'remote access gateway', '9.1', '9.3', 'critical', 1, 1)
('RTU-11', 'feeder RTU', '3.2', '3.4', 'high', 0, 0)
string comparison '3.10' < '3.4': True | numeric: False

Two assets need action, the gateway first (this is the Chapter 5 advisory). HMI-01 has no advisory and does not appear; HIS-01 is already past the fix. Note that ORDER BY v.severity sorts the words alphabetically ("critical" < "high" < "medium"), which happens to be correct here and would be wrong with "low". A severity rank column is the robust design.

9.5 Practical activity

(1) Change the join to a LEFT JOIN and list assets with no advisory record — is "no advisory" the same as "no vulnerability"? (2) Write a parameterised query that returns one asset by ID; then show, with a deliberately unsafe string-built query on your in-memory database only, how ' OR '1'='1 returns every row.

The LEFT JOIN shows HMI-01 with nulls in the advisory columns. "No advisory" means no advisory in this feed for product HMI-S, not that the HMI has no flaw; it may have a vendor bulletin outside the feed or none published yet. For part (2), the unsafe query must only run against your in-memory database, never a real one.

9.6 Common errors and limits

Comparing versions, dates or numbers stored as text.
Inner joins that silently drop unmatched rows.
Building SQL with string concatenation.
Trusting product names to match exactly across vendor feeds and local inventories.

Lab 5 shows where three of these errors would bite. The version comparison would treat "3.10" as older than "3.4" if done as text, wrongly marking an updated RTU as vulnerable. The inner join drops HMI-01 silently. And the product names "RTU-K" and "VPN-G" only join because the sample was typed consistently; real vendor feeds spell products differently and need a mapping table.

Exercises — chapter 9

EX017 — Count by site. Objective: summarise with SQL. Inputs: Lab 5. Steps: write a query that returns, per site, the number of assets needing an update. Expected evidence: query and result. Rubric: 3 = correct GROUP BY with the version filter, result Head office 1, Substation North 1; 2 = counts all assets; 1 = syntax only; 0 = none.

EX018 — Severity order. Objective: fix a fragile sort. Inputs: Lab 5 with an extra advisory severity "low". Steps: explain why ORDER BY severity would misplace "low", and give two fixes. Expected evidence: explanation and fixes. Rubric: 3 = alphabetical ordering puts "low" before "medium"; fixes: a numeric rank column or a lookup table/CASE expression; 2 = one fix; 1 = explanation only; 0 = none.

Chapter synthesis

Relational data lets you answer cross-cutting security questions with one query, if keys, types and joins are right. Compare versions numerically, look for what joins drop, rank severities with numbers and never build SQL from untrusted strings.