JSONField has_key, has_keys, has_any_keys lookups do not properly handle quotes on Oracle and SQLite

[Simon Charette]

The following test reproduces issues on both Oracle and SQLite

tests/model_fields/test_jsonfield.py

@@ -636 +636 @@
                     [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 (​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 ​bypass this limitation or using ​a different escaping strategy could possibly be used to adjust ​the Oracle implementation.

---

In the case of SQLite the problem is with the double-quote character " because escapes generated by json.dumps ​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.

[Mariusz Felisiak]

It's related to the #32213.