[Django] #27491: TruncDay function arguments aren't quoted properly when you print the SQL query

13 views

Skip to first unread message

Django

unread,

Nov 15, 2016, 4:37:41 PM11/15/16

to django-...@googlegroups.com

#27491: TruncDay function arguments aren't quoted properly when you print the SQL
query
-------------------------------------+-------------------------------------
Reporter: Nick | Owner: nobody
Retallack |
Type: | Status: new
Uncategorized |
Component: Database | Version: 1.10
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Here's how I discovered it.

Create a model:

{{{
class Thing(models.Model):
timestamp = models.DateTimeField(auto_now_add=True)
}}}

Now lets query it:

{{{
$ ./manage.py shell
>>> from django.db.models.functions import TruncDay
>>> from stuff.models import Thing
>>> print
Thing.objects.annotate(truncstamp=TruncDay('timestamp')).values('truncstamp').query
SELECT CAST(DATE_FORMAT(CONVERT_TZ(`stuff_thing`.`timestamp`, 'UTC', UTC),
'%Y-%m-%d 00:00:00') AS DATETIME) AS `truncstamp` FROM `stuff_thing`
}}}

Look at this query. UTC isn't quoted! If you try to paste this into your
MySQL console, that quoting is not going to work. That parameter remains
unquoted no matter what you set your TIME_ZONE to.

--
Ticket URL: <https://code.djangoproject.com/ticket/27491>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Nov 15, 2016, 6:05:02 PM11/15/16

to django-...@googlegroups.com

#27491: TruncDay function arguments aren't quoted properly when you print the SQL
query
-------------------------------------+-------------------------------------

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Marten Kenbeek):

* status: new => closed
* resolution: => invalid

Comment:

This is an undocumented, private API, but the
[https://github.com/django/django/blob/74ed20b49ade9f1cbd9af294e35478d8e0f59344/django/db/models/sql/query.py#L221
docstring] explains this behaviour:

> Returns the query as a string of SQL with the parameter values
> substituted in (use sql_with_params() to see the unsubstituted string).
>
> **Parameter values won't necessarily be quoted correctly, since that
is**
> **done by the database interface at execution time.**

You should never inject parameters into the query yourself, or you'll risk
SQL injection attacks -- this extends to `Query.__str__` as well. Trying
to quote the parameters would give the false impression that this is safe
to execute, which it isn't.

If you need to execute the generated query manually, you could use `sql,
params = queryset.query.sql_with_params()` to get the SQL query and
parameters separately, which you can pass to the database cursor. Of
course, all caveats of undocumented methods apply.