Concepts > Parameters

Parameters

Parameters in Rockset are a mechanism for securely passing variables into SQL Queries. Query Lambdas support receiving parameters at runtime and setting default values for parameters. This document explains how to use Parameters in Rockset SQL Queries and Query Lambdas.

When to use Parameters

Use parameters to safely specify literal values in your SQL at runtime. Parameters are automatically escaped and are thus safe from SQL injection attacks. Instead of inserting values into a SQL string manually (eg. by string concatenation), you should use parameters.

Execute a Parameterized Query

From the Console

  1. Navigate to the Query Editor on the console. Navigate to the Parameters tab in the lower section below the query editor Parameters
  2. Click “Add Parameter”
  3. In the modal, name your parameter, select a type from the list of Supported Types, and provide a value for your parameter. Submit the modal to finish creating the parameter. An example screenshot is provided below.
    1. Make sure that the value of your parameter is formatted correctly for the type. Formats are specified in the Supported Types section below, as well as in supporting text in the modal parameters modal
  4. Write a SQL query that uses your parameter. To use parameter foo in your SQL, you can insert the text :foo anywhere in the SQL. Rockset will interpret this value as a literal of the type you specified, so you don’t need to put quotes around it. Eg: SELECT :foo.
  5. Execute your new query.

From the API

Both the Query Endpoint and Query Lambdas support parameters out of the box. Please refer to those docs for an example of executing a query with parameters.

Creating a Query Lambda with Default Parameters

Query Lambdas support saving default values for parameters. These default values are used when the Query Lambda is invoked at runtime, if an explicit value for that parameter is not passed. Default values are not required for parameters in Query Lambdas.

First follow the steps to execute a query from the console.
  1. Click “Create Query Lambda” in the blue command bar above the query editor. Name your Query Lambda and optionally set a default value for the parameters you defined. You can also select “No Default Value” if you want the lambda to fail when a certain parameter isn’t specified. Submit the form to finish creating a Query Lambda. You should land on the Query Lambda Details page. Default Parameters
  2. Select an API Key and copy paste your desired code snippet from the Query Lambda Details page. These snippets contain default values for the parameters that should be replaced by the values you intend to call the Query Lambda with. Test that the lambda returns the output you expected, based on the parameters specified or omitted.

Supported Types for Parameters

TypeExample ValueFormat
stringfoo
int0
float0
date2020-01-01YYYY-[M]M-[D]D
datetime2020-01-01 00:00:00YYYY-[M]M-[D]D[( )[H]H:[M]M:[S]S[.DDDDDD]]
time00:00:00[H]H:[M]M:[S]S[.DDDDDD]
timestamp2020-01-01T00:00:00.000000ZYYYY-[M]M-[D]D[(T)[H]H:[M]M[:[S]S[.DDDDDD]]][time zone]