Django check: Avoid raw SQL queries

This check discourages usage of raw SQL queries, which are subject to SQL injection.

Description

Sometimes the ORM isn’t expressive enough and you need raw SQL. There are several APIs for doing this. In order of desirability:

  1. Raw queries
  2. The RawSQL annotation
  3. Using database cursors directly
  4. Avoid Queryset.extra (don’t use .extra, it has serious safety concerns, and should be considered deprecated).

Avoid if possible

You can usually use a database view, materialized view, or stored procedure to get the same effect with better injection protection.

Always use (query, params) even if params is empty (e.g., raw(query, [])). This pattern reminds you to add params to the params list, rather than the query.

The query itself should always be a static string, rather than constructed using string formatting, concatenation, etc.

If you find the ORM is often not expressive enough, consider replacing Django’s ORM with SQLAlchemy, which is a more powerful and expressive ORM. You’ll lose out on conveniences like the admin, model forms, and model-based generic views, but will gain a more powerful and expressive API.

This check will alert on these cases, for example:

# SHOULD MATCH
# String raw query parameter
query = 'SELECT * FROM myapp_person WHERE last_name = %s' % lname
Person.objects.raw(query)

# Using queryset.annotate() with a raw SQL query
from django.db.models.expressions import RawSQL
queryset.annotate(val=RawSQL("select col from sometable where othercol = %s", (someparam,)))

This check will not alert on these cases:

# The raw() is called without a parameter
Person.objects.raw()

# RawSQL is overriden / shadowed
def RawSQL(foo: str): pass
RawSQL("testing")

References

  1. RawSQL queries
  2. RawSQL annotation
  3. Using database cursors directly
  4. Stored procedures
  5. Using views