node-postgres: Error: syntax error at or near "$1" with super simple query

any query using .query(string, [value...], fn) gives me this error, even with the appropriate number of values, did the api change or something? Even simple stuff like:

    client.query('create user $1 with password \'Something1\'', ['tobi'], function(err){
      if (err) throw err;
      console.log('created');
    });

I’m probably doing something obvious wrong 😄

About this issue

Original URL
State: closed
Created 10 years ago
Comments: 19 (13 by maintainers)

Most upvoted comments

@visionmedia It’s always fun the first time you realize PostgreSQL server doesn’t accept parameters in certain places where you’d love to have them be used. Most commands don’t accept parameters and a few places in queries you think it would be nice to use them they aren’t accepted. 😦

The good news is there are manual escaping helpers already built into node-postgres as well for the times when you want to insert user created or untrusted content into a part of a query or command where parameters aren’t accepted.

https://github.com/brianc/node-postgres/blob/master/lib/client.js#L228 https://github.com/brianc/node-postgres/blob/master/lib/client.js#L247

I apologize these aren’t better documented.

If you’re using the native bindings they actually use the escape written into libpq. If you’re using the pure JavaScript client the escape is as close to a direct port of the libpq escape functions as possible.

+12

brianc on Mar 15, 2014

https://github.com/segmentio/pg-escape should be sufficient 😄

tj on Mar 17, 2014

I can’t find anything specifically to back that up from docs – so maybe I’m wrong 😦. I do see some people who’ve tried to do that and have had experienced the same error (in java): http://www.postgresql.org/message-id/925094a20608222109s438a5b41g2886f41e9ddf7417@mail.gmail.com

On another note if you want to do a quick check to see if query is work like you’d expect you could try the following (from the readme):

client.query('SELECT $1::int AS numbor', ['1'], function(err, result) {
  if (err) throw err;
  console.log(result.rows[0].numbor);
});

lalitkapoor on Mar 14, 2014