Search code examples
node.jspostgresqlpgword-cloud

Is it possible to use ts_stat with parameterized query Postgresql

I'm trying to use ts_stat from PostgreSQL on a ts_vector column. The problem is that I need to protect it from SQL injections, so I can't use "concat" string or replace ? to pass a "normal query" to ts_stat.

What I'm looking for is something like this: 

SELECT * FROM ts_stat($$ SELECT tsv_column FROM table where otherColumn ILIKE ? $$)

I'm using node.js with https://www.npmjs.com/package/pg to do the query. The ultimate goal is to be able to query a word and the count of its appearances like so:

word - count

apple - 650025

cookie - 1500

I'll use them as a word cloud. If you have other ideas that could help me doing this I'm open minded, but it needs to be pretty fast (maximum 6 seconds on a database with 80,000 entries on a column that contains a chunk of text). Right now I'm able to get what I want in 5 - 6 seconds but the query can't be parameterized and it needs to be.

Thanks!

Solution

  • Use the "format" function.

    SELECT * FROM ts_stat(format($$ SELECT tsv_column FROM table where otherColumn ILIKE %L $$,user_value_here));

    This won't protect the special meaning of user-supplied _ or %, but if you wanted to disallow those special meanings you probably shouldn't use ILIKE in the first place.