5

This is a follow up question to https://stackoverflow.com/a/50337990/1370984 .

It mentions knex('table').where('description', 'like', '%${term}%') as prone to sql injection attacks. Even a comment mentions the first case as prone to injection attacks. Yet the reference provided never mentions .where being prone to injection attacks.

Is this a mistake? Why would knex allow .where to be prone to injection attacks but not .whereRaw('description like \'%??%\'', [term]) . Aren't the arguments being parameterized in both cases?

SILENT
  • 3,916
  • 3
  • 38
  • 57
  • @tadman This is a real issue? I looked at knex's `where` documentation and it doesn't mention it being sql injection prone. http://knexjs.org/#Builder-where In fact, the only mention of sql injection attacks is for `Raw` - http://knexjs.org/#Raw . This is concerning. What other features of knex are prone to sql injection attacks? – SILENT Jan 08 '20 at 22:08
  • Knex maintenance here. Hi! I just wanted to mention also here that the premises of this question are all `false` and please ignore @tadman's comments here. One should not even use `??` binding in this case. **tl;dr fake news** – Mikael Lepistö Jan 10 '20 at 10:10
  • @MikaelLepistö Thanks for clarifying. – tadman Jan 10 '20 at 19:48

1 Answers1

10

This is a follow up question to https://stackoverflow.com/a/50337990/1370984 .

It mentions knex('table').where('description', 'like', '%${term}%') as prone to sql injection attacks. Even a comment mentions the first case as prone to injection attacks. Yet the reference provided never mentions .where being prone to injection attacks.

I'm knex maintainer and I have commented there that 

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

is NOT vulnerable to SQL injection attacks.

Is this a mistake? Why would knex allow .where to be prone to injection attacks but not .whereRaw('description like \'%??%\'', [term]) . Aren't the arguments being parameterized in both cases?

That .whereRaw is vulnerable when you interpolate values directly to sql string (like for example ?? identifier replacement does).

Correct use for .whereRaw in this case would be for example:

.whereRaw("?? like '%' || ? || '%'", ['description', term])

Where all identifiers are quoted correctly and term is sent to DB as parameter binding.

So the answer and most of the comments added to that answer are just plain wrong.

Mikael Lepistö
  • 18,909
  • 3
  • 68
  • 70