Explain SQL Injection (SQLi) vulnerabilities and defense mechanisms (Prepared Statements vs ORM)

SQL Injection (SQLi) is a critical web security vulnerability (OWASP Top 10) where an attacker interferes with database queries by injecting malicious SQL fragments through user input fields.

The Vulnerability Mechanism

When user input is directly concatenated into SQL strings, the database interpreter cannot distinguish between the query structure and user data:

// VULNERABLE CODE:
const query = "SELECT * FROM users WHERE email = '" + userInput + "'";
// If userInput = "[email protected]' OR '1'='1"
// Executed SQL: SELECT * FROM users WHERE email = '[email protected]' OR '1'='1'
// Bypasses authentication completely!

Primary Defense: Parameterized Queries (Prepared Statements)

Prepared statements pre-compile the SQL statement structure on the database engine. User input is supplied separately as parameters and treated strictly as literal data values, never as executable code:

// SECURE PARAMETERIZED QUERY (better-sqlite3 / pg):
const stmt = db.prepare('SELECT * FROM users WHERE email = ? AND status = ?');
const user = stmt.get(userInput, 1);

Defense-in-Depth Measures

1. Principle of Least Privilege: Web application database credentials should have minimal privileges (e.g., cannot drop tables or execute administrative procedures).
2. Input Validation & Allow-listing: Validate formats using strict regex for expected inputs (e.g., email syntax, alphanumeric IDs).
3. Web Application Firewall (WAF): Inspects HTTP requests for SQLi patte s at the network perimeter.