npm.io
1.9.1 • Published 3d ago

sql-escaper

Licence
MIT
Version
1.9.1
Deps
0
Size
77 kB
Vulns
0
Weekly
0
Stars
1

SQL Escaper

NPM Version NPM Downloads Coverage
GitHub Workflow Status (Node.js) GitHub Workflow Status (Bun) GitHub Workflow Status (Deno)

Motivation

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.

Rework includes:

  • TypeScript by default.
  • Support for Uint8Array, BigInt, and Temporal.
  • Support for both CJS and ESM exports.
  • Up to ~44% faster than sqlstring.
  • Tells a real SET or ON DUPLICATE KEY UPDATE clause apart from the same words in a value, a column name, or another statement.
  • Support for the NO_BACKSLASH_ESCAPES SQL mode (sqlstring only escapes correctly with it disabled).
  • Support from Node.js 12 onward (sqlstring supports Node.js 0.6).

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.

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.


Install

# Node.js
npm i sql-escaper
# Bun
bun add sql-escaper
# Deno
deno add npm:sql-escaper

MySQL2

MySQL2 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@latest
mysqljs/mysql

You 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).


Usage

For up-to-date documentation, always follow the README.md in the GitHub repository.

Quickstart
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');

API

escape

Escapes a value for safe use in SQL queries.

escape(value: SqlValue, stringifyObjects?: boolean, timezone?: Timezone, sqlMode?: SqlMode): string
escape(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

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

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

Buffers are converted to hex strings:

escape(Buffer.from([0, 1, 254, 255]), true);
// => "X'0001feff'"
Objects

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):

escape({ a: 'b', c: () => {} });
// => "`a` = 'b'"

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'"

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 purpose
Arrays

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

Sets are treated like arrays:

escape(new Set([1, 2, 3]), true);
// => '1, 2, 3'

escapeId

Escapes an identifier (database, table, or column name).

escapeId(value: SqlValue, forbidQualified?: boolean): string
escapeId('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`'

format

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): string
format('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 ??'
Objects in SET clauses

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:

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 in SET clauses

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]'"
SQL mode

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 TypeError

Also 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.

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')"

raw

Creates a raw SQL value that will not be escaped.

raw(sql: string): Raw
escape(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 TypeError

expand

Marks 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[]): Expand

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.

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'

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.

TypeScript

You can import the available types:

import type {
  Expand,
  Raw,
  SqlMode,
  SqlValue,
  TemporalValue,
  Timezone,
} from 'sql-escaper';

Performance

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.

Benchmarks ran on GitHub Actions (ubuntu-latest) using Node.js LTS. Results may vary depending on runner hardware and runtime version.


Caution

Based on the original sqlstring documentation.

  • The escaping methods in this library only work when the NO_BACKSLASH_ESCAPES SQL mode is disabled (which is the default state for MySQL servers), unless noBackslashEscapes is set in the SQL mode argument.
  • This library performs client-side escaping to generate SQL strings. The syntax for format may 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 as utf8mb4.
  • When using 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.
  • 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.
  • NaN and Infinity are 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.

Security Policy

GitHub Workflow Status (with event)

Please check the SECURITY.md.


Contributing

See the Contributing Guide and please follow our Code of Conduct


Acknowledgements


License

SQL Escaper is under the MIT License.

Keywords