v0.8.0: Variables, Tagging mods & Syntax highlighting →

Passing Parameters to Queries & Controls

A query may optionally define parameters. When executing the query, either from an interactive query session or from a control, you can specify values for the parameters to be used for the query execution.

Using Parameters in Queries

Variable usage and interpolation in Steampipe is based on and conforms to Terraform. Special consideration must be made for passing variables into queries, however, as both the HCL parser AND the SQL parser must account for the variables.

Internally, the Steampipe execution layer uses Postgres SQL Prepared Statements to define queries that accept parameters, and the execute command to run them. Note that when using SQL prepared statements, passed parameters are treated as values and SQL-injection is not possible (as long as you don't call unsafe functions from the body and pass parameters).

When defining a query, you may use positional parameters ($1, $2, $3, ...) in the query definition. For each of these positional parameters, you should define a param block that names and describes the parameter. Note that Steampipe will assign the parameters in the order that the param blocks are defined - the first param block describes $1, the second describes $2, etc:

query "instances_in_state" {
sql = "select instance_id, instance_state from aws_ec2_instance where instance_state = $1;"
param "state" {
default = "stopped"
}
}

You can also pass list values as parameters, and they will converted to postgres arrays in the query:

query "instances_in_states" {
sql = "select instance_id, instance_state from aws_ec2_instance where instance_state = any($1);"
param "states" {
default = ["stopped", "running"]
}
}

Passing Parameters

You can run a query or control by name from steampipe query. If the query provides defaults for all the parameters, you can run it without arguments in the same way you would run a query or control that takes no parameters, and it will run with the default values:

query.instances_in_state

If the query does not provide a default, or you wish to run the query with a different value, you can pass an argument to the query.

You can pass them by name:

query.instances_in_state(state => "running")

Or by position:

query.instances_in_state("running")

Using Parameters in Controls

Controls can refer to a parameterized query with the query argument, and can pass arguments to the query in the args argument:

query "instances_invalid_state" {
sql = <<-EOT
select
arn as resource,
case
when instance_state = any($1) then 'alarm'
else 'ok'
end as status,
instance_id || ' is ' || instance_state as reason,
region,
account_id
from
aws_ec2_instance
EOT
param "invalid_states" {
default = ["running"]
}
}
control "stopped_instances" {
title = "EC2 instances that are stopped"
query = query.instances_invalid_state
args = {
"invalid_states" = ["stopped", "stopping"]
}
}

Alternatively, you may specify inline sql for a control, and define parameters as you would for a query:

control "stopped_instances_inline" {
title = "Stopped EC2 instances"
sql = <<-EOT
select
arn as resource,
case
when instance_state = any($1) then 'alarm'
else 'ok'
end as status,
instance_id || ' is ' || instance_state as reason,
region,
account_id
from
aws_ec2_instance
EOT
param "invalid_states" {
default = ["stopped", "stopping"]
}
}

Note that you may either reference a query object with the query argument or use inline sql with the sql argument from your control, but not both, and the behavior is subtly different, as can be seen in the examples above:

  • The query argument is a reference to a query resource. You cannot define parameters (param blocks) for the control, but you can pass them as arguments (args) to the query, if the query has parameters defined.
  • The sql argument is a string. When the control specifies a sql string, it essentially behaves like a query, and thus you can define parameters (in param blocks) in the same manner as a query resource. You cannot specify args in this case, but you can set a default for the parameters which essentially accomplishes the same thing.

Using Parameters with Variables

It is common for arguments and parameter defaults to refer to input variables, so that users of the mod can change the values without modifying the code:

variable "bad_states" {
type = list(string)
default = ["stopped", "stopping"]
}
control "stopped_instances" {
title = "EC2 instances that are stopped"
query = query.instances_invalid_state
args = {
"invalid_states" = var.bad_states
}
}