Skip to content
Tutorials Center
My learning

Topics

Node.js Reference ยท node:sqlite and Runtime Services

Named and positional parameters in node:sqlite

How to bind values in node:sqlite: ? and ?NNN placeholders, :name, @name and $name, mixing styles, unknown names, NULL defaults and why parameters stop SQL injection.

A parameter is a placeholder in SQL text that you fill with a value when the statement runs. Instead of writing WHERE name = 'Ada' you write WHERE name = ? and pass "Ada" to run(), get(), all() or iterate(). SQLite receives the SQL and the value separately, so the value is always treated as data and can never be read as SQL. That is the reason parameters, not string building, are the cure for SQL injection.

There are two styles. Positional placeholders (? or ?NNN) are filled from the values you list. Named placeholders (:name, @name or $name) are filled from the keys of an object. Both can appear in one statement.

NODE
db.prepare("SELECT * FROM users WHERE name = ? AND age > ?").all("Ada", 30);
db.prepare("SELECT * FROM users WHERE name = :name").all({ name: "Ada" });

Try it

Node.js Positional, numbered and named

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Node.js Mistakes that pass silently or fail loudly

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Syntax

Placeholder Filled from Notes
? The next positional value Numbered automatically from 1 in the order they appear.
?NNN The positional value number NNN (1-based) The same number can appear several times.
:name The key name (or ":name") of the parameter object Standard SQLite spelling.
@name The key name (or "@name") Same as :name.
$name The key name (or "$name") Same as :name.
NODE
statement.run([namedParameters][, ...anonymousParameters])

The optional object must come first, followed by any positional values. A statement can combine the two, as the first example does, but the clearest style is one kind per statement.

Settings.

Setting Effect
setAllowBareNamedParameters(flag) When true (the default) the key a matches :a, @a or $a. When false the key must carry the prefix.
setAllowUnknownNamedParameters(flag) When true, object keys that match no placeholder are ignored instead of throwing. Default false.

Errors. A key that matches no placeholder throws ERR_INVALID_STATE ("Unknown named parameter"). More positional values than placeholders throws ERR_SQLITE_ERROR ("column index out of range"). A value that cannot be bound, such as undefined or a Symbol, throws ERR_INVALID_ARG_TYPE. A bigint too large for 64 bits throws ERR_INVALID_ARG_VALUE.

Usage notes

  • Parameters stop injection. Put every value from outside your program in a placeholder. Never splice text into SQL, even "trusted" numbers, unless you have validated them yourself.
  • Placeholders are values only. They cannot replace a table name, a column name, a keyword or an ORDER BY direction. Check these against a fixed list in JavaScript and build the text from that.
  • Missing means NULL. Forgetting an argument does not raise an error. A forgotten value in WHERE col = ? matches nothing, because col = NULL is never true. In INSERT it stores NULL.
  • Dates need conversion. A Date object is not a supported value type, and if it is the only argument it is mistaken for the parameter object and bound as NULL. Convert it with toISOString() or getTime() first.
  • IN lists need one placeholder per item. Build ?, ?, ? from the array length ("?".repeat joined with commas) and spread the values.
  • Prefer named placeholders for statements with more than three or four values, because the call site documents itself.