postgresqlsails.jssails-postgresql

Sails ORM: How to pass value with array in valuesToEscape parameter


I've rawQuery which requires comma separated string but according to the documentation here, the second argument should be an array:

An array of dynamic, untrusted strings to SQL-escape and inject within the SQL string using the appropriate template syntax for this model's database. (If you have no dynamic values to inject, then just use an empty array here.)

var rawQuery = 'SELECT * FROM "user" WHERE "user"."email" IN ($1)';
User.query(rawQuery, ['a@a.com', 'b@b.com'], function (err, rawResult) {
  if (err) { return res.serverError(err); }
    return res.ok(rawResult.rows);
});

How can I make this query work without passing a variable through an array? I can directly add the variable like this

var rawQuery = 'SELECT * FROM "user" WHERE "user"."email" IN (' + foo +')';

But it will be prone to SQL injection attack.


Solution

  • To run the query directly without using the parameter injection mode, you need to remove SQL command especial characters, otherwise you will be prune to injection attacks, as you said.

    There are packages that do that for you: the most popular are npm: sql-escape and npm sqlstring

    They will add escape characters to any especial character into your string:

    var escape = require('sql-escape');
    
    var result = escape('my sweet "string"'); 
    //result: 'my sweet \\"string\\"'