Design-Level Defenses: SQL Injection, XSS, & CSRF

SQL Injection (SQLi) occurs when untrusted user input is directly concatenated into a dynamic SQL query string. An attacker injects SQL syntax characters (' OR 1=1 --, UNION SELECT) to alter the query logic and extract, mutate, or delete entire database tables.

Why String Sanitization and Regex Filtering Fail:

Junior engineers often attempt to sanitize input with regexes or blacklists (e.g., removing ' or DROP). Attackers easily bypass these with URL-encoding, hex representation, Unicode mutations, or SQL comment tricks (/**/).

The Architectural Permanent Fix: Parameterized Queries (Prepared Statements)

Prepared statements separate code structure from data input at the database wire protocol level:

1. Compilation Phase: The database compiles the SQL statement into an Abstract Syntax Tree (AST) before any user input is evaluated:

   sql
   Copy
   PREPARE find_user (text) AS SELECT id, email, role FROM users WHERE email = $1;

2. Execution Phase: The application transmits the user input separately as raw data parameters:

   sql
   Copy
   EXECUTE find_user ('alice@corp.com'' OR ''1''=''1');

3. The Security Guarantee: The database treats the entire input strictly as a literal string value for $1. It is mathematically impossible for the input to alter the AST syntax or execute arbitrary SQL commands.