#35842: JSONField has_key, has_keys, has_any_keys lookups do not properly handle
quotes on Oracle and SQLite
-------------------------------------+-------------------------------------
Reporter: Simon Charette | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: 5.0 | Severity: Normal
Keywords: oracle sqlite json | Triage Stage:
key quote | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
The following test reproduces issues on both Oracle and SQLite
{{{#!diff
diff --git a/tests/model_fields/test_jsonfield.py
b/tests/model_fields/test_jsonfield.py
index ff42b1a14c..b1090b30ac 100644
--- a/tests/model_fields/test_jsonfield.py
+++ b/tests/model_fields/test_jsonfield.py
@@ -636,6 +636,25 @@ def test_has_key_number(self):
[obj],
)
+ def test_has_key_special_chars(self):
+ value = {
+ 'double"': "",
+ "single'": "",
+ "dollar$": "",
+ "mixed$\"'.": "",
+ }
+ obj = NullableJSONModel.objects.create(
+ value=value
+ )
+ obj.refresh_from_db()
+ self.assertEqual(obj.value, value)
+ for key in value:
+ with self.subTest(key=key):
+ self.assertSequenceEqual(
+ NullableJSONModel.objects.filter(value__has_key=key),
+ [obj],
+ )
+
@skipUnlessDBFeature("supports_json_field_contains")
def test_contains(self):
tests = [
}}}
In the case of Oracle the issue arise because it doesn't supporting
binding variables in `JSON_EXISTS`
([https://github.com/django/django/pull/11452#issuecomment-510323462
original discussion]) so while the `json.dumps` of the key is believed to
protect from SQL injections it can still result in crashes if the key
contains a single quote character. Using the `PASSING` clause could
possibly allow us to [https://docs.oracle.com/en/database/oracle/oracle-
database/23/adjsn/clauses-used-in-functions-and-conditions-for-json.html
#GUID-DE9F29D3-1C23-4271-9DCD-E585866576D2 bypass this limitation] or
using
[https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm#i42617
a different escaping strategy] could possibly be used to adjust
[https://github.com/django/django/blob/ec7d69035a408b357f1803ca05a7c991cc358cfa/django/db/models/fields/json.py#L231-L237
the Oracle implementation].
---
In the case of SQLite the problem is with the double-quote character `"`
because escapes generated by `json.dumps`
[https://stackoverflow.com/questions/67993982/sqlite-with-special-
character-in-the-json-path/67994603#67994603 are not properly interpreted
by SQLite].
In other words `"foo\"bar"` is not properly interpreted as `'foo"bar` and
while SQLite allows you not to quote keys (e.g. `JSON_TYPE(%s,
'$.foo\"bar') IS NOT NULL`) the solution is not viable for keys that
contain both a double-quote and a symbol such as `.` as exemplified by the
''mixed'' key in the provide test.
--
Ticket URL: <https://code.djangoproject.com/ticket/35842>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/0107019290f2f592-a2895051-feec-49b3-a1e2-8c6472a83ca9-000000%40eu-central-1.amazonses.com.
