TruncDay function arguments aren't quoted properly when you print the SQL query

[Nick Retallack]

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.

[Marten Kenbeek]

This is an undocumented, private API, but the ​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.