How to declare a string with double quotes in Query config object in node-postgres

Question

I'm using node.js to access a PostgreSQL table using a Query config object;

The select I want to build is 'SELECT * FROM "Customers" WHERE "CustomersID" = 1' which works fine with the DB.

Now, this is the Query object I'm using;

const query = {
   text: 'SELECT * FROM $1 WHERE "CustomersID" = $2',
   values : ["Customers",1],
}

but I get "syntax error at or near "$1" error. I tried different ways with ' or/and " but doesn't seem to work for me.

What is the right way of declaring $1 as a string with double quotes in values array?

Thank Gus

Answer 1

Parameterized Queries / Prepared Statements are formatted on the server-side, which disallows variables for columns or table names, to counter SQL injection, which is sufficient though an unnecessary overkill, and thus a limitation imposed by PostgreSQL server.

Separate escaping column/table names is actually sufficient for protecting against SQL injection, but it must be done on your side, and the basic driver node-postgres does not support it within its query formatting.

As an alternative, pg-promise fully supports this:

db.query('SELECT * FROM $1:name WHERE "CustomersID" = $2', ['Customers', 1]);
// OR:
db.query('SELECT * FROM $1~ WHERE "CustomersID" = $2', ['Customers', 1]);

It uses its own SQL Names notation to properly escape column/table names. We can append either :name or ~ to the variable to indicate that the variable represents an SQL Name, as opposed to a regular value.