Column names with >1 apostrophe not escaped correctly in .query()

[jkgeyti]

Column names with >1 apostrophe not escaped correctly in .query():

var pg = require('pg'); // version 4.4.4
var conString = "postgres://...";

// pg.connect(conString, function(err, client, done) {  
//   client.query("CREATE TABLE atable ( \"column''name\" TEXT )");
// });

pg.connect(conString, function(err, client, done) {  
  client.query("SELECT * FROM atable");
});

$ node bug.js
undefined:4
this['column\''name'] = rowData[0] == null ? null : parsers[0](rowData[0]);
               ^^^^

SyntaxError: Unexpected token ILLEGAL
    at Function (native)
    at Result.addFields (.../node_modules/pg/lib/result.js:98:20)
    at Query.handleRowDescription (.../node_modules/pg/lib/query.js:54:16)
    at null.<anonymous> (.../node_modules/pg/lib/client.js:103:24)
    at emitOne (events.js:77:13)
    at emit (events.js:169:7)
    at Socket.<anonymous> (.../node_modules/pg/lib/connection.js:109:12)
    at emitOne (events.js:77:13)
    at Socket.emit (events.js:169:7)
    at readableAddChunk (_stream_readable.js:146:16)

[brianc]

node-postgres does not do any escaping of query text. The string is passed to the socket utf8 encoded with no modification. This is by design. If a higher level library using node-postgres is supposed to escape things for you then you might open an issue there; otherwise, you need to manually escape the string

[jkgeyti]

But the query is valid - It's a simple SELECT * FROM table, and column names are allowed to contain apostrophes in postgres. I don't see how a higher-level library can fix this.

The problem is that node-postgres tries to extract column names from the data returned from the database, and fails, because of the incorrect handling of column names in the (what seems to be) code generation in addFields.

[brianc]

ohhhh sorry! i see what youre saying now! sorry i am on vacation so didnt read closely. care to submit a PR with a fix for that issue?

[jkgeyti]

Done.

Apparently, this problem came up in #507 , but in the accepted fix, only the first apostrophe was being replaced (.replace("'","\\'")). I've changed it to .replace(/'/g,"\\'").

I further did a quick search, and found a few other calls to replace, where I think you'd want a /.../g instead, but I'm not familiar enough with the codebase, that I feel comfortable changing something like that. It's probably worth double checking, though!!

Enjoy your holiday :-)

[jkgeyti]

Author merged #935

Closing issue