BigQueryCheckOperator does not support parameterized queries

[aldenstpage]

Apache Airflow Provider(s)

google

Versions of Apache Airflow Providers

apache-airflow-providers-google==8.10.0

Apache Airflow version

2.7.3

Operating System

Debian 12

Deployment

Google Cloud Composer

Deployment details

No response

What happened

BigQueryCheckOperator doesn't support parameterized queries.

What you think should happen instead

BigQueryCheckOperator should support query parameters.

How to reproduce

This is a missing feature, so reproduction is not applicable.

Anything else

The various official BigQuery operators have inconsistent support for parameterized queries. Some, like BigQueryInsertJobOperator, have built-in support; others, like BigQueryCheckOperator, don't appear to support query parameters at all. The Google Cloud blog actually uses string formatting for passing parameters to demonstrate BigQueryCheckOperator.

SQL injection stubbornly remains in the OWASP top 10 vulnerabilities, as it has for decades. The way that we prevent SQL injection is to use query parameters.

Even though BigQueryCheckOperator is a read-only job that returns a boolean, meaning the potential attack surface is small to non-existent, it is less than ideal to have to use string formatting to introduce parameters for a number of reasons:

1. I want to build good habits in my team. "Never use string formatting to introduce parameters" sets a better precedent than "never use string formatting to introduce parameters except in BigQueryCheckOperator". Creating exceptions to the rule is normalization of deviance. It opens the door to introducing remote-exploitable injection vulnerabilities in places where it actually matters.
2. Security scanner tools will (correctly) flag these as potential SQL injection vulnerabilities. Scanners cannot have the context to understand that these are generally not exploitable.
3. A user of the library shouldn't have to decide whether to use query parameters or not; they should just be consistently available.

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[boring-cyborg]

Thanks for opening your first issue here! Be sure to follow the issue template! If you are willing to raise PR to address this issue please do so, no need to wait for approval.

[potiuk]

Feel free to implement it @aldenstpage

[aldenstpage]

Thanks, @potiuk. I've submitted a PR. #40558