javascript - How to escape `$like` wildcard characters `%` and `_` in sequelize? - Stack Overflow

admin管理员组

文章数量:1392007

How does one tell sequelize (using sqlite3) to escape wildcard characters (%,_) in a $like clause?

For example, I'd like to generate a query parable to the one below which finds the literal string _cool_ in any "things.description" (not just the word "cool" surrounded by any characters):

SELECT * FROM things WHERE description LIKE '\_cool\_' ESCAPE '\';  

I'm guessing it would look something like this but the resulting query is not what I would hope:

Thing.findAll({
  where: {
    description: {
      $like: ['?', '_cool_'],
      // ... or ...
      $like: '\\_cool\\_',
      $escape: '\\',
    }
  }
});

// => Executing (default):
// SELECT `id`, `description`
//   FROM `Things` AS `Thing`
//   WHERE `Thing`.`description` LIKE '?','_cool_';

Unfortunately I cannot drop into a raw query because the "LIKE" clause will be part of a larger dynamic query.

Any ideas?

How does one tell sequelize (using sqlite3) to escape wildcard characters (%,_) in a $like clause?

For example, I'd like to generate a query parable to the one below which finds the literal string _cool_ in any "things.description" (not just the word "cool" surrounded by any characters):

SELECT * FROM things WHERE description LIKE '\_cool\_' ESCAPE '\';  

I'm guessing it would look something like this but the resulting query is not what I would hope:

Thing.findAll({
  where: {
    description: {
      $like: ['?', '_cool_'],
      // ... or ...
      $like: '\\_cool\\_',
      $escape: '\\',
    }
  }
});

// => Executing (default):
// SELECT `id`, `description`
//   FROM `Things` AS `Thing`
//   WHERE `Thing`.`description` LIKE '?','_cool_';

Unfortunately I cannot drop into a raw query because the "LIKE" clause will be part of a larger dynamic query.

Any ideas?

• javascript
• sqlite
• sequelize.js

Share Improve this question Follow edited Mar 1, 2017 at 0:42 maerics asked Feb 28, 2017 at 22:19 maericsmaerics 157k4747 gold badges277277 silver badges299299 bronze badges 3

• Wouldnt that be \_cool\_ if you're escaping a singular backslash? – Xorifelse Commented Feb 28, 2017 at 22:32
• @Xorifelse in JavaScript the string literal '\\_' equals the string "\_". – maerics Commented Feb 28, 2017 at 22:36
• 1 Looks like this may just be a current shorting in sequelize github./sequelize/sequelize/issues/4317 – maerics Commented Mar 1, 2017 at 4:35

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 3

A full solution based on @piotrbienias that takes any user input and properly escapes it -

if (typeof query.string !== 'string') {
    throw Error('expecting string')
}
const inputRaw = query.string.replace(/(_|%|\\)/g, '\\$1');
const inputSec = sequelize.escape(`%${inputRaw}%`);
description = {
    $like: sequelize.literal(`${inputSec} ESCAPE '\\'`)
};

For the example string _cool_ this will translate to -

LIKE '%\\_cool\\_%' ESCAPE '\\'

You can change the logic to add other wildcards depending on your actual use case

if your using mysql and not sqlite the default escape is anyway \ so the ESCAPE declaration can be removed

The only thing that es to my mind is something like

description: {
    $like: sequelize.literal(`'\\_cool\\_' ESCAPE '\\'`)
}

That generates SQL like

LIKE '\_cool\_' ESCAPE '\'

本文标签： javascriptHow to escape like wildcard charactersandin sequelizeStack Overflow