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.
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+":*")