prepared statements: parameters don’t work with ORDER BY

[scottsd]

queries such as “SELECT * FROM city WHERE countrycode=$1 ORDER BY district” are ok.
However, when using “ORDER BY $1”, then ORDER is ignored.

How to replicate:
psql -f world.sql (https://gist.github.com/scottsd/5118089)

pg.connect(process.env.DATABASE_URL, function(err, db) {
var sql = "SELECT * FROM city "
sql += " WHERE countrycode='PHL' "
sql += " ORDER BY $1 LIMIT 20 OFFSET 20"
console.log(sql)

        db.query(sql, ["district, name"], function(err, result) {
            responseBody.data = result.rows

            res.send(JSON.stringify(responseBody))
        })

    })

results:
{
"data": [
{
"id": 789,
"name": "Iligan",
"countrycode": "PHL",
"district": "Central Mindanao",
"population": 285061
},
{
"id": 790,
"name": "Calamba",
"countrycode": "PHL",
"district": "Southern Tagalog",
"population": 281146
},
{
"id": 791,
"name": "Mandaluyong",
"countrycode": "PHL",
"district": "National Capital Reg",
"population": 278474
},
…

[brianc]

I seriously appreciate the really good details you provided in this issue. Unfortunately, parameters are not supported in LIMIT, GROUP, or ORDER BY clauses within PostgreSQL (or any sql dialect I know of actually). I'm not sure why...maybe it has to do with not being able to generate a query plan effectively with dynamic parameters there. The only place you can use parameters that I know of is in the WHERE clause. That's just how PostgreSQL (and other flavors) work.

[brianc]

As a follow up - it's extra important you sanitize the data from the user if you're using it to build dynamic queries. I suggest possibly looking up the column names for the table from PostgreSQL and then "grabbing the column name" from that list by comparing it to the dynamic value case insensitive or something. Or using a white-list approach anyway.

[scottsd]

I see. Note that both LIMIT and OFFSET work for me.
Thanks for the tip on sanitization.

[brianc]

Ah sorry! I was wrong about LIMIT/OFFSET. 😦

[H4oK3]

"parameters are not supported in ORDER BY"; is this documented anywhere? I just noticed that on some platform it does not work, I would like to know is it a defined behavior or a bug?

[charmander]

@H4oK3 Parameters always represent values, so when you use them in ORDER BY it’s as values, e.g. ORDER BY 'foo'.

[brianc]

Its not documented in node-postgres because its not specific to this lib actually, its just how postgres itself works. https://www.postgresql.org/docs/9.5/xfunc-sql.html Their docs should cover the supported places for prams. Some higher level node libs will do client-side string substitution in your queries and allow parameters of different places and pass those things to node-postgres. Thats totally cool if you wanna use those, node-postgres specifically is concerned with implementing the postgres client protocol so it doesnt do that.

…

On Fri, Dec 14, 2018 at 9:52 PM Charmander ***@***.***> wrote: @H4oK3 <https://github.com/H4oK3> Parameters always represent values, so when you use them in ORDER BY it’s as values, e.g. ORDER BY 'foo'. — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#300 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AADDocJLFH8NfFoAXAhaRCVm8w9tryhaks5u5HIDgaJpZM4AfFto> .