How to properly escape raw SQL query (plainto_tsquery) in Postgres / node

Question

I'm writing a raw SQL query to implement Postgres full text search in my node backend. I've looked through the official docs, which state:

plainto_tsquery transforms unformatted text querytext to tsquery. The text is parsed and normalized much as for to_tsvector, then the & (AND) Boolean operator is inserted between surviving words.

but I'm not familiar enough with all the different SQL injection techniques to know for certain whether the following will be properly escaped:

'SELECT * FROM "Products" WHERE "catalog_ts_vector" @@ plainto_tsquery(\'english\', ' + search_term + ')'

The user will be able to enter whatever search_term they want via the URI.

Do I need to do further escaping/manipulation, or is this functionality fully baked into plainto_tsquery() and other Postgres safeguards?

Edit

As a side note, I plan to strip out most non-alphanumeric characters (including parentheses) with .replace(/[^\w-_ .\&]|\(\)/g, ' '); that should go a long way, but I'm still curious if this is even necessary.

Answer 1

Most likely you're using pg module as PostgreSQL client for node.js. In this case you don't need to worry about sql injection, pg prevents it for you. Just not use string concatination to create query, use parameterized queries (or prepared statement):

var sql = 'SELECT * FROM "Products" WHERE "catalog_ts_vector" @@ plainto_tsquery(\'english\', $1)';
var params = [search_term];

client.query(sql, params, function(err, result) {
    // handle error and result here
});

Also look at Prepared Statment part of pg wiki and PostgreSQL PREPARE statement.

UPD What about sequelize - it uses pg module by default, but you can specify you preferable pg client in dialectModulePath config parameter (see here). Also you can use parameterized queries in sequelize too. Even better - you can use named parameters. So you code will be:

var sql = 'SELECT * FROM "Products" WHERE "catalog_ts_vector" @@ plainto_tsquery(\'english\', :search_term)';
var params = { search_term: search_term }

sequelize.query(sql, Product, null, params).then(function(products) {
    // handle your products here
})

Where Product is your sequelize product model.