fix(spanner): escape embedded backticks in dbapi escape_name

[sakthivelmanii]

In google-cloud-spanner DB-API, escape_name() did not check for or double embedded backtick characters (`) when wrapping identifiers. This allowed identifiers containing backticks to break out of backtick-quoted identifier scopes (CWE-89 identifier injection).

This commit updates escape_name() to:

• Detect embedded backtick characters in identifier names.
• Escape internal backticks by adding backslashes (replace("", "\")) when enclosing identifiers in backticks.
• Add unit test cases for embedded backticks in test_escape_name.

[gemini-code-assist]

Code Review

This pull request updates the escape_name function in parse_utils.py to escape backticks by doubling them, and adds corresponding unit tests. The review feedback points out a critical security vulnerability (SQL injection) where identifiers containing other special characters (such as comments or semicolons) but no spaces, hyphens, or backticks could bypass escaping. A code suggestion is provided to resolve this by strictly validating identifiers and escaping any that are not valid regular ASCII identifiers.

[parthea]

Wait for #17880 to be merged first

Done. Please merge main into this branch

[olavloite]

GoogleSQL does not use 'doubled quotes' to escape quotes inside quotes. Instead, you should use a backslash for that:

-- This is invalid in GoogleSQL
select 1 as `fo``o`

-- This is the correct way to escape a quote in GoogleSQL
select 1 as `fo\`o`

[sakthivelmanii]

Done

[olavloite]

There are more corner cases that need to be thought through here:

1. Backslash (see also below): Backslash starts an escape sequence in GoogleSQL, unless itself is escaped. Should we escape backslashes as well to be consistent with how we handle existing backticks in identifiers? E.g. what should we do with a string like this: "test\"
2. Identifiers with multiple elements (dot operator): The identifier my_schema.my_table is now turned into this: `my_schema.my_table`.
3. Already quoted identifiers: `my_table` becomes ``my_table``

[sakthivelmanii]

Done

[olavloite]

This check should be done after the check whether the identifier is already quoted. Otherwise, a dot inside a validly quoted identifier would break up that identifier into multiple (invalid) parts.

Also, the current loop below actually allows similar SQL injection attacks that this is intended to fix. The following string would be seen as a validly quoted identifier:

# String literal with 1 literal backslash before the internal backtick:
payload = "`col" + "\\" + "`; DROP TABLE users; --`"
# Represented in Python string repr as: '`col\\`; DROP TABLE users; --`'

This results in this SQL:

`col\`; DROP TABLE users; --`

Could we instead use a regex to prevent this type of SQL injection attack, and also simplify the implementation a bit:

import re

# re.DOTALL ensures '.' in '\\.' matches newlines inside valid multiline backtick identifiers.
# \A and \Z enforce strict start and end anchors.
_QUOTED_IDENTIFIER_RE = re.compile(r"\A`(\\.|[^`\\])*`\Z", re.DOTALL)

def escape_name(name):
    if not name:
        return name

    # 1. Check if the string is ALREADY a valid GoogleSQL quoted identifier
    if _QUOTED_IDENTIFIER_RE.match(name):
        return name

    # 2. Handle qualified identifiers (e.g., schema.table or `schema`.table)
    if "." in name:
        parts = name.split(".")
        return ".".join(escape_name(part) for part in parts)

    # 3. Check if it is a regular ASCII identifier and not a reserved keyword
    is_valid_regular_identifier = (
        (name[0].isalpha() or name[0] == "_")
        and all(c.isalnum() or c == "_" for c in name)
        and name.isascii()
    )

    if not is_valid_regular_identifier or name.upper() in SPANNER_RESERVED_KEYWORDS:
        escaped = name.replace("\\", "\\\\").replace("`", "\\`")
        return f"`{escaped}`"

    return name

[sakthivelmanii]

Done