SQL Escaper is a rework of sqlstring (created by Douglas Wilson) that reads the structure of each query to decide how its values are formatted.
- TypeScript by default.
- Support for
Uint8Array,BigInt, andTemporal. - Support for both CJS and ESM exports.
- Up to ~44% faster than sqlstring.
- Tells a real
SETorON DUPLICATE KEY UPDATEclause apart from the same words in a value, a column name, or another statement. - Support for the
NO_BACKSLASH_ESCAPESSQL mode (sqlstring only escapes correctly with it disabled). - Support from Node.js 12 onward (sqlstring supports Node.js 0.6).
Tip
SQL Escaper has the same API as the original sqlstring 1, so it can be used as a drop-in replacement. If it breaks any sqlstring usage, please report it as a bug.
Important
🔐 SQL Escaper is intended to fix a potential SQL Injection vulnerability reported in 2022. When the original sqlstring is combined with mysqljs/mysql or MySQL2, objects passed as values can be expanded into SQL fragments, potentially allowing attackers to manipulate the query structure. See sidorares/node-mysql2#4051 for details.
1 In format, regardless of the stringifyObjects value, objects used outside of SET or ON DUPLICATE KEY UPDATE contexts are stringified as '[object Object]' unless you mark them with expand. This is a security measure against that vulnerability and is not considered a breaking change for sqlstring usage.
# Node.js
npm i sql-escaper# Bun
bun add sql-escaper# Deno
deno add npm:sql-escaperMySQL2 uses SQL Escaper as its default escaping library since version 3.17.0, so you only need to update it to the latest version:
npm i mysql2@latestYou can use an override in your package.json:
"dependencies": {
"mysql": "^2.18.1"
},
"overrides": {
"sqlstring": "npm:sql-escaper"
}Next, clean the node_modules and reinstall the dependencies (npm i).
For up-to-date documentation, always follow the README.md in the GitHub repository.
import { escape, escapeId, expand, format, raw } from 'sql-escaper';
escape("Hello 'World'", true);
// => "'Hello \\'World\\''"
escapeId('table.column');
// => '`table`.`column`'
format('SELECT * FROM ?? WHERE id = ?', ['users', 42]);
// => 'SELECT * FROM `users` WHERE id = 42'
format('INSERT INTO users SET ?', [
expand({ name: 'foo', email: 'bar@test.com' }, ['name', 'email']),
]);
// => "INSERT INTO users SET `name` = 'foo', `email` = 'bar@test.com'"
format('SELECT * FROM users WHERE ?', [expand({ id: 42 })]);
// => 'SELECT * FROM users WHERE `id` = 42'
escape(raw('NOW()'), true);
// => 'NOW()'With CommonJS, load the same functions with require:
const { escape, escapeId, expand, format, raw } = require('sql-escaper');Escapes a value for safe use in SQL queries.
escape(value: SqlValue, stringifyObjects?: boolean, timezone?: Timezone, sqlMode?: SqlMode): stringescape(undefined, true); // 'NULL'
escape(null, true); // 'NULL'
escape(true, true); // 'true'
escape(false, true); // 'false'
escape(5, true); // '5'
escape("Hello 'World", true); // "'Hello \\'World'"Dates are converted to YYYY-MM-DD HH:mm:ss.sss format:
escape(new Date(2012, 4, 7, 11, 42, 3, 2), true);
// => "'2012-05-07 11:42:03.002'"Invalid dates return NULL:
escape(new Date(NaN), true); // 'NULL'You can specify a timezone:
const date = new Date(Date.UTC(2012, 4, 7, 11, 42, 3, 2));
escape(date, true, 'Z'); // "'2012-05-07 11:42:03.002'"
escape(date, true, '+01'); // "'2012-05-07 12:42:03.002'"
escape(date, true, '-05:00'); // "'2012-05-07 06:42:03.002'"Among Temporal values, Temporal.Instant and Temporal.ZonedDateTime are absolute points in time and honor the timezone argument exactly like Date, with millisecond precision:
const instant = Temporal.Instant.from('2012-05-07T11:42:03.002Z');
escape(instant, true, 'Z'); // "'2012-05-07 11:42:03.002'"
escape(instant, true, '+0200'); // "'2012-05-07 13:42:03.002'"Temporal.PlainDateTime, Temporal.PlainDate, and Temporal.PlainTime are wall-clock values and are emitted verbatim as DATETIME / DATE / TIME literals, ignoring timezone:
escape(Temporal.PlainDate.from('2012-05-07')); // "'2012-05-07'"
escape(Temporal.PlainTime.from('11:42:03')); // "'11:42:03'"Buffers are converted to hex strings:
escape(Buffer.from([0, 1, 254, 255]), true);
// => "X'0001feff'"When stringifyObjects is set to a non-nullish value (recommended), objects are stringified instead of being expanded into key = value pairs, unless marked with expand:
escape({ a: 'b' }, true);
// => "'[object Object]'"Objects with a toSqlString method will have that method called:
escape({ toSqlString: () => 'NOW()' }, true);
// => 'NOW()'Without stringifyObjects, plain objects are converted to key = value pairs (discouraged):
escape({ a: 'b', c: 'd' });
// => "`a` = 'b', `c` = 'd'"Function properties in objects are ignored (discouraged), and an object left without a key = value pair throws a TypeError:
escape({ a: 'b', c: () => {} });
// => "`a` = 'b'"
escape({ c: () => {} }); // throws TypeErrorCaution
Without stringifyObjects, an object reaching escape where a value is expected reshapes the query:
const userInput = { id: true }; // e.g. JSON.parse('{"id":true}')
/** Unsafe 🔓 (value position) */
'DELETE FROM entries WHERE id = ' + escape(userInput);
// => "DELETE FROM entries WHERE id = `id` = true"
// `id` = `id` is always true, so every row is deleted ❗️
/** Valid ✅ (SET assignment) */
'UPDATE users SET ' + escape({ name: 'foo', role: 'admin' });
// => "UPDATE users SET `name` = 'foo', `role` = 'admin'"Tip
Instead, format expands an object only where it is safe (e.g., a SET assignment) or marked with expand, and stringifies it everywhere else, so the same input cannot reshape the query:
const userInput = { id: true }; // e.g. JSON.parse('{"id":true}')
/** Unsafe 🔐 (value position) */
format('DELETE FROM entries WHERE id = ?', [userInput]);
// => "DELETE FROM entries WHERE id = '[object Object]'"
// stringified to an inert value
/** Valid ✅ (SET assignment) */
format('UPDATE users SET ?', [{ name: 'foo', role: 'admin' }]);
// => "UPDATE users SET `name` = 'foo', `role` = 'admin'"
// expanded on purposeArrays are turned into comma-separated lists:
escape([1, 2, 'c'], true);
// => "1, 2, 'c'"Nested arrays are turned into grouped lists (useful for bulk inserts):
escape(
[
[1, 2, 3],
[4, 5, 6],
],
true
);
// => '(1, 2, 3), (4, 5, 6)'Sets are treated like arrays:
escape(new Set([1, 2, 3]), true);
// => '1, 2, 3'Escapes an identifier (database, table, or column name).
escapeId(value: SqlValue, forbidQualified?: boolean): stringescapeId('id');
// => '`id`'
escapeId('table.column');
// => '`table`.`column`'
escapeId('i`d');
// => '`i``d`'Qualified identifiers (with .) can be forbidden:
escapeId('id1.id2', true);
// => '`id1.id2`'Arrays are turned into comma-separated identifier lists:
escapeId(['a', 'b', 't.c']);
// => '`a`, `b`, `t`.`c`'Formats a SQL query by replacing ? placeholders with escaped values and ?? with escaped identifiers.
format(sql: string, values?: SqlValue | SqlValue[], stringifyObjects?: boolean, timezone?: Timezone, sqlMode?: SqlMode): stringformat('SELECT * FROM ?? WHERE id = ?', ['users', 42]);
// => 'SELECT * FROM `users` WHERE id = 42'
format('? and ?', ['a', 'b']);
// => "'a' and 'b'"Triple (or more) question marks are ignored:
format('? or ??? and ?', ['foo', 'bar', 'fizz', 'buzz']);
// => "'foo' or ??? and 'bar'"If no values are provided, the SQL is returned unchanged:
format('SELECT ??');
// => 'SELECT ??'Warning
The keys of an object expanded here become column names, so a request body bound as it arrives lets the client choose which columns the query changes. Set stringifyObjects to true so that only marked objects expand, and mark such an object with the columns it may expand through expand.
When stringifyObjects is falsy, objects used in SET or ON DUPLICATE KEY UPDATE contexts are expanded into key = value pairs as escape expands them:
format('UPDATE users SET ?', [{ name: 'foo', email: 'bar@test.com' }]);
// => "UPDATE users SET `name` = 'foo', `email` = 'bar@test.com'"
format(
'INSERT INTO users (name, email) VALUES (?, ?) ON DUPLICATE KEY UPDATE ?',
['foo', 'bar@test.com', { name: 'foo', email: 'bar@test.com' }]
);
// => "INSERT INTO users (name, email) VALUES ('foo', 'bar@test.com') ON DUPLICATE KEY UPDATE `name` = 'foo', `email` = 'bar@test.com'"When stringifyObjects is truthy, objects are stringified unless marked with expand:
format('UPDATE users SET ?', [{ name: 'foo' }], true);
// => "UPDATE users SET '[object Object]'"Maps are treated like plain objects, preserving insertion order:
format('UPDATE users SET ?', [
new Map([
['name', 'foo'],
['email', 'bar@test.com'],
]),
]);
// => "UPDATE users SET `name` = 'foo', `email` = 'bar@test.com'"Outside of SET or ON DUPLICATE KEY UPDATE, a Map is stringified as '[object Map]' unless marked with expand:
format('SELECT * FROM users WHERE data = ?', [new Map([['id', 1]])]);
// => "SELECT * FROM users WHERE data = '[object Map]'"When the server runs with the ANSI_QUOTES SQL mode, double quotes delimit identifiers instead of strings. Set ansiQuotes so format reads them the same way, or a double-quoted identifier that ends in a backslash can misplace the values after it:
const sqlMode = { ansiQuotes: true };
format('SELECT "dir\\" FROM t WHERE id = ?', [1], false, 'local', sqlMode);
// => 'SELECT "dir\\" FROM t WHERE id = 1'When the server runs with the NO_BACKSLASH_ESCAPES SQL mode, a backslash in a string is an ordinary character, so the default escaping lets a value end its string early. Set noBackslashEscapes to write every value in a form both modes read the same way, in escape and format:
const sqlMode = { noBackslashEscapes: true };
format('SELECT ? AS dir, ? AS name', ['C:\\', "it's"], false, 'local', sqlMode);
// => "SELECT REPLACE('C:\\%', '\\%', LEFT('\\%', 1)) AS dir, 'it''s' AS name"
format("SELECT 'C:\\' AS dir, ? AS name", ["it's"], false, 'local', sqlMode);
// throws TypeErrorAlso set noBackslashEscapes whenever the mode cannot be known for sure, for example when it comes from the SERVER_STATUS_NO_BACKSLASH_ESCAPES status flag, which can lag behind the session. A value holding a backslash is written as an expression that rebuilds it, which MySQL rejects where only a quoted string is allowed, such as IDENTIFIED BY.
A string written in the query itself reads differently in each mode when it holds a backslash, so pass that text as a value instead. With the option set, format throws a TypeError where a quote follows an odd number of backslashes, as in the last query above.
Setting noBackslashEscapes to anything other than a boolean throws a TypeError. format throws neither error when no values are provided, and an empty array counts as none.
A value with a toSqlString method receives the SQL mode as its argument, to pass on to the escape or format calls that build its SQL. Text escaped before the outer call, such as a value escaped into a raw string, must be given the same mode by hand:
const sqlMode = { noBackslashEscapes: true };
const author = {
toSqlString: (mode) =>
format(
'SELECT id FROM users WHERE name = ?',
["it's"],
false,
'local',
mode
),
};
format(
'SELECT * FROM posts WHERE author_id IN (?)',
[author],
false,
'local',
sqlMode
);
// => "SELECT * FROM posts WHERE author_id IN (SELECT id FROM users WHERE name = 'it''s')"Creates a raw SQL value that will not be escaped.
raw(sql: string): Rawescape(raw('NOW()'), true);
// => 'NOW()'Inside an expanded object, raw values are preserved (discouraged):
escape({ id: raw('LAST_INSERT_ID()') });
// => '`id` = LAST_INSERT_ID()'Only accepts strings:
raw(42); // throws TypeErrorMarks an object or a Map to be expanded into key = value pairs in any position of the statement, regardless of the stringifyObjects value.
expand(value: Record<string, unknown> | Map<string, SqlValue>, columns?: readonly string[]): ExpandWarning
The keys of a marked object become column names. For an object your code did not build, such as a request body, pass the columns it may expand, or the client chooses which columns the query reads or changes:
const body = { name: 'foo', role: 'admin' }; // e.g. a request body
format('UPDATE users SET ? WHERE id = ?', [
expand(body, ['name', 'email']),
1,
]);
// => "UPDATE users SET `name` = 'foo' WHERE id = 1"Only the value's own keys that the list names expand, in the order it names them, and an allowed column the value lacks is left out instead of set to NULL, so a value holding none of them throws a TypeError.
format('SELECT ?? FROM ?? WHERE ? AND status IN (?) LIMIT ?', [
['id', 'name'],
'users',
expand({ role: 'admin' }),
['active', 'pending'],
10,
]);
// => "SELECT `id`, `name` FROM `users` WHERE `role` = 'admin' AND status IN ('active', 'pending') LIMIT 10"
format(
'UPDATE users SET ? WHERE id = ?',
[expand({ name: 'foo' }), { id: true }],
true
);
// => "UPDATE users SET `name` = 'foo' WHERE id = '[object Object]'"
escape(expand({ id: 1 }), true);
// => '`id` = 1'- An object with no column to set also throws a
TypeErrorinstead of producing an empty clause.
A marked object produces the same text it produces in a SET clause. Only the marked value expands, so other objects in the statement keep the default behavior, and a mark nested inside an array, a Set, an object, or a Map is stringified like any nested object:
format('SELECT * FROM users WHERE id IN (?)', [[expand({ id: 1 })]]);
// => "SELECT * FROM users WHERE id IN ('[object Object]')"Only accepts an object or a Map, with the allowed columns as an array of strings:
expand('id = 1'); // throws TypeError
expand([{ id: 1 }]); // throws TypeError
expand(raw('NOW()')); // throws TypeError
expand({ id: 1 }, 'id'); // throws TypeError- A value marked by one copy of SQL Escaper (for example, your own dependency) expands in another (for example, the one MySQL2 uses), limited to the columns given when it was marked.
Tells whether a value is marked by expand in any copy of SQL Escaper, so code that cannot expand a mark, such as a prepared statement, can refuse it.
isExpand(value: unknown): value is ExpandisExpand(expand({ id: 1 }));
// => true
isExpand({ id: 1 });
// => falseTurns named placeholders into positional ones, :name into ? and ::name into ??, and returns the values in placeholder order. It reads the query by the same rules as format and escapes nothing, so the result can go to format or to a prepared statement.
toPositional(sql: string, values?: object, sqlMode?: SqlMode): PositionalQuerytoPositional('SELECT ::column FROM users WHERE id = :id -- :note', {
column: 'email',
id: 42,
});
// => { sql: 'SELECT ?? FROM users WHERE id = ? -- :note', values: ['email', 42] }
const { sql, values } = toPositional(
'UPDATE users SET name = :name WHERE id = :id',
{ name: 'foo', id: 42 }
);
format(sql, values);
// => "UPDATE users SET name = 'foo' WHERE id = 42"A missing name takes undefined, a repeated name repeats its value, and a value the object inherits is read too. A ? mixed with names takes the next numbered value (values[0], values[1], and so on), as in named-placeholders.
A query without a placeholder is returned as written with no values, whatever values holds. A query with one throws a TypeError when values is undefined or null.
With noBackslashEscapes set in the SQL mode, a quote after an odd number of backslashes throws the same TypeError as in format only when a placeholder follows it, since only then could the two modes place a value differently.
You can import the available types:
import type {
Expand,
PositionalQuery,
Raw,
SqlMode,
SqlValue,
TemporalValue,
Timezone,
} from 'sql-escaper';Each benchmark formats 10,000 queries using format with 100 values, comparing SQL Escaper against the original sqlstring through hyperfine:
| # | Benchmark | sqlstring | SQL Escaper | Difference |
|---|---|---|---|---|
| 1 | Select 100 values | 173.0 ms | 122.0 ms | 1.42x faster |
| 2 | Insert 100 values | 173.4 ms | 126.5 ms | 1.37x faster |
| 3 | Insert 100 strings requiring escape | 331.5 ms | 185.1 ms | 1.79x faster |
| 4 | Insert 100 dates | 376.5 ms | 264.0 ms | 1.43x faster |
| 5 | SET with 100 values | 178.7 ms | 140.8 ms | 1.27x faster |
| 6 | SET with 100 objects | 238.4 ms | 158.7 ms | 1.50x faster |
| 7 | ON DUPLICATE KEY UPDATE with 100 values | 315.4 ms | 235.6 ms | 1.34x faster |
| 8 | ON DUPLICATE KEY UPDATE with 100 objects | 369.5 ms | 282.9 ms | 1.31x faster |
| 9 | Multi-statement SET with 100 objects | 244.3 ms | 162.2 ms | 1.51x faster |
- See how the benchmarks are run in the benchmark directory.
Note
Benchmarks ran on GitHub Actions (ubuntu-latest) using Node.js LTS. Results may vary depending on runner hardware and runtime version.
Based on the original sqlstring documentation.
- The escaping methods in this library only work when the
NO_BACKSLASH_ESCAPESSQL mode is disabled (which is the default state for MySQL servers), unlessnoBackslashEscapesis set in the SQL mode argument. - This library performs client-side escaping to generate SQL strings. The syntax for
formatmay look similar to a prepared statement, but it is not one. The escaped SQL is safe only on a connection whose character set cannot turn another character into a quote, a backslash, or a backtick, such asutf8mb4. - When using
formatortoPositional, placeholders inside strings ('...'and"..."), backtick identifiers, and comments (#,/* */, and--followed by whitespace) are left as they are. Executable comments (/*! */) and optimizer hints (/*+ */) are read as SQL, so their placeholders are replaced. - When structured user input is provided as the value to escape, validate the shape of the input, as the resulting escaped string may contain more than a single value.
NaNandInfinityare left as-is. MySQL does not support these values, so trying to insert them triggers an error.- The string provided to
raw()will skip all escaping, so be careful when passing in unvalidated input.
Please check the SECURITY.md.
See the Contributing Guide and please follow our Code of Conduct 🚀
- SQL Escaper is adapted from sqlstring (MIT).
- Special thanks to Douglas Wilson for the original sqlstring project and its contributors.
SQL Escaper is under the MIT License.