SQL Injection: Prepared Statement Bypass

TL;DR

Prepared statements are a strong defence against SQL injection, but they aren’t foolproof. This guide explains how attackers can sometimes bypass them and what you can do to prevent it.

1. Understanding Prepared Statements

Prepared statements separate the SQL code from the data. Instead of directly embedding user input into your queries, placeholders are used. The database then treats the input as *data* rather than executable code. This prevents malicious SQL commands from being injected.

2. Common Bypass Techniques

1. Concatenation/Escaping Issues: If you concatenate strings before passing them to a prepared statement, or if your escaping isn’t correct, it can be bypassed.
2. Incorrect Parameterisation: Using parameters incorrectly (e.g., passing multiple values into a single parameter placeholder) can lead to injection vulnerabilities.
3. Database-Specific Features: Some databases have features that allow for code execution even within prepared statements, if not handled carefully.
4. Object Injection/Type Confusion: In some languages and database drivers, it’s possible to inject objects or different data types into parameters that are then interpreted as SQL commands.

3. Example Bypass Scenario (PHP)

Let’s look at a PHP example where concatenation bypasses the prepared statement.

Code Vulnerability

prepare($query);
    $stmt->execute();
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

In this case, even though a prepared statement is used, the concatenation of '$username' directly into the query string allows for SQL injection. An attacker could input something like ' OR '1'='1 to bypass authentication.

Corrected Code

prepare("SELECT * FROM users WHERE username = :username AND password = 'password'");
    $stmt->bindParam(':username', $username);
    $stmt->execute();
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

This corrected version uses a named placeholder :username and binds the user input to it, preventing injection.

4. Prevention Steps

1. Always Use Parameterised Queries: Never concatenate strings directly into your SQL queries.
2. Use Appropriate Data Types: Ensure that you are passing data of the correct type to your parameters. Avoid implicit type conversions.
3. Input Validation and Sanitisation: Validate user input on both the client-side *and* server-side. Sanitize input to remove potentially harmful characters, but don’t rely on sanitisation as a primary defence.
4. Least Privilege Principle: Grant database users only the minimum necessary permissions.
5. Database-Specific Security Features: Understand and utilise any security features offered by your specific database system (e.g., stored procedures, input validation functions).
6. Regular Code Reviews: Have your code reviewed by another developer to identify potential vulnerabilities.
7. Web Application Firewalls (WAFs): Implement a WAF to detect and block common SQL injection attacks.

5. Testing for Prepared Statement Bypass

Testing is crucial.

• Manual Injection Attempts: Try injecting various payloads into your input fields (e.g., ' OR '1'='1, '; DROP TABLE users; --).
• Automated Scanning Tools: Use SQL injection scanning tools to identify potential vulnerabilities.
• Fuzzing: Provide a large amount of random data as input to see if it causes unexpected behaviour or errors.