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.

alexpods · Accepted Answer · 2015-04-03 21:36:44Z

11

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.

edited Apr 3, 2015 at 21:36

answered Apr 3, 2015 at 18:21

alexpods

48.7k10 gold badges104 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tyler Over a year ago

I'm not using pg directly; using sequelize instead. It does look like they have a way to escape values as well: sequelize.getQueryInterface().escape(search_term). If you can incorporate this into your answer, I'm happy to mark it as a solution.

Tyler Over a year ago

Thanks for the update. Note that the format (params as 4th option to sequelize.query) has recently been deprecated: github.com/sequelize/sequelize/blob/master/…

Tyler Over a year ago

See here for new format with options.replacements: docs.sequelizejs.com/en/latest/docs/raw-queries/#replacements

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related