Rule Information
Tags
pythonpsycopg2sql-injectionpostgresqldatabaseCWE-89OWASP-A03
CWE References
Interactive Playground
Experiment with the vulnerable code and security rule below. Edit the code to see how the rule detects different vulnerability patterns.
pathfinder scan --ruleset python/PYTHON-LANG-SEC-080 --project .1
2
3
4
5
6
7
8
rule.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
About This Rule
Understanding the vulnerability and how it is detected
psycopg2 is the most widely used PostgreSQL adapter for Python. SQL injection occurs when user-supplied data is concatenated or formatted into the SQL query string instead of being passed as separate parameters. When the query is constructed with string formatting (% operator, .format(), or f-strings), an attacker can embed SQL syntax characters in their input to modify the query structure.
The correct approach with psycopg2 is to use parameterized queries: pass the SQL template as a literal string with %s placeholders and user data as a separate tuple argument to cursor.execute(). psycopg2 handles proper escaping and quoting of parameters at the database protocol level.
This rule detects patterns where SQL strings built with string formatting are passed to psycopg2's cursor.execute() or cursor.executemany() methods.
Security Implications
Potential attack scenarios if this vulnerability is exploited
1
Database Records Exfiltration
UNION SELECT injection allows an attacker to retrieve data from any table in the PostgreSQL database. This includes user credentials, session tokens, private data, and application secrets stored in the database.
2
Authentication Bypass
Login queries built with string concatenation can be bypassed with ' OR '1'='1 payloads or similar techniques, granting access without valid credentials.
3
Data Manipulation and Destruction
Stacked queries or subquery injection can issue INSERT, UPDATE, or DELETE statements, allowing attackers to alter data, create backdoor accounts, or wipe database tables.
4
PostgreSQL Server Escalation
PostgreSQL's COPY TO/FROM PROGRAM (superuser), pg_read_file(), and pg_read_binary_file() can read server filesystem files. On misconfigured servers, SQL injection can escalate to OS command execution.
How to Fix
Recommended remediation steps
- 1Always use parameterized queries with %s placeholders and pass user data as the second argument tuple to cursor.execute().
- 2Never construct SQL strings with % operator, .format(), or f-strings when user-supplied values are involved.
- 3Use psycopg2.sql module (psycopg2.sql.SQL, psycopg2.sql.Identifier) for dynamic column names, table names, or ORDER BY directions that cannot be parameterized.
- 4Grant the PostgreSQL database user the minimum required privileges to prevent SQL injection from accessing unintended schemas or escalating to system functions.
- 5Enable PostgreSQL's pg_audit extension to log all SQL statements for security monitoring.
Detection Scope
How Code Pathfinder analyzes your code for this vulnerability
This rule performs taint analysis tracing user-controlled data through string formatting operations to psycopg2 cursor.execute() and cursor.executemany() call sites. Sources include HTTP request parameters, os.environ, file reads, and other external inputs. The sink is the first argument (position 0) of cursor.execute().
Compliance & Standards
Industry frameworks and regulations that require detection of this vulnerability
CWE Top 25
CWE-89 ranked #3 in 2023 Most Dangerous Software Weaknesses
OWASP Top 10
A03:2021 - Injection
PCI DSS v4.0
Requirement 6.2.4 - Protect web-facing applications against SQL injection
NIST SP 800-53
SI-10: Information Input Validation
References
External resources and documentation
Similar Rules
Explore related security rules for Python
Frequently Asked Questions
Common questions about psycopg2 SQL Injection via String Formatting
Use %s as a placeholder in the SQL template string (regardless of the actual data
type — psycopg2 handles type conversion). Pass user values as a tuple (or list)
as the second argument to cursor.execute(): cursor.execute("SELECT * FROM t WHERE id = %s", (user_id,)).
Never use Python's % string formatting operator on the SQL string itself.
Column names and table names cannot be parameterized with %s — they are SQL identifiers,
not values. Use psycopg2.sql.Identifier for safe identifier quoting:
from psycopg2 import sql; cursor.execute(sql.SQL("SELECT {} FROM t").format(sql.Identifier(col_name))).
Additionally, validate identifiers against an allowlist of known-valid names.
Yes, and it is required. Always use %s for all parameterized values including integers.
psycopg2 casts the Python value to the appropriate PostgreSQL type. Using string
interpolation for integers (f"WHERE id = {user_id}") is still vulnerable if the
variable could contain a non-integer value due to type coercion or logic bugs.
Yes. Code Pathfinder's inter-procedural taint analysis traces data flow from HTTP
request parameters through variable assignments and function calls to cursor.execute().
It catches cases where the SQL string is built in one function and executed in another.
cursor.mogrify() formats a query string with parameters using psycopg2's safe escaping,
returning the formatted bytes. The result is safe to use in cursor.execute() directly.
However, using mogrify() to build SQL strings for manual concatenation is still
dangerous. Use cursor.execute(sql, params) directly instead of mogrify().
psycopg3 uses the same parameterization approach but with different syntax: %s for
positional parameters (as in psycopg2) or %(name)s for named parameters. The same
rule applies: use parameterized queries with separate parameter tuples, never string
formatting in SQL strings.
New feature
Get these findings posted directly on your GitHub pull requests
The psycopg2 SQL Injection via String Formatting rule runs in CI and posts inline review comments on the exact lines — no dashboard, no SARIF viewer.