[Detail Bug] Unescaped identifiers in stage transforms cause SQL injection and break quoted identifiers

[detail-app]

Detail Bug Report

https://app.detail.dev/org_4c5b920c-9e04-4530-a4d2-953927859978/bugs/bug_7f249b7f-83c3-4adc-a1b8-da9b15520608

Summary

• Context: The stage.py file contains transform functions that convert Snowflake stage commands (CREATE STAGE, LIST, PUT) into SQL queries for DuckDB execution.
• Bug: Identifiers (stage names, database names, schema names, URLs) are directly interpolated into SQL strings using f-strings without escaping or parameterization, causing SQL injection vulnerabilities.
• Actual vs. expected: When an identifier contains a single quote (which Snowflake allows in quoted identifiers like "my'stage"), the generated SQL breaks with a syntax error instead of properly handling the quote.
• Impact: Users cannot create or use stages with single quotes or other special characters in their names, which are valid in Snowflake. Additionally, if user input were to flow into these identifiers, it could enable SQL injection attacks.

Code with bug

insert_sql = f"""
    INSERT INTO _fs_global._fs_information_schema._fs_stages
    (created_on, name, database_name, schema_name, url, has_credentials, has_encryption_key, owner,
    comment, region, type, cloud, notification_channel, storage_integration, endpoint, owner_role_type,
    directory_enabled)
    SELECT
        '{now}', '{stage_name}', '{catalog}', '{schema}', '{url}', 'N', 'N', 'SYSADMIN',  # <-- BUG 🔴 Unescaped interpolation
        '', NULL, '{stage_type}', {f"'{cloud}'" if cloud else "NULL"}, NULL, NULL, NULL, 'ROLE',
        'N'
    WHERE NOT EXISTS (
        SELECT 1 FROM _fs_global._fs_information_schema._fs_stages
        WHERE name = '{stage_name}' AND database_name = '{catalog}' AND schema_name = '{schema}'  # <-- BUG 🔴 Unescaped interpolation
    )
    """

query = f"""
    SELECT *
    from _fs_global._fs_information_schema._fs_stages
    where database_name = '{catalog}' and schema_name = '{schema}' and name = '{stage_name}'  # <-- BUG 🔴 Unescaped interpolation
"""

query = f"""
    SELECT *
    from _fs_global._fs_information_schema._fs_stages
    where database_name = '{catalog}' and schema_name = '{schema}' and name = '{stage_name}'  # <-- BUG 🔴 Unescaped interpolation
"""

Example

• Create a stage with a single quote in a quoted identifier: CREATE STAGE "my'stage".
• After normalise_ident() strips the double quotes, the value becomes: my'stage.
• This is interpolated into the SQL as 'my'stage', prematurely closing the string literal.
• Resulting malformed SQL snippet:

  SELECT
      '...timestamp...', 'my'stage', 'DB1', 'SCHEMA1', ...

• DuckDB parser error: 001003 (42000): Invalid expression / Unexpected token.

Exploit scenario

• In list_stage(), a crafted stage name such as x' OR '1'='1' -- leads to:

  ... WHERE database_name = 'DB1' AND schema_name = 'SCHEMA1' AND name = 'x' OR '1'='1' --'

  This changes the WHERE clause semantics and can return unintended rows.

Recommended fix

• Build SQL using sqlglot expressions so values are emitted as properly escaped literals.

  insert_expr = exp.Insert(
      this=exp.Table(
          this=exp.Identifier(this="_fs_stages"),
          db=exp.Identifier(this="_fs_information_schema"),
          catalog=exp.Identifier(this="_fs_global"),
      ),
      expression=exp.Select(
          expressions=[
              exp.Literal.string(now),
              exp.Literal.string(stage_name),  # <-- FIX 🟢 Use literal builders instead of string interpolation
              exp.Literal.string(catalog),
              exp.Literal.string(schema),
              exp.Literal.string(url),
              # ...
          ],
          # ...
      ),
  )

  Alternatively, escape single quotes by doubling them before interpolation, but this is less robust than expression building.