Escaping knex mysql query statements

Question

I'm fairly new to knex and databases in general, so this is a beginner question. I found no clear mention in the knex docs about this. Are non-raw knex queries automatically "safe"?

Secondly, for raw queries, I have several raw statements similar to this:

var condition = _.map(ids, function(id) {
    return '`id`=' + id;
}).join(' OR ');

knex('categories')
    .whereRaw(condition)
    .select('*')
    .catch(_error.bind(null, cb))
    .then(function(res) { ... });

Would escaping the id in the condition with a function described here be sufficient to escape that query? What else to look out fo in such a scenario?

Answer 1

All knex queries are safe, also the knex.raw() queries if you use parameter binding syntax where ? are replaced with escaped values (http://knexjs.org/#Raw).

Query that you are doing would be better be done without raw as follows

knex('categories').whereIn('id', ids).catch(...).then(...);

If you want to use automatic escaping of column reference a.k.a identifier you may use whereRaw('?? = ?', ['id', value]) which escapes first part as identifier and second part as value.

So with parameter escaping your example would be something like this:

var condition = _.map(ids, function() {
    return '?? = ?';
}).join(' OR ');

var conditionParameters = _.flatten(_.map(ids, function(id) {
    return ['id', id];
}));

knex('categories')
    .whereRaw(condition, conditionParameters)
    .select('*')
    .catch(_error.bind(null, cb))
    .then(function(res) { ... });

However I have to say that there is pretty much always better ways to do the queries in knex than using raw conditions made this way.