CODEWITHSUNDEEP
javascriptmysqlnode.jsknex.js
Jun
Jun

Reputation: 113

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.

Upvotes: 10

Views: 29176

Answers (10)

Bruno Aquino
Bruno Aquino

Reputation: 11

I know this question is 2 years old, but reading the Knex documentation I found out they added a function for this scenario.

knex('users').whereLike('email', '%mail%');

Upvotes: 1

ABHISHEK GUPTA
ABHISHEK GUPTA

Reputation: 71

For this to resolve you can add the search term in a separate variable and add % at the beginning and end of the string. For ex:

const likeParams = '%' + selectedKeyword + '%';
knex('users').where('title', 'ilike', likeParams);

Upvotes: 0

Stepan Turchenko
Stepan Turchenko

Reputation: 131

whereILike

http://knexjs.org/guide/query-builder.html#whereilike

This is doc for this


knex('users').whereILike('email', '%mail%')

knex('users')
  .whereILike('email', '%MAIL%')
  .andWhereILike('email', '%.COM')
  .orWhereILike('email', '%NAME%')

Upvotes: 0

Andrew Kycha
Andrew Kycha

Reputation: 203

So I was looking for a correct way to apply LOWER function to the parameter. Here's the solution that seems to be working fine for me:

builder.whereRaw('LOWER(last_name) LIKE LOWER(?)', [`%${lastName}%`])

Upvotes: 5

Daniel Cheung
Daniel Cheung

Reputation: 4829

@coockoo's answer is incorrect for both of the SQLs. Their first one would still allow % through as Knex does not escape % for LIKE operations. The second SQL simply does not work as Knex wraps the bound value with quotes.

The correct way should be

const term = '10%'
const b = knex('table').where('description', 'like', `%${term.replaceAll('%', '\\%')}%`)

The output of b.toString() is:

select * from "table" where "description" like E'%10\\%%'

Postgres will interpret E'\\%' as '\%', which is an escaped percentage sign according to: https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-LIKE by default if you are on newer Postgres version.

This can be verified with this table:

CREATE TABLE test (
    description character varying(256)
);

INSERT INTO test VALUES ('a%b');

and test the following:

  1. Without escaping % like others have suggested. This shouldn't work.
knex('test').where('description', 'like', 'a%%%b').toString()

select * from "test" where "description" like 'a%%%b'

 description
-------------
 a%b
(1 row)

  1. Escaping % before giving to Knex.

    1. This should return no rows:
    knex('test').where('description', 'like', 'a\\%\\%\\%b').toString()

    select * from "test" where "description" like E'a\\%\\%\\%b'

     description
-------------
(0 rows)
    1. This should return 'a%b':
    knex('test').where('description', 'like', 'a\\%b').toString()

    select * from "test" where "description" like E'a\\%b'

     description
-------------
 a%b
(1 row)

SQL Fiddle: http://sqlfiddle.com/#!17/d2f5e/1

Upvotes: 2

UserCah
UserCah

Reputation: 26

Hey have you tried this 

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

just the regular javascript escaping works just fine.

Upvotes: -1

Loren
Loren

Reputation: 14896

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

Upvotes: 0

coockoo
coockoo

Reputation: 2392

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.

Upvotes: 9

ShaH
ShaH

Reputation: 170

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.

Upvotes: -3

user8406805
user8406805

Reputation:

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

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

Upvotes: 0

Related Questions