CODEWITHSUNDEEP
sqlnode.jsexpress
tonysepia
tonysepia

Reputation: 3510

Node.JS Express form SQL Select query based on supplied parameters

I am using querystring to get a set of search parameters for the enpoint in a RESTful manner:

www.api.com/products?name=apple&colour=red

or

www.api.com/products?name=apple&colour=red&size=large

As you can see, not all parameters are mandatory.

To prevent SQL injection I must use placeholders when placing variables into the query:

connection.query("SELECT * FROM products WHERE name= ? AND colour= ?",
    [
     req.body.name,
     req.body.colour
    ]

However, if occasionally the size parameter is supplied as well, how do I dynamically create a different query? Writing conditionals for all combinations of search columns seems like a bad solution, and there must be a better way to do this. How do I dynamically create the SQL query based on the supplied search parameters (of course, I would individually check them for validity with express-validator and only allow the correct column names)?

Upvotes: 0

Views: 2759

Answers (2)

ForbesLindesay
ForbesLindesay

Reputation: 10712

You could do it safely with string concatenation, as long as you're careful:

const conditions = [];
const values = [];

if (req.query.name) { conditions.push(`name=?`); values.push(req.query.name); }
if (req.query.colour) { conditions.push(`colour=?`); values.push(req.query.colour); }
if (req.query.size) { conditions.push(`size=?`); values.push(req.query.size); }

connection.query(
  "SELECT * FROM products " + (conditions.length ? ("WHERE " + conditions.join(" AND ")) : ""),
  values,
);

But then it's entirely up to you to ensure that the positions in the values array match the orders of the ?s in the query. It's also entirely up to you/your team to maintain the discipline to only concatenate strings that are trusted and not accidentally include user input.

https://www.atdatabases.org/docs/sql provides a safe way to build SQL queries like this. I would do something like:

const conditions = [];

if (req.query.name) conditions.push(sql`name=${req.query.name}`);
if (req.query.colour) conditions.push(sql`colour=${req.query.colour}`);
if (req.query.size) conditions.push(sql`size=${req.query.size}`);

connection.query(sql`
  SELECT * FROM products ${
    conditions.length
    ? sql`WHERE ${sql.join(conditions, ' AND ')}`
    : sql``}
`);

Like using knex the @database/pg or @databases/mysql drivers combined with the sql tag prevent you accidentally creating SQL injection vulnerabilities, but they also let you keep using actual SQL syntax, which adds significant flexibility.

Upvotes: 3

tonysepia
tonysepia

Reputation: 3510

I have just found Knex.js ( knex like query dynamically add) to be an acceptable option.

If any other options exist, please advice

Upvotes: 0

Related Questions