SQL Injection Anti-Pattern
Severity: Critical
Summary
Attackers execute arbitrary SQL commands by manipulating user input. String concatenation in queries (frequently AI-generated from insecure training data) enables database compromise, data exfiltration, authentication bypass, and remote code execution.
The Anti-Pattern
The anti-pattern is concatenating user data into SQL statements, allowing attackers to break query structure and inject malicious SQL.
BAD Code Example
python
# VULNERABLE: String concatenation creates injection vector.
import sqlite3
def get_user(db_connection, username):
# User input concatenated directly into query.
query = "SELECT * FROM users WHERE username = '" + username + "'"
cursor = db_connection.cursor()
cursor.execute(query)
return cursor.fetchone()
# Attack: username = "admin' OR '1'='1' --"
# Result: "SELECT * FROM users WHERE username = 'admin' OR '1'='1' --'"
# Returns all users, bypassing authentication.
GOOD Code Example
python
# SECURE: Parameterized queries prevent injection.
import sqlite3
def get_user(db_connection, username):
# Parameters sent separately and escaped by database driver.
# Malicious input cannot alter query logic.
query = "SELECT * FROM users WHERE username = ?"
cursor = db_connection.cursor()
cursor.execute(query, (username,))
return cursor.fetchone()
# Named parameters (preferred for clarity):
# query = "SELECT * FROM users WHERE username = :username"
# cursor.execute(query, {"username": username})
Detection
- •Look for string concatenation (
+,||,concat(), f-strings, template literals) used to build SQL queries. - •Search for calls to
execute(),query(), orraw()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
- • Use parameterized queries: Always use prepared statements for all database operations.
- • Never concatenate user input: Avoid direct string concatenation in SQL.
- • Use ORM libraries: Tools with built-in SQL injection protection (SQLAlchemy, Django ORM, Hibernate).
- • Apply least privilege: Database accounts should have minimal necessary permissions.
- • Validate input as defense-in-depth: Not primary defense, but supplements parameterization.
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.