ashwin-sampathkumar opened a new pull request, #41465:
URL: https://github.com/apache/beam/pull/41465
<!--
Thank you for your contribution! Follow this checklist to help us
incorporate your contribution quickly and easily:
- [ ] If this contribution is large, please file an Apache [Individual
Contributor License Agreement](https://www.apache.org/licenses/icla.pdf).
- [ ] Replace these instructions with a description of your change and why
you have made it.
- [ ] Mention the appropriate issue in your description (for example:
`addresses #123` or `fixes #456`).
- [ ] Update `CHANGES.md` with noteworthy changes.
See [CONTRIBUTING.md]() for more details.
-->
### Description
This PR addresses a SQL injection vulnerability in the BigQuery Enrichment
transforms (`BigQueryEnrichmentHandler` and
`BigQueryVectorSearchEnrichmentHandler`) by transitioning from raw string
interpolation to native BigQuery parameterized queries.
Previously, row restriction templates (e.g., `id='{}'`) were populated using
Python's string `.format()`, which is susceptible to SQL injection and breaks
when payload strings contain unescaped quotes or malicious syntax.
**Key Changes:**
* **Native Query Parameterization:** Binds all dynamic payloads (strings,
integers, and `FLOAT64` vector embeddings) natively via
`bigquery.QueryJobConfig(query_parameters=...)` using scalar and array
parameters.
* **Strict Identifier Validation:** Added rigorous regex-based validation
for identifiers that must be string-interpolated (like `table_name`,
`column_names`, and `distance_type`) to ensure they cannot be used to break out
of backticks or inject SQL commands.
* **Backward Compatibility via SQL Scanner:** Introduced a robust SQL
template scanner (`scan_sql_template`) that parses templates while respecting
SQL literals, comments, and raw regexes. This automatically strips legacy
quotes around placeholders (e.g., converting `id='{}'` to `id=?`) and maps
partial string matches to `CONCAT()`, ensuring existing user pipelines continue
to work seamlessly without modification.
* **Safe Batch Chunking:** Added chunking logic to ensure batch queries
never exceed BigQuery's hard limit of 10,000 query parameters per request.
* **JSON Serialization for Options:** Safely serializes `VECTOR_SEARCH`
`options` as a JSON-formatted SQL string literal.
### Testing
* Added comprehensive unit tests in `bigquery_test.py` and
`bigquery_vector_search_test.py` mocking the BigQuery client to verify
`job_config.query_parameters`.
* Verified that payloads containing `' OR 1=1 --` or `DROP TABLE` remain
safely bound as query variables rather than executable logic.
* Verified that BQ parameter limits (10,000) trigger proper chunking.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]