>
Software Security Training

Exercise: The Shattered Database

In this activity, you will prevent SQL Injection (SQLi) by using Parameterized Queries.

Scenario: A hacker enters this username: ' OR '1'='1.
The insecure code is commented out. Rewrite it securely!

How to use cursor.execute()

❌ BAD (Unsafe):
Do not use string formatting (f-strings or +).
cursor.execute(f"SELECT ... '{user}'")


✅ GOOD (Secure):
Pass variables as a tuple in the 2nd argument.
sql = "SELECT ... WHERE name = ?"
cursor.execute(sql, (user,))

Note: If you have two variables, use (var1, var2).

Your Task

Modify the `login_user` function:

  1. Use Placeholders: Use `?` in the query string instead of variables.
  2. Pass Parameters: Pass `(username, password)` as a tuple to `cursor.execute`.
PYTHON EDITOR (db_module.py) Engine: Initializing...
1
DATABASE LOGS
Ready... Waiting for login simulation.
from js import window import sqlite3 window.python_is_ready = True try: document = window.document status = document.getElementById("engine-status") status.innerHTML = "Engine: Python Active" except: pass def python_test_logic(user_code): results = [] def add_log(msg, type="normal"): results.append({"msg": msg, "type": type}) add_log("[SYSTEM] Initializing In-Memory SQLite DB...", "system") # Setup Mock DB conn = sqlite3.connect(":memory:") cursor = conn.cursor() cursor.execute("CREATE TABLE users (id INTEGER, username TEXT, password TEXT)") cursor.execute("INSERT INTO users VALUES (1, 'admin', 'supersecret')") cursor.execute("INSERT INTO users VALUES (2, 'guest', 'guest123')") conn.commit() test_env = {'sqlite3': sqlite3} try: exec(user_code, test_env) if "login_user" not in test_env: add_log("[!] Error: Function 'login_user' not found.", "fail") return results, "FAILED" login_user = test_env["login_user"] # --- TEST 1: ATTACK SIMULATION --- add_log("--- TEST 1: SQL Injection Attack ---", "system") attack_input = "' OR '1'='1" attack_success = False try: user = login_user(cursor, attack_input, "anything") if user and user[1] == 'admin': attack_success = True except Exception as e: pass if attack_success: add_log(" -> FAIL: ADMIN ACCOUNT COMPROMISED!", "fail") return results, "FAILED - Hacked" else: add_log(" -> PASS: Injection failed (Admin not returned).", "pass") # --- TEST 2: FUNCTIONALITY CHECK --- add_log("--- TEST 2: Valid Login Check ---", "system") try: valid_user = login_user(cursor, 'admin', 'supersecret') if valid_user and valid_user[1] == 'admin': add_log(" -> PASS: Valid login works.", "pass") else: add_log(" -> FAIL: Valid login returned None.", "fail") return results, "FAILED - Logic Error" except Exception as e: add_log(f" -> FAIL: Runtime Error: {e}", "fail") return results, "FAILED - Error" # --- TEST 3: STRICT SYNTAX CHECK --- add_log("--- TEST 3: Syntax Inspection ---", "system") clean_code = "\n".join([line for line in user_code.split("\n") if not line.strip().startswith("#")]) if "SELECT" not in clean_code or "FROM" not in clean_code: add_log(" -> FAIL: Query logic missing (SELECT/FROM).", "fail") return results, "FAILED" if "cursor.execute" in clean_code: if "?" in clean_code and (", (" in clean_code or ",(" in clean_code): add_log(" -> PASS: Parameterized query syntax detected.", "pass") add_log(" SUCCESS: SQL Injection mitigated.", "pass") return results, "PASSED - Secure" elif "%s" in clean_code or "f\"" in clean_code or "format(" in clean_code: add_log(" -> FAIL: String formatting detected.", "fail") return results, "FAILED" else: add_log(" -> FAIL: Could not verify secure syntax.", "fail") return results, "FAILED" except Exception as e: add_log(f"[!] Runtime Error: {e}", "fail") return results, "ERROR" return results, "UNKNOWN" window.python_test_runner = python_test_logic