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

Question

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 1

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 it is to be formatted using SQL Names:

const bar = function(orderBy) {
    const qs = 'select * from mytable order by ${orderBy:name}';
    return db.many(qs, {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.