Repository navigation
Making parametrized queries in BigQuery should be easy #6386
Description
If you want developers to not make SQL injection vulnerabilities, you should make their job easier.
Look at sqlite3 API design:
c.execute('SELECT * FROM stocks WHERE symbol=?', t).
Compare this to BigQuery:
query = """
SELECT word, word_count
FROM `bigquery-public-data.samples.shakespeare`
WHERE corpus = @corpus
AND word_count >= @min_word_count
ORDER BY word_count DESC;
"""
query_params = [
bigquery.ScalarQueryParameter('corpus', 'STRING', 'romeoandjuliet'),
bigquery.ScalarQueryParameter('min_word_count', 'INT64', 250)
]
job_config = bigquery.QueryJobConfig()
job_config.query_parameters = query_params
query_job = client.query(
query,
# Location must match that of the dataset(s) referenced in the query.
location='US',
job_config=job_config) # API request - starts the query
Why I need to put query params into job config?
Why I need to mutate the job config?
Query params should be a query function parameter. Or at least part of QueryJobConfig constructor, so I can do:
query_job = client.query(
query,
# Location must match that of the dataset(s) referenced in the query.
location='US',
job_config=QueryJobConfig(query_pameters=query_params)) # API request - starts the query
Activity
tseaver commentedon Nov 5, 2018
@tswast, @alixhami RFC
tswast commentedon Nov 5, 2018
@usamec You can do exactly the same code as in SQLite with the google-cloud-bigquery API. See the DB-API module reference: https://googleapis.github.io/google-cloud-python/latest/bigquery/dbapi.html
That said, I agree that we could make it less verbose to create the query parameter configuration by using the same helper code we do in the DB-API module.
I chose to have very few parameters to the
query()method because there are many options in the API's query job resource, and it's not at all clear where to draw the line. Also, having a QueryJobConfig object matches much more closely to that of the underlying REST API.Regarding adding constructor options for each parameter, it would make the handwritten config class code harder to understand and make updates to. It means we'd need to map from constructor arguments to properties. It's also not at all clear where to draw the line for properties. There are many properties of the QueryJobConfig class and I think it'd be way to many arguments to have one for each property.
alixhami commentedon Nov 5, 2018
I like the idea of adding the properties to the constructor as optional arguments. I'll look into this now and see if I can find a way to do that without creating a big maintenance burden.
alixhami commentedon Nov 6, 2018
Thanks for the suggestion @usamec! Job config properties can now be specified in the constructor. The update made it into the release today - version 1.7.0 (changelog)