37

I'm using a MySql database and was trying to find a MySQL alternative to tedious.js (a SQL server parameterised query builder).I'm using Node.js for my backend.

I read that the .raw() command from knex.js is susceptible to sql injection, if not used with bindings. But are the other commands and knex.js as a whole safe to use to prevent sql injection? Or am I barking up the wrong tree?

Harsh Saudagar
  • 458
  • 1
  • 5
  • 14

1 Answers1

97

Read carefully from knex documentation how to pass values to knex raw (https://knexjs.org/guide/raw.html#raw).

If you are passing values as parameter binding to raw like:

knex.raw('select * from foo where id = ?', [1])

In that case parameters and query string are passed separately to database driver protecting query from SQL injection.

Other query builder methods always uses binding format internally so they are safe too.

To see how certain query is passed to database driver one can do:

knex('foo').where('id', 1).toSQL().toNative()

Which will output SQL string and bindings that are given to driver for running the query (https://runkit.com/embed/2yhqebv6pte6).

Biggest mistake that one can do with knex raw queries is to use javascript template string and interpolate variables directly to SQL string format like:

knex.raw(`select * from foo where id = ${id}`) // NEVER DO THIS

One thing to note is that knex table/identifier names cannot be passed as bindings to driver, so with those one should be extra careful to not read table / column names from user and use them without properly validating them first.

Edit:

By saying that identifier names cannot be passed as bindings I mean that when one is using ?? knex -binding for identifier name, that will be rendered as part of SQL string when passed to the database driver.

Jan
  • 453
  • 1
  • 5
  • 13
Mikael Lepistö
  • 18,909
  • 3
  • 68
  • 70
  • Thanks for the swift reply. This helped a lot! – Harsh Saudagar Apr 05 '18 at 07:01
  • 1
    @mikael-lepistö I was able to use table/identifier names as bindings with the `??` syntax. Fun fact, I learnt about the syntax by finding the issue you opened about it https://github.com/tgriesser/knex/issues/1003 – Philippe Hebert Aug 09 '18 at 13:27
  • 3
    @PhilippeHebert I think it is easiest accessible though the Knex Ref API: https://knexjs.org/#Ref – Jakob Oct 12 '18 at 06:42
  • 1
    So the following is also protected, right? `knex('table').where('description', 'like', '%${term}%')` . For some reason, https://stackoverflow.com/questions/50327070/how-do-i-escape-in-knex-where-like-query/50337990#50337990 says otherwise and they are referring to your answer. – SILENT Jan 08 '20 at 22:39
  • Where like is protected. Ref: https://github.com/knex/documentation/issues/73#issuecomment-572482153 – Loren Jan 09 '20 at 20:27
  • @SILENT yup it is safe, but still I don't know if it matches % sign correctly if it is in name. – Mikael Lepistö Jan 09 '20 at 23:41
  • Thanks @Loren and MikaelLepistö . As long as the query is protected and works, I'm good. I searched the web and posted this question on https://stackoverflow.com/questions/59654157/is-knex-where-prone-to-sql-injection-attacks but forgot to post the question on knex's github. – SILENT Jan 10 '20 at 04:35
  • I am unclear about why we should not use string interpolation in knex.raw. Could you throw more light on it. – Zakir saifi Feb 21 '20 at 08:01
  • @Zakirsaifi because it is exactly the same that string concatenation which is the most common way to do sql injection holes. https://en.wikipedia.org/wiki/SQL_injection – Mikael Lepistö Feb 24 '20 at 12:26
  • I'm unclear as to whether the answer to this question is 'yes' or 'no' for non-raw queries. Can any of you clarify? – Code Wiget May 06 '20 at 15:36
  • @CodeWiget if you use parameter bindings for raw queries like in the first example with `?` -mark in the query, then that parameter is protected from sql injection. – Mikael Lepistö May 07 '20 at 08:48
  • @MikaelLepistö What about for standard queries, like knex.query().insert(...) – Code Wiget May 07 '20 at 16:09
  • Out of curiosity: is there any use case for direct variable interpolation in raw queries? If not, why is not prevented by the library? – Joel H Nov 11 '20 at 17:43
  • 1
    @JoelH I don't think it is possible to prevent that (it is a javascript feature). They can be useful for example if you are generating SQL strings. – Mikael Lepistö Nov 12 '20 at 07:35
  • If you want string interpolation, you can use the @databases npm library. npm i @databases/pg. Its sql method prevents injection. Its usefull for raw queries, it can go along with knex – Ali Mert Çakar Feb 22 '21 at 10:19