brianc/node-postgres

Table/Column names in parameterized queries

Closed this issue · 5 comments

stepankuzmin commented

Hi there! It seems that there is no way to build parameterized query with table/column substitutions.

Eg.

const query = 'select distinct GeometryType($1) as geomtype from $2';

client
    .query(query, [geometry_field, table])
    .then(({ rows }) => rows[0].geomtype)
    .catch(error => console.error(error));

I'm getting

{ error: syntax error at or near "$2"
    at Connection.parseE (/Users/stepan/projects/urbica-martin/node_modules/pg/lib/connection.js:546:11)
    at Connection.parseMessage (/Users/stepan/projects/urbica-martin/node_modules/pg/lib/connection.js:371:19)
    at Socket.<anonymous> (/Users/stepan/projects/urbica-martin/node_modules/pg/lib/connection.js:114:22)
    at emitOne (events.js:115:13)
    at Socket.emit (events.js:210:7)
    at addChunk (_stream_readable.js:266:12)
    at readableAddChunk (_stream_readable.js:253:11)
    at Socket.Readable.push (_stream_readable.js:211:10)
    at TCP.onread (net.js:585:20)
  name: 'error',
  length: 91,
  severity: 'ERROR',
  code: '42601',
  detail: undefined,
  hint: undefined,
  position: '51',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'scan.l',
  line: '1086',
  routine: 'scanner_yyerror' }
abenhamdine commented

It's not a limitation of this library, but a limitation of postgres itself.
Column names and table names are only allowed as litterals in a parameterized query.

You should either generate the wanted query text in a plsql function or with javascript client side.

  • 👍3
stepankuzmin commented

I see, thanks for the reply!

govindrai commented

@abenhamdine @brianc pg-promise supports parameterization of column/table names, see https://www.npmjs.com/package/pg-promise#sql-names. Can we support this in node-postgres now?

charmander commented

@govindrai No, that’s not actual parameterization, just a query-building convenience. The parameters you see used with node-postgres are a feature of PostgreSQL, and nothing has changed there. https://www.postgresql.org/docs/current/sql-prepare.html

govindrai commented

Ah, I apologize for the disturbance and I appreciate and thank you for that re-clarification!