1# A03: SQL Injection — vulnerable vs safe (Python / SQLAlchemy)
2 
3# ❌ VULNERABLE — string interpolation in SQL
VULNERABLE — f-string or + concatenation in SQL is always wrong
4def get_user_by_name(username: str):
5    query = f"SELECT * FROM users WHERE username = '{username}'"
6    # Attacker sends: username = "admin' OR '1'='1"
7    # Query becomes: SELECT * FROM users WHERE username = 'admin' OR '1'='1'
8    # Returns all users!
9    return db.execute(query).fetchall()
10 
11# ❌ ALSO VULNERABLE — string formatting
12def search_products(category: str):
13    return db.execute(
14        "SELECT * FROM products WHERE category = '" + category + "'"
15    ).fetchall()
16 
17# ✅ SAFE — parameterised query (positional placeholder)
SAFE — ? placeholder; driver handles escaping
18def get_user_by_name_safe(username: str):
19    return db.execute(
20        "SELECT * FROM users WHERE username = ?",
21        (username,)   # Driver escapes the value — injection impossible
22    ).fetchall()
ORM uses parameterised queries automatically
23 
24# ✅ SAFE — ORM with parameterised queries under the hood
25def get_user_by_name_orm(username: str):
26    return User.query.filter_by(username=username).first()
27 
28# ✅ SAFE — SQLAlchemy text() with bound parameters
29from sqlalchemy import text
30def search_safe(category: str, min_price: float):
31    return db.execute(
32        text("SELECT * FROM products WHERE category = :cat AND price >= :price"),
33        {"cat": category, "price": min_price}
34    ).fetchall()
35 
36# BONUS: stored procedure input validation
37import re
38def validate_username(username: str) -> bool:
39    return bool(re.match(r'^[a-zA-Z0-9_]{3,50}$', username))