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 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

Answer 2

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