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.
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
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.
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. |
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 BYdirection. 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, becausecol = NULLis never true. InINSERTit storesNULL. - Dates need conversion. A
Dateobject is not a supported value type, and if it is the only argument it is mistaken for the parameter object and bound asNULL. Convert it withtoISOString()orgetTime()first. INlists need one placeholder per item. Build?, ?, ?from the array length ("?".repeatjoined with commas) and spread the values.- Prefer named placeholders for statements with more than three or four values, because the call site documents itself.