DOCSEARCHbyAlgolia
Submit Docs

sqlstring Documentation

repository·master·Indexed 19 days ago

https://github.com/mysqljs/sqlstring

A simple utility for escaping and formatting SQL strings for MySQL to prevent SQL injection. It provides methods for escaping values via SqlString.escape(), formatting queries with placeholders using SqlString.format(), escaping identifiers with SqlString.escapeId(), and including unescaped fragments with SqlString.raw().

Tokens
1.5K
Snippets
7
Records
7
Agent score
15%

What's inside sqlstring

  1. How SqlString.format() works with objects and identifiers

    master

    The SqlString.format() method can combine identifier escaping (??), value escaping (?), and object mapping. When an object is passed as a value to .escape() or .format(), SqlString.escapeId() is used internally to escape the object's keys to prevent SQL injection.

    Example of using an object for a SET clause:

    var post  = {id: 1, title: 'Hello MySQL'};
var sql = SqlString.format('INSERT INTO posts SET ?', post);
console.log(sql); // INSERT INTO posts SET `id` = 1, `title` = 'Hello MySQL'

  3. Use SqlString.raw() for unescaped SQL fragments

    master

    To include MySQL functions or raw SQL fragments (like NOW()) as dynamic values in a formatted query, use SqlString.raw(). This creates an object that will be left untouched when used in a ? placeholder.

    Caution: The string provided to SqlString.raw() skips all escaping. Do not pass unvalidated user input into SqlString.raw().

    var CURRENT_TIMESTAMP = SqlString.raw('CURRENT_TIMESTAMP()');
var sql = SqlString.format('UPDATE posts SET modified = ? WHERE id = ?', [CURRENT_TIMESTAMP, 42]);
console.log(sql); // UPDATE posts SET modified = CURRENT_TIMESTAMP() WHERE id = 42

  4. Escape query values with SqlString.escape()

    master

    To prevent SQL Injection, use SqlString.escape() to safely escape user-provided data.

    Caution: These methods work correctly only when the NO_BACKSLASH_ESCAPES SQL mode is disabled (the default for MySQL servers). This library performs client-side escaping to generate a resulting SQL string; it is not a true prepared statement.

    Value Type Mapping:

    • Numbers: Left untouched.
    • Booleans: Converted to true / false.
    • Date objects: Converted to 'YYYY-mm-dd HH:ii:ss' strings.
    • Buffers: Converted to hex strings (e.g., X'0fa5').
    • Strings: Safely escaped.
    • Arrays: Turned into lists (e.g., ['a', 'b'] becomes 'a', 'b').
    • Nested arrays: Turned into grouped lists for bulk inserts (e.g., [['a', 'b'], ['c', 'd']] becomes ('a', 'b'), ('c', 'd')).
    • Objects with toSqlString method: The method is called and its return value is used as raw SQL.
    • Standard Objects: Turned into key = 'val' pairs for each enumerable property. Functions are skipped; nested objects use .toString().
    • undefined / null: Converted to NULL.
    • NaN / Infinity: Left as-is (Note: MySQL does not support these).
    var userId = 'some user provided value';
var sql    = 'SELECT * FROM users WHERE id = ' + SqlString.escape(userId);
console.log(sql); // SELECT * FROM users WHERE id = 'some user provided value'

  5. Format queries with placeholders using SqlString.format()

    master

    Use SqlString.format() to prepare queries with ? placeholders. Multiple placeholders are mapped to values in the order they are provided in the array.

    Note: Unlike MySQL prepared statements, SqlString.format replaces all ? characters, even those inside comments or strings.

    var userId = 1;
var sql    = SqlString.format('SELECT * FROM users WHERE id = ?', [userId]);
console.log(sql); // SELECT * FROM users WHERE id = 1

  6. Escape query identifiers with SqlString.escapeId()

    master

    When database, table, or column names are provided by users, escape them using SqlString.escapeId(identifier).

    • Qualified Identifiers: It supports escaping parts of a path (e.g., table.column becomes `table`.`column`).
    • Literal Identifiers: To prevent . from being treated as a qualifier, pass true as the second argument (e.g., escapeId('date.2', true) becomes `date.2`).
    • Placeholders: You can use ?? as a placeholder for identifiers in SqlString.format() (Note: This syntax is experimental and subject to change).
    var sorter = 'date';
var sql    = 'SELECT * FROM posts ORDER BY ' + SqlString.escapeId(sorter);
console.log(sql); // SELECT * FROM posts ORDER BY `date` 

// Qualified
var sql2   = 'SELECT * FROM posts ORDER BY ' + SqlString.escapeId('posts.' + sorter);
console.log(sql2); // SELECT * FROM posts ORDER BY `posts`.`date` 

// Experimental ?? syntax
var columns = ['username', 'email'];
var userId = 1;
var sql3 = SqlString.format('SELECT ?? FROM ?? WHERE id = ?', [columns, 'users', userId]);
console.log(sql3); // SELECT `username`, `email` FROM `users` WHERE id = 1

  7. Import sqlstring

    master

    The sqlstring module is the entrypoint for SQL escaping and formatting. You can require it to access the SqlString class and its associated utility methods for safely preparing SQL queries.

    const SqlString = require('sqlstring');