google-bigqueryparameterized-query

Google Bigquery - Running parameterized queries - php


From Google Bigquery documentation:

Running parameterized queries

BigQuery supports query parameters to help prevent SQL injection when queries are constructed using user input. This feature is only available with standard SQL syntax.

To specify a named parameter, use the @ character followed by an identifier, such as @param_name.

And Google Bigquery has sample codes for python and Java to use parameterized queries.

https://cloud.google.com/bigquery/querying-data#bigquery-query-params-python

Google Bigquery does not have php sample codes for Running parameterized queries here.

I tried use @ in php like in there python and java codes, it does not work.

Any advice?

Thanks!

I added the codes as requests by Elliott and Mosha

Codes:

 $query = "SELECT * FROM [myproject.mydateset.users]  where user_id = '$userId' LIMIT 1000";
$queryResults = $this->bigQuery->runQuery($query);

This query is fine. But it is not preventing the sql injection.

I tried to change the query to

$query = "SELECT * FROM [myproject.mydateset.users]  where user_id = '@$userId' LIMIT 1000";

or

$query = "SELECT * FROM [myproject.mydateset.users]  where user_id = @$userId LIMIT 1000";

to prevent the sql injection. Both of the queries do not work.


Solution

  • I don't have a project set up to try this out, so I apologize if there are syntax errors or other oversights, but please see if this works. I based this on the PHP API in Github. You will need to make sure to use standard SQL for your query rather than legacy SQL.

    $bigQuery = new BigQueryClient([
        'projectId' => $projectId,
    ]);
    
    $query = "SELECT COUNT(DISTINCT word) AS distinct_words
    FROM `bigquery-public-data.samples.shakespeare`
    WHERE corpus = @corpus_name;";
    
    $queryResults = $bigQuery->runQuery(
        $query,
        ['useLegacySql' => false],
        ['queryParameter' => new QueryParameter([
           'name' => 'corpus_name',
           'parameterType' => new QueryParameterType([
             'type' => 'STRING',
           ]),
           'parameterValue' => new QueryParameterValue([
             'value' => 'kingrichardii',
           ]),
         ],
    );