How to prevent query injection on Google Big Query

Question

I'm writing some Google Big-query dynamic reporting utilities to our website, that will allow users to select a parameter to be replaced in the query. Given this query "template":

SELECT  name ,
        birthday
FROM    [dataset.users]
WHERE   registration_date = '{{registration_date}}'

we take the {{registration_date}} value from the user and replace it in the template, resulting in a query:

SELECT  name ,
        birthday
FROM    [dataset.users]
WHERE   registration_date = '2013-11-11'

How I can prevent sql-injection like attacks in this scenario, given that I'm executing the queries using the Google Big-query client API, and the API don't allow one to use positioned parameters as on traditional RDBMS apis.

Answer 1

Check "Defense Option 3: Escaping All User Supplied Input" in OWASP:

https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet#Defense_Option_3:_Escaping_All_User_Supplied_Input

UPDATE: Parametrized queries are an option now

• https://cloud.google.com/bigquery/docs/parameterized-queries

Answer 2

Since the launch of standard SQL in BigQuery, it has been possible to use query parameters as a way of preventing SQL injection. In your query, you can specify named parameters using @ followed by a name, e.g.

SELECT x, y FROM T WHERE x <= @x_max AND y = @target_y;

You can then provide the parameter values through the query_parameters attribute of the API.