MySQL Between Bug

[raffaele-clevermind]

When using a field type "Date" for the mysql adapter if using the method "find" with a "between" condition like:

let schema = new Schema(config.driver, config);
let reliefs = schema.define( 'reliefs', {
  id: { type: schema.Integer},
  datetime: { type: schema.Date},
}, { primaryKeys: [ 'id' ] } );

reliefs.find({
  where: {
    datetime:{between:['"2018-11-11 17:00:39"', '"2018-11-13 17:00:39"']}
  }
}, ( error, results ) => {
  console.log( _.map( results, 'datetime' ) );
});

caminte will generate a SQL syntax error since it will generate this query:

SELECT * FROM `reliefs` WHERE `datetime` BETWEEN 2018-11-11 17:00:39 AND 2018-11-13 17:00:39

where the dates are not escaped.

The reason depends on the mysql adapter in the "parseCond" method. I added 2 comments in the code below to show the two points that come into play

function parseCond(cs, key, props, conds, self) {
    'use strict';
    var keyEscaped = '`' + key.replace(/\./g, '`.`') + '`';
//here the value is correctly parsed to 
//"STR_TO_DATE("2018-11-11 17:00:39", "%Y-%m-%d %H:%i:%s") AND STR_TO_DATE("2018-11-13 17:00:39", "%Y-%m-%d %H:%i:%s")"
    var val = self.toDatabase(props[key], conds[key]); 
    if (conds[key] === null) {
        cs.push(keyEscaped + ' IS NULL');
    } else if (conds[key].constructor.name === 'Object') {
        Object.keys(conds[key]).forEach(function (condType) {
//but here the value is wrongly converted to "[ '2018-11-11 17:00:39', '2018-11-13 17:00:39' ]"
            val = self.toDatabase(props[key], conds[key][condType]);
            var sqlCond = keyEscaped;
            if ((condType === 'inq' || condType === 'nin') && val.length === 0) {
                cs.push(condType === 'inq' ? 0 : 1);
                return true;
            }
            switch (condType) {
                case 'gt':
                    sqlCond += ' > ';
                    break;
                case 'gte':
                    sqlCond += ' >= ';
                    break;
                case 'lt':
                    sqlCond += ' < ';
                    break;
                case 'lte':
                    sqlCond += ' <= ';
                    break;
                case 'between':
                    sqlCond += ' BETWEEN ';
                    break;
                case 'inq':
                case 'in':
                    sqlCond += ' IN ';
                    break;
                case 'nin':
                    sqlCond += ' NOT IN ';
                    break;
                case 'neq':
                case 'ne':
                    sqlCond += ' != ';
                    break;
                case 'regex':
                    sqlCond += ' REGEXP ';
                    break;
                case 'like':
                    sqlCond += ' LIKE ';
                    break;
                case 'nlike':
                    sqlCond += ' NOT LIKE ';
                    break;
                default:
                    sqlCond += ' ' + condType + ' ';
                    break;
            }
            if (condType === 'between') {
                sqlCond += val[0] + ' AND ' + val[1];
            } else if (condType === 'in' || condType === 'inq' || condType === 'nin') {
                sqlCond += "(" + val + ")";
            } else {
                sqlCond += val;
            }
            cs.push(sqlCond);
        });

    } else if (/^\//gi.test(conds[key])) {
        var reg = val.toString().split('/');
        cs.push(keyEscaped + ' REGEXP "' + reg[1] + '"');
    } else {
        cs.push(keyEscaped + ' = ' + val);
    }
    return cs;
}