# PYTHON-LANG-SEC-084: Formatted SQL Query Passed to cursor.execute()

> **Severity:** HIGH | **CWE:** CWE-89 | **OWASP:** A03:2021

- **Language:** Python
- **Category:** Python Core
- **URL:** https://codepathfinder.dev/registry/python/lang/PYTHON-LANG-SEC-084
- **Detection:** `pathfinder scan --ruleset python/PYTHON-LANG-SEC-084 --project .`

## Description

This rule detects the general pattern of SQL queries constructed using string formatting
operations (f-strings, % operator, .format(), or string concatenation with +) and
passed to any database cursor.execute() method, regardless of the specific database
driver being used.

This covers drivers not addressed by more specific rules, including sqlite3, MySQL
Connector, PyMySQL, cx_Oracle, and any custom database abstraction layer that exposes
a cursor.execute() interface.

String-formatted SQL is the root cause of SQL injection vulnerabilities across all
database drivers. The pattern is always the same: untrusted data embedded in the SQL
string before it reaches the database engine, where the database interprets the
embedded data as SQL syntax.


## Vulnerable Code

```python
import sqlite3

# SEC-084: formatted SQL (general)
conn2 = sqlite3.connect("test.db")
cursor = conn2.cursor()
cursor.execute("SELECT * FROM products WHERE id = " + product_id)
```

## Secure Code

```python
import sqlite3
import mysql.connector

# INSECURE: String formatting in SQL
# cursor.execute(f"SELECT * FROM users WHERE email = '{email}'")
# cursor.execute("SELECT * FROM orders WHERE id = " + str(order_id))

# SECURE: sqlite3 - use ? placeholders
def get_sqlite_user(conn: sqlite3.Connection, username: str) -> dict:
    cursor = conn.execute(
        "SELECT id, username, email FROM users WHERE username = ?",
        (username,)
    )
    row = cursor.fetchone()
    return {"id": row[0], "username": row[1]} if row else {}

# SECURE: MySQL Connector - use %s placeholders
def get_mysql_user(conn: mysql.connector.MySQLConnection, user_id: int) -> dict:
    cursor = conn.cursor(dictionary=True)
    cursor.execute(
        "SELECT id, username, email FROM users WHERE id = %s",
        (user_id,)
    )
    return cursor.fetchone() or {}

# SECURE: SQLAlchemy text() with bindparams
from sqlalchemy import text

def search_records(session, category: str, limit: int) -> list:
    result = session.execute(
        text("SELECT id, title FROM records WHERE category = :cat LIMIT :lim"),
        {"cat": category, "lim": limit}
    )
    return result.fetchall()

```

## Detection Rule (Python SDK)

```python
from rules.python_decorators import python_rule
from codepathfinder import calls, flows, QueryType
from codepathfinder.presets import PropagationPresets


@python_rule(
    id="PYTHON-LANG-SEC-084",
    name="Formatted SQL Query",
    severity="HIGH",
    category="lang",
    cwe="CWE-89",
    tags="python,sql-injection,formatted-query,CWE-89",
    message="SQL query built with string formatting detected. Use parameterized queries instead.",
    owasp="A03:2021",
)
def detect_formatted_sql():
    """Detects cursor.execute() calls (audit for string formatting in SQL)."""
    return calls("cursor.execute", "cursor.executemany",
                 "connection.execute", "conn.execute")
```

## How to Fix

- Use the placeholder syntax appropriate to your database driver: ? for sqlite3, %s for psycopg2/MySQL/pg8000, $1/$2 for asyncpg, :name for SQLAlchemy text().
- Never construct SQL strings using f-strings, % operator, .format(), or string concatenation with user-controlled values.
- For ORMs, use the ORM's query building API rather than raw SQL; fall back to text() with explicit bindparams when raw SQL is needed.
- Validate and restrict dynamic SQL elements (ORDER BY directions, column names) to allowlists when they cannot be parameterized.
- Enable database-level audit logging to detect and alert on unusual query patterns that may indicate exploitation attempts.

## Security Implications

- **Universal SQL Injection Risk:** SQL injection via string-formatted queries is the most widespread database
vulnerability class. Every database engine (PostgreSQL, MySQL, SQLite, Oracle,
MSSQL) is affected when query strings are constructed from user input rather
than using parameterized queries.

- **SQLite Local Database Exposure:** Python applications using sqlite3 with string-formatted queries are vulnerable.
Local SQLite databases may contain sensitive application state, user data, and
credentials that can be exfiltrated or corrupted via SQL injection.

- **ORM Bypass via Raw SQL:** Applications using ORMs often include escape hatches for raw SQL (Django's
connection.cursor().execute(), SQLAlchemy's text()). String-formatted raw SQL
in these contexts bypasses the ORM's parameterization protections.

- **Blind SQL Injection via Timing:** Even when query results are not directly returned to the user (e.g., in background
workers or event processors), time-based blind SQL injection using SLEEP() or
pg_sleep() allows an attacker to extract data one bit at a time.


## FAQ

**Q: What placeholder syntax should I use for my specific database driver?**

sqlite3: ? (question mark). psycopg2, aiopg, pg8000, MySQL Connector, PyMySQL: %s.
asyncpg: $1, $2, $3 (numbered). cx_Oracle: :name (named) or :1, :2 (positional).
SQLAlchemy text(): :param_name. ODBC drivers (pyodbc): ? (question mark). Always
consult your driver's documentation; the concept is the same but syntax varies.


**Q: Is string formatting ever acceptable in SQL strings?**

String formatting is acceptable for static, developer-controlled values that are
not user-influenced. For example, table names or column names from a hardcoded
allowlist. However, once any user-controlled value could influence the SQL string,
even indirectly, parameterization is required for that value.


**Q: Does this rule catch SQL injection in raw queries via SQLAlchemy?**

Yes. SQLAlchemy's text() function accepts raw SQL strings. If the string passed to
text() is formatted with user input instead of using SQLAlchemy's bindparam() or
the :name syntax, it is flagged. SQLAlchemy's ORM query methods are safe by default
and are not flagged.


**Q: What about stored procedures — can they be vulnerable to SQL injection?**

Calling stored procedures with parameterized arguments is safe. However, if a stored
procedure uses EXEC sp_executesql or dynamic SQL internally with concatenated
parameters, the injection risk exists inside the stored procedure. Audit stored
procedure code for dynamic SQL with parameter concatenation.


**Q: How do I handle LIKE queries with parameterization?**

For LIKE queries, include the wildcard characters in the Python string before
parameterization: cursor.execute("SELECT * FROM t WHERE name LIKE %s", (f"%{user_input}%",)).
Do not include % in the SQL template. The database driver handles escaping of the
user input including any % or _ characters that would be interpreted as LIKE wildcards.


**Q: Is there a way to automatically detect all SQL string formatting issues?**

Code Pathfinder's taint analysis tracks user input from all sources through string
operations to cursor.execute() calls. Combined with the database-specific rules
(PYTHON-LANG-SEC-080 through 083), this rule provides broad coverage of SQL
injection patterns across all Python database drivers.


## References

- [CWE-89: SQL Injection](https://cwe.mitre.org/data/definitions/89.html)
- [OWASP SQL Injection Prevention Cheat Sheet](https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html)
- [Python DB-API 2.0 Specification (PEP 249)](https://peps.python.org/pep-0249/)
- [OWASP Top 10 A03:2021 Injection](https://owasp.org/Top10/A03_2021-Injection/)
- [SQLAlchemy: Using textual SQL](https://docs.sqlalchemy.org/en/14/core/sqlelement.html#sqlalchemy.sql.expression.text)

---

Source: https://codepathfinder.dev/registry/python/lang/PYTHON-LANG-SEC-084
Code Pathfinder — Open source, type-aware SAST with cross-file dataflow analysis