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. Query parameters can be used as substitutes for arbitrary expressions. Parameters cannot be used as substitutes for identifiers, column names, table names, or other parts of the query.
To specify a named parameter, use the @
character followed by an
identifier,
such as @param_name
. For example, this query finds all the words in a
specific Shakespeare corpus with counts that are at least the specified value.
#standardSQL SELECT word, word_count FROM `bigquery-public-data.samples.shakespeare` WHERE corpus = @corpus AND word_count >= @min_word_count ORDER BY word_count DESC
Alternatively, use the placeholder value ?
to specify a positional parameter.
Note that a query can use positional or named parameters but not both.
Console
Parameterized queries are not supported by the Cloud Console.
bq
Use --parameter
to provide values for parameters in the form
name:type:value
. An empty name produces a positional parameter.
The type may be omitted to assume STRING
.
The --parameter
flag must be used in conjunction with the flag
--use_legacy_sql=false
to specify standard SQL syntax.
(Optional) Specify your location using the
--location
flag.
bq query \ --use_legacy_sql=false \ --parameter=corpus::romeoandjuliet \ --parameter=min_word_count:INT64:250 \ 'SELECT word, word_count FROM `bigquery-public-data.samples.shakespeare` WHERE corpus = @corpus AND word_count >= @min_word_count ORDER BY word_count DESC'
API
To use named parameters, set the parameterMode
to NAMED
in the query
job
configuration.
Populate queryParameters
with the list of parameters in the query
job
configuration. Set the name
of each parameter with the @param_name
used in
the query.
Enable standard SQL syntax
by setting useLegacySql
to false
.
{
"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;",
"queryParameters": [
{
"parameterType": {
"type": "STRING"
},
"parameterValue": {
"value": "romeoandjuliet"
},
"name": "corpus"
},
{
"parameterType": {
"type": "INT64"
},
"parameterValue": {
"value": "250"
},
"name": "min_word_count"
}
],
"useLegacySql": false,
"parameterMode": "NAMED"
}
Try it in the Google APIs Explorer.
To use positional parameters, set the parameterMode
to POSITIONAL
in the
query
job configuration.
C#
Before trying this sample, follow the C# setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery C# API reference documentation.
To use named parameters:Before trying this sample, follow the C# setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery C# API reference documentation.
To use positional parameters:Go
Before trying this sample, follow the Go setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Go API reference documentation.
To use named parameters:Java
Before trying this sample, follow the Java setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Java API reference documentation.
To use named parameters:Node.js
Before trying this sample, follow the Node.js setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Node.js API reference documentation.
To use named parameters:Python
Before trying this sample, follow the Python setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Python API reference documentation.
To use named parameters:Using arrays in parameterized queries
To use an array type in a query parameter set the type to ARRAY<T>
where T
is the type of the elements in the array. Construct the value as a
comma-separated list of elements enclosed in square brackets, such as [1, 2,
3]
.
See the data types reference for more information about the array type.
Console
Parameterized queries are not supported by the Cloud Console.
bq
This query selects the most popular names for baby boys born in US states starting with the letter W.
bq query \ --use_legacy_sql=false \ --parameter='gender::M' \ --parameter='states:ARRAY<STRING>:["WA", "WI", "WV", "WY"]' \ 'SELECT name, SUM(number) AS count FROM `bigquery-public-data.usa_names.usa_1910_2013` WHERE gender = @gender AND state IN UNNEST(@states) GROUP BY name ORDER BY count DESC LIMIT 10'
Be careful to enclose the array type declaration in single quotes so that the
command output is not accidentally redirected to a file by the >
character.
API
To use an array-valued parameter set the
parameterType
to ARRAY
in
the query
job configuration.
If the array values are scalars set the parameterType
to the type of the values, such as STRING
. If the array values are
structures set this to STRUCT
and add the needed field definitions to
structTypes
.
For example, this query selects the most popular names for baby boys born in US states starting with the letter W.
{
"query": "SELECT name, sum(number) as count\nFROM `bigquery-public-data.usa_names.usa_1910_2013`\nWHERE gender = @gender\nAND state IN UNNEST(@states)\nGROUP BY name\nORDER BY count DESC\nLIMIT 10;",
"queryParameters": [
{
"parameterType": {
"type": "STRING"
},
"parameterValue": {
"value": "M"
},
"name": "gender"
},
{
"parameterType": {
"type": "ARRAY",
"arrayType": {
"type": "STRING"
}
},
"parameterValue": {
"arrayValues": [
{
"value": "WA"
},
{
"value": "WI"
},
{
"value": "WV"
},
{
"value": "WY"
}
]
},
"name": "states"
}
],
"useLegacySql": false,
"parameterMode": "NAMED"
}
C#
Before trying this sample, follow the C# setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery C# API reference documentation.
Go
Before trying this sample, follow the Go setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Go API reference documentation.
Java
Before trying this sample, follow the Java setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Java API reference documentation.
Node.js
Before trying this sample, follow the Node.js setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Node.js API reference documentation.
Python
Before trying this sample, follow the Python setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Python API reference documentation.
Using timestamps in parameterized queries
To use a timestamp in a query parameter, the underlying REST API takes a value
of type TIMESTAMP
in the format YYYY-MM-DD HH:MM:SS.DDDDDD time_zone
. If you
are using the client libraries, you create a built-in date object in that
language, and the library converts it to the right format. For more information,
see the following language-specific examples.
For more information about the TIMESTAMP
type, see the
data types reference.
Console
Parameterized queries are not supported by the Cloud Console.
bq
This query adds an hour to the timestamp parameter value.
bq query \ --use_legacy_sql=false \ --parameter='ts_value:TIMESTAMP:2016-12-07 08:00:00' \ 'SELECT TIMESTAMP_ADD(@ts_value, INTERVAL 1 HOUR)'
API
To use a timestamp parameter set the
parameterType
to TIMESTAMP
in the query job configuration.
This query adds an hour to the timestamp parameter value.
{
"query": "SELECT TIMESTAMP_ADD(@ts_value, INTERVAL 1 HOUR);",
"queryParameters": [
{
"name": "ts_value",
"parameterType": {
"type": "TIMESTAMP"
},
"parameterValue": {
"value": "2016-12-07 08:00:00"
}
}
],
"useLegacySql": false,
"parameterMode": "NAMED"
}
C#
Before trying this sample, follow the C# setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery C# API reference documentation.
Go
Before trying this sample, follow the Go setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Go API reference documentation.
Java
Before trying this sample, follow the Java setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Java API reference documentation.
Node.js
Before trying this sample, follow the Node.js setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Node.js API reference documentation.
Python
Before trying this sample, follow the Python setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Python API reference documentation.
Using structs in parameterized queries
To use a struct in a query parameter set the type to STRUCT<T>
where T
defines the fields and types within the struct. Field definitions are
separated by commas and are of the form field_name TF
where TF
is the type
of the field. For example, STRUCT<x INT64, y STRING>
defines a struct with a
field named x
of type INT64
and a second field named y
of type STRING
.
For more information about the STRUCT
type, see the
data types reference .
Console
Parameterized queries are not supported by the Cloud Console.
bq
This trivial query demonstrates the use of structured types by returning the parameter value.
bq query \ --use_legacy_sql=false \ --parameter='struct_value:STRUCT<x INT64, y STRING>:{"x": 1, "y": "foo"}' \ 'SELECT @struct_value AS s'
API
To use a struct parameter set the
parameterType
to STRUCT
in the query job configuration.
Add an object for each field of the struct to
structTypes
in the job's queryParameters
.
If the struct values are scalars set the
type
to the type of the values, such as STRING
. If the struct values are arrays
set this to ARRAY
, and set the nested arrayType
field to the appropriate
type. If the struct values are structures set type
to STRUCT
and add the
needed structTypes
.
This trivial query demonstrates the use of structured types by returning the parameter value.
{
"query": "SELECT @struct_value AS s;",
"queryParameters": [
{
"name": "struct_value",
"parameterType": {
"type": "STRUCT",
"structTypes": [
{
"name": "x",
"type": {
"type": "INT64"
}
},
{
"name": "y",
"type": {
"type": "STRING"
}
}
]
},
"parameterValue": {
"structValues": {
"x": {
"value": "1"
},
"y": {
"value": "foo"
}
}
}
}
],
"useLegacySql": false,
"parameterMode": "NAMED"
}
C#
The BigQuery client library for .NET does not support struct parameters.
Go
Before trying this sample, follow the Go setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Go API reference documentation.
Java
Before trying this sample, follow the Java setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Java API reference documentation.
Node.js
Before trying this sample, follow the Node.js setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Node.js API reference documentation.
Python
Before trying this sample, follow the Python setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Python API reference documentation.