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.

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

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

Solution 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

Comments

[user5670895]

The first example has the possibility of SQL injection. The second is using named parameters, so should be safe.

[UserInteractive]

The first one does not work for me. When term is 'foo' the binding is 'undefinedfooundefined'.

[SILENT]

@user128216 Why is the first example where('description', 'like', %${term}%) prone sql injection? Isn't this as safe as the parameter binding? The ref link doesn't mention where being prone to sql injection, only raw.

[Loren]

The term in a .where is escaped, so there's no risk of injection: github.com/knex/documentation/issues/73#issuecomment-5724821‌​53

[Mikael Lepistö]

The second one is badly wrong and shouldn't work at all (?? binding should be used only for identifiers, so if there is dot in term it will fail). First one should be fine except I have no idea if that actually is able to match % char.

[Mikael Lepistö]

By reading SQL docs this kind of make sense and could work. Though knex('users').whereRaw('?? like ? escape \\%, ['name', '%\\%foo%']) might be more near to working solution.