Best practice for creating SQL SELECT queries whil

问题:

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 various fields. These are then fed into a SQL query with pg-promise and the intended behaviour is the results are passed back to the user in JSON format.

A very minimal working example would be as follows.

HTML form:

<form action="/search" method="get">
    <fieldset>
        <legend>Variable A</legend>
            <div>
                <input type="checkbox" name="variable_a" value="apple">
                <label for="variable_a">
                    Apple
                </label>
            </div>
            <div>
                <input type="checkbox" name="variable_a" value="orange">
                <label for="variable_a">
                    Orange
                </label>
            </div>
    </fieldset>

    <fieldset>
        <legend>Variable B</legend>
            <div>
                <input type="checkbox" name="variable_b" value="pear">
                <label for="variable_b">
                    Pear
                </label>
            </div>
            <div>
                <input type="checkbox" name="variable_b" value="banana">
                <label for="variable_b">
                    Banana
                </label>
            </div>
    </fieldset>
    <button type="submit">Search</button>
</form>

From this an URL like the following is created /search?variable_b=pear&variable_b=banana

The problem I have is when trying to create a 'catch all' SQL SELECT query to handle this search.

This is the SQL query I have created in pg-promise:

router.get('/search', function(req, res, next) {
    db.any(`SELECT * FROM food 
            WHERE variable_a IN ($1:csv)
            AND variable_b IN ($2:csv)`, [req.query.variable_a, req.query.variable_b])
        .then(result=>res.send(result))
        .catch();
});

This fails given the /search?variable_b=pear&variable_b=banana URL, but works with say the following URL /search?variable_a=apple&variable_b=banana.

This is undoubtedly because in the example above req.query.variable_a is undefined as no checkboxes were selected and the SQL query falls over with IN (). I should perhaps add, if variable_a or variable_b isn't defined by a checkbox, in this case the intended behaviour is there is no filter on said columns.

My question is what is the best way of handling this?

I feel like I could probably create a lot of if/else logic to handle potential undefined req.query variables and resulting SQL queries but this seems messy and not elegant.

回答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! ;)

回答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