Google Bigquery - Running parameterized queries - php

0
votes

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.

2
google-bigqueryparameterized-query
Can you share a sample of the PHP code that you tried to run? - Elliott Brossard
It would be helpful to see code snippets, particularly how you set parameter values; and more detailed explanation of what "does not work" means. - Mosha Pasumansky

2 Answers

2
votes

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',
       ]),
     ],
);
1
votes

I tried this and worked..[google-BigQuery]

$cloud = new ServiceBuilder([
    'keyFilePath' => 'project-auth-file.json'
]);

$bigQuery = $cloud->bigQuery();

 $query = 'select id 
             from `api-project-id.dbname.tablename` 
            where userId = @user_id;';

 $_userId = 202;

 $queryJobConfig = $bigQuery->query($query)
      ->parameters([
          'user_id' => (int)$_userId
      ]);

  $queryResults = $bigQuery->runQuery($queryJobConfig);

  foreach ($queryResults as $row) {
      echo "<br>". $row['id'];
  }

{google-BigQuery}