9

I have a model with calls the products with SORT/PAGE/PER PAGE It works fine with numerical values as parameters but not strings. This works fine...

params.limit = 12
client.query('SELECT * FROM products LIMIT $1', [params.limit], function(err, result)

However this does not...

params.sort = 'product_id'
params.direction = 'DESC'
client.query('SELECT * FROM products ORDER BY $1 $2', [params.sort, params.direction], function(err, result)`

I assume it is because it is wrapping the word DESC as 'DESC' but I don't know how to achieve this without inject it directly into the string.

Also with LIMIT passing an integer always work but passing ALL doesn't I assume for the same reason.

Any assistance would be super useful!

Matt The Ninja
  • 2,641
  • 4
  • 28
  • 58
  • 2
    You can't do it, node-postgres is quite limited when it comes to query formatting. [pg-promise](https://github.com/vitaly-t/pg-promise), however, has a much better query formatting. And in your example that's a typical [raw query parameter injection](https://github.com/vitaly-t/pg-promise/wiki/Learn-by-Example#raw-text) – vitaly-t Sep 10 '15 at 02:41
  • Thanks, I will certainly check this one out! – Matt The Ninja Sep 10 '15 at 08:08

2 Answers2

4

I have come across this same situation and it seems there are not many solutions around.

It seems putting order and orderby into the query parameter list places them as string literals like order by "name" "desc" which reverts back to order by id asc because they are now not descriptors. @gordon-linoff 's answer seems nice but I guess it still falls into the same trap for $1.

The following solution first uses string literals to build "order" query string then adds "where", "limit" and "offset" by parameters. However, it requires the developer to know what tables/columns are available and also which ones will be ordered (ordering some columns is meaningless).

let params = { page: 3, limit: 10, orderby: "name", order: "ASC" };

let orderby = ["id", "name", "family", "age"].includes(params.orderby)
  ? params.orderby
  : "id";

let order = ["ASC", "DESC"].includes(params.order.toUpperCase())
  ? params.order
  : "ASC";

client.query(
  `SELECT name, family, age \
   FROM customers \
   WHERE id=$1 \
   ORDER BY ${orderby} ${order} \
   LIMIT $2 \
   OFFSET $3`,
  [id, params.limit, params.page]
);

EDIT: "string literals" are an ES6+ feature. for older JS versions, you can just use string concatenation by addition symbol: w="world"; str="hello" + w

PS: you can get column names before making the final string, but requires an extra connection to the database. though, you may place this in the initialization steps. check this answer for quick how-to: https://dba.stackexchange.com/a/22368/150156

Yılmaz Durmaz
  • 2,374
  • 12
  • 26
1

I think you might have to do an explicit comparison:

order by (case when $2 = 'ASC' then $1 end) ASC,
         (case when $2 = 'DESC' then $1 end) DESC
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Does it fail in any particular way? – Gordon Linoff Sep 06 '15 at 23:21
  • I am also in a situation to use `ORDER BY` as a param but in a prepared statement( that won't escape quotes!). Let me try this way. I think `connection.escape` won't work as its a prepared one. – KTM Mar 09 '21 at 16:32