skip to content

Why is building an expo-sqlite query with template-string interpolation dangerous, and how do ? and $name parameter bindings prevent SQL injection?

level: middleimportance: must knowfreq 55%

answer

  1. values travel separately from SQL text
  2. variadic, array or object params
  3. $name recommended, also :name and @name
  4. execAsync binds nothing
  5. identifiers cannot be bound, whitelist them

basics

~20 s

Interpolated input can become SQL code. expo-sqlite's bound parameters (? placeholders or $name keys) compile the statement first and pass values separately, so input stays data. execAsync binds nothing, so it must never receive input.

solid answer

~40 s

If I write ``db.getAllAsync(`SELECT * FROM expenses WHERE category = '${category}'`)``, a category like `x' OR '1'='1` changes the query itself: that is **SQL injection**. expo-sqlite's query methods take parameters instead. Positional `?` placeholders are bound from variadic arguments or an array; named placeholders are bound from an object whose keys include the prefix, for example `{ $month: '2026-09' }`; `:name` and `@name` work too, but `$name` is recommended because `$` is legal in JavaScript identifiers. The statement is prepared first and the values are bound afterwards, so SQLite never parses them as SQL. Bindable values are strings, numbers, `null`, booleans (stored as 1 or 0) and `Uint8Array`/`ArrayBuffer` blobs. Two limits: `execAsync` accepts no parameters, and identifiers such as a column name in `ORDER BY` cannot be bound, so those come from a whitelist.

go deeper

for a junior

Recall that values go in as parameters, never pasted into the SQL string, and that ? takes positional values while $name takes an object.

for a middle

Explain prepare-then-bind, the three parameter forms, prefixed object keys, the bindable value types, and why execAsync is excluded from user input.

for a senior

Handle what binding cannot cover: whitelisted identifiers and sort orders, generated IN lists, and a review checklist that catches interpolated values.

for a principal

Decide whether raw SQL, the db.sql tag or a query builder or ORM is the team default, trading injection safety by construction against flexibility.

## What goes wrong with interpolation A query string is **code**. When user input is pasted into it, the input can change the code: ```typescript // Dangerous: category comes from a text field const rows = await db.getAllAsync( `SELECT * FROM expenses WHERE category = '${category}'` ); ``` A category value of `x' OR '1'='1` turns the filter into one that matches every row; other payloads can do worse. On a phone the database is the user's own, but the input may come from anywhere: a deep link, a synced record, a pasted CSV. And a quote in an honest value such as `kids' toys` simply breaks the query. ## How binding fixes it With **parameter binding** the SQL text and the values travel separately. expo-sqlite's `runAsync`, `getFirstAsync`, `getAllAsync`, `getEachAsync`, their `*Sync` twins and prepared statements all **prepare** the statement first (SQLite compiles it with placeholders), then **bind** the values into those placeholders. The values are never parsed as SQL, so a quote is just a character in a string. ## The three ways to pass parameters | Form | SQL placeholder | JavaScript | When to use | |---|---|---|---| | variadic | `?` | `db.getAllAsync(sql, month, category)` | a few positional values | | array | `?` | `db.getAllAsync(sql, [month, category])` | values already in an array | | object | `$month`, `:month`, `@month` | `db.getAllAsync(sql, { $month: month })` | many values, or one used twice | With the object form the **keys include the prefix**: `{ $month: "2026-09" }`, not `{ month: "2026-09" }`. The documentation recommends `$name` because JavaScript accepts `$` in identifiers without quoting. ```typescript const rows = await db.getAllAsync<{ category: string; total: number }>( `SELECT category, SUM(amount_cents) AS total FROM expenses WHERE substr(spent_on, 1, 7) = $month GROUP BY category`, { $month: "2026-09" } ); ``` ## What values can be bound The bind value type is `string | number | null | boolean | Uint8Array | ArrayBuffer`: - **booleans** are converted to `1` or `0`; - **`undefined`** is sent as `null`; - **`Uint8Array` and `ArrayBuffer`** go into BLOB columns; - **dates** are not a bindable type, so store an ISO string or a number and convert yourself. ## Where binding does not reach 1. **`execAsync` has no parameters at all.** The source comment says the queries "are not escaped for you". Keep it for fixed scripts: schema creation, `PRAGMA`s, seed data you wrote. 2. **Identifiers and keywords cannot be placeholders.** A table name, a column name or `ASC`/`DESC` is part of the statement's structure, not a value. Binding `ORDER BY ?` binds a constant, so every row sorts the same. Map user choices onto a fixed set of known strings instead: ```typescript const SORTS = { newest: "spent_on DESC", largest: "amount_cents DESC" } as const; const orderBy = SORTS[choice] ?? SORTS.newest; const rows = await db.getAllAsync( `SELECT * FROM expenses WHERE substr(spent_on, 1, 7) = ? ORDER BY ${orderBy}`, month ); ``` 3. **`IN (...)` lists** need one placeholder per value; build the `?, ?, ?` string from the array length, never from the values. ## Binding lists and binary data Two cases need a little more care: ```typescript // IN (...) with a variable number of categories const placeholders = categories.map(() => "?").join(", "); const rows = await db.getAllAsync( `SELECT * FROM expenses WHERE category IN (${placeholders})`, categories ); // a receipt image stored as a BLOB await db.runAsync("UPDATE expenses SET receipt = ? WHERE id = ?", imageBytes, id); ``` The first builds only the **shape** of the SQL (a run of `?`) from the array's length; the values still travel as parameters. The second passes a `Uint8Array` directly, and reading the column later returns a `Uint8Array` again. ## The tagged-template shortcut Since expo-sqlite 55, `db.sql` offers a tagged template: ``await db.sql`SELECT * FROM expenses WHERE category = ${category}` ``. It looks like interpolation but is not: every `${}` becomes a bound `?`. The same rule about identifiers applies there too. ## Checklist for review - No template literal passed to a query method contains `${}` with a runtime value, except a whitelisted identifier. - No `execAsync` call contains runtime values. - Object parameters use prefixed keys that match the SQL.

  • Your object parameters are ignored and every value binds as NULL. What is the likely bug?
    The object keys lack the prefix used in the SQL. With `WHERE spent_on = $day` the object must be `{ $day: '2026-09-14' }`; a key of `day` matches no placeholder, so `$day` stays unbound and SQLite treats it as NULL.
  • How do you let users sort expenses by any column safely?
    Column names and sort directions cannot be bound, so map the user's choice onto a fixed whitelist of known `ORDER BY` fragments and interpolate only the whitelisted string. Values in the same query still go through placeholders.
  • Is SQL injection a real risk in a local, single-user mobile database?
    Yes, in two ways. Input can arrive from outside the user's control, such as a deep link, imported file or synced record, and can corrupt or delete their data. And even honest input with a quote breaks interpolated queries. Binding fixes both at no cost.

saying these in an interview costs you the question

  • Escaping quotes by hand instead of binding parameters
  • Passing runtime values into execAsync
  • Using object keys without the $ prefix for $name placeholders
  • Binding a column name or ASC/DESC with a ? placeholder
  • Believing a local database cannot suffer SQL injection