parthea · Nov 4, 2026 · Nov 4, 2026 · Nov 4, 2026 · Nov 4, 2026
@@ -12,6 +12,7 @@
 # See the License for the specific language governing permissions and
 # limitations under the License.
 import base64
+import collections.abc
 import re
 
 import sqlalchemy
@@ -37,7 +38,7 @@
     selectable,
 )
 from sqlalchemy.sql.default_comparator import operator_lookup
-from sqlalchemy.sql.operators import json_getitem_op
+from sqlalchemy.sql.operators import json_getitem_op, json_path_getitem_op
 
 from google.cloud import spanner_dbapi
 from google.cloud.sqlalchemy_spanner import version as sqlalchemy_spanner_version
@@ -88,6 +89,96 @@ def reset_connection(dbapi_conn, connection_record, reset_state=None):
 OPERATORS[json_getitem_op] = operator_lookup["json_getitem_op"]
 
 
+_SPANNER_STRING_ESCAPE_TABLE = str.maketrans(
+    {
+        "\\": "\\\\",
+        '"': '\\"',
+        "'": "\\'",
+        "\n": "\\n",
+        "\r": "\\r",
+        "\t": "\\t",
+        **{
+            chr(c): f"\\u{c:04x}"
+            for c in (*range(0x00, 0x20), 0x7F)
+            if chr(c) not in ("\n", "\r", "\t")
+        },
+    }
+)
+
+
+def _escape_json_path_key(key):
+    """Escape a JSON member key for use inside double-quoted GoogleSQL JSONPath.
+
+    GoogleSQL's JSONPath lexer (`kEscKeyLexerStandard`) consumes `\\[^"]` as a
+    two-character pair during tokenization, while `RemoveBackSlashFollowedByChar`
+    only unescapes `\\"`. Doubling backslashes only when immediately preceding
+    `"` or end-of-string preserves interior backslashes while preventing any
+    trailing or quote-adjacent backslash from unescaping a `"` delimiter.
+    """
+    s = str(key)
+    s = re.sub(r'(\\+)(?="|\Z)', r"\g<1>\g<1>", s)
+    return s.replace('"', '\\"')
+
+
+def _escape_spanner_string_literal(value, dialect=None):
+    """Escape a string value as a double-quoted GoogleSQL string literal."""
+    escaped = str(value).translate(_SPANNER_STRING_ESCAPE_TABLE)
+    if dialect is not None and dialect.identifier_preparer._double_percents:
+        escaped = escaped.replace("%", "%%")
+    return f'"{escaped}"'
+
+
+def _is_json_path_sequence(value):
+    """Return True if value is a sequence of JSON path steps (not str/bytes)."""
+    return not isinstance(value, (str, bytes, bytearray)) and isinstance(
+        value, collections.abc.Sequence
+    )
+
+
+def _format_json_path_step(elem):
+    """Format a single JSONPath member key or integer array index step."""
+    if isinstance(elem, int) and not isinstance(elem, bool):
+        return "[%s]" % elem
+    return '."%s"' % _escape_json_path_key(elem)
+
+
+class _FormatTypeMixin:
+    def _format_value(self, value):
+        if _is_json_path_sequence(value):
+            return "$%s" % "".join(_format_json_path_step(elem) for elem in value)
+        return "$%s" % _format_json_path_step(value)
+
+    def bind_processor(self, dialect):
+        super_proc = self.string_bind_processor(dialect)
+
+        def process(value):
+            if value is None:
+                return None
+            formatted = self._format_value(value)
+            if super_proc:
+                formatted = super_proc(formatted)
+            return formatted
+
+        return process
+
+    def literal_processor(self, dialect):
+        def process(value):
+            if value is None:
+                return "NULL"
+            formatted = self._format_value(value)
+            return _escape_spanner_string_literal(formatted, dialect)
+
+        return process
+
+
+class SpannerJSONIndexType(_FormatTypeMixin, types.JSON.JSONIndexType):
+    """Format a JSON index for Spanner JSON_VALUE path."""
+
+
+class SpannerJSONPathType(_FormatTypeMixin, types.JSON.JSONPathType):
+    """Format a JSON path sequence for Spanner JSON_VALUE path."""
+
+
 # PickleType that can be used with Spanner.
 # Binary values are automatically encoded/decoded to/from base64.
 # Usage:
@@ -341,52 +432,47 @@ def visit_like_op_binary(self, binary, operator, **kw):
             binary.right._compiler_dispatch(self, **kw),
         )
 
-    def _generate_generic_binary(self, binary, opstring, eager_grouping=False, **kw):
-        """The method is overriden to process JSON data type cases."""
-        _in_binary = kw.get("_in_binary", False)
-
-        kw["_in_binary"] = True
+    def _json_value_binary(self, binary, eager_grouping=False, **kw):
+        """Build a parameterized or safely escaped JSON_VALUE() function call."""
+        left_text = self.process(binary.left, eager_grouping=eager_grouping, **kw)
+        right = binary.right
 
-        if isinstance(opstring, str):
-            text = (
-                binary.left._compiler_dispatch(
-                    self, eager_grouping=eager_grouping, **kw
-                )
-                + opstring
-                + binary.right._compiler_dispatch(
-                    self, eager_grouping=eager_grouping, **kw
+        if isinstance(right, elements.BindParameter):
+            if not isinstance(
+                right.type, (types.JSON.JSONIndexType, types.JSON.JSONPathType)
+            ):
+                right = right._clone(maintain_key=True)
+                right.type = (
+                    SpannerJSONPathType()
+                    if getattr(binary, "operator", None) is json_path_getitem_op
+                    else SpannerJSONIndexType()
                 )
-            )
-            if _in_binary and eager_grouping:
-                text = "(%s)" % text
+            right_text = self.process(right, eager_grouping=eager_grouping, **kw)
+        elif isinstance(right, elements.ClauseElement):
+            right_text = self.process(right, eager_grouping=eager_grouping, **kw)
         else:
-            # got JSON data
-            right_value = getattr(
-                binary.right, "value", None
-            ) or binary.right._compiler_dispatch(
-                self, eager_grouping=eager_grouping, **kw
+            raw_value = getattr(right, "value", right)
+            right_text = SpannerJSONIndexType().literal_processor(self.dialect)(
+                raw_value
             )
 
-            text = (
-                binary.left._compiler_dispatch(
-                    self, eager_grouping=eager_grouping, **kw
-                )
-                + """, "$."""
-                + str(right_value)
-                + '"'
+        return "JSON_VALUE(%s, %s)" % (left_text, right_text)
+
+    def _generate_generic_binary(self, binary, opstring, eager_grouping=False, **kw):
+        """The method is overriden to process JSON data type cases."""
+        if isinstance(opstring, str):
+            return super()._generate_generic_binary(
+                binary, opstring, eager_grouping=eager_grouping, **kw
             )
-            text = "JSON_VALUE(%s)" % text
+        return self._json_value_binary(binary, eager_grouping=eager_grouping, **kw)
 
-        return text
+    def visit_json_getitem_op_binary(self, binary, operator, **kw):
+        """Build a JSON_VALUE() function call for single-key JSON indexing."""
+        return self._json_value_binary(binary, **kw)
 
     def visit_json_path_getitem_op_binary(self, binary, operator, **kw):
         """Build a JSON_VALUE() function call."""
-        expr = """JSON_VALUE(%s, "$.%s")"""
-
-        return expr % (
-            self.process(binary.left, **kw),
-            self.process(binary.right, **kw),
-        )
+        return self._json_value_binary(binary, **kw)
 
     def render_literal_value(self, value, type_):
         """Render the value of a bind parameter as a quoted literal.
@@ -407,8 +493,28 @@ def render_literal_value(self, value, type_):
             # (e.g. JSON)
             return self.process(elements.Null._instance())
 
-        raw = ["\\", "'", '"', "\n", "\t", "\r"]
-        if isinstance(value, str) and any(single in value for single in raw):
+        if isinstance(type_, (types.JSON.JSONIndexType, types.JSON.JSONPathType)):
+            processor = type_._cached_literal_processor(self.dialect)
+            if processor:
+                return processor(value)
+
+        raw = ("\\", "'", '"', "\n", "\t", "\r")
+        has_ctrl = isinstance(value, str) and any(
+            (ord(ch) < 0x20 and ch not in ("\n", "\t")) or ord(ch) == 0x7F
+            for ch in value
+        )
+        if isinstance(value, str) and (
+            has_ctrl or any(single in value for single in raw)
+        ):
+            if (
+                has_ctrl
+                or '"""' in value
+                or value.endswith('"')
+                or value.endswith("\\")
+            ):
+                return _escape_spanner_string_literal(value, self.dialect)
+            if self.dialect.identifier_preparer._double_percents:
+                value = value.replace("%", "%%")
             value = 'r"""{}"""'.format(value)
             return value
         else:
@@ -905,6 +1011,10 @@ class SpannerDialect(DefaultDialect):
     execution_ctx_cls = SpannerExecutionContext
     _json_serializer = JsonObject
     _json_deserializer = JsonObject
+    colspecs = {
+        types.JSON.JSONIndexType: SpannerJSONIndexType,
+        types.JSON.JSONPathType: SpannerJSONPathType,
+    }
 
     @classmethod
     def dbapi(cls):

@@ -30,6 +30,7 @@
 from tests.mockserver_tests.mock_server_test_base import (
     MockServerTestBase,
     add_result,
+    add_single_result,
     add_update_count,
 )
 
@@ -143,6 +144,57 @@ def _test_select_json(self, description, expected):
             venue = session.execute(select(Venue)).first()[0]
             eq_(venue.description, expected)
 
+    def _test_select_json_value(self, expr_fn, expected_param, expected_result):
+        from tests.mockserver_tests.json_model import Venue
+
+        sql = "SELECT JSON_VALUE(venues.description, @a0) AS anon_1 \nFROM venues"
+        add_single_result(sql, "anon_1", TypeCode.STRING, [(expected_result,)])
+        engine = self.create_engine()
+
+        with Session(engine.execution_options(read_only=True)) as session:
+            val = session.scalar(select(expr_fn(Venue.description)))
+            eq_(val, expected_result)
+
+        requests = [
+            r for r in self.spanner_service.requests if isinstance(r, ExecuteSqlRequest)
+        ]
+        eq_(1, len(requests))
+        eq_(sql, requests[0].sql)
+        eq_(expected_param, requests[0].params["a0"])
+        eq_(TypeCode.STRING, requests[0].param_types["a0"].code)
+        return requests[0]
+
+    def test_select_json_key(self):
+        self._test_select_json_value(
+            lambda col: col["type"].as_string(), '$."type"', "Stadium"
+        )
+
+    def test_select_json_int_index_zero(self):
+        self._test_select_json_value(
+            lambda col: col[0].as_string(), "$[0]", "first_item"
+        )
+
+    def test_select_json_path(self):
+        self._test_select_json_value(
+            lambda col: col[("info", 0, "size")].as_string(),
+            '$."info"[0]."size"',
+            "Great",
+        )
+
+    def test_select_json_key_sql_injection_prevented(self):
+        malicious_key = (
+            'display") FROM venues WHERE FALSE UNION ALL '
+            "SELECT (SELECT secret FROM secrets) -- injected"
+        )
+        req = self._test_select_json_value(
+            lambda col: col[malicious_key].as_string(),
+            '$."display\\") FROM venues WHERE FALSE UNION ALL '
+            'SELECT (SELECT secret FROM secrets) -- injected"',
+            None,
+        )
+        assert "UNION ALL" not in req.sql
+        assert "secrets" not in req.sql
+
 
 def add_venue_query_result(sql: str, description: str):
     result = result_set.ResultSet(