sql-escaper
`sql-escaper` is a MySQL-oriented client-side SQL formatter derived from `sqlstring`. It converts JavaScript strings, numbers, booleans, dates, binary data, arrays, sets, maps, and selected objects into SQL literals; quotes identifiers with backticks; replaces `?` value and `??` identifier placeholders; and permits deliberately raw fragments. Its AST-aware formatter limits object expansion to `SET` and `ON DUPLICATE KEY UPDATE` contexts, addressing a query-shape injection problem in the older mysql/sqlstring combination. It is not a database driver or a prepared-statement implementation.
This is a thoughtful security-focused successor to sqlstring, especially for mysqljs/mysql overrides and unavoidable client-side formatting. Do not install it beside a current MySQL2 driver or mistake its convenient placeholders for prepared statements.
Use it if
- You maintain mysqljs/mysql and want the documented npm override that swaps its `sqlstring` dependency for the safer compatible formatter
- You directly format MySQL SQL in Node, Bun, or Deno and need sqlstring-compatible APIs plus TypeScript, BigInt, Uint8Array, Map, Set, or Temporal support
- You intentionally generate auditable MySQL strings for a driver API that cannot use server-side placeholders
- You need context-aware object handling that expands assignments in SET clauses but stringifies objects in ordinary value positions
- You use MySQL2 3.17.0 or newer: its README says SQL Escaper is already the default escaping library, so a direct dependency duplicates what the driver provides
- Your driver supports bound parameters or prepared statements: those keep data separate from SQL and are safer than generating a complete query string client-side
- Your server enables MySQL NO_BACKSLASH_ESCAPES: the README says this library's escaping works only when that mode is disabled
- You target PostgreSQL, SQLite, SQL Server, or a mixed-dialect application: backtick identifiers, literal rules, and context parsing here are designed around MySQL syntax
- You need placeholders inside SQL comments or quoted strings left alone: format replaces every `?` it recognizes there too, so templates containing literal question marks are a poor fit
Setup reality
The package itself is easy to install: no dependencies, bundled types, dual ESM/CommonJS exports, and declared support for Node 12+, Bun 1+, and Deno 2+. The difficult part is proving that string generation is appropriate. Prefer your driver's parameter API first. If you use MySQL2 3.17.0 or newer, do nothing because it already delegates escaping to this library. For mysqljs/mysql 2.18.1, the README documents an npm override from `sqlstring` to `npm:sql-escaper`, followed by a clean reinstall; test the lockfile and generated queries because that changes a transitive security boundary. The `format()` placeholders resemble prepared statements but are not: they are replaced locally before the string reaches MySQL. All question-mark placeholders are candidates, including ones in comments and string literals. The escaping contract assumes `NO_BACKSLASH_ESCAPES` is disabled. Validate structured input even though 1.5.1 protects ordinary value positions, because arrays expand to lists and objects deliberately expand inside `SET` and `ON DUPLICATE KEY UPDATE`. Pass `stringifyObjects: true` to `escape()` when a value position must never expand. Reject `NaN` and `Infinity` yourself because the library emits them unchanged and MySQL rejects them. Treat `raw()` and objects with `toSqlString()` as trusted-code-only escape hatches. Date output uses the local timezone unless you pass one, invalid dates become `NULL`, and explicit UTC is usually the least surprising production default.
Patterns
Escape a scalar valueescape-scalar-value
import { escape } from 'sql-escaper';
const literal = escape("O'Reilly", true);
// => "'O\\'Reilly'"Passing true prevents plain objects from expanding into assignments. Prefer driver parameters when the value is going straight to a query.
Quote a qualified identifierescape-identifier
import { escapeId } from 'sql-escaper';
const column = escapeId('users.display_name');
// => '`users`.`display_name`'Use escapeId only for trusted, validated identifier choices. Values need escape or a `?` placeholder, not backticks.
Format an identifier and value togetherformat-values-and-identifiers
import { format } from 'sql-escaper';
const sql = format(
'SELECT * FROM ?? WHERE id = ?',
['users', 42],
);
// => 'SELECT * FROM `users` WHERE id = 42'`??` consumes an identifier and `?` consumes a value. This returns a finished SQL string, not a statement with bound parameters.
Expand a validated object in SETformat-set-clause
const patch = { name: 'Ada', email: 'ada@example.com' };
const sql = format(
'UPDATE users SET ? WHERE id = ?',
[patch, 42],
);Objects expand only in recognized SET or ON DUPLICATE KEY UPDATE contexts. Allowlist keys before accepting an object derived from request data.
Format an insert with duplicate-key updatesformat-upsert
const sql = format(
'INSERT INTO users (email, name) VALUES (?, ?) ON DUPLICATE KEY UPDATE ?',
[
'ada@example.com',
'Ada',
{ name: 'Ada' },
],
);The final object is expanded because the AST recognizes the duplicate-key update clause. Validate its keys just as you would a SET patch.
Format a value list for INformat-in-list
const ids = [3, 5, 8];
const sql = format(
'SELECT * FROM users WHERE id IN (?)',
[ids],
);
// => 'SELECT * FROM users WHERE id IN (3, 5, 8)'Handle an empty array before formatting because `IN ()` is invalid SQL and may require an always-false branch instead.
Format rows for a bulk insertformat-bulk-values
const rows = [
[1, 'Ada'],
[2, 'Grace'],
];
const sql = format(
'INSERT INTO users (id, name) VALUES ?',
[rows],
);Nested arrays become grouped value lists. Check that every row has the expected length and enforce a batch-size limit.
Render a Date in UTCescape-date-utc
const createdAt = new Date('2026-08-08T12:00:00.000Z');
const sqlDate = escape(createdAt, true, 'Z');
// => "'2026-08-08 12:00:00.000'"Pass a timezone explicitly to avoid host-dependent output. An invalid Date is converted to NULL instead of throwing.
Render binary data as a MySQL hex literalescape-binary-data
const bytes = new Uint8Array([0, 1, 254, 255]);
const literal = escape(bytes, true);
// => "X'0001feff'"Buffer and Uint8Array values become hex literals. Apply application-level size limits before constructing a large SQL string.
Insert a fixed SQL functionuse-trusted-raw-fragment
import { raw } from 'sql-escaper';
const sql = format(
'UPDATE jobs SET processed_at = ? WHERE id = ?',
[raw('NOW()'), 42],
);raw skips all escaping. Never pass request data, configuration strings, or user-selected expressions to it.
Replace mysqljs/mysql's sqlstring dependencyoverride-sqlstring
{
"dependencies": {
"mysql": "^2.18.1"
},
"overrides": {
"sqlstring": "npm:sql-escaper"
}
}This is the README's npm override path for mysqljs/mysql. Clean-install dependencies, inspect the lockfile, and run query regression tests afterward.
Reject numbers MySQL cannot storereject-nonfinite-number
function finiteSqlNumber(value: number): string {
if (!Number.isFinite(value)) {
throw new TypeError('Expected a finite number');
}
return escape(value, true);
}The library leaves NaN and Infinity unchanged, but MySQL does not support those literals and will reject the query.
Alternatives
| Package | Registry | Pick it when |
|---|---|---|
| mysql2 | npm | Choose the full MySQL driver and its prepared-statement API; current releases already use SQL Escaper internally |
| sqlstring | npm | Keep it only for legacy compatibility when the object-expansion behavior and old Node support are required and inputs are tightly controlled |
| pg-format | npm | Choose it for PostgreSQL-specific identifier and literal formatting rather than applying MySQL quoting rules |
| slonik | npm | Choose it for PostgreSQL when tagged SQL, enforced parameterization, and a full connection layer fit the application |