ORDER BY command using a prepared statement parameter pg-promise

user_9663497 0
Viewed 852

I'm new to Node, APIs and SQL and I hit a wall. I'm using pg-promise prepared statements and I'm trying to use a sorted parameter with the ORDERED BY command to do the usual sorting of the results but it doesn't work as the always come in ordered the same. If I us the parameter's name directly it does work instead, can you see what am I doing wrong with my query? As usual many thanks for your time and help. This is my method:

if (city, region, country, category, minPrice, maxPrice, orderedBy, sorted) {
    if (sorted == 'asc') {
      await db.any({
        name: 'get-city-category-price-range-ordered-by-asc-products',
        // text: 'SELECT * FROM products WHERE city = $1 AND region = $2 AND country = $3 AND category = $4 AND price >= $5 AND price <= $6 ORDER BY price ASC', // working
        text: 'SELECT * FROM products WHERE city = $1 AND region = $2 AND country = $3 AND category = $4 AND price >= $5 AND price <= $6 ORDER BY $7 ASC', // not working
        values: [city, region, country, category, minPrice, maxPrice, orderedBy]
      })
        .then(result => {
          console.log('get-city-category-price-range-ordered-by-asc-products:', result);
          if (result.length > 0) {
            res.status(200).send({
              data: result
            });
          }
          else {
            res.status(404).send({
              error: 'No product found.'
            });
          }
        })
        .catch(function (error) {
          console.log('get-city-category-price-range-ordered-by-asc-products error:', error);
        });

    } else if (sorted == 'desc') {
      await db.any({
        name: 'get-city-category-price-range-ordered-by-desc-products',
        // text: 'SELECT * FROM products WHERE city = $1 AND region = $2 AND country = $3 AND category = $4 AND price >= $5 AND price <= $6 ORDER BY price DESC', // working
        text: 'SELECT * FROM products WHERE city = $1 AND region = $2 AND country = $3 AND category = $4 AND price >= $5 AND price <= $6 ORDER BY $7 DESC', // not working
        values: [city, region, country, category, minPrice, maxPrice, orderedBy]
      })
        .then(result => {
          console.log('get-city-category-price-range-ordered-by-desc-products:', result);
          if (result.length > 0) {
            res.status(200).send({
              data: result
            });
          }
          else {
            res.status(404).send({
              error: 'No product found.'
            });
          }
        })
        .catch(function (error) {
          console.log('get-city-category-price-range-ordered-by-desc-products error:', error);
        });
    }
  }
pg-promise node.js postgresql
2 Answers

Unfortunately, you cannot use query parameters for things like column and table names, only for values. So this will never work, you'll need to build the SQL text by hand by adding the proper "order by" statement. All the warnings about SQL injection then apply, so please check that your orderBy field is valid (for example, only contains letters, no whitespace or other characters).

user_8275930

When you opt for PreparedStatement, your query formatting is done on the server side. So if you decided to use PreparedStatement for exactly that purpose, to get values formatted by the server, then you will have to do without the rich query formatting that pg-promise supports internally for regular queries, and limiting yourself to what PostgreSQL server can do in that regard. And unlike pg-promise, PostgreSQL server doesn't allow dynamic SQL names or identifiers anywhere.

However, if your chose PreparedStatement because you think in your case it will execute faster, then you can still benefit from rich query formatting supported by pg-promise, by formatting the query separately, and then executing PreparedStatement with pre-formatted query, i.e. without any parameters:

const text = pgp.as.format(`SELECT * FROM products WHERE city = $/city/
                            AND region = $/region/ AND country = $/country/
                            AND category = $/category/
                            AND price BETWEEN $/minPrice/ AND $/maxPrice/
                            ORDER BY $/orderedBy:name/ $/sorted:value/`, {
                              city, region, country, category,
                              minPrice, maxPrice, orderedBy, sorted
                            });

const name = 'get-price-range-ordered-by-products-' + sorted; // unique query name

await db.any({name, text});

As you can see, the sorting order can also be set dynamically, no need duplicating your code. And we append sorted to the query's name, because it generates a different execution path, which requires a unique query name.

P.S. I'm the author of pg-promise.

user_11020510
Related