Use parameterized queries with InfluxQL

Parameterized queries in InfluxDB 3 Enterprise let you dynamically and safely change values in a query. If your application code allows user input to customize values or expressions in a query, use a parameterized query to make sure untrusted input is processed strictly as data and not executed as code.

Parameterized queries:

• help prevent injection attacks, which can occur if input is executed as code
• help make queries more reusable

In InfluxDB 3, a parameterized query is an InfluxQL or SQL query that contains one or more named parameter placeholders–variables that represent input data.

• Use parameters in WHERE expressions

• Parameter data types

  • Data type examples
  • Time expressions
  • Not compatible with parameters

• Parameterize an SQL query

• Execute parameterized InfluxQL queries

  • Use the HTTP API

  • Use InfluxDB Flight RPC clients

• Client support for parameterized queries

• Not supported

Use parameters in WHERE expressions

You can use parameters in WHERE clause predicate expressions-–for example, the following query contains a $temp parameter:

SELECT * FROM measurement WHERE temp > $temp

When executing a query, you specify parameter name-value pairs. The value that you assign to a parameter must be one of the parameter data types.

{"temp": 22.0}

The InfluxDB Querier parses the query text with the parameter placeholders, and then generates query plans that replace the placeholders with the values that you provide. This separation of query structure from input data ensures that input is treated as one of the allowed data types and not as executable code.

Parameter data types

A parameter value can be one of the following data types:

• Null
• Boolean
• Unsigned integer (u_int64)
• Integer (int64)
• Double (float64)
• String

Data type examples

{
  "string": "Living Room",
  "double": 3.14,
  "unsigned_integer": 1234,
  "integer": -1234,
  "boolean": false,
  "null": Null,
}

Time expressions

To parameterize time bounds, substitute a parameter for a timestamp literal–for example:

SELECT *
FROM home
WHERE time >= $min_time

For the parameter value, specify the timestamp literal as a string–for example:

// Assign a timestamp string literal to the min_time parameter.
parameters := influxdb3.QueryParameters{
    "min_time": "2022-01-01 00:00:00.00",
}

InfluxDB executes the query as the following:

SELECT *
FROM home
WHERE time >= '2022-01-01 00:00:00.00'

Not compatible with parameters

If you use parameters for the following, your query might not work as you expect:

• In clauses other than WHERE, such as SELECT or GROUP BY
• As function arguments, such as avg($temp)
• In place of identifiers, such as column or table names
• In place of duration literals, such as time > now() - $min_duration

Parameterize an SQL query

To use a parameterized query, do the following:

1. In your query text, use the $parameter syntax to reference a parameter name–for example, the following query contains $room and $min_temp parameter placeholders:

   SELECT *
   FROM home
   WHERE time > now() - 7d
   AND temp >= $min_temp
   AND room = $room

2. Provide a value for each parameter name. If you don’t assign a value for a parameter, InfluxDB returns an error. The syntax for providing parameter values depends on the client you use–for example:

   Go

   // Define a QueryParameters struct--a map of parameters to input values.
   parameters := influxdb3.QueryParameters{
       "room": "Kitchen",
       "min_temp": 20.0,
   }

After InfluxDB receives your request and parses the query, it executes the query as

SELECT *
FROM home
WHERE time > now() - 7d
AND temp >= 20.0
AND room = 'Kitchen'

Execute parameterized InfluxQL queries

Use the HTTP API

InfluxDB 3 Enterprise provides the /api/v3/query_influxql HTTP API endpoint for executing InfluxQL queries with parameters.

POST /api/v3/query_influxql

Send a JSON object that contains db (database), q (query), and params (parameter name-value pairs) properties in the request body.

The following example sends a parameterized InfluxQL query to the /api/v3/query_influxql endpoint:

<div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl">curl <span class="s2">&#34;http://localhost:8181/api/v3/query_influxql&#34;</span> <span class="se">\

Replace the following:

• DATABASE_NAME: the name of the database to query
• AUTH_TOKEN: your database token with permission to query the specified database