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

[tj]

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 :D

[lalitkapoor]

@visionmedia I don't think prepared statements can be used with the create user command (or similar). I'll see if I can find documentation to back this up.

[lalitkapoor]

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]

Looks like I'm not able to prepare the statement using the prepare command in the psql shell (9.3):

PREPARE cu (text) AS CREATE USER $1 WITH PASSWORD 'something';
ERROR:  syntax error at or near "CREATE"

[tj]

Ah I see, I thought the injection escaping was done by this library for .query()

[lalitkapoor]

Using .query with arguments string, [value...], fn will generate a prepared statement if I'm not mistaken.

prepared statements have several benefits include performance enhancements - via cached query planning and also security - injection escaping. So the way you used the query method was just fine :) Unfortunately it seems prepared statements can't be used with the create command. :/

So you'll want to do some injection escaping on your own, build the query string, and then just pass string, fn as the arguments to query.

Hope that helps clarify things :)

[lalitkapoor]

More details on how sql injection is handled in this lib: https://github.com/brianc/node-postgres/wiki/FAQ#8-does-node-postgres-handle-sql-injection

[tj]

Cool thanks! Maybe we can add an alternative method so people don't have to re-implement escaping outside of the library

[brianc]

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

[tj]

Haha yeah :D I've only used mysql/sqlite, I actually thought it was a feature of this lib. Would you be interested in some sort of method that allows escaping for cases like this? Not sure off hand what a decent method name would be

[brianc]

Yeah, I think it would be handy for sure. I have a few thoughts...

However it ends up it should be pretty clear about being intended for when you cannot add parameters to a query in a normal way. I think having to manually escape your inputs with hand-rolled string concatenation when they cannot be parameterized is a pain and error prone, but I don't want it to be confused as a general purpose substitute for parameterized queries.

So I'm wondering if, like you said, it could be it's own module. Something like pg-escape and you could use it like...

var escape = require('pg-escape')
client.query(escape('create user ? with password \Something\`, ['my_username']))

I used the ? placeholder from mysql there instead of the PostgreSQL $n placeholder because that might advertise it more as something that's not happening within the PostgreSQL server itself? Not sure...maybe that's ugly? Another advantage of it being it's own module is it could contain a lovely readme file that explained something like "Generally you wanna use parameterized SQL, but sometimes commands don't take parameters where you want. You can use this to safely escape those commands" or something.

Of course it could also just be added onto the query or client object as another method. My only hesitation with that is this module already has a lot of functionality, as you've probably noticed. I'm actually working right now in spinning parts of it out into other modules for better reuse and to hopefully lower the barrier of entry for collaboration. It it were stuck onto the client object it could look like this maybe...

var text = client.escape('create user $1 with password \Something\', ['my_username'])
client.query(text, function(err, rows) {

})

or if the client-side escaping used the question mark placeholder you could get crazy:

var text = client.escape('select ? from my_table where id = $1', ['my_column_name'])
client.query(text, ['some_id'], function(err, rows) {

});

What do you think?

[hoegaarden]

I'd like to see a module like pg-escape ... the client could depend on it and export it again like client.escape(...).

About ? vs $n: I think, both are not sufficient. What should ? or $1 stand for? An identifier? A literal? I think it should be handled more like by the format function, so i propose the same "type system":

• %s for a simple string
• %I for an identifier
• %L for a literal

Good? Bad? Ugly?

[tj]

@hoegaarden sounds good to me, I'll whip something up

[tj]

quick question, what's the reasoning behind using the escaped mode for https://github.com/brianc/node-postgres/blob/master/lib/client.js#L247? I'm not too familiar, but if all it does is escape slashes couldn't a normal string constant be used?

[benighted]

@visionmedia that function also escapes single quotes, which along with backslashes are the only things you need to escape in postgres string literals. Single quotes are escaped with a preceding single quote, and backslashes are escaped with a preceding backslash. The c-style backslash escaping is non-standard and will produce a warning unless the literal is prefixed with E as you can see in that function when hasBackslash is true.