# PYTHON-DJANGO-SEC-006: Django Tainted SQL String Construction

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

- **Language:** Python
- **Category:** Django
- **URL:** https://codepathfinder.dev/registry/python/django/PYTHON-DJANGO-SEC-006
- **Detection:** `pathfinder scan --ruleset python/PYTHON-DJANGO-SEC-006 --project .`

## Description

This rule detects SQL injection vulnerabilities in Django applications where user
input from HTTP request parameters is used to construct SQL strings via f-strings,
string concatenation, or % formatting before being passed to a database execution
function.

This rule complements the specific sink rules (SEC-001 through SEC-004) by catching
the pattern where the SQL construction and execution are in separate functions or
files. A common pattern is a view that builds a query string from request parameters
and passes it to a database utility function which calls cursor.execute() or raw().
The taint flows through the string construction step and reaches the sink in a
different code location.

The rule specifically tracks the construction of SQL strings using user-controlled
data, making it effective at catching patterns like:
sql = f"SELECT * FROM orders WHERE user_id = {user_id}"
or:
sql = "SELECT * FROM users WHERE name = '" + username + "'"


## Vulnerable Code

```python
from django.db import connection
from django.db.models.expressions import RawSQL
from django.http import HttpRequest

# SEC-006: Tainted SQL string (same as SEC-001 pattern)
def vulnerable_tainted_sql(request):
    search = request.GET.get('q')
    query = "SELECT * FROM items WHERE name LIKE '%" + search + "%'"
    cursor = connection.cursor()
    cursor.execute(query)
    return cursor.fetchall()
```

## Secure Code

```python
from django.http import JsonResponse
from django.db import connection
from myapp.models import Order

def search_orders(request):
    user_id = request.GET.get('user_id', '')
    status = request.GET.get('status', '')
    try:
        user_id = int(user_id)
    except ValueError:
        return JsonResponse({'error': 'Invalid user_id'}, status=400)
    # SECURE: Build query with placeholders, pass values separately
    with connection.cursor() as cursor:
        cursor.execute(
            "SELECT id, total FROM orders WHERE user_id = %s AND status = %s",
            [user_id, status]
        )
        orders = cursor.fetchall()
    return JsonResponse({'orders': orders})

def dynamic_filter(request):
    status = request.GET.get('status', '')
    min_total = request.GET.get('min_total', '0')
    try:
        min_total = float(min_total)
    except ValueError:
        return JsonResponse({'error': 'Invalid total'}, status=400)
    # SECURE: Use Django ORM to build dynamic queries safely
    qs = Order.objects.all()
    if status:
        qs = qs.filter(status=status)
    if min_total > 0:
        qs = qs.filter(total__gte=min_total)
    return JsonResponse({'orders': list(qs.values('id', 'total', 'status'))})

```

## Detection Rule (Python SDK)

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

# Common Django request sources
_DJANGO_SOURCES = [
    calls("request.GET.get"),
    calls("request.POST.get"),
    calls("request.GET"),
    calls("request.POST"),
    calls("request.COOKIES.get"),
    calls("request.FILES.get"),
    calls("*.GET.get"),
    calls("*.POST.get"),
]


@python_rule(
    id="PYTHON-DJANGO-SEC-006",
    name="Tainted SQL String Construction",
    severity="HIGH",
    category="django",
    cwe="CWE-89",
    tags="python,django,sql-injection,string-format,OWASP-A03,CWE-89",
    message="User input used in SQL string construction. Use parameterized queries.",
    owasp="A03:2021",
)
def detect_django_tainted_sql_string():
    """Detects request data used in string formatting that reaches execute()."""
    return flows(
        from_sources=_DJANGO_SOURCES,
        to_sinks=[
            calls("cursor.execute"),
            calls("*.execute"),
        ],
        sanitized_by=[
            calls("escape"),
            calls("escape_string"),
            calls("escape_sql"),
            calls("int"),
        ],
        propagates_through=PropagationPresets.standard(),
        scope="global",
    )
```

## How to Fix

- Never use f-strings, % formatting, or string concatenation to embed user input into SQL strings; use parameterized query placeholders (%s) with a separate values list.
- Refactor dynamic SQL building patterns to use Django's ORM Q() objects, filter chaining, and annotate() for type-safe dynamic query construction.
- When raw SQL is unavoidable, build the SQL string as a static template with %s placeholders and keep all dynamic values in a separate list passed to execute().
- Perform code review specifically looking for SQL string construction that touches request parameters, even when the execution happens in a different function.
- Use Django Debug Toolbar in development to inspect generated SQL and verify parameterization is working correctly.

## Security Implications

- **Multi-Hop Injection via String Building:** The most dangerous injection patterns involve SQL strings built incrementally
across multiple function calls. User input may be appended to a query string in
one function, the partially-built string passed to another, and the final string
executed in a third. Taint analysis across these hops catches what per-function
review would miss.

- **F-String SQL Construction:** Python f-strings are concise and natural to write, making them a common pitfall
for SQL construction. A query like f"SELECT * FROM t WHERE id = {user_id}" looks
harmless but is a direct injection vector. Any value of user_id that contains
SQL metacharacters will alter the query structure.

- **Conditional SQL Building Patterns:** A common pattern is building WHERE clause conditions dynamically based on which
filters a user requests. Each condition appended with concatenation is a separate
injection point. This pattern requires careful use of Django's Q() objects or
parameterized query fragments instead.

- **Logging and Debugging SQL as a Secondary Path:** Developers often construct SQL strings for logging or debugging purposes without
intending to execute them, but these strings sometimes end up being executed in
error handling paths or when debug logging is disabled. This rule catches
construction regardless of the intended use.


## FAQ

**Q: How does this rule differ from SEC-001 through SEC-004?**

SEC-001 through SEC-004 focus on specific sink functions: cursor.execute(),
raw(), extra(), and RawSQL(). SEC-006 focuses on the construction of tainted
SQL strings regardless of which sink they eventually reach. It catches patterns
where the string building happens in a different file or function from the
execution, or where the tainted string flows through multiple intermediate
variables before reaching a sink.


**Q: Can this rule detect SQL injection in helper functions called from views?**

Yes. This is exactly the use case SEC-006 is designed for. If a Django view
calls request.GET.get('filter'), constructs an f-string SQL query, and passes
that string to a database utility function in a separate module, the rule follows
the taint chain across the function call and flags the construction-to-sink flow.


**Q: Does casting with int() prevent this rule from triggering?**

Yes. Explicit type conversion with int() or float() is recognized as a sanitizer
because a successful int() conversion guarantees the value is numeric and cannot
contain SQL metacharacters. If the conversion raises ValueError, the application
should handle that as invalid input. After a successful int() call, the taint is
cleared and subsequent SQL construction using that value will not be flagged.


**Q: What about dynamic query building where column names come from user input?**

Column names cannot be parameterized and int/float casting does not apply.
For user-controlled column or table names, use an allowlist: validate that the
input matches one of a predetermined set of permitted identifiers before including
it in the SQL string. Any other approach is unsafe regardless of how the resulting
string is executed.


**Q: Our ORM generates some raw SQL internally. Will this rule flag ORM-generated queries?**

No. The rule tracks data originating from Django request sources. ORM-generated
SQL strings are not tainted from the rule's perspective because they are not
constructed from user request data. Only strings that contain user-controlled
values as a result of f-string, concatenation, or % formatting operations will
be flagged.


**Q: How should I restructure code that builds SQL conditions conditionally?**

Replace conditional SQL string building with Django's Q() object pattern:
build a list of Q() objects based on the conditions that apply, then combine
them with the & and | operators and pass to filter(). This is safe, readable,
and testable. For complex cases involving database-specific functions, use
RawSQL() with the params argument for each dynamic value.


## 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)
- [Django Raw SQL Queries](https://docs.djangoproject.com/en/stable/topics/db/sql/)
- [Django Q Objects for Complex Queries](https://docs.djangoproject.com/en/stable/topics/db/queries/#complex-lookups-with-q-objects)
- [OWASP SQL Injection](https://owasp.org/www-community/attacks/SQL_Injection)
- [Django Security - SQL Injection Protection](https://docs.djangoproject.com/en/stable/topics/security/#sql-injection-protection)

---

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