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