{
"cells": [
{
"cell_type": "raw",
"metadata": {},
"source": [
"---\n",
"title: \"Notebook 11: Cybersecurity — Defending the Clinic\"\n",
"subtitle: \"COMP 1150 — Computer Science Concepts\"\n",
"author: \"Brendan Shea, PhD\"\n",
"date: last-modified\n",
"---"
]
},
{
"cell_type": "markdown",
"metadata": {
"colab_header": true
},
"source": [
"\n",
"[](https://colab.research.google.com/github/brendanpshea/computing_concepts_python/blob/main/v2/notebooks/COMP1150_NB11_Cybersecurity.ipynb) \n",
"[Download .ipynb](https://raw.githubusercontent.com/brendanpshea/computing_concepts_python/main/v2/notebooks/COMP1150_NB11_Cybersecurity.ipynb) · [View on GitHub](https://github.com/brendanpshea/computing_concepts_python/blob/main/v2/notebooks/COMP1150_NB11_Cybersecurity.ipynb)\n"
]
},
{
"cell_type": "markdown",
"id": "0d3d9486",
"metadata": {},
"source": [
"## Learning Outcomes\n",
"\n",
"By the end of this notebook, you will be able to:\n",
"\n",
"- Use the CIA triad — confidentiality, integrity, availability — to reason about what a system must protect\n",
"- Explain how SQL injection works, and stop it with parameterized queries\n",
"- Describe cross-site scripting and the general rule of never trusting user input\n",
"- Distinguish hashing from encryption, and store passwords safely with salting\n",
"- Keep secrets like API keys out of your code and out of your git history\n",
"\n",
"*Maps to course LOs: 12*"
]
},
{
"cell_type": "markdown",
"id": "9ce52fb1",
"metadata": {},
"source": [
"## The Clinic That Leaked\n",
"\n",
"At 6 a.m., **Mina Harker's** phone rings. Mina is chief risk officer at Harker Risk & Insurance, and the caller is **Victor Frankenstein**, CTO of Frankenstein BioLab. His clinic's patient portal — a small web app, the kind a student could build in a weekend — has just leaked forty thousand medical records.\n",
"\n",
"There was no break-in. No door was forced. The building alarms never went off. Someone simply typed the right thing into the wrong box on the login page, and the app handed over its entire database.\n",
"\n",
"Mina has seen this a hundred times. \"Victor,\" she says, pulling on her coat, \"your app has doors you never counted. Attackers don't pick locks. They walk through the doors you forgot were there.\""
]
},
{
"cell_type": "markdown",
"id": "56ec915e",
"metadata": {},
"source": [
"This is the notebook where your programs grow enemies.\n",
"\n",
"In the last notebook you built a web server and opened it to the network — where *anyone* can send it a request. Most are ordinary visitors. A few are not. This notebook is about that few: the attacks that target a small web app like the one you'll build for your final project, and the defenses that stop them.\n",
"\n",
"Mina's method is the whole plan. She doesn't guess where the danger is. She **enumerates every door and tests each one**:\n",
"\n",
"1. **What are we even protecting?** The three questions behind every security decision.\n",
"2. **The open door** — SQL injection, the attack that leaked Victor's records.\n",
"3. **Poisoned input** — why *all* user input is a threat, and cross-site scripting.\n",
"4. **The locked door** — hashing, salting, and how to store passwords safely.\n",
"5. **The key under the mat** — secrets like passwords and API keys, and how to not leak them.\n",
"\n",
"At the end, you'll run a real security audit: take a deliberately broken app and harden it, door by door."
]
},
{
"cell_type": "markdown",
"id": "056b1367",
"metadata": {},
"source": [
"## 1. What Are We Protecting? The CIA Triad\n",
"\n",
"Before you can defend a system, you have to know what \"safe\" even means for it. Security professionals answer that with three questions, known as the **CIA triad** — nothing to do with the agency, everything to do with three properties every system needs:\n",
"\n",
"- **Confidentiality** — can the *wrong people read* the data? (Victor's leak was a confidentiality failure: outsiders read patient records.)\n",
"- **Integrity** — can the wrong people *change* the data? (Imagine an attacker altering a prescription's dosage.)\n",
"- **Availability** — can they *stop the system working* at all? (Imagine the portal knocked offline so no doctor can reach any record.)\n",
"\n",
"Every attack in this notebook breaks at least one of these. Every defense protects at least one. When Mina audits a system, she walks every asset past all three questions."
]
},
{
"cell_type": "code",
"execution_count": 1,
"id": "8022afb5",
"metadata": {},
"outputs": [
{
"data": {
"image/svg+xml": [
"\n",
"\n",
"\n",
"\n",
"\n"
],
"text/plain": [
""
]
},
"execution_count": 1,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"#| label: fig-cia\n",
"#| fig-alt: \"Diagram of the CIA triad applied to patient records: confidentiality is the wrong people reading it, integrity is the wrong people changing it, and availability is stopping it from working at all.\"\n",
"import graphviz\n",
"graphviz.Source(r\"\"\"digraph G {\n",
" bgcolor=\"transparent\"; rankdir=TB;\n",
" node [shape=box, style=\"rounded,filled\", fontname=\"Helvetica\", fontsize=11];\n",
" triad [label=\"Patient Records\", shape=diamond, fillcolor=\"#f3e9d8\"];\n",
" c [label=\"Confidentiality\\nwrong people READ it\\n(the leak)\", fillcolor=\"#dde8f0\"];\n",
" i [label=\"Integrity\\nwrong people CHANGE it\\n(altered dosage)\", fillcolor=\"#dde8f0\"];\n",
" a [label=\"Availability\\nthey STOP it working\\n(portal offline)\", fillcolor=\"#e3efe1\"];\n",
" triad -> c; triad -> i; triad -> a;\n",
"}\"\"\")"
]
},
{
"cell_type": "markdown",
"id": "e487f1d2",
"metadata": {},
"source": [
"**Reading it:** one asset — the patient records — faces three distinct dangers. A defense that guarantees confidentiality (say, hiding the data) does nothing for availability (the data can still be knocked offline). That's why security is never one lock. It's a checklist you run on everything valuable."
]
},
{
"cell_type": "markdown",
"id": "5d769720",
"metadata": {},
"source": [
"### Modeling Threats as Data\n",
"\n",
"Mina doesn't keep this in her head — she writes it down. A **threat model** is just a list of \"what could go wrong,\" and it's ordinary data you already know how to handle: a list of dictionaries, one per threat.\n",
"\n",
"The next cell is the start of Mina's threat model for the clinic. Each entry names an asset, the threat to it, and which CIA property it breaks."
]
},
{
"cell_type": "code",
"execution_count": 2,
"id": "b8105dee",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"confidentiality <- patient records (leaked via login box)\n",
"integrity <- prescriptions (dosage altered)\n",
"availability <- the portal (flooded offline)\n"
]
}
],
"source": [
"threat_model = [\n",
" {\"asset\": \"patient records\", \"threat\": \"leaked via login box\", \"breaks\": \"confidentiality\"},\n",
" {\"asset\": \"prescriptions\", \"threat\": \"dosage altered\", \"breaks\": \"integrity\"},\n",
" {\"asset\": \"the portal\", \"threat\": \"flooded offline\", \"breaks\": \"availability\"},\n",
"]\n",
"\n",
"for row in threat_model:\n",
" print(f\"{row['breaks']:16} <- {row['asset']} ({row['threat']})\")"
]
},
{
"cell_type": "markdown",
"id": "12cc6666",
"metadata": {},
"source": [
"That's the whole discipline in one loop: enumerate what you have, name what could happen to it, label the property at risk. A messy list like this, written *before* you're attacked, is worth more than any single clever defense. It turns \"are we secure?\" — a question no one can answer — into \"have we handled every row?\" — a question you can."
]
},
{
"cell_type": "markdown",
"id": "82f348d5",
"metadata": {},
"source": [
"### 💭 Think About It — Which Letter Matters Most?\n",
"\n",
"The three properties aren't equally important for every system. Rank C, I, and A for each of these, and be ready to defend your order:\n",
"\n",
"- a hospital's patient records\n",
"- a bank's account balances\n",
"- a public group chat for planning a birthday party\n",
"\n",
"There's no single right ranking — that's the point. Security starts with knowing what *you* most cannot afford to lose."
]
},
{
"cell_type": "markdown",
"id": "121ac8f4",
"metadata": {},
"source": [
"## 2. The Open Door: SQL Injection\n",
"\n",
"Now the attack that leaked Victor's clinic. It's called **SQL injection**, and it's been near the top of every \"most dangerous vulnerabilities\" list for twenty years. To see it, we need to look at how Victor's login worked.\n",
"\n",
"When someone logs in, the app takes the username they typed and builds a database query to look them up. Victor's code built that query the tempting, disastrous way: by gluing the user's text directly into the SQL string.\n",
"\n",
"The next cell sets up a tiny version of Victor's user database, so we can watch the attack happen safely."
]
},
{
"cell_type": "code",
"execution_count": 3,
"id": "b4a7a784",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Set up 3 users.\n"
]
}
],
"source": [
"import sqlite3\n",
"\n",
"db = sqlite3.connect(\":memory:\")\n",
"db.execute(\"CREATE TABLE users (name TEXT, role TEXT)\")\n",
"db.executemany(\"INSERT INTO users VALUES (?, ?)\",\n",
" [(\"mina\", \"admin\"), (\"victor\", \"doctor\"), (\"igor\", \"clerk\")])\n",
"db.commit()\n",
"print(\"Set up 3 users.\")"
]
},
{
"cell_type": "markdown",
"id": "a5dac406",
"metadata": {},
"source": [
"Here is Victor's mistake — the vulnerable lookup. It builds the query by pasting the username straight into the SQL text with an f-string. For a normal username like `mina`, it works perfectly, which is exactly why the bug survives to production."
]
},
{
"cell_type": "code",
"execution_count": 4,
"id": "641fa428",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"QUERY: SELECT * FROM users WHERE name = 'mina'\n",
"[('mina', 'admin')]\n"
]
}
],
"source": [
"def unsafe_login(username):\n",
" query = f\"SELECT * FROM users WHERE name = '{username}'\"\n",
" print(\"QUERY:\", query)\n",
" return db.execute(query).fetchall()\n",
"\n",
"print(unsafe_login(\"mina\"))"
]
},
{
"cell_type": "markdown",
"id": "f19a2737",
"metadata": {},
"source": [
"Normal input, normal result: one row for `mina`. The query reads `... WHERE name = 'mina'`. Nothing looks wrong.\n",
"\n",
"Now the attacker. Instead of a username, they type this into the login box:\n",
"\n",
"```\n",
"' OR '1'='1\n",
"```\n",
"\n",
"### 🔮 Predict Before You Run\n",
"\n",
"Look at how `unsafe_login` builds its string. When the username is `' OR '1'='1`, what will the finished `QUERY` line actually say? And `'1'='1'` is *always* true — so how many rows will come back? Write down your guess, then run the next cell."
]
},
{
"cell_type": "code",
"execution_count": 5,
"id": "06eb56ec",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"QUERY: SELECT * FROM users WHERE name = '' OR '1'='1'\n",
"[('mina', 'admin'), ('victor', 'doctor'), ('igor', 'clerk')]\n"
]
}
],
"source": [
"attack = \"' OR '1'='1\"\n",
"print(unsafe_login(attack))"
]
},
{
"cell_type": "markdown",
"id": "e4f4caa9",
"metadata": {},
"source": [
"### Understanding the Attack\n",
"\n",
"The finished query became:\n",
"\n",
"```\n",
"SELECT * FROM users WHERE name = '' OR '1'='1'\n",
"```\n",
"\n",
"The attacker's quote mark closed Victor's string early, and their `OR '1'='1'` bolted on a condition that is *always true*. So the database happily returned **every user** — Mina, Victor, and Igor. Scale that from three rows to forty thousand patients, and you have Victor's morning.\n",
"\n",
"The root cause has a name worth remembering: **the data became code.** The app meant the username to be *data* — a value to look up. But because it was pasted into the query text, a cleverly written username turned into *commands* the database obeyed. Every injection attack, of every kind, is a version of this one confusion."
]
},
{
"cell_type": "markdown",
"id": "8aff930f",
"metadata": {},
"source": [
"### The Fix: Parameterized Queries\n",
"\n",
"The fix is not to hunt for bad characters or ban apostrophes. It's to *never let data into the code position in the first place.* You do this with a **parameterized query**: leave a `?` placeholder in the SQL, and hand the value to the database *separately*. The database then treats it as pure data — never as commands, no matter what's in it.\n",
"\n",
"Watch the same attack hit the safe version."
]
},
{
"cell_type": "code",
"execution_count": 6,
"id": "d63f65f2",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"normal: [('mina', 'admin')]\n",
"attack: []\n"
]
}
],
"source": [
"def safe_login(username):\n",
" return db.execute(\"SELECT * FROM users WHERE name = ?\", (username,)).fetchall()\n",
"\n",
"print(\"normal:\", safe_login(\"mina\"))\n",
"print(\"attack:\", safe_login(\"' OR '1'='1\"))"
]
},
{
"cell_type": "markdown",
"id": "cc466ab0",
"metadata": {},
"source": [
"### Understanding the Fix\n",
"\n",
"- The `?` is a placeholder. The real value rides in the second argument, the tuple `(username,)`.\n",
"- The database keeps the query and the value in separate hands. The username `' OR '1'='1` is now looked up as a *literal name* — and since no user is actually called that, the attack returns an empty list.\n",
"- Normal logins still work exactly as before.\n",
"\n",
"This is the entire defense, and it's less code than the broken version. The rule for the rest of your life as a programmer: **never build a SQL query by gluing in user input. Always use `?` placeholders.** Your database library — the `sqlite3` you already know — has supported this the whole time."
]
},
{
"cell_type": "markdown",
"id": "fbed97fc",
"metadata": {},
"source": [
"### ✏️ Your Turn — Close Victor's Other Door\n",
"\n",
"Victor has a second vulnerable function: `find_role`, which looks up a user's role. It's built the unsafe way. Rewrite it to use a parameterized query, then confirm the attack string returns nothing."
]
},
{
"cell_type": "code",
"execution_count": 7,
"id": "6683c631",
"metadata": {},
"outputs": [],
"source": [
"#| eval: false\n",
"# ⬇️ This is the VULNERABLE version. Fix it.\n",
"def find_role(username):\n",
" query = f\"SELECT role FROM users WHERE name = '{username}'\"\n",
" return db.execute(query).fetchall()\n",
"\n",
"# TODO: rewrite find_role to use a ? placeholder.\n",
"# Then test it:\n",
"# print(find_role(\"mina\")) # should find the admin\n",
"# print(find_role(\"' OR '1'='1\")) # should return []"
]
},
{
"cell_type": "markdown",
"id": "4e03b575",
"metadata": {},
"source": [
"## 3. Poisoned Input: Never Trust the User\n",
"\n",
"SQL injection is one case of a much larger law, and **Abraham Van Helsing** — who has spent his career hunting things that hide inside ordinary-looking bodies — states it plainly: *\"All input is a suspect until proven innocent.\"*\n",
"\n",
"Any data that comes from *outside* your program — a form field, a URL, an uploaded file, a response from another server — could have been crafted by an attacker. SQL injection poisons a database query. Its cousin poisons a *web page*.\n",
"\n",
"### Cross-Site Scripting (XSS)\n",
"\n",
"Suppose the clinic portal lets patients leave a comment, and later shows those comments to staff. A normal patient writes \"Running five minutes late.\" An attacker writes this:\n",
"\n",
"```\n",
"\n",
"```\n",
"\n",
"If the portal drops that text straight onto the page, the victim's browser can't tell the attacker's `\n",
"escaped safe: <script>steal_the_session()</script>\n"
]
}
],
"source": [
"import html\n",
"\n",
"attacker_comment = \"\"\n",
"print(\"stored raw: \", attacker_comment)\n",
"print(\"escaped safe: \", html.escape(attacker_comment))"
]
},
{
"cell_type": "markdown",
"id": "a89f6f97",
"metadata": {},
"source": [
"The escaped version displays as text and does nothing. Here is the reassuring part: the template tool Flask uses (Jinja) does this escaping **automatically** for every value you drop into a page. Much of your XSS defense is handled for you — as long as you don't go out of your way to disable it. The danger returns the moment you mark something \"safe\" to skip escaping, which is why you should almost never do that."
]
},
{
"cell_type": "markdown",
"id": "fa8b1314",
"metadata": {},
"source": [
"### Validation: Checking Input Before You Trust It\n",
"\n",
"Escaping handles output. The other half of Van Helsing's law is **input validation**: before you accept a value, check that it's the *shape* you expected — right type, sane length, allowed characters. A field asking for an age should reject `\"; DROP TABLE\"` for the simple reason that it isn't a number.\n",
"\n",
"The next cell is a small validator for the clinic's intake form."
]
},
{
"cell_type": "code",
"execution_count": 9,
"id": "a14a78e4",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"'37' -> (True, 37)\n",
"'-4' -> (False, 'Age must be digits only.')\n",
"'200' -> (False, 'Age must be between 0 and 120.')\n",
"\"'; DROP TABLE users; --\" -> (False, 'Age must be digits only.')\n"
]
}
],
"source": [
"def validate_age(raw):\n",
" if not raw.isdigit():\n",
" return False, \"Age must be digits only.\"\n",
" age = int(raw)\n",
" if age < 0 or age > 120:\n",
" return False, \"Age must be between 0 and 120.\"\n",
" return True, age\n",
"\n",
"for test in [\"37\", \"-4\", \"200\", \"'; DROP TABLE users; --\"]:\n",
" print(f\"{test!r:28} -> {validate_age(test)}\")"
]
},
{
"cell_type": "markdown",
"id": "f6bc9f80",
"metadata": {},
"source": [
"Only the sensible value survives. Validation won't catch every attack on its own — you still use parameterized queries and escaping — but it's the first gate, and it turns away the crudest attacks before they get anywhere.\n",
"\n",
"*(A related attack, **cross-site request forgery** (CSRF), tricks a logged-in user's browser into sending a request they never intended — like a hidden button that transfers money using their active session. Full web frameworks including Flask ship with built-in CSRF protection you switch on; it's worth knowing the name exists.)*"
]
},
{
"cell_type": "markdown",
"id": "66d0a16a",
"metadata": {},
"source": [
"### ✏️ Your Turn — Van Helsing's Filter\n",
"\n",
"The clinic wants a validator for a patient's phone number: it must be **exactly 10 digits**, nothing else. Write `validate_phone(raw)` that returns `True` for `\"5075551234\"` and `False` for a number that's too short, has letters, or contains a `