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

Question

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?

I tried everything with the latest pg package 8.8.0, node 12/14/16/18, postgres Postgres 13 and it all works for me: your initial code (and my answer lol). We will need more data on your environment to reproduce it (node version, pg version, etc) — Evgeniy Chekan
– Evgeniy Chekan, Commented Sep 8, 2022 at 14:37
I'll try to investigate on the Postgres version. The issue is occuring on postgres12.8 (AWS RDS version) — Johan
– Johan, Commented Sep 8, 2022 at 14:45
I'd suggest installing fresh postgres locally and trying it with your code. If that works, then the issue is on the RDS side. If it doesn't, then it's something with your code (which works for me) environment, and maybe try moving it elsewhere to debug further — Evgeniy Chekan
– Evgeniy Chekan, Commented Sep 9, 2022 at 13:18

Evgeniy Chekan · Accepted Answer · 2022-09-08 13:49:18Z

-1

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]);

answered Sep 8, 2022 at 13:49

Evgeniy Chekan

2,6651 gold badge17 silver badges23 bronze badges

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

1 Comment

Johan Over a year ago

Thank you for your answer, but that did't seems to do the job for me. Postgres throwing a new error 42P18: could not determine data type of parameter $1, forcing me to specify CONCAT('%', $1::text, '%'), but still, back to the same error again :(

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related