How to escape in Sequelize?

Question

I'm using Sequelize with Node.js/Express and I'm not sure how to escape with Sequelize in the where part.

var sequelize = ...;
var productId = 5; var productName = "test";
var product = sequelize.define('product',findAll({
       where: {
           $or: [
                {productId: this.mysql.escapeId(productId)},
                {productName: {$like: this.mysql.escapeId('%' + productName + '%')}},
            ]
       }
    })
   .then(result => ...);

This is not working, I obtain the bellowing query :

SELECT `productId`, `productName` FROM `product` AS `product` WHERE (`product`.`productId` = '`5`' OR `product`.`productName` LIKE '\'%test%\'' ORDER BY `product`.`productId` ASC

which give me nothing as results. So how to escape with Sequelize ? I also tried the function Sequelize.escape, but I got the error "TypeError: Sequelize.escape is not a function".

And if there's no need to escape the values thanks to Sequelize, I don't understand how it will stay safe from a SQL injection attack. Example : productId = '5; DELETE * FROM SOMETHING;'

Thanks a lot for your help !

Have a good day,

vanessa

Solution 1

As @FiftiN correctly states in his answer sequelize handles escaping in most instances.

The only instance i needed manual escaping was when composing raw sql queries. Sequelize raw sql queries use placeholders ($ or ?) which are also escaped:

sequelize.query('SELECT * FROM projects WHERE status = ?',
  { replacements: ['active'], type: sequelize.QueryTypes.SELECT }
)

Though when working with special sql functions i found it more appropriate to escape manually sometimes:

var SqlString = require('sequelize/lib/sql-string')
var input = SqlString.escape("'string'( \"value")
sequelize.query(
    `SELECT * FROM projects WHERE regexp_matches("status", '^\'${input} *\\w*\'')`,
    {type: sequelize.QueryTypes.SELECT }
)

The strange regular expression just serves as an example why the standard escape mechanism may feel clunky. The example also uses ES6 template strings.

I found the escape method in https://github.com/sequelize/sequelize/issues/1132.

WARNING: The escape method takes additional parameters like the sql dialect. Make sure you understand it's workings before relying on it! Additionally the method may change or no longer exist in future releases as it is not documented in the official documentation.

Solution 2

No need to escape is this case, Sequelize do it.

Comments

[Dan]

Issue with replacements in Sequelize is that numeric values are quoted.