SQL Injection & Prevention

A major security vulnerability in web services is the confusion between execution instructions (code) and user input (data). When a backend service executes an operation by concatenating user strings directly into a database query string, it violates the code-data separation boundary.

This vulnerability is called SQL Injection (SQLi).

If user inputs are appended directly to SQL queries, an attacker can input characters (such as single quotes ' or comments --) that alter the structure of the SQL parser's syntax tree. This allows attackers to bypass authentication filters, retrieve confidential records, modify database tables, or execute remote system commands.

To understand how SQLi works, consider a backend authentication routine:

SELECT * FROM users WHERE username = 'USER_INPUT' AND password = 'PASSWORD_INPUT';

If an attacker inputs the string ' OR '1'='1 as the username, the database engine parses the combined string:

SELECT * FROM users WHERE username = '' OR '1'='1' AND password = 'PASSWORD_INPUT';

Because the OR '1'='1' condition is always true, the query parser evaluates the statement as true for every row in the table, bypassing the password validation check and returning all database profiles.

SQL injection exploits fall into three main categories based on how the attacker extracts data:

1. In-band SQLi (Classic)

The attacker uses the same channel to launch the attack and collect the results.

• Union-based SQLi: The attacker appends a UNION statement to the original query to merge results from another table (e.g. retrieving passwords from a users table via a public products search endpoint).
• Error-based SQLi: The attacker inputs strings that trigger deliberate database syntax errors, forcing the engine to display details (like column names or software versions) in the error logs returned to the user.

2. Blind SQLi

The database does not return results or errors on the screen, but the attacker can infer data by asking the database yes-or-no questions:

• Boolean-based Blind SQLi: The attacker injects queries that return different web page layouts depending on whether a condition evaluates to true or false.
• Time-based Blind SQLi: The attacker injects queries containing sleep functions (such as PG_SLEEP(5)). If the server takes five seconds longer to respond, the attacker confirms the condition is true.

3. Out-of-band SQLi

The attacker triggers database operations that transmit data over another protocol (such as DNS requests or HTTP queries) to a server they control.

The primary defense against SQL Injection is using Prepared Statements (also called Parameterized Queries):

Separating Syntax from Value

Prepared statements enforce a strict boundary between execution syntax and parameters. When a database prepares a query, it compiles the SQL template syntax tree before evaluating the input variables:

1. COM_PREPARE: The backend application sends a template query containing placeholders (like ? or $1) to the database (e.g. SELECT * FROM users WHERE username = ?). The database compiles this SQL statement, builds the syntax tree, and optimizes the execution plan.
2. COM_EXECUTE: The application sends only the literal parameter values (e.g. ' OR '1'='1) to the compiled template. The database inserts these values directly into the syntax leaf nodes as literal values. Because the query syntax structure is already fixed, the injection payload cannot modify the execution tree, neutralizing the attack.

While prepared statements prevent SQLi, backend systems should apply additional security layers to minimize risks:

• Strict Input Validation: Use type casting (e.g. casting incoming parameters to integers) and regular expression whitelists to validate fields before passing them to the database.
• Principle of Least Privilege: Run database connections using isolated accounts with restricted permissions. A web application account should only have SELECT, INSERT, and UPDATE permissions on specific tables, and should be blocked from calling administrative actions like DROP TABLE or accessing system configuration tables.
• Web Application Firewalls (WAF): Deploy a WAF at network boundaries to analyze incoming payloads and block requests containing SQL injection patterns before they reach the web server.

• OWASP Injection Prevention Cheat Sheet — Best practices for securing web applications against SQL injection across different programming frameworks.
• Advanced SQL Injection in SQL Server Applications — Chris Anley's whitepaper detailing database exploit structures and vulnerability paths.
• Web Security Academy: SQL Injection — PortSwigger's interactive labs and tutorials for testing union-based and blind SQL injection vulnerabilities.