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]

Reply via email to