OpenCyber Academy
Bachelor's · Chapter 9 · English
Course code: OCA-106Chapter 09: OCA-106-C009

Chapter 9 — Data and databases

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 Tables"]
  N2["9.2 Parameterised queries"]
  N3["Lab — Lab — safe search"]
  N4["9.3 Lab result"]
  N5["9.4 Permissions"]
  N6["9.5 Backups"]
  N7["Data minimisation"]
  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 Tables"]
  N2["9.2 Parameterised queries"]
  N3["Lab — Lab — safe search"]
  N4["9.3 Lab result"]
  N5["9.4 Permissions"]
  N6["9.5 Backups"]
  N7["Data minimisation"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6 --> N7 --> Q["EX025"]

[Learning outcomes]
    |
    v
[9.1 Tables]
    |
    v
[9.2 Parameterised queries]
    |
    v
[Lab — Lab — safe search]
    |
    v
[9.3 Lab result]
    |
    v
[9.4 Permissions]
    |
    v
[9.5 Backups]
    |
    v
[Data minimisation]
    |
    v
[Worked example] --> [Exercise EX025]

Learning outcomes

You will (1) design simple database tables for an application; (2) use parameterised queries; (3) handle special characters in searches safely; (4) limit database permissions.

9.1 Tables

CampusEats has tables for students, menu items, orders and order lines. Each order line refers to a menu item by its identifier, and prices are copied at order time so later price changes do not alter past orders.

Copying the price means a receipt always shows what the student actually paid. Lena checks that order lines cannot refer to items that do not exist, using a database constraint rather than relying on application code alone. The constraint makes a whole class of bugs impossible.

9.2 Parameterised queries

Queries send SQL text and values separately, so values cannot change the query's structure. This prevents SQL injection (CWE-89, OWASP A03).

Lena reviews every query in CampusEats and finds none built by joining strings with user input. She adds a rule to the code review checklist: any query that includes user text must use parameters, with no exceptions for "internal" pages. Parameters also make queries easier to read and let the database reuse their plans, so the secure way is also the convenient one.

Lab — Lab — safe search

"""Parameterised search with LIKE: user text stays data, wildcards are escaped (synthetic menu)."""
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE menu (item TEXT)")
db.executemany("INSERT INTO menu VALUES (?)", [("100% juice",), ("1000 island salad",), ("veg curry",)])
def search(text):
    pat = "%" + text.replace("\\", "\\\\").replace("%", "\\%").replace("_", "\\_") + "%"
    return [r[0] for r in db.execute("SELECT item FROM menu WHERE item LIKE ? ESCAPE '\\'", (pat,))]
print("search '100%':", search("100%"))
print("search 'curry':", search("curry"))

Run with python3 lab_sqlite.py. Verified output:

search '100%': ['100% juice']
search 'curry': ['veg curry']

9.3 Lab result

Searching for "100%" returns only "100% juice". Without escaping, % is a wildcard in LIKE and "100%" would also match "1000 island salad". The lab escapes %, _ and backslash in the user's text and uses a parameter, so the search means exactly what the student typed.

The example shows that parameters prevent injection but do not make text mean what the user intended; special characters inside a pattern still need handling. Lena adds tests for each special character, including a search for an underscore, which would otherwise match any single character and return unexpected dishes.

9.4 Permissions

The application's database account can read and write its tables but cannot drop tables or create users. Reporting uses a read-only account.

If an injection flaw were ever found, these limits would cap the damage: an attacker could not delete the database or create an administrator account. Lena checks the permissions directly in the database rather than trusting the setup notes, and records the result. Reviewing them each term catches rights that were granted temporarily during a migration and never removed.

9.5 Backups

Nightly encrypted backups are tested monthly by restoring to a test database.

A backup that has never been restored is a hope, not a control. The monthly test restores the latest copy, checks that recent orders are present and records how long the restore took, so the society knows how quickly it could recover after a failure or a mistaken deletion. Backup keys are stored separately from the backups, and only two named society members can use them, which protects the student data the backups contain.

Data minimisation

CampusEats stores only what it needs: name, university email, orders and dietary preferences the student chooses to share. It does not store card numbers; the payment provider handles them. Old orders are deleted after two years. Storing less reduces the harm if data leaks and simplifies compliance with data protection rules.

Database migrations

Changes to tables are written as migration scripts, reviewed like code and applied by the pipeline, never by hand on the live database. Each migration can be rolled back.

Worked example

A student reports that searching "50% off" shows unrelated dishes. Lena traces it to unescaped wildcards in an older search function and applies the lab's approach.

Synthetic case. Data and reports are synthetic.

Errors and limits

Parameterising values does not protect dynamic table or column names; use allowlists for those.

Exercises

Synthesis

Keep user input as data, escape pattern characters and limit database rights.

Continue studying — chapter 09 of 40

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

Worked answers

EX025. So later price changes do not alter past orders.

EX026. Because % is a wildcard in LIKE, matching anything after "100".

EX027. It cannot drop tables or create users.