Prepared Statements

SereneDB supports prepared statements where parameters are substituted when the query is executed. This can improve readability and is useful for preventing SQL injections.

Syntax

There are three syntaxes for denoting parameters in prepared statements: auto-incremented (?), positional ($1), and named ($param). Note that not all clients support all of these syntaxes, e.g., the JDBC client only supports auto-incremented parameters in prepared statements.

Example Dataset

In the following, we introduce the three different syntaxes and illustrate them with examples using the following table.

SQL

CREATE TABLE person (name VARCHAR, age BIGINT);
INSERT INTO person VALUES ('Alice', 37), ('Ana', 35), ('Bob', 41), ('Bea', 25);

In our example query, we'll look for people whose name starts with a B and are at least 40 years old. This will return a single row <'Bob', 41>.

Auto-Incremented Parameters: ?

SereneDB supports using prepared statements with auto-incremented indexing, i.e., the position of the parameters in the query corresponds to their position in the execution statement. For example:

SQL

PREPARE query_person AS    SELECT *    FROM person    WHERE starts_with(name, ?)      AND age >= ?;

Using the CLI client, the statement is executed as follows.

SQL

EXECUTE query_person('B', 40);

 name | age
------+-----
 Bob  |  41

Positional Parameters: $1

Prepared statements can use positional parameters, where parameters are denoted with an integer ($1, $2). For example:

SQL

PREPARE query_person AS    SELECT *    FROM person    WHERE starts_with(name, $2)      AND age >= $1;

Using the CLI client, the statement is executed as follows. Note that the first parameter corresponds to $1, the second to $2, and so on.

SQL

EXECUTE query_person(40, 'B');

 name | age
------+-----
 Bob  |  41

Named Parameters: $parameter

SereneDB also supports named parameters where parameters are denoted with $parameter_name. For example:

SQL

PREPARE query_person AS    SELECT *    FROM person    WHERE starts_with(name, $name_start_letter)      AND age >= $minimum_age;

Using the CLI client, the statement is executed as follows.

SQL

EXECUTE query_person(name_start_letter := 'B', minimum_age := 40);

 name | age
------+-----
 Bob  |  41

Dropping Prepared Statements: DEALLOCATE

To drop a prepared statement, use the DEALLOCATE statement:

SQL

DEALLOCATE query_person;

Alternatively, use:

SQL

DEALLOCATE PREPARE query_person;