Node.js sequelize pre-validate dynamic user provided 'where' clause. To avoid possible sql column injection.

whereValidator.js

const columnKeyRegexValidation = /^[a-zA-Z0-9_]+$/;

const sequelizeValidOperators = {
  and: { validation: isDict },
  or: { validation: isArrayOfDict },
  gt: { validation: isPrimitive },
  gte: { validation: isPrimitive },
  lt: { validation: isPrimitive },
  lte: { validation: isPrimitive },
  ne: { validation: isPrimitive },
  eq: { validation: isPrimitive },
  not: { validation: isPrimitive },
  between: { validation: isArrayOfPrimitives },
  notBetween: { validation: isArrayOfPrimitives },
  in: { validation: isArrayOfPrimitives },
  notIn: { validation: isArrayOfPrimitives },
  like: { validation: isPrimitive },
  notLike: { validation: isPrimitive },
};

const sequelizeOperatorList = Object.keys(sequelizeValidOperators);

function isObject(v) { return (v === Object(v)); }

function isDict(v) {
  return (isObject(v) && !(v instanceof Array) && !(v instanceof Date));
}

function isArray(v) {
  return (isObject(v) && (v instanceof Array));
}

function isPrimitive(v) { return (!isObject(v)); }

function isArrayOfPrimitives(v) { return (isArray(v) && v.every(v => isPrimitive(v))); }

function isArrayOfDict(v) { return (isArray(v) && v.every(v => isDict(v))); }

function isValidColumnName(v, allowedColumns) {
  if (allowedColumns !== undefined) {
    return allowedColumns.includes(v);
  }
  return (v.match(columnKeyRegexValidation));
}

function isValidOperator(v) { return (sequelizeOperatorList.includes(v)); }

function isValidKey(v, allowedColumns) {
  return (isValidOperator(v) || isValidColumnName(v, allowedColumns));
}

function isValidValue(k, v) {
  if (isValidColumnName(k) && isDict(v)) {
    return isValidQueryObject(v);
  } else if (isValidOperator(k) && sequelizeValidOperators[k].validation(v)) {
    if (isDict(v)) {
      return isValidQueryObject(v);
    } else if (isArrayOfDict(v)) {
      return v.every(vd => isValidQueryObject(vd));
    }
    return true;
  }
  return false;
}

/**
 * A function to help you avoid column injection into sequelize.
 * @param obj {object} The object who will be passed to the 'where' query parameter
 * @param allowedColumns (array) Optional array of
 * allowed columns name (use /^[a-zA-Z0-9_]+$/ validation by default)
 * @returns {boolean}
 */
function isValidQueryObject(obj, allowedColumns) {
  return Object
    .keys(obj)
    .every(k => (isValidKey(k, allowedColumns) && isValidValue(k, obj[k])));
}

module.exports = {
  isValidQueryObject,
};