# PYTHON-DJANGO-SEC-005: Raw SQL Usage Audit via RawSQL Expression

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

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

## Description

This audit rule flags all usages of the RawSQL() expression in Django applications
regardless of whether tainted data is detected flowing into the SQL string. It is a
visibility rule designed to surface all raw SQL expressions for manual security review.

Django's RawSQL() allows embedding raw SQL fragments in ORM querysets via annotate(),
filter(), and order_by(). When used correctly with a static SQL string and all
dynamic values passed via the params tuple, it is safe. However, these usages are
frequently modified by developers who later add dynamic values without realizing they
must also update the params argument.

This rule uses pattern matching on DjangoExpressions.method("RawSQL") rather than
taint analysis, providing full coverage of all RawSQL() call sites in the codebase.
This makes it suitable for security audits and compliance reviews where a complete
inventory of raw SQL usage is required.


## Vulnerable Code

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

# SEC-005: Raw SQL usage (audit)
def audit_rawsql():
    expr = RawSQL("SELECT 1", [])
    return expr
```

## Secure Code

```python
from django.http import JsonResponse
from django.db.models.expressions import RawSQL
from myapp.models import Product

def get_products_with_rank(request):
    # SECURE: SQL string is a static literal, dynamic values use params tuple
    products = Product.objects.annotate(
        weighted_rank=RawSQL(
            "price * weight_factor + %s",
            (10.0,)  # Static constant in params is fine; user values must also go here
        )
    ).order_by('-weighted_rank').values('id', 'name', 'weighted_rank')
    return JsonResponse({'products': list(products)})

def get_products_safe(request):
    # BEST PRACTICE: Use ORM expressions to avoid raw SQL entirely
    from django.db.models import F, ExpressionWrapper, FloatField
    products = Product.objects.annotate(
        computed=ExpressionWrapper(
            F('price') * F('weight_factor'),
            output_field=FloatField()
        )
    ).values('id', 'name', 'computed')
    return JsonResponse({'products': list(products)})

```

## Detection Rule (Python SDK)

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

class DjangoExpressions(QueryType):
    fqns = ["django.db.models.expressions"]

# 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-005",
    name="Raw SQL Usage Detected (Audit)",
    severity="MEDIUM",
    category="django",
    cwe="CWE-89",
    tags="python,django,sql-injection,raw-sql,audit,CWE-89",
    message="Raw SQL usage detected. Ensure parameterized queries are used.",
    owasp="A03:2021",
)
def detect_django_raw_sql_audit():
    """Audit rule: detects any usage of raw SQL APIs."""
    return DjangoExpressions.method("RawSQL")
```

## How to Fix

- Treat every RawSQL() call site as requiring security review to confirm the SQL string is a static literal.
- Migrate RawSQL() usages to equivalent Django ORM expressions (F(), Value(), Case(), database functions) wherever possible to eliminate the audit surface.
- Document the reason for each RawSQL() call in a code comment explaining why ORM alternatives are insufficient.
- Enforce code review policy requiring security sign-off on new RawSQL() additions.
- Use PYTHON-DJANGO-SEC-004 (taint-based rule) in CI to catch actual injection flows, and this audit rule for periodic compliance reviews.

## Security Implications

- **Latent Injection Risk from Future Modifications:** Even currently-safe RawSQL() calls with static SQL strings represent a latent
risk. Developers who later modify these calls to include dynamic values may
inadvertently embed them in the SQL string instead of the params tuple, introducing
injection vulnerabilities that are not immediately obvious during code review.

- **Audit Coverage for Compliance:** Security audits, penetration testing reports, and compliance frameworks often
require a complete inventory of raw SQL usage. This rule provides that inventory
for RawSQL() specifically, complementing taint-based rules that only flag confirmed
injection flows.

- **Injection via Third-Party Code:** RawSQL() usages in shared utility functions or mixins may be called from multiple
views, some of which may pass user-controlled data as the SQL string. Auditing
all call sites identifies these shared functions that need careful review of all
their callers.

- **Database-Specific SQL in Annotations:** RawSQL() is often used for database-specific functions not available in Django's
ORM. These usages may be less scrutinized during code review because they are
seen as necessary technical debt, making them a common location for overlooked
injection vulnerabilities.


## FAQ

**Q: Why does this rule flag my RawSQL() call even though I'm using params correctly?**

This is an audit rule, not a taint-based injection rule. It flags all RawSQL()
usages regardless of whether they are safe. This is intentional: it creates an
inventory of all raw SQL usage for security review. Use PYTHON-DJANGO-SEC-004
for taint-based injection detection in CI. This rule is better suited for periodic
security audits and compliance reviews.


**Q: How do I suppress findings from this rule for verified-safe RawSQL() calls?**

Add a comment documenting why the RawSQL() usage is safe and configure a
suppression annotation. Alternatively, maintain an allowlist of approved RawSQL()
calls in your security documentation. The goal of this audit rule is visibility,
not zero findings -- all findings should be reviewed and disposition documented.


**Q: What is the difference between this rule and PYTHON-DJANGO-SEC-004?**

SEC-004 uses taint analysis to detect confirmed injection flows from request
parameters to RawSQL() SQL strings. SEC-005 uses pattern matching to flag all
RawSQL() usages regardless of data flow. SEC-004 has fewer false positives and
is appropriate for CI gates. SEC-005 has higher coverage and is appropriate for
security audits where completeness matters more than precision.


**Q: Can I configure this rule to only run during security audits, not on every PR?**

Yes. Configure two separate rule sets in your CI pipeline: one for PR checks
that includes only taint-based rules like SEC-004, and one for scheduled security
scans or release gates that includes audit rules like SEC-005. This gives you
fast PR feedback without noise while maintaining complete audit coverage.


**Q: What percentage of RawSQL() usages typically contain injection vulnerabilities?**

In practice, most RawSQL() usages that are flagged by this audit rule are safe
because they use the params argument correctly. The value of this rule is in
catching the minority that do not, and in ensuring all usages are documented and
reviewed. Teams maintaining large Django codebases often discover 5-15% of
RawSQL() calls lack proper parameterization when running this audit.


## References

- [CWE-89: SQL Injection](https://cwe.mitre.org/data/definitions/89.html)
- [Django RawSQL Expression Documentation](https://docs.djangoproject.com/en/stable/ref/models/expressions/#django.db.models.expressions.RawSQL)
- [OWASP SQL Injection Prevention Cheat Sheet](https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html)
- [Django Security - SQL Injection Protection](https://docs.djangoproject.com/en/stable/topics/security/#sql-injection-protection)
- [Django Database Expressions](https://docs.djangoproject.com/en/stable/ref/models/expressions/)
- [OWASP SQL Injection](https://owasp.org/www-community/attacks/SQL_Injection)

---

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