How to parameterize column names in a pg query

Question

I am trying to dynamically change the column queried in a WHERE statement.

My code looks like this

const pg = require('pg');
const pool = new pg.Pool();
await pool.connect();

let selector = {
  key: 'foo',
  value: 'bar',
};

const result = await pool.query(`SELECT * FROM foobar WHERE ${selector.key}=$1::text`, [selector.value]);

There is only a few key values I switch between. While the key is pre-defined and not from user input I would prefer not to use a template string.

Is there a way to parameterize the column name?

I have tried a few ways.

Just using a default parameter

await pool.query('SELECT * FROM foobar WHERE $1=$2::text', [selector.key, selector.value]);

This doesn't work as it parameterizes the key as a string so is just doing a string comparison.

Casting as oid (this was mentioned somewhere but I can't remember where)

await pool.query('SELECT * FROM foobar WHERE $1::oid=$2::text', [selector.key, selector.value]);

This throws an error as pg doesn't have a way to cast text to oid.

Luke · Accepted Answer · 2021-04-28 21:36:14Z

1

+50

Plpgsql could be a possible solution. Consider the following function:

CREATE OR REPLACE FUNCTION run_prepared_on_foobar(q text) RETURNS foobar AS $$
DECLARE
    retval foobar;
BEGIN
    EXECUTE q INTO retval;
    RETURN retval;
END $$ LANGUAGE plpgsql;

Once this is in place, you can then call it by passing any prepared statement:

SELECT * FROM run_prepared_on_foobar('SELECT * FROM foobar WHERE '||$1::text||'='||$2::int)

This is extremely poor in performance since the planner will not be able to peek inside what is going on in the function. All estimates will be random.

Shenanigans of plpgsql aside, a better approach would be to have extra code to switch over different queries depending on external conditions.

answered Apr 28, 2021 at 21:36

Luke

2,5061 gold badge32 silver badges41 bronze badges

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

1 Comment

Jin Thakur Over a year ago

The question was for query parameters and not executing a parameterized function.

ax. · Accepted Answer · 2021-04-23 19:52:11Z

1

You cannot use postgres parameters as identifiers (=column name). (1, via 2)

What's wrong with your current code's template string?

edited Apr 23, 2021 at 19:52

answered Apr 23, 2021 at 19:46

ax.

60.4k9 gold badges85 silver badges75 bronze badges

2 Comments

Tom Over a year ago

That's unfortunate. It introduces the possibility of SQL injection if a user is able to manipulate the key.

ax. Over a year ago

Didn't you say the key is pre-defined and not from user input?

Nico Serrano · Accepted Answer · 2023-07-11 05:29:10Z

1

As previous answers state: column names cannot be used in parameterized queries, they need to be specified in the SQL string. To escape any potential injections I used:


import { Client, Pool } from 'pg';

const { escapeIdentifier } = new Client();
const pool = new Pool({ /*config here*/ });

const columnName = 'id';
const userId = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11';

const result = pool.query(
  `SELECT * FROM users WHERE ${escapeIdentifier(columnName)} = $1`,
  [userId]
);

Source: https://node-postgres.com/apis/utilities#pgescapeidentifier

answered Jul 11, 2023 at 5:29

Nico Serrano

8279 silver badges15 bronze badges

Collectives™ on Stack Overflow

How to parameterize column names in a pg query

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related