id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
35842	JSONField has_key, has_keys, has_any_keys lookups do not properly handle quotes on Oracle and SQLite	Simon Charette	Sage Abdullah	"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."	Bug	closed	Database layer (models, ORM)	dev	Normal	fixed	oracle sqlite json key quote	Sage Abdullah	Accepted	1	0	0	0	0	0