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

Question

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

You could call a stored procedure instead of a free-form query. — ang
– ang, Commented Jul 24, 2019 at 15:58
@Matthew, Do you mean Stored Procedure in the DB itself, effectively moving the IF/THEN logic into the DB? — tonysepia
– tonysepia, Commented Jul 24, 2019 at 16:34
Postgres in this case. To be honest, I was more interested in ways of handling such situations within NodeJS. For example, at validation stage there is a .optional validator... — tonysepia
– tonysepia, Commented Jul 24, 2019 at 16:43
if ( size === 'large') { connection.query... } else { ... } — ang
– ang, Commented Jul 24, 2019 at 17:29

ForbesLindesay · Accepted Answer · 2019-07-29 13:36:21Z

3

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.

edited Jul 29, 2019 at 13:36

answered Jul 26, 2019 at 9:53

ForbesLindesay

10.8k3 gold badges52 silver badges76 bronze badges

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

4 Comments

tonysepia Over a year ago

if I may suggest a correction, we should put the following code in quotes: conditions.length ? ("WHERE " + conditions.join(" AND ")) : "" , Otherwise 'SELECT * FROM...' is being considered a part of the ternary operator

ForbesLindesay Over a year ago

The ${...} wrapping it already ensures that the SELECT * FROM ... bit isn’t part of the ternary.

tonysepia Over a year ago

I am referring to the example at the top of your post, which doesn't have it

ForbesLindesay Over a year ago

Ah, you're quite right. I didn't read your comment carefully enough. I've edited my answer to fix the problem.

tonysepia · Accepted Answer · 2019-07-24 15:15:18Z

0

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

If any other options exist, please advice

answered Jul 24, 2019 at 15:15

tonysepia

3,5604 gold badges30 silver badges54 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related