Lesson 7 — Security, Performance and Real-World Practice

SQL injection

Attack where user input is executed as part of a query.

Least privilege

Give each account only the permissions it truly needs.

Row level security

Policies limiting which rows a user can see or change.

Query plan

The steps the database will take; inspected with EXPLAIN.

Point-in-time recovery

Restoring a database to any moment before an incident.

1. 1Parameterise: Replace every string-built query with placeholders.
2. 2Trim privileges: App user gets SELECT/INSERT/UPDATE on its tables only.
3. 3Add policies: Row level security so a student reads only their own progress.
4. 4Index the hot paths: EXPLAIN the three slowest queries and index the filtered columns.
5. 5Prove the backup: Restore last night's backup into a scratch database and query it.

python# VULNERABLE — the input becomes code
name = request.form["name"]
cur.execute(f"SELECT * FROM student WHERE name = '{name}'")
#  name = "' OR '1'='1"  ->  returns every student

# SAFE — the input stays data
cur.execute("SELECT * FROM student WHERE name = %s", (name,))

# And in the database itself:
# CREATE POLICY own_rows ON progress
#   FOR SELECT TO app_user USING (user_id = current_setting('app.user_id')::uuid);

The parameterised version sends the query and the value separately, so the database never parses user input as SQL.