Incorrect escaping in Django SQL query

[Roshan Raghupathy]

Hi,

I came across an issue yesterday caused by TruncDay function. You can find it here: https://stackoverflow.com/questions/44287443/incorrect-escaping-in-django-sql-query

Copy pasting from there:

Here's the query I'm trying to run:

MyModel.objects.filter(created__lt=functions.TruncDay(Value(timezone.now(), output_field=DateTimeField())))

It translates to:

SELECT <field-list> FROM "mymodel" WHERE "mymodel"."created" < (DATE_TRUNC('day', %%s AT TIME ZONE %s))

before Django performs parameter substitution. Note that the first %s has been escaped to %%s. This causes the parameter substitution to throw an exception.

Is this intended behaviour or a bug?

I dug into the internals a bit to figure this out and the issue comes from this line where the query till that point is escaped but later, they are never escaped back to the original form for correct parameter substitution. I went ahead and added code to replace `%%s` with `%s` before parameter substitution and it worked fine. Am I missing something here or should I file a bug(has it been filed already?)?

[Tim Graham]

I couldn't find any testing of TruncDay with Value().

Is there a difference between the query you're trying to construct and MyModel.objects.filter(created__lt=timezone.now().date()) ?

[Tim Graham]

Cross posted to django-users: https://groups.google.com/d/msg/django-users/nNgcv2QajCI/hPRwIeVNBAAJ

In the future, please keep the conversation in one place. django-users is fine for asking "is it a bug?" questions.