How do I escape % in Knex where like query?

问题:

I'm using knex to generate my SQL queries. In knex documentation, it shows this

knex('users').where('columnName', 'like', '%rowlikeme%')

Now in my application, I did this:

function search(term) {
  term = "%" + term + "%";
  knex('table').where('description', 'like', term);
  // ...
}

How can I escape % so that it searches for % as part of the term as well?

Thanks.

回答1:

For this case I use

rather string interpolation from es6 (safe version)

knex('table').where('description', 'like', `%${term}%`)

or ?? parameter binding

knex('table').whereRaw('description like \'%??%\'', [term])

But in the first case, you must be 100% sure that term is valid, because of the possibility of SQL injection.

回答2:

Use the RLIKE instead of LIKE as below, It should work for you.

function search(term){
    knex('table').where('description','rlike',term);
    ...
}

回答3:

I have not used knex in a while so I can not test this. But have you tried to literally escape %? I hope that is what you want.

let term = "%something%"
let parsedTerm = term.replace("%", "\%")
console.log(parsedTerm);

Tell me about that please.

回答4:

Knex doesn't have an equivalent to the ESCAPE keyword [1], so you have to do a raw query like this, which will search for users with name === "%foo%":

knex.raw('select * from users where name like ? escape \', ['\%foo\%'])

And this, with an unescaped wildcard at the beginning of the search term, will search for users with name ending in "%foo%":

knex.raw('select * from users where name like ? escape \', ['%\%foo\%'])

[1] Closed feature request: https://github.com/knex/knex/issues/648

回答5:

Hey have you tried this

knex('Quotes').where('quoteBody', 'like', **'%'+Quote+'%'**)

just the regular javascript escaping works just fine.