CODEWITHSUNDEEP
node.jspostgresqlnode-postgres
Medallyon
Medallyon

Reputation: 120

How do I properly escape the single quote in node-postgres queries?

I need to execute the following query:

db.query("SELECT * FROM items WHERE name ILIKE '%$1%';", [ query ])

It seems like node-postgres doesn't replace the $1 parameter and I don't know why. My query works if I change the line to:

db.query(`SELECT * FROM items WHERE name ILIKE '%${query}%';`)

But now I have problems when the query contains single quotes (').

Should I replace all single quotes using regex (as in query.replace(/'/g, "''")) (I don't think that's recommended)?

Otherwise, how can I get node-postgres to accept my parameter? I think it has something to do with the encapsulating % symbols. I keep running into the same problem with this issue.

Upvotes: 1

Views: 1882

Answers (1)

mu is too short
mu is too short

Reputation: 434665

Your placeholder isn't replaced because '%$1%' is an SQL string literal that just happens to look like it contains a $1 placeholder. You can add the percents in JavaScript using string operations:

db.query("SELECT * FROM items WHERE name ILIKE $1", [ `%${query}%` ])
// ---------------------------------------------------^^^^^^^^^^^^

or in SQL using SQL's string operations:

db.query("SELECT * FROM items WHERE name ILIKE '%' || $1 || '%'", [ query ])
// --------------------------------------------^^^^^^----^^^^^^

Upvotes: 4

Related Questions