CODEWITHSUNDEEP
javascriptsqlnode.jspostgresql
Tom
Tom

Reputation: 159

How to parameterize column names in a pg query

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.

  1. 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.

  1. 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.

Upvotes: 0

Views: 1169

Answers (3)

Nico Serrano
Nico Serrano

Reputation: 797

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

Upvotes: 1

Luke
Luke

Reputation: 2454

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.

Upvotes: 1

ax.
ax.

Reputation: 59927

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

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

Upvotes: 1

Related Questions