Opened 4 years ago

Last modified 4 years ago

#24605 closed Bug

Database identifiers are not properly escaped in some queries — at Version 2

Reported by: Nikolay Kurevin Owned by: nobody
Component: Database layer (models, ORM) Version: 1.7
Severity: Release blocker Keywords: regression, database
Cc: Simon Charette, priidukull Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Nikolay Kurevin)

Happens on 1.7.2-1.7.7, does not happens on 1.7.1

Rough setup:

class Bmodel(models.Model):
    pass

class Amodel(models.Model):
    bmodel = models.ForeignKey(Bmodel, related_name='Amodel_bmodel')

class Cmodel(models.Model):
    amodel = models.ForeignKey(Amodel)

Depending on order of Q filters there are 2 outcomes:

>>> models.Amodel.objects.exclude(Q(active=False, bmodel__is_active=False) & Q(cmodel__isnull=True)).query.__str__()

u'SELECT (...cut...) FROM "coreApi_amodel" INNER JOIN "coreApi_bmodel" ON ( "coreApi_amodel"."bmodel_id" = "coreApi_bmodel"."id" ) WHERE NOT ("coreApi_amodel"."active" = False AND "coreApi_bmodel"."is_active" = False AND "coreApi_amodel"."id" IN (SELECT U0."id" AS "id" FROM coreApi_amodel U0 LEFT OUTER JOIN "coreApi_cmodel" U1 ON ( U0."id" = U1."amodel_id" ) WHERE (U1."id" IS NULL AND U0."id" = (coreApi_amodel."id"))))'

>>> models.Amodel.objects.exclude(Q(active=False, bmodel__is_active=False) & Q(cmodel__isnull=True))
Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File ".../.venv/src/django/django/db/models/query.py", line 116, in __repr__
    data = list(self[:REPR_OUTPUT_SIZE + 1])
  File ".../.venv/src/django/django/db/models/query.py", line 141, in __iter__
    self._fetch_all()
  File ".../.venv/src/django/django/db/models/query.py", line 966, in _fetch_all
    self._result_cache = list(self.iterator())
  File ".../.venv/src/django/django/db/models/query.py", line 265, in iterator
    for row in compiler.results_iter():
  File ".../.venv/src/django/django/db/models/sql/compiler.py", line 700, in results_iter
    for rows in self.execute_sql(MULTI):
  File ".../.venv/src/django/django/db/models/sql/compiler.py", line 786, in execute_sql
    cursor.execute(sql, params)
  File ".../.venv/src/django/django/db/backends/utils.py", line 81, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File ".../.venv/src/django/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File ".../.venv/src/django/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File ".../.venv/src/django/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
ProgrammingError: relation "coreapi_amodel" does not exist
LINE 1: ...pi_amodel"."id" IN (SELECT U0."id" AS "id" FROM coreApi_am...
                                                             ^

Change Q order:

>>> models.Amodels.objects.exclude(Q(cmodel__isnull=True) & Q(active=False, bmodel__is_active=False)).query.__str__()

u'SELECT (...cut...) FROM "coreApi_amodel" INNER JOIN "coreApi_bmodel" ON ( "coreApi_amodel"."bmodel_id" = "coreApi_bmodel"."id" ) WHERE NOT ("coreApi_amodel"."id" IN (SELECT U0."id" AS "id" FROM "coreApi_amodel" U0 LEFT OUTER JOIN "coreApi_cmodel" U1 ON ( U0."id" = U1."amodel_id" ) WHERE U1."id" IS NULL) AND "coreApi_amodel"."active" = False AND "coreApi_bmodel"."is_active" = False)'

>>> models.Amodels.objects.exclude(Q(cmodel__isnull=True) & Q(active=False, bmodel__is_active=False))
[...results...]

Change History (2)

comment:1 Changed 4 years ago by Tim Graham

Summary: [1.7.7] DB identifiers are not properly escaped in some case (ProgrammingError: relation does not exist)Database identifiers are not properly escaped in some queries
Type: UncategorizedBug

Could you please edit the ticket to include all the models and fields that your query references?

comment:2 Changed 4 years ago by Nikolay Kurevin

Description: modified (diff)
Note: See TracTickets for help on using tickets.
Back to Top