SQL Injection Anti-Pattern

Severity: Critical

Summary

SQL injection is a critical vulnerability that allows attackers to execute arbitrary SQL commands by manipulating user input. This anti-pattern focuses on the common mistake of building SQL queries using string concatenation, which is frequently generated by AI models trained on insecure code. The risk includes complete database compromise, data exfiltration, authentication bypass, and even remote code execution.

The Anti-Pattern

The core of the SQL injection anti-pattern is the direct concatenation of user-provided data into SQL statements. This allows an attacker to break out of the intended query structure and inject their own malicious SQL.

BAD Code Example

# VULNERABLE: String concatenation in SQL queries
import sqlite3

def get_user(db_connection, username):
    # User input is directly concatenated into the query, creating an injection vector.
    query = "SELECT * FROM users WHERE username = '" + username + "'"
    cursor = db_connection.cursor()
    cursor.execute(query)
    return cursor.fetchone()

# Example of a successful attack:
# username = "admin' OR '1'='1' --"
# Resulting query: "SELECT * FROM users WHERE username = 'admin' OR '1'='1' --'"
# This query would return all users, bypassing authentication.

GOOD Code Example

# SECURE: Parameterized queries (prepared statements)
import sqlite3

def get_user(db_connection, username):
    # Parameters are sent separately from the query and escaped by the database driver,
    # preventing any malicious input from altering the query's logic.
    query = "SELECT * FROM users WHERE username = ?"
    cursor = db_connection.cursor()
    cursor.execute(query, (username,))
    return cursor.fetchone()

# With named parameters (preferred for clarity)
# def get_user_named(db_connection, username):
#     query = "SELECT * FROM users WHERE username = :username"
#     cursor = db_connection.cursor()
#     cursor.execute(query, {"username": username})
#     return cursor.fetchone()

Detection

• Look for string concatenation (+, ||, concat(), f-strings, template literals) used to build SQL queries.
• Search for calls to execute(), query(), or raw() that take a single string variable which may contain user input.
• Check for the use of .format(), %s, or ${} within SQL query strings.
• Review any code that dynamically constructs SQL based on user input without proper parameterization.

Prevention

• [ ] Always use parameterized queries or prepared statements for all database operations.
• [ ] Never concatenate user input directly into SQL strings.
• [ ] Use an Object-Relational Mapping (ORM) library with built-in protection against SQL injection.
• [ ] Apply the Principle of Least Privilege to database accounts, ensuring they only have the permissions necessary for their tasks.
• [ ] Validate and sanitize all user input as a defense-in-depth measure, although this should not be the primary defense against SQL injection.

Related Security Patterns & Anti-Patterns

• Command Injection Anti-Pattern: Similar injection vulnerability but for shell commands.
• LDAP Injection Anti-Pattern: Injection vulnerability in LDAP queries.
• XPath Injection Anti-Pattern: Injection vulnerability in XML queries.
• Missing Input Validation Anti-Pattern: A root cause that enables many injection attacks.

References

• OWASP Top 10 A05:2025 - Injection
• OWASP GenAI LLM01:2025 - Prompt Injection
• OWASP API Security API8:2023 - Security Misconfiguration
• OWASP SQL Injection Prevention Cheat Sheet
• CWE-89: SQL Injection
• CAPEC-66: SQL Injection
• PortSwigger: Sql Injection
• Source: sec-context