How to dynamically build Postgres query from API parameters in Nodejs?

Question

I'm looking for a way to dynamically build a SQL query for an unknown number of API parameters that may come back. As a simple example, consider the following query:

router.get("/someEndpoint", authorizationFunction, async (req, res) => {
  let sql = `
    SELECT *
    FROM some_table
    WHERE username = $1
      ${req.query.since !== undefined ? "AND hire_date >= $2" : ""}
  `

  const results = await pool.query(sql, [req.query.user, req.query.since]);
}

where pool is defined as

const Pool = require("pg").Pool; 
const pool = new Pool({<connection parameters>});

The problem I'm having is that if req.query.since is not provided, then the SQL query only requires a single bound parameter ($1). This is presented as an error that says bind message supplies 2 parameters, but prepared statement "" requires 1. Since I don't know which parameters a user will provide until the time of the query, I'm under the impression that I need to provide all possible value, and let the query figure it out.

I've seen a lot of posts that point to pg-promise as a solution, but I'm wondering if that's necessary. Is there a way that I can solve this with my current setup? Perhaps I'm thinking about the problem incorrectly?

Thanks in advance!

Stefanov.sm · Accepted Answer · 2022-10-24 14:20:53Z

1

Add a trivial expression text that contains $2 and evaluates to true instead of "", for example

  SELECT * FROM some_table WHERE username = $1 AND 
  ${req.query.since !== undefined ? " hire_date >= $2": " (true or $2 = $2)"}

The planner will remove it anyway. Added true or just in case $2 is null.
Still it would be cleaner like this

if (req.query.since !== undefined)
{
  let sql = `SELECT * FROM some_table WHERE username = $1 AND hire_date >= $2`;
  const results = await pool.query(sql, [req.query.user, req.query.since]);
}
else
{
  let sql = `SELECT * FROM some_table WHERE username = $1`;
  const results = await pool.query(sql, [req.query.user]);
}

What about SQLi risk BTW?

edited Oct 24, 2022 at 14:20

answered Oct 24, 2022 at 13:49

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to dynamically build Postgres query from API parameters in Nodejs?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related