Sometimes the ORM isn’t expressive enough and you need raw SQL. There are several APIs for doing this. In order of desirability:
- Raw queries
- The RawSQL annotation
- Using database cursors directly
- 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.
(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")