Opened 21 months ago

Last modified 20 months ago

#28214 assigned Bug

IndexError for JSONField queries containing percent character in key

Reported by: mrsanders Owned by: André Ericson
Component: contrib.postgres Version: 1.11
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


Using Django 1.11.1 and Postgres 9.5.6.

A JSON field may legitimately contain a percent symbol in a key, for example:

{"%total": 10}

This may be queried as follows (assuming the JSONField in the Django model is called 'my_json_field'):

>>> qs=MyModel.objects.filter(**{"my_json_field__%total": 10})

However when the Queryset is evaluated, it raises an IndexError:

>>> len(qs)
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/usr/local/lib/python3.6/site-packages/django/db/models/", line 232, in __len__
  File "/usr/local/lib/python3.6/site-packages/django/db/models/", line 1103, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/usr/local/lib/python3.6/site-packages/django/db/models/", line 53, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch)
  File "/usr/local/lib/python3.6/site-packages/django/db/models/sql/", line 886, in execute_sql
    raise original_exception
  File "/usr/local/lib/python3.6/site-packages/django/db/models/sql/", line 876, in execute_sql
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/", line 80, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/", line 65, in execute
    return self.cursor.execute(sql, params)
IndexError: tuple index out of range

...and when attempting to print the generated query, a ValueError is raised:

>>> print(qs.query)
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/usr/local/lib/python3.6/site-packages/django/db/models/sql/", line 234, in __str__
    return sql % params
ValueError: unsupported format character 't' (0x74) at index 1498

It seems that something isn't escaping the percent before doing a string formatting operation.

Changing the query to double-up the percent symbol causes the query to work (although it shouldn't):

>>> qs=MyModel.objects.filter(**{"my_json_field__%%total": 10}

Change History (3)

comment:1 Changed 21 months ago by Simon Charette

Triage Stage: UnreviewedAccepted

comment:2 Changed 20 months ago by André Ericson

Owner: set to André Ericson
Status: newassigned

comment:3 Changed 20 months ago by André Ericson


how should we tackle this? psycopg2 documentation says "in order to include a literal % in the query you can use the %% string."

If we decide to escape it, it will break for people that already fixed this in their own code by pre-escaping the JSON keys (ie: '%%total' key).

Another approach would be to add a note in the JSONField documentation that '%' must be escaped.

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