Parameterized Queries

In the Simple Program tutorial, we learned how to execute basic queries. In real applications, you'll often execute queries based on user input or variable values. On this page, you'll learn how to safely handle parameters.

In ldbc, we strongly recommend using parameterized queries to prevent SQL injection attacks. Parameterized queries allow you to separate SQL code from data, enabling safer database access.

Parameter Basics

In ldbc, there are two main ways to embed parameters in SQL statements:

1. Dynamic parameters - Used as regular parameters, processed by PreparedStatement to prevent SQL injection attacks
2. Identifier escaping - Safely embeds table names and column names in backticks using the ident function

Adding Dynamic Parameters

First, let's create a query without parameters.

sql"SELECT name, email FROM user".query[(String, String)].to[List]

Next, let's incorporate the query into a method and add a parameter to select only data matching the user-specified id. We insert the id argument into the SQL statement as $id, just like string interpolation.

val id = 1

sql"SELECT name, email FROM user WHERE id = $id".query[(String, String)].to[List]

When we execute the query using a connection, it works without issues.

// Create Connector
val connector = Connector.fromDataSource(datasource)

sql"SELECT name, email FROM user WHERE id = $id"
  .query[(String, String)]
  .to[List]
  .readOnly(connector)

What's happening here? It looks like we're just dropping string literals into an SQL string, but we're actually building a PreparedStatement, and the id value is ultimately set by a call to setInt. This protects our application from SQL injection attacks.

You can use parameters of various types:

val id: Int = 1
val name: String = "Alice"
val active: Boolean = true
val createdAt: LocalDateTime = LocalDateTime.now()

sql"INSERT INTO user (id, name, active, created_at) VALUES ($id, $name, $active, $createdAt)"

In ldbc, appropriate encoders are provided for each type, safely converting Scala/Java values to SQL values.

Multiple Parameters

Multiple parameters can be used in the same way.

val id = 1
val email = "alice@example.com"

// Create Connector
val connector = Connector.fromDataSource(datasource)

sql"SELECT name, email FROM user WHERE id = $id AND email > $email"
  .query[(String, String)]
  .to[List]
  .readOnly(connector)

Combining Queries

When building large queries, you can combine multiple SQL fragments.

val baseQuery = sql"SELECT name, email FROM user"
val whereClause = sql"WHERE id > $id"
val orderClause = sql"ORDER BY name ASC"

val query = baseQuery ++ whereClause ++ orderClause

SQL Helper Functions

ldbc provides many helper functions for easily constructing complex SQL clauses.

Handling IN Clauses

A common challenge in SQL is using a series of values in an IN clause. In ldbc, this can be easily implemented using the in function.

val ids = NonEmptyList.of(1, 2, 3)

// Create Connector
val connector = Connector.fromDataSource(datasource)

(sql"SELECT name, email FROM user WHERE " ++ in("id", ids))
  .query[(String, String)]
  .to[List]
  .readOnly(connector)

This is equivalent to the following SQL:

SELECT name, email FROM user WHERE (id IN (?, ?, ?))

Note that ids must be a NonEmptyList because an IN clause cannot be empty.

Other Helper Functions

ldbc provides many other convenient functions:

Generating VALUES Clauses

val users = NonEmptyList.of(
  (1, "Alice", "alice@example.com"),
  (2, "Bob", "bob@example.com")
)

(sql"INSERT INTO user (id, name, email) " ++ values(users))

WHERE Clause Conditions

You can easily construct AND and OR conditions:

val activeFilter = sql"active = true"
val nameFilter = sql"name LIKE ${"A%"}"
val emailFilter = sql"email IS NOT NULL"

// WHERE (active = true) AND (name LIKE 'A%') AND (email IS NOT NULL)
val query1 = sql"SELECT * FROM user " ++ whereAnd(activeFilter, nameFilter, emailFilter)

// WHERE (active = true) OR (name LIKE 'A%')
val query2 = sql"SELECT * FROM user " ++ whereOr(activeFilter, nameFilter)

Generating SET Clauses

You can easily generate SET clauses for UPDATE statements:

val name = "New Name"
val email = "new@example.com"

val updateValues = set(
  sql"name = $name",
  sql"email = $email",
  sql"updated_at = NOW()"
)

sql"UPDATE user " ++ updateValues ++ sql" WHERE id = 1"

Generating ORDER BY Clauses

val query = sql"SELECT * FROM user " ++ orderBy(sql"name ASC", sql"created_at DESC")

Optional Conditions

When conditions are optional (may not exist), you can use functions with the Opt suffix:

val nameOpt: Option[String] = Some("Alice")
val emailOpt: Option[String] = None

val nameFilter = nameOpt.map(name => sql"name = $name")
val emailFilter = emailOpt.map(email => sql"email = $email")

// Since nameFilter is Some(...) and emailFilter is None, the WHERE clause will only contain "name = ?"
val query = sql"SELECT * FROM user " ++ whereAndOpt(nameFilter, emailFilter)

Identifier Escaping

Sometimes you may want to parameterize structural parts of the SQL statement, such as column names or table names. In such cases, use the ident function.

While dynamic parameters (regular $value) are processed by PreparedStatement and replaced with ? in the query string, ident wraps identifiers in backticks and embeds them directly into the SQL statement. It also removes NUL characters, allowing identifiers to be handled safely.

val column = "name"
val table = "user"

// Treating as a dynamic parameter would result in "SELECT ? FROM user"
// sql"SELECT $column FROM user".query[String].to[List]

// Using ident results in "SELECT `name` FROM `user`"
sql"SELECT ${ident(column)} FROM ${ident(table)}".query[String].to[List]

Common use cases for ident:

// Dynamic column selection
val sortColumn = "created_at"

sql"SELECT * FROM user ORDER BY ${ident(sortColumn)} DESC"

// Dynamic table selection
val schema = "public"
val table = "user"

sql"SELECT * FROM ${ident(schema)}.${ident(table)}"

Note: While ident escapes with backticks, it is recommended to use it only with trusted values (constants, configuration values, etc.). Avoid using user input directly as identifiers. If you want to validate a value as an identifier beforehand, use isSimpleIdentifier described below.

Parameter Escaping and sql_mode

Dynamic parameters are handled by PreparedStatement, so escaping is normally not something you need to think about. However, the client-side PreparedStatement of the ldbc connector (useServerPrepStmts = false, the default) assembles the query on the client, so the escaping depends on the server sql_mode.

ldbc tracks the session sql_mode and escapes string literals as follows.

In a session with NO_BACKSLASH_ESCAPES, a backslash is an ordinary character, so \' does not neutralize the quote. ldbc therefore switches to doubling the quote.

The sql_mode is read when the connection is established and then updated from every OK/EOF packet received from the server. A SET SESSION sql_mode = ... issued after connecting is reflected in subsequent query construction.

No configuration is required on your side.

Quoting Identifiers and Values (JDBC 4.3 compatible API)

The quoting methods standardised in JDBC 4.3 are available on Statement and PreparedStatement. Use them when assembling SQL statements outside the sql interpolator, or when you need compatibility with the standard JDBC API.

for
  stmt <- conn.createStatement()
  a    <- stmt.enquoteLiteral("G'Day")              // 'G''Day'
  b    <- stmt.enquoteIdentifier("my table", false) // `my table`
  c    <- stmt.enquoteIdentifier("user", true)      // `user`
  d    <- stmt.enquoteNCharLiteral("Hello")         // N'Hello'
  e    <- stmt.isSimpleIdentifier("user_name")      // true
  f    <- stmt.isSimpleIdentifier("select")         // false (reserved word)
yield ()

Following the MySQL rules, isSimpleIdentifier treats an identifier as simple when it consists only of [0-9a-zA-Z$_] or extended characters (U+0080 and above), is not made up solely of digits, is at most 64 characters long, and is not a reserved word. When the ANSI_QUOTES sql_mode is enabled, the identifier quote character is " rather than a backtick.

Note: to embed an identifier inside the sql interpolator, keep using ident. These methods require a Statement instance, so they are mainly for assembling SQL statements as strings.

Conditional SQL Fragments

When you want to conditionally append a SQL fragment, use the when function.

val limit: Option[Int] = Some(10)

sql"SELECT name, email FROM user" ++ when(limit.isDefined)(sql" LIMIT ${limit.get}")

when(condition)(fragment) appends fragment only when condition is true. When false, it produces an empty fragment.

You can combine multiple conditions:

val nameFilter: Option[String] = Some("Alice")
val activeOnly: Boolean = true

val query =
  sql"SELECT * FROM user" ++
  when(nameFilter.isDefined)(sql" WHERE name = ${nameFilter.get}") ++
  when(activeOnly)(sql" AND active = true")

Pagination

For list queries that commonly require LIMIT / OFFSET, use the paginate function for concise pagination.

// Specify both limit and offset
sql"SELECT name, email FROM user " ++ paginate(limit = 20, offset = 40)
// → SELECT name, email FROM user LIMIT ? OFFSET ?

// Specify limit only
sql"SELECT name, email FROM user " ++ paginate(limit = 20)
// → SELECT name, email FROM user LIMIT ?

Example calculating offset from a page number:

val pageSize = 20
val page     = 3  // 1-based

sql"SELECT name, email FROM user ORDER BY id " ++ paginate(limit = pageSize, offset = (page - 1) * pageSize)

Note: Passing a negative value for limit or offset throws an IllegalArgumentException.

Next Steps

Now you understand how to use parameterized queries. With the ability to handle parameters, you can build more complex and practical database queries.

Next, proceed to Selecting Data to learn how to retrieve data in various formats.