CODEWITHSUNDEEP
node.jspostgresql
tblznbits
tblznbits

Reputation: 6778

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

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!

Upvotes: 0

Views: 480

Answers (1)

Stefanov.sm
Stefanov.sm

Reputation: 13029

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?

Upvotes: 1

Related Questions