How To Write Parameterized Sql Query To Prevent SQL Injection?

I initially discovered that this was an issue when I tried to search for terms that had been prepended with a hashtag, which it turns out is a comment delimiter in SQL. The search

Solution 1:

I'm not a Knex.js user, but looking at the docs it seems that Knex's use of JavaScript object syntax to define predicates is how it achieves parameterization.

However as you're using built-in functions you need to use whereRaw.

Looking at the docs ( http://knexjs.org/#Builder-whereRaw ) and ( http://knexjs.org/#Raw-Bindings ) I think you want to do this:

.whereRaw('question LIKE :term OR note LIKE :term OR user_name LIKE :term', { term: '%' + term + '%' ] } )

Knex doesn't have an orWhereRaw, so you should use the longhand version if you want to logically separate the predicates:

term = '%' + term + '%';

.orWhere( knex.raw( 'question  LIKE ?', [ term ] ) )
.orWhere( knex.raw( 'note      LIKE ?', [ term ] ) )
.orWhere( knex.raw( 'user_name LIKE ?', [ term ] ) )

Note ? is for positional parameters, and :term is for named parameters.

Solution 2:

It seems that the only time in which you really need to worry about sql injection is if you are using knex.raw() or any other pure sql command. In other words, Knex escapes the input for you automatically.

As for the hashtag issue, after messing around with PG Commander I discovered that I could search for #'s just fine. I just needed to url encode hashtags before sending them to my backend... A little embarrassing but I learned something new today.