search

brianc / node-postgres

PostgreSQL client for node.js.
https://node-postgres.com
MIT License
12.3k stars 1.23k forks source link

Syntax Error at end of input, but query runs and returns results anyways #751

Closed danelfrink closed 9 years ago

danelfrink commented 9 years ago

The query is syntactically correct, and runs without errors when pasted directly into postgres. However, I get an syntax error immediately causing my code to move on before the result set is returned.

Example Query: select "vehicle"."year" as "Year", "vehicle"."make" as "Make", "vehicle"."model" as "Model", "vehicle"."submodel" as "SubModel" from "coverking"."item" inner join "coverking"."vehicle" on "vehicle"."vehicle_id" = "item"."vehicle_id" where "vehicle"."make" in ('Cadillac', 'Chevrolet') and "vehicle"."model" in ('Eldorado', 'Equinox', 'Escalade', 'Escalade ESV', 'Escalade EXT', 'Express 1500', 'Express 2500', 'Express 3500', 'Fleetwood') and "vehicle"."year" in ('2015', '2014', '2013', '2012') and "item"."position" in ('Front') INTERSECT select "vehicle"."year" as "Year", "vehicle"."make" as "Make", "vehicle"."model" as "Model", "vehicle"."submodel" as "SubModel" from "coverking"."item" inner join "coverking"."vehicle" on "vehicle"."vehicle_id" = "item"."vehicle_id" where "vehicle"."make" in ('Cadillac', 'Chevrolet') and "vehicle"."model" in ('Eldorado', 'Equinox', 'Escalade', 'Escalade ESV', 'Escalade EXT', 'Express 1500', 'Express 2500', 'Express 3500', 'Fleetwood') and "vehicle"."year" in ('2015', '2014', '2013', '2012') and "item"."position" in ('Middle or 2nd Row') INTERSECT select "vehicle"."year" as "Year", "vehicle"."make" as "Make", "vehicle"."model" as "Model", "vehicle"."submodel" as "SubModel" from "coverking"."item" inner join "coverking"."vehicle" on "vehicle"."vehicle_id" = "item"."vehicle_id" where "vehicle"."make" in ('Cadillac', 'Chevrolet') and "vehicle"."model" in ('Eldorado', 'Equinox', 'Escalade', 'Escalade ESV', 'Escalade XT', 'Express 1500', 'Express 2500', 'Express 3500', 'Fleetwood') and "vehicle"."year" in ('2015', '2014', '2013', '2012') and "item"."position" in ('Rear')

ERROR MESSAGE:

{ [error: syntax error at end of input]

[message]: 'syntax error at end of input', name: 'error', length: 84, severity: 'ERROR', code: '42601', detail: undefined, hint: undefined, position: '74', internalPosition: undefined, internalQuery: undefined, where: undefined, file: 'scan.l', line: '1045',

routine: 'scanner_yyerror' }

STACK TRACE:

error: syntax error at end of input at Connection.parseE (/vagrant/restapi/node_modules/pg.js/lib/connection.js:534:11) at Connection.parseMessage (/vagrant/restapi/node_modules/pg.js/lib/connection.js:361:17) at Socket. (/vagrant/restapi/node_modules/pg.js/lib/connection.js:105:22) at Socket.emit (events.js:95:17) at Socket. (_streamreadable.js:765:14) at Socket.emit (events.js:92:17) at emitReadable (_stream_readable.js:427:10) at emitReadable (_stream_readable.js:423:5) at readableAddChunk (_stream_readable.js:166:9) at Socket.Readable.push (_stream_readable.js:128:10) error: syntax error at end of input at Connection.parseE (/vagrant/restapi/node_modules/pg.js/lib/connection.js:534:11) at Connection.parseMessage (/vagrant/restapi/node_modules/pg.js/lib/connection.js:361:17) at Socket. (/vagrant/restapi/node_modules/pg.js/lib/connection.js:105:22) at Socket.emit (events.js:95:17) at Socket. (_streamreadable.js:765:14) at Socket.emit (events.js:92:17) at emitReadable (_stream_readable.js:427:10) at emitReadable (_stream_readable.js:423:5) at readableAddChunk (_stream_readable.js:166:9) at Socket.Readable.push (_stream_readable.js:128:10)

Let me know if I need to add anything else. Thanks.

hkocam commented 9 years ago

hi,

you need to escape either single or double-quotes depending on what you use in your js

kind regards

2015-03-20 17:46 GMT+01:00 danelfrink notifications@github.com:

The query is syntactically correct, and runs without errors when pasted directly into postgres. However, I get an syntax error immediately causing my code to move on before the result set is returned.

Example Query: select "vehicle"."year" as "Year", "vehicle"."make" as "Make", "vehicle"."model" as "Model", "vehicle"."submodel" as "SubModel" from "coverking"."item" inner join "coverking"."vehicle" on "vehicle"."vehicle_id" = "item"."vehicle_id" where "vehicle"."make" in ('Cadillac', 'Chevrolet') and "vehicle"."model" in ('Eldorado', 'Equinox', 'Escalade', 'Escalade ESV', 'Escalade EXT', 'Express 1500', 'Express 2500', 'Express 3500', 'Fleetwood') and "vehicle"."year" in ('2015', '2014', '2013', '2012') and "item"."position" in ('Front') INTERSECT select "vehicle"."year" as "Year", "vehicle"."make" as "Make", "vehicle"."model" as "Model", "vehicle"."submodel" as "SubModel" from "coverking"."item" inner join "coverking"."vehicle" on "vehicle"."vehicle_id" = "item"."vehicle_id" where "vehicle"."make" in ('Cadillac', 'Chevrolet') and "vehicle"."model" in ('Eldorado', 'Equinox', 'Escalade', 'Escalade ESV', 'Escalade EXT', 'Express 1500', 'Express 2500', 'Express 3500', 'Fleetwood') and "vehicle"."year" in ('2015', '2014', '2013', '2012') and "item"."position" in ('Middle or 2nd Row') INTERSECT select "vehicle"."year" as "Year", "vehicle"."make" as "Make", "vehicle"."model" as "Model", "vehicle"."submodel" as "SubModel" from "coverking"."item" inner join "coverking"."vehicle" on "vehicle"."vehicle_id" = "item"."vehicle_id" where "vehicle"."make" in ('Cadillac', 'Chevrolet') and "vehicle"."model" in ('Eldorado', 'Equinox', 'Escalade', 'Escalade ESV', 'Escalade XT', 'Express 1500', 'Express 2500', 'Express 3500', 'Fleetwood') and "vehicle"."year" in ('2015', '2014',

'2013', '2012') and "item"."position" in ('Rear')

ERROR MESSAGE:

{ [error: syntax error at end of input]

[message]: 'syntax error at end of input', name: 'error', length: 84, severity: 'ERROR', code: '42601', detail: undefined, hint: undefined, position: '74', internalPosition: undefined, internalQuery: undefined, where: undefined, file: 'scan.l', line: '1045', routine: 'scanner_yyerror' } STACK TRACE:

error: syntax error at end of input at Connection.parseE (/vagrant/restapi/node_modules/pg.js/lib/connection.js:534:11) at Connection.parseMessage (/vagrant/restapi/node_modules/pg.js/lib/connection.js:361:17) at Socket. (/vagrant/restapi/node_modules/pg.js/lib/connection.js:105:22) at Socket.emit (events.js:95:17) at Socket. (

_streamreadable.js:765:14) at Socket.emit (events.js:92:17) at emitReadable (

_stream_readable.js:427:10) at emitReadable (_stream_readable.js:423:5) at readableAddChunk (_stream_readable.js:166:9) at Socket.Readable.push (_stream_readable.js:128:10) error: syntax error at end of input at Connection.parseE (/vagrant/restapi/node_modules/pg.js/lib/connection.js:534:11) at Connection.parseMessage (/vagrant/restapi/node_modules/pg.js/lib/connection.js:361:17) at Socket. (/vagrant/restapi/node_modules/pg.js/lib/connection.js:105:22) at Socket.emit (events.js:95:17) at Socket. (_streamreadable.js:765:14) at Socket.emit (events.js:92:17) at emitReadable (_stream_readable.js:427:10) at emitReadable (_stream_readable.js:423:5) at readableAddChunk (_stream_readable.js:166:9) at Socket.Readable.push (_stream_readable.js:128:10)

Let me know if I need to add anything else. Thanks.

— Reply to this email directly or view it on GitHub https://github.com/brianc/node-postgres/issues/751.

ngervasi commented 9 years ago

Or even better just use double quotes in the sql for aliases and pass the parameters using the parameters array, this also protects you against SQL injections:

var sql = 'SELECT a AS "first", b AS "second" FROM table WHERE x IN ($1,$2,$3)'
client.query(sql, ['this','that','another'], function(err, result) { ... })
danelfrink commented 9 years ago

Thank you, but I'm using KnexJS to build the queries so I just copied what it outputs as the query. and this is a tool developed for local use so injection attacks are not of concern for this project.

danelfrink commented 9 years ago

Found out something else was crashing my system when this query was called. Thanks, guys!