Repository files navigation

Simple SQL escape and format for MySQL

$ npm install sqlstring

varSqlString=require('sqlstring');

Caution These methods of escaping values only works when theNO_BACKSLASH_ESCAPESSQL mode is disabled (which is the default state for MySQL servers).

Caution This library performs client-side escaping, as this is a libraryto generate SQL strings on the client side. The syntax for functions likeSqlString.format may look similar to a prepared statement, but it is notand the escaping rules from this module are used to generate a resulting SQLstring. The purpose of escaping input is to avoid SQL Injection attacks.In order to support enhanced support likeSET andIN formatting, thismodule will escape based on the shape of the passed in JavaScript value,and the resulting escaped string may be more than a single value. Whenstructured user input is provided as the value to escape, care should be takento validate the shape of the input to validate the output will be what isexpected.

In order to avoid SQL Injection attacks, you should always escape any userprovided data before using it inside a SQL query. You can do so using theSqlString.escape() method:

varuserId='some user provided value';varsql='SELECT * FROM users WHERE id = '+SqlString.escape(userId);console.log(sql);// SELECT * FROM users WHERE id = 'some user provided value'

Alternatively, you can use? characters as placeholders for values you wouldlike to have escaped like this:

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

Multiple placeholders are mapped to values in the same order as passed. For example,in the following queryfoo equalsa,bar equalsb,baz equalsc, andid will beuserId:

varuserId=1;varsql=SqlString.format('UPDATE users SET foo = ?, bar = ?, baz = ? WHERE id = ?',['a','b','c',userId]);console.log(sql);// UPDATE users SET foo = 'a', bar = 'b', baz = 'c' WHERE id = 1

This looks similar to prepared statements in MySQL, however it really just usesthe sameSqlString.escape() method internally.

Caution This also differs from prepared statements in that all? arereplaced, even those contained in comments and strings.

Different value types are escaped differently, here is how:

• Numbers are left untouched
• Booleans are converted totrue /false
• Date objects are converted to'YYYY-mm-dd HH:ii:ss' strings
• Buffers are converted to hex strings, e.g.X'0fa5'
• Strings are safely escaped
• Arrays are turned into list, e.g.['a', 'b'] turns into'a', 'b'
• Nested arrays are turned into grouped lists (for bulk inserts), e.g.[['a', 'b'], ['c', 'd']] turns into('a', 'b'), ('c', 'd')
• Objects that have atoSqlString method will have.toSqlString() calledand the returned value is used as the raw SQL.
• Objects are turned intokey = 'val' pairs for each enumerable property onthe object. If the property's value is a function, it is skipped; if theproperty's value is an object, toString() is called on it and the returnedvalue is used.
• undefined /null are converted toNULL
• NaN /Infinity are left as-is. MySQL does not support these, and tryingto insert them as values will trigger MySQL errors until they implementsupport.

You may have noticed that this escaping allows you to do neat things like this:

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

And thetoSqlString method allows you to form complex queries with functions:

varCURRENT_TIMESTAMP={toSqlString:function(){return'CURRENT_TIMESTAMP()';}};varsql=SqlString.format('UPDATE posts SET modified = ? WHERE id = ?',[CURRENT_TIMESTAMP,42]);console.log(sql);// UPDATE posts SET modified = CURRENT_TIMESTAMP() WHERE id = 42

To generate objects with atoSqlString method, theSqlString.raw() method canbe used. This creates an object that will be left un-touched when using in a?placeholder, useful for using functions as dynamic values:

Caution The string provided toSqlString.raw() will skip all escapingfunctions when used, so be careful when passing in unvalidated input.

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

If you feel the need to escape queries by yourself, you can also use the escapingfunction directly:

varsql='SELECT * FROM posts WHERE title='+SqlString.escape('Hello MySQL');console.log(sql);// SELECT * FROM posts WHERE title='Hello MySQL'

If you can't trust an SQL identifier (database / table / column name) because it isprovided by a user, you should escape it withSqlString.escapeId(identifier) like this:

varsorter='date';varsql='SELECT * FROM posts ORDER BY '+SqlString.escapeId(sorter);console.log(sql);// SELECT * FROM posts ORDER BY `date`

It also supports adding qualified identifiers. It will escape both parts.

varsorter='date';varsql='SELECT * FROM posts ORDER BY '+SqlString.escapeId('posts.'+sorter);console.log(sql);// SELECT * FROM posts ORDER BY `posts`.`date`

If you do not want to treat. as qualified identifiers, you can set the secondargument totrue in order to keep the string as a literal identifier:

varsorter='date.2';varsql='SELECT * FROM posts ORDER BY '+SqlString.escapeId(sorter,true);console.log(sql);// SELECT * FROM posts ORDER BY `date.2`

Alternatively, you can use?? characters as placeholders for identifiers you wouldlike to have escaped like this:

varuserId=1;varcolumns=['username','email'];varsql=SqlString.format('SELECT ?? FROM ?? WHERE id = ?',[columns,'users',userId]);console.log(sql);// SELECT `username`, `email` FROM `users` WHERE id = 1

Please note that this last character sequence is experimental and syntax might change

When you pass an Object to.escape() or.format(),.escapeId() is used to avoid SQL injection in object keys.

You can useSqlString.format to prepare a query with multiple insertion points,utilizing the proper escaping for ids and values. A simple example of this follows:

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

Following this you then have a valid, escaped query that you can then send to the database safely.This is useful if you are looking to prepare the query before actually sending it to the database.You also have the option (but are not required) to pass instringifyObject andtimeZone,allowing you provide a custom means of turning objects into strings, as well as alocation-specific/timezone-awareDate.

This can be further combined with theSqlString.raw() helper to generate SQLthat includes MySQL functions as dynamic vales:

varuserId=1;vardata={email:'foobar@example.com',modified:SqlString.raw('NOW()')};varsql=SqlString.format('UPDATE ?? SET ? WHERE `id` = ?',['users',data,userId]);console.log(sql);// UPDATE `users` SET `email` = 'foobar@example.com', `modified` = NOW() WHERE `id` = 1

MIT