Safe Raw SQL Queries

Introduction

Sometimes the ORM isn't enough—complex queries, performance optimization, or database-specific features require raw SQL. Here's how to write raw queries safely.

Key Concepts

Parameterized Queries: SQL with placeholders (%s) where values are passed separately.

Raw Manager: Model.objects.raw() for queries returning model instances.

Database Cursor: connection.cursor() for arbitrary queries.

Real World Context

Performance-critical views sometimes require raw SQL for complex joins, window functions, or database-specific features. A single f-string in a raw query can turn an optimized view into an injection vulnerability that automated scanners will find within hours of deployment.

Deep Dive

Using Model.objects.raw()

python
# DANGEROUS - never do this
User.objects.raw(f"SELECT * FROM users WHERE name = '{name}'")

# SAFE - parameterized
User.objects.raw("SELECT * FROM users WHERE name = %s", [name])

# Named parameters
User.objects.raw(
    "SELECT * FROM users WHERE status = %(status)s AND role = %(role)s",
    {'status': 'active', 'role': 'admin'}
)

Using Database Cursor

python
from django.db import connection

# SAFE
with connection.cursor() as cursor:
    cursor.execute(
        "SELECT COUNT(*) FROM articles WHERE category_id = %s",
        [category_id]
    )
    count = cursor.fetchone()[0]

# Multiple parameters
cursor.execute(
    "UPDATE users SET status = %s WHERE last_login < %s",
    ['inactive', cutoff_date]
)

Handling LIKE Patterns

python
# User input with LIKE - escape special chars
search = search_term.replace('%', '\\%').replace('_', '\\_')
cursor.execute(
    "SELECT * FROM articles WHERE title LIKE %s",
    [f'%{search}%']
)

Common Pitfalls

1. Using Python string formatting out of habit — f-strings and .format() are natural in Python but deadly in SQL; always use %s placeholders.
2. Forgetting to escape LIKE wildcards — User input containing % or _ in raw LIKE queries matches unintended rows; escape these characters manually.

Best Practices

1. Always use placeholders: Never f-strings or format().
2. Prefer ORM: Only use raw SQL when necessary.
3. Escape LIKE wildcards: % and _ have special meaning.

Summary

Raw SQL is safe when using parameterized queries with %s placeholders. Never use string interpolation, always pass values as a separate list, and escape LIKE wildcards in search terms.