CODEWITHSUNDEEP
sqlpostgresqlescapingfull-text-searchknex.js
cameron1024
cameron1024

Reputation: 10196

Knex not properly escaping raw postgres queries

I am using Knex (with typescript) to try to query a postgres database. My database table products has a column name that I want to search through as the user types into a search box. For example, a query of just the letter 'p' should return all products with a name that contains a word that begins with 'p'. For this, I am using the ts_vector and ts_query functions. My query looks like this:

const query = ... // got from user input
const result = await knex(knex.raw('products'))
  .whereRaw(`to_tsvector(name) @@ to_tsquery('?:*')`, query)
  .select('*')

When I run this query, I get the following error:

Unhandled error { error: select * from products where to_tsvector(name) @@ to_tsquery('$1:*') - bind message supplies 1 parameters, but prepared statement "" requires 0

If I replace the whereRaw line with: .whereRaw(`to_tsvector(name) @@ to_tsquery('p:*')`), it correctly runs the query, selecting products whose names contain words beginning with a P.

It seems like there is some conflict with the postgres syntax and knex's raw queries. I want to use a raw query over using `${query}:*` because I want my inputs to be sanitized and protected from SQL injection. How can I get Knex to properly escape this?

I have tried various combinations of quotes, slashes and colons, but none seem to work. Any help would be appreciated.

Upvotes: 0

Views: 1127

Answers (1)

jjanes
jjanes

Reputation: 44363

PostgreSQL doesn't process placeholders when they are inside quotes (and I am a little surprised that knex does).

You need to do the concatenation explicitly, either inside PostgreSQL:

.whereRaw(`to_tsvector(name) @@ to_tsquery(? ||':*')`,query)

Or inside typescript:

.whereRaw(`to_tsvector(name) @@ to_tsquery(?)`, query+":*")

Upvotes: 3

Related Questions