Stop interpolating SurrealQL: use prepared statements for safe, cached queries

The problem: interpolating user input into SurrealQL

Imagine an API endpoint that lists products in a category. A common first draft builds the query by concatenating the category name into the SurrealQL string:

let query = `SELECT * FROM product WHERE category = '${req.params.category}'`;

This works until a user sends a category value such as electronics'; DELETE product; --. SurrealDB will execute the injected statement because the client library sent one batch containing two statements. Even without malicious input, the database cannot reuse a query plan because every category produces a unique query string.

SurrealDB prepared statements solve both problems. You define the query once with $variable placeholders, bind values separately, and execute. The database treats bound values as data, not executable SurrealQL, and can cache the plan for repeated calls.

How PREPARE, BIND, and EXECUTE work

A prepared statement has three phases:

1. PREPARE: Send the SurrealQL template to the server. The server parses it and, if supported, caches an execution plan.
2. BIND: Supply values for each $variable placeholder. Binding happens outside the query string, so values cannot alter the statement structure.
3. EXECUTE: Run the prepared statement with the bound values and return results.

In the nsql REPL or over HTTP, the pattern looks like this:

PREPARE get_products SELECT * FROM product WHERE category = $category LIMIT $limit;
BIND get_products, $category='electronics', $limit=10;
EXECUTE get_products;

Run this in nsql after authenticating to a namespace and database, or send the equivalent JSON to SurrealDB's HTTP endpoint. The user or API key needs read access to the target namespace and database; no administrative permission is required for a SELECT.

Expected check: PREPARE should return without a syntax error. EXECUTE should return rows matching the bound category. If you bind the wrong number of values or use the wrong order, SurrealDB returns an error instead of silently substituting values.

Binding order matters

Placeholders are matched positionally, not by name, unless your client driver maps them explicitly. If your statement declares $category, $limit, the bind call must supply values in that same order. Swapping them causes a type or count error rather than a silent mix-up.

Pagination: PAGE versus OFFSET

SurrealDB supports two pagination styles. PAGE n uses key-based pagination, which remains stable when rows are inserted or deleted ahead of the current position. OFFSET n skips a fixed number of rows, which can shift as concurrent writes change the table.

Add pagination to the prepared statement:

PREPARE get_products_page SELECT * FROM product WHERE category = $category PAGE $page LIMIT $limit;
BIND get_products_page, $category='electronics', $page=1, $limit=20;
EXECUTE get_products_page;

For the next page, rebind $page to 2 and execute again. You do not need to re-prepare unless you change the query text.

Transforming rows with LET

SurrealQL's LET clause lets you compute values inside the prepared statement before returning them to the client. For example, you can derive a price in cents:

PREPARE get_product_summary SELECT * FROM product WHERE category = $category;
LET $price_cents = (price * 100);
RETURN { id, name, price_cents };

This keeps transformation logic close to the data and reduces post-processing in the application layer.

Trade-offs and production checks

Prepared statements are not free. The server caches plans by statement text, so if you run ALTER TABLE or change indexes, a cached plan can become stale. Re-prepare statements after DDL changes, or clear the client's prepared-statement cache in production deployments.

Parameter binding is strict. SurrealDB does not guess which placeholder corresponds to which value. A mismatch returns an error, which is safer than silent substitution but requires your application to track placeholder order carefully.

Practical verification: Run the same prepared statement twice with identical bindings. If plan caching is active, the server should reuse the plan for the second execution. Confirm this by checking the response metadata for a consistent plan identifier, depending on your client driver and SurrealDB version.

Closing

Stop building SurrealQL strings with user input. Define the query once with $variable placeholders, bind values separately, and choose PAGE for stable pagination or OFFSET when you need row-number navigation. Re-prepare after schema changes, and verify that repeated executions reuse the same plan. Your queries will be safer, faster, and easier to maintain.