Escaping a string with a single quote in parameterized query with node-postgres

bob6664569 0
Viewed 27

The issue

I'm currently facing an issue using node-postgres parameterized queries:

const pg = require('pg'); // Latest package version yet, 8.8.0
// ...Skipping connecting stuff...
const searchString = "%let's do it%";
const res = pg.query('SELECT name FROM folders WHERE name ILIKE $1', [searchString]);

I originally thought that the parameterized query was supposed to handle the escaping mechanism on its own, but in fact it doesn't appear to be the case, this query causing the following error to be thrown on the postgres server: 42601: syntax error at or near "let"

First try

When I try to manually escape the string with a double quote:

const searchString = "%let's do it%";
const res = pg.query('SELECT name FROM folders WHERE name ILIKE $1', [searchString.replace("'", "''"]);

This time I got no error, but also no results despite several matching titles in the table. In fact, running this query actually even shows multiple results:

SELECT name FROM folders WHERE name ILIKE '%let''s do it%'

Second experiment

Same thing with an injection-friendly code that succeeds beautifully:

const res = pg.query(`SELECT name FROM folders WHERE name ILIKE '%${searchString.replace("'", "''")}%'`);

Result

I'm still not getting any success with the parameterized queries... Is there something I'm doing wrong?

node-postgres postgresql node.js
1 Answers

It is possible that the problem is not with the quote symbol, but with the percent symbols. Your initial code should work, just move percent symbols out of the param:

const searchString = "%let's do it%";
const res = pg.query(`SELECT name FROM folders WHERE name ILIKE CONCAT('%', $1, '%')`, [searchString]);
Evgeniy Chekan0
Related