node.js + postgres: Syntax error at or near "$1"

Question

I am having trouble understanding the npm package pg and its "dynamic statements".

I want to be able to generate INSERT INTO statements but I only get syntax error in return

var pg = require('pg');
var connectionString = 'postgres://postgres@localhost/db';

pg.connect(connectionString, function(err, client, done) {
  if (err) { return console.error(err); }

  var queryConfig = {
    text: 'INSERT INTO users($1, $2, $3) VALUES($4, $5, $6);',
    values: ['username', 'firstname', 'lastname', 'ab', 'A', 'B']
  };

  client.query(queryConfig, function (err, result) {
    if (err) { return console.error(err); }
    done();
  });
});

The error I get is:

{ [error: syntax error at or near "$1"]
  name: 'error',
  length: 84,
  severity: 'ERROR',
  code: '42601',
  detail: undefined,
  hint: undefined,
  position: '19',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  file: 'scan.l',
  line: '1053',
  routine: 'scanner_yyerror' }

Can anybody help me to understand what I'm doing wrong?

mu is too short · Accepted Answer · 2014-05-22 04:35:18Z

28

Databases generally don't let you use placeholders for identifiers (table names, column names, ...). PostgreSQL is complaining because you're trying to use the first three placeholders as column names:

INSERT INTO users($1, $2, $3) ...

You can think of identifiers like variable names in JavaScript: you can't put a variable name into another variable without some sort of eval chicanery. Similarly, you can't put a column name in a placeholder.

You should specify the column names without placeholders:

var queryConfig = {
  text: 'INSERT INTO users(username, firstname, lastname) VALUES($1, $2, $3);',
  values: ['ab', 'A', 'B']
};

edited May 22, 2014 at 4:35

answered May 21, 2014 at 23:23

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

7 Comments

mraxus Over a year ago

Cewl, I was thinking it was something like that. Thx mu!

mu is too short Over a year ago

@KentWong Yes, you can't use placeholders for identifiers (i.e. table names, column names, ...), placeholders only work with values. So $1, $2, and $4 wouldn't work there.

U2ros Over a year ago

Okay, but what if you need the table name to be dynamic?

mu is too short Over a year ago

@U2ros Then you're stuck with string manipulation. The database interface should have a method for quoting/escaping identifiers that you'd use to make the dynamic table name safe or you could whitelist the table name to only allow known safe values.

mu is too short Over a year ago

@KFunk As I said above, look for a "quote identifier" function in the database interface, then you'll be able to properly quote things and avoid injection issues. Alternatively, whitelist the identifiers.

|

Collectives™ on Stack Overflow

node.js + postgres: Syntax error at or near "$1"

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related