Using LIMIT / ORDER BY with pg Postgres NodeJS as a Parameter

Question

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!

Answer 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

Answer 2

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

It seems putting order and orderby into 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 above seems nice but I guess it still falls 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 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]
);