Yaur Yaur - 3 months ago 18
Node.js Question

"non-integer constant in ORDER BY" when using pg-promise with named parameters

I am trying to write a simple query using the pgp-promise library. My original implementation looks like:

var bar = function(orderBy){
var qs = 'select * from mytable order by ${orderBy};';
return db.many(qs,{orderBy:orderBy});
}
...
bar('id').then(...)


But this gives an error of
non-integer constant in ORDER BY

I have also tried adding quotes aroung
${orderBy}
and adding double quotes to the orderBy paramater to no avail. I have a working solution by doing
var qs = 'select * from mytable order by "' + orderBy + '";'
though it should be obvious why I don't want code like that in the project.

My question: Is there a way to get pg-promise to build a query with an order by clause that isn't vulnerable to sql injection?

Answer

Is there a way to get pg-promise to build a query with an order by clause that isn't vulnerable to sql injection?

The value for ORDER BY clause is an SQL name, and as such it is formatted using the SQL Names format:

var bar = function(orderBy) {
  var qs = 'select * from mytable order by ${orderBy~}';
  return db.many(qs, {orderBy:orderBy});
}

whereas :raw / ^ is injecting raw text, which is vulnerable to SQL injections when it comes from outside, and to be used only for strings that have been created and pre-formatted inside the server.

Comments