Opened 7 years ago

Closed 7 years ago

#12611 closed (wontfix)

Incorrect quoting in QuerySet.query.__str__()

Reported by: Jason McVetta Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Keywords: sql quoting
Cc: Triage Stage: Someday/Maybe
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

When I call __str__() on a QuerySet object's query member, it returns invalid SQL. Arguments to the WHERE clause are not quoted. This bug makes it difficult to log queries then feed them to the database's EXPLAIN command for performance analysis.

For instance, if we construct a QuerySet like this:

ogtt75_events = [
    'ogtt75_fasting_pos',
    'ogtt75_random_pos',
    'ogtt75_30m_pos',
    'ogtt75_1hr_pos',
    'ogtt75_90m_pos',
    'ogtt75_2hr_pos',
    ]
q_obj = Q(name__in=ogtt75_events)
q_obj &= Q(patient__event__name='pregnancy_diagnosis')
qs = Event.objects.filter(q_obj)

it will return SQL like this (fed through sqlparse library for pretty indentation):

SELECT "hef_event"."id",
       "hef_event"."name",
       "hef_event"."date",
       "hef_event"."patient_id",
       "hef_event"."timestamp",
       "hef_event"."run_id",
       "hef_event"."content_type_id",
       "hef_event"."object_id"
FROM "hef_event"
INNER JOIN "emr_patient" ON ("hef_event"."patient_id" = "emr_patient"."id")
INNER JOIN "hef_event" T3 ON ("emr_patient"."id" = T3."patient_id")
WHERE ("hef_event"."name" IN (ogtt75_fasting_pos,
                              ogtt75_random_pos,
                              ogtt75_30m_pos,
                              ogtt75_1hr_pos,
                              ogtt75_90m_pos,
                              ogtt75_2hr_pos)
       AND T3."name" = pregnancy_diagnosis)
ORDER BY "hef_event"."date" ASC, "hef_event"."patient_id" ASC, "hef_event"."name" ASC

This behavior was observed using the latest trunk checkout of Django, with PostgreSQL as backend.

Change History (1)

comment:1 Changed 7 years ago by Russell Keith-Magee

Needs documentation: unset
Needs tests: unset
Patch needs improvement: unset
Resolution: wontfix
Status: newclosed
Triage Stage: UnreviewedSomeday/Maybe

I completely acknowledge the problem, but it is non-trivial to fix. Database quoting is actually done by the database cursor at the time of query execution. What is returned by QuerySet.query is the best attempt at rendering the query as it will be executed, but there's no guarantee it will be completely accurate.

Marking wontfix due to the absence of a workable solution. If anyone can propose something (that doesn't involve reimplementing the full quoting behavior of a DB cursor), please reopen with that suggestion.

Note: See TracTickets for help on using tickets.
Back to Top