package
🛡️ Faster SQL escape and format for JavaScript (Node.js, Bun, and Deno).
npm install sql-escaperGitHub-flavored package documentation.
Across all versions.
Download trend unavailable.
Runtime metadata.
Tagged topics.
Estimated unpacked size across 1 packages.
60.5 kB
Includes dependencies.
weslley.io
w.eslley@ymail.com
SQL Escaper is a rework of sqlstring (created by Douglas Wilson), by using an AST-based approach to parse and format SQL queries while maintaining its same API.
Uint8Array, BigInt, and Temporal.[!TIP]
SQL Escaper has the same API as the original sqlstring, so it can be used as a drop-in replacement. If SQL Escaper breaks any API usage compared to sqlstring, please, report it as a bug. Pull Requests are welcome.
[!IMPORTANT]
🔐 SQL Escaper is intended to fix a potential SQL Injection vulnerability reported in 2022. By combining the original sqlstring with mysqljs/mysql or MySQL2, objects passed as values could be expanded into SQL fragments, potentially allowing attackers to manipulate query structure. See sidorares/node-mysql2#4051 for details.
Regardless of the
stringifyObjectsvalue, objects used outside ofSETorON DUPLICATE KEY UPDATEcontexts are always stringified as'[object Object]'. This is a security measure to prevent SQL Injection and is not interpreted as a breaking change for sqlstring usage.
# Node.js
npm i sql-escaper# Bun
bun add sql-escaper# Deno
deno add npm:sql-escaperFor MySQL2, it already uses SQL Escaper as its default escaping library since version 3.17.0, so you just need to update it to the latest version:
npm i mysql2@latestYou can use an overrides in your package.json:
"dependencies": {
"mysql": "^2.18.1"
},
"overrides": {
"sqlstring": "npm:sql-escaper"
}node_modules and reinstall the dependencies (npm i).12.For up-to-date documentation, always follow the README.md in the GitHub repository.
import { escape, escapeId, 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 ?', [{ name: 'foo', email: 'bar@test.com' }]);
// => "INSERT INTO users SET `name` = 'foo', `email` = 'bar@test.com'"
escape(raw('NOW()'), true);
// => 'NOW()'import { escape, escapeId, format, raw } from 'sql-escaper';const { escape, escapeId, format, raw } = require('sql-escaper');Escapes a value for safe use in SQL queries.
escape(value: SqlValue, stringifyObjects?: boolean, timezone?: Timezone): 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'"Temporal values are supported too.
Temporal.Instant and Temporal.ZonedDateTime are absolute points in time and
honor the timezone argument exactly like Date (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:
escape({ a: 'b' }, true);
// => "'[object Object]'"Objects with a toSqlString method will have that method called:
escape({ toSqlString: () => 'NOW()' }, true);
// => 'NOW()'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):
escape({ a: 'b', c: () => {} });
// => "`a` = 'b'"[!CAUTION]
Without
stringifyObjects, plain objects are converted tokey = valuepairs, so an object reachingescapewhere a value is expected reshapes the query, for example: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,
formatonly expands an object where it is safe (e.g., aSETassignment) 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 purpose
Arrays 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, turning into comma-separated lists with natural deduplication:
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 ??'When stringifyObjects is falsy, objects used in SET or ON DUPLICATE KEY UPDATE contexts are automatically expanded into key = value pairs:
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 always stringified:
format('UPDATE users SET ?', [{ name: 'foo' }], true);
// => "UPDATE users SET '[object Object]'"Maps are treated like plain objects, preserving insertion order. In SET or ON DUPLICATE KEY UPDATE contexts, they are expanded into key = value pairs:
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]', the same security measure applied to objects:
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, set ansiQuotes so double-quoted text is read as an identifier, where a backslash is an ordinary character:
const sqlMode = { ansiQuotes: true };
format('SELECT "dir\\" FROM t WHERE id = ?', [1], false, 'local', sqlMode);
// => 'SELECT "dir\\" FROM t WHERE id = 1'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 TypeErrorYou can import the available types:
import type { Raw, SqlMode, SqlValue, 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 | 249.0 ms | 177.8 ms | 1.40x faster |
| 2 | Insert 100 values | 247.7 ms | 185.3 ms | 1.34x faster |
| 3 | Insert 100 strings requiring escape | 436.9 ms | 257.5 ms | 1.70x faster |
| 4 | Insert 100 dates | 611.9 ms | 415.1 ms | 1.47x faster |
| 5 | SET with 100 values | 258.8 ms | 207.4 ms | 1.25x faster |
| 6 | SET with 100 objects | 344.8 ms | 241.0 ms | 1.43x faster |
| 7 | ON DUPLICATE KEY UPDATE with 100 values | 462.0 ms | 362.7 ms | 1.27x faster |
| 8 | ON DUPLICATE KEY UPDATE with 100 objects | 559.4 ms | 437.8 ms | 1.28x faster |
| 9 | Multi-statement SET with 100 objects | 348.7 ms | 243.4 ms | 1.43x faster |
[!NOTE]
Benchmarks ran on GitHub Actions (
ubuntu-latest) using Node.js LTS. Results may vary depending on runner hardware and runtime version.
[!TIP]
The Node.js 12+ requirement is what allows SQL Escaper to leverage modern engine optimizations and achieve the performance gains over the original.
Based on the original sqlstring documentation.
NO_BACKSLASH_ESCAPES SQL mode is disabled (which is the default state for MySQL servers).format may look similar to a prepared statement, but it is not — the escaping rules from this module are used to produce the resulting SQL string.format, 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.ANSI_QUOTES SQL mode, set ansiQuotes, or the placeholders after a double-quoted identifier that ends in a backslash can be skipped or misplaced.NaN and Infinity are left as-is. MySQL does not support these values, and trying to insert them will trigger MySQL errors.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 under the MIT License.