Best Practice For Creating SQL SELECT Queries While Handling Potential Undefined Values

I'm currently creating a NodeJS website using PostgreSQL via pg-promise. I have a page with an HTML form with checkboxes to select variables to search the database for using vario

Solution 1:

This issue is the same as was logged here: https://github.com/vitaly-t/pg-promise/issues/442

Basically, pg-promise query formatting engine generates SQL according to your formatting parameters. It does NOT do any syntax verification on your resulting SQL.

You are generating IN (), which is invalid SQL, so you get the error.

You should check for the presence of the variable, and not even try to generate such a query when the variable is missing, because your query wouldn't be able to yield anything good then.

Example:

router.get('/search', (req, res, next) => {
    const variables = ['variable_a', 'variable_b', 'variable_c'];
    const conditions = variables.filter(v => v in req.query)
        .map(v => pgp.as.format('$1:name IN ($2:csv)', [v, req.query[v]]))
        .join(' AND ');

    conditions = conditions && 'WHERE ' + conditions;

    db.any('SELECT * FROM food $1:raw', conditions)
        .then(result => res.send(result))
        .catch(error => {/* handle the error */});
});

There can be other solutions, as pg-promise is very generic, it does not limit you the way you approach this.

For example, instead of this:

v => pgp.as.format('$1:name IN ($2:csv)', [v, req.query[v]])

you can do this:

v => pgp.as.name(v) + ' IN (' + pgp.as.csv(req.query[v]) + ')';

which will produce the same result. Whichever you like! ;)

Solution 2:

first - your input will keep only last selected value

<input type="checkbox" name="variable_a" value="apple">

or you should use name with [] to inform that its an array

second - you can use ? statement just inside params or var

req.query.variable_a ? req.query.variable_a : null

And inside your SQL - if you didnt send any of vars - you want get result cause its strict AND statement - var undefined - the query return false