How SQL Injection happens in Python BigQuery connectors and how to fix it

How SQL Injection Happens in Python BigQuery Connectors and How to Fix It

Introduction

The common/data_source/bigquery_connector.py file handles all SQL query construction for a BigQuery-backed data source—reading configuration values like project_id, dataset_id, table_id, and timestamp_column at initialization and embedding them directly into query strings. A flaw in the __init__ method and downstream _build_base_query() logic meant that every one of those identifiers was trusted unconditionally, creating a direct path from connector configuration to raw BigQuery SQL execution.

This matters because BigQuery connectors are often configured through web interfaces, API payloads, or infrastructure-as-code files—surfaces that are reachable by users who should not have the ability to run arbitrary SQL. If you've ever written f"SELECT * FROM{project}.{dataset}.{table}" without first checking what those variables contain, this post is for you.

The Vulnerability Explained

What the vulnerable code looked like

Starting at line 138 in the original __init__ method, the connector simply stripped whitespace from incoming configuration values and stored them:

# BEFORE — vulnerable initialization
self.project_id = (project_id or "").strip()
self.dataset_id = (dataset_id or "").strip()
self.table_id   = (table_id   or "").strip()

Those values were then used in f-string query construction further down the file (lines 202, 207, 212, 303, 306, 310). A simplified example of what that looks like:

# Downstream query construction — the injection sink
query = (
    f"SELECT * FROM `{self.project_id}.{self.dataset_id}.{self.table_id}` "
    f"WHERE {self.timestamp_column} >= @start_time"
)

There is no escaping, no quoting of identifier parts, and no validation that the strings are legal BigQuery identifiers. The backtick quoting around the table reference helps for some characters, but timestamp_column is placed directly into the WHERE clause with no quoting at all.

How an attacker exploits this

The PR's threat model describes two concrete attack paths:

Attack 1 — Malicious table_id:

table_id = "my_table` WHERE 1=1; DROP TABLE important_data; --"

This closes the backtick, appends a destructive statement, and comments out the rest of the query. The resulting SQL becomes:

SELECT * FROM `project.dataset.my_table` WHERE 1=1; DROP TABLE important_data; --`
WHERE timestamp >= @start_time

Attack 2 — Malicious timestamp_column:

timestamp_column = "col1) OR 1=1 --"

Since timestamp_column is placed unquoted directly into the WHERE clause, this produces:

WHERE col1) OR 1=1 -- >= @start_time

Which evaluates to a tautology, returning all rows regardless of the intended time filter.

Attack 3 — Arbitrary query passthrough:
The connector also accepts a self.query field for completely custom SQL. If an attacker can set this field through a configuration API, they can run any BigQuery SQL the service account is authorized to execute—including reading sensitive tables or calling BigQuery ML functions.

Real-world impact

This is a web service. The PR explicitly notes: "vulnerabilities in request handlers are directly exploitable by remote attackers." Any API endpoint that allows a user to create or update a data source connector is a direct exploitation vector. The blast radius includes:
- Data exfiltration: UNION SELECT or subquery injection to read tables the user shouldn't access
- Data destruction: DROP TABLE or DELETE statements
- Cost escalation: Injecting expensive full-table scans to drive up BigQuery billing
- Privilege escalation: Calling BigQuery functions or accessing datasets outside the intended scope

The Fix

Two allowlist validators, applied at initialization

The fix adds two compiled regular expressions at module level and two validation functions that are called in __init__ before any value is stored:

# New module-level constants
_IDENTIFIER_RE = re.compile(r"^[A-Za-z_][A-Za-z0-9_]*$")
_PROJECT_ID_RE = re.compile(r"^[A-Za-z][A-Za-z0-9_-]*$")

_IDENTIFIER_RE matches standard BigQuery identifiers: must start with a letter or underscore, followed by letters, digits, or underscores only. No spaces, no backticks, no semicolons, no SQL metacharacters.

_PROJECT_ID_RE is slightly more permissive because GCP project IDs legitimately contain hyphens (e.g., my-gcp-project-123), but still excludes all SQL-meaningful characters.

def _validate_identifier(value: Optional[str], name: str) -> Optional[str]:
    if not value:
        return value
    if not _IDENTIFIER_RE.fullmatch(value):
        raise ConnectorValidationError(f"Invalid BigQuery identifier for {name!r}")
    return value


def _validate_project_id(value: str, name: str) -> str:
    if not value:
        return value
    if not _PROJECT_ID_RE.fullmatch(value):
        raise ConnectorValidationError(f"Invalid BigQuery identifier for {name!r}")
    return value

Note the use of .fullmatch() rather than .match() or .search(). This is critical: .match() anchors only at the start, so a value like valid_name; DROP TABLE x would pass a .match() check but fail .fullmatch().

Before and after

# BEFORE — no validation
self.project_id = (project_id or "").strip()
self.dataset_id = (dataset_id or "").strip()
self.table_id   = (table_id   or "").strip()

# AFTER — allowlist validation at assignment
self.project_id = _validate_project_id((project_id or "").strip(), "project_id")
self.dataset_id = _validate_identifier((dataset_id or "").strip(), "dataset_id")
# table_id and timestamp_column follow the same pattern

Now, if table_id is set to my_table\ WHERE 1=1; DROP TABLE important_data; --, the_validate_identifierfunction raises aConnectorValidationErrorimmediately ininit`—before any SQL string is ever constructed. The attack never reaches the query builder.

Why this approach is correct

Identifier validation is fundamentally different from value parameterization. BigQuery's parameterized query API (using @param_name placeholders) correctly handles data values like timestamps and strings. But table names, dataset names, column names, and project IDs are SQL structural elements—they cannot be passed as parameters. The only safe approach is to validate them against a strict allowlist of legal characters before interpolation.

The fix applies this validation at the earliest possible point: object construction. This means no code path through the connector can ever reach query-building logic with an unvalidated identifier.

Key Takeaways

• f-string SQL construction with identifier variables is dangerous even when values come from configuration, not just from HTTP request bodies. Configuration APIs are attack surfaces too.
• timestamp_column in a WHERE clause is more dangerous than table identifiers because it's placed unquoted, making injection easier and the resulting SQL more predictable for an attacker.
• .fullmatch() is the correct method for allowlist regex validation in Python—.match() and .search() both leave the door open for bypass.
• The self.query passthrough field (allowing arbitrary custom SQL) deserves its own access control review independent of this fix—it is a separate, intentional bypass of all query construction logic.
• Validating in __init__ rather than in each query-building method ensures the invariant is enforced once and cannot be bypassed by future code changes that add new query paths.

How Orbis AppSec Detected This

• Source: Connector initialization parameters (project_id, dataset_id, table_id, timestamp_column) passed in from external configuration at common/data_source/bigquery_connector.py:138
• Sink: f-string SQL query construction in _build_base_query() and related methods at lines 202, 207, 212, 303, 306, 310
• Missing control: No allowlist validation or escaping of identifier values before interpolation into SQL strings
• CWE: CWE-89 — Improper Neutralization of Special Elements used in an SQL Command
• Fix: Added _validate_identifier() and _validate_project_id() functions using compiled fullmatch() regexes, called in __init__ before storing any identifier value

Orbis AppSec automatically detected this vulnerability and opened a pull request with the fix. Try Orbis AppSec on your repositories to find and fix issues like this automatically.

Conclusion

This vulnerability is a reminder that SQL injection isn't limited to login forms and search boxes. Any system that constructs SQL from configuration data—BigQuery connectors, BI tool integrations, data pipeline builders—carries the same risk. The pattern of f"SELECT * FROM {user_controlled_value}" is dangerous regardless of whether user_controlled_value came from an HTTP parameter or a YAML config file.

The fix here is elegant in its simplicity: two regular expressions, two validation functions, and three lines changed in __init__. The entire connector's query-building logic—across six vulnerable call sites—is now protected by a single enforcement point at object construction. That's the right architecture for security invariants: enforce them once, at the boundary, and let the rest of the code assume safety.