SQL Parameter Escaper & AST Injection Armor
Concatenating unescaped user strings directly into SQL queries causes devastating SQL injection vulnerabilities. Production database libraries completely separate SQL code structure from data parameters using parameterized placeholders (? or %s), neutralizing malicious quotes and injected clauses.
# Dangerous:
# db.execute(f"SELECT * FROM users WHERE name='{user_input}'")
# Safe parameterized execution:
cursor.execute("SELECT * FROM users WHERE name = ?", (user_input,))
# SQL Parameter Escaper & AST Injection Armor Writing `query = f"SELECT * FROM users WHERE email = '{user_input}'"` is responsible for billions of leaked records across the history of the web. 🛑🔓 When an attacker enters `admin' OR '1'='1' --`, the raw string manipulates the database’s **Abstract Syntax Tree (AST)**. The query's grammar changes from a single user lookup to an unconditional boolean truth statement returning every account in the database. **Parameterized Queries** are the only true defense: 1. The database receives the query skeleton: `"SELECT * FROM users WHERE email = ?"`. 2. The database parses and compiles the execution plan. 3. The database receives the parameter tuple `("admin' OR '1'='1' --",)` as **pure literal data**. Even if the string contains quotes, semicolons, or `DROP TABLE`, the engine treats it purely as characters of an email address. ```python # The Safe Pattern: cursor.execute("SELECT * FROM users WHERE email = ?", (user_input,)) ``` ## Pop Quiz Why does parameterized query execution (`?` or `%s`) completely neutralize SQL injection payloads like `' OR '1'='1`? - [ ] The database engine compiles the SQL AST first; user parameters are transmitted separately as literal data values, never as executable syntax. - [ ] The database driver automatically wraps injected strings with base64 decoding instructions. - [ ] Parameterized queries disable administrative user permissions across all database tables. - [ ] Because modern database drivers convert SQL statements to MongoDB queries. ## Real-World Challenge Implement a query security validator `validate_parameterized_query(query: str, params: tuple) -> bool`. - Reject queries containing raw string formatting/concatenation markers (`f"..."`, `+`, `%s` without parameter bindings). - Ensure the count of `?` placeholders exactly equals `len(params)`. - Raise `ValueError` if the query is malformed or placeholder count mismatches. <details> <summary>Solution</summary> ```python import re def validate_parameterized_query(query: str, params: tuple) -> bool: if not isinstance(query, str) or not isinstance(params, tuple): raise TypeError("query must be str and params must be tuple") # Detect unparameterized interpolation anti-patterns in template if re.search(r"['\"][ ]*(\+|%|\.format)", query): raise ValueError("Unsafe SQL string concatenation detected in query template") # Count placeholders placeholder_count = query.count("?") if placeholder_count != len(params): raise ValueError( f"Parameter count mismatch: query defines {placeholder_count} placeholders, " f"but received {len(params)} parameters." ) return True # Verification # 1. Valid parameterized queries assert validate_parameterized_query( "SELECT * FROM accounts WHERE tenant_id = ? AND role = ?", (105, "billing_admin") ) is True # 2. Parameter count mismatch mismatch_caught = False try: validate_parameterized_query("SELECT * FROM users WHERE id = ?", (1, 2)) except ValueError: mismatch_caught = True assert mismatch_caught is True # 3. Concatenation anti-pattern concat_caught = False try: validate_parameterized_query("SELECT * FROM users WHERE name = ' ' + %s", ()) except ValueError: concat_caught = True assert concat_caught is True ``` </details>