caminte icon indicating copy to clipboard operation
caminte copied to clipboard

sqlite: in query operator doesn't work when operand is an array of strings

Open timbowhite opened this issue 9 years ago • 0 comments

Example:

var caminte = require('caminte'),
    Schema  = caminte.Schema,
    schema  = new Schema('sqlite3', {database: ':memory:'}),
    Foo = schema.define('foo', {
        bar: { type: schema.String}
    });

schema.on('connected', function(){
    var f = new Foo({
        bar: 'baz'
    });

    f.save(function(err, fnew){
        if (err) throw err;
        console.log('f saved', fnew);

        Foo.find({where: {bar: fnew.bar}}, function(err, rows){
            if (err) throw err;
            console.log('found 1 row', rows);

            Foo.find({where: {bar: {in: [fnew.bar]}}}, function(err, rows){
                if (err) throw err;
                console.log('no rows, expecting 1', rows);
            });
        });
    });
});

Looks like the parseCond() function is double escaping filter values at lines 757 and 762 in lib/adapters/sqlite3.js:

function parseCond(cs, key, props, conds, self) {
    var keyEscaped = '`' + key.replace(/\./g, '`.`') + '`';
    var val = self.toDatabase(props[key], conds[key]);  // <-------- 1st escape 
    if (conds[key] === null || conds[key] === undefined) {
        cs.push(keyEscaped + ' IS NULL');
    } else if (conds[key].constructor.name === 'Object') {
        Object.keys(conds[key]).forEach(function (condType) {
            val = self.toDatabase(props[key], conds[key][condType]);   // <-------- 2nd escape 

So the IN query ends up looking like this:

SELECT * FROM `foo` WHERE `bar` IN ("%22baz%22")

Not sure if other db adapters are affected.

timbowhite avatar Jan 21 '16 01:01 timbowhite