How do I escape % in Knex where like query?

Question

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.

Answer 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.

Answer 2

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}%`])

Answer 3

@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)

2. 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)
   

   2. 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