创建SQL SELECT查询并处理潜在的未定义值的最佳实践 [英] Best practice for creating SQL SELECT queries while handling potential undefined values

问题描述

我目前正在通过 pg-promise 使用PostgreSQL创建NodeJS网站。

I'm currently creating a NodeJS website using PostgreSQL via pg-promise.

我有一个带有HTML表单的页面，该页面带有复选框以选择变量来搜索数据库以使用各种字段。然后将它们输入到带有 pg-promise 的SQL查询中，预期的行为是将结果以JSON格式传递回用户。

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

由此创建如下所示的URL / search？variable_b = pear& ; variable_b = banana

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

我遇到的问题是试图创建全部捕获 SQL SELECT查询来处理此搜索。

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

这是我在 pg-promise 中创建的SQL查询：

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();
});

鉴于 / search，此操作失败了？variable_b = pear& variable_b =香蕉 URL，但可以说以下URL / search？variable_a = apple& variable_b =香蕉。

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.

这无疑是因为在上面的示例中 req.query.variable_a 是未定义的，因为未选中任何复选框，并且SQL查询使用失败了IN（）。我也许应该补充一下，如果 variable_a 或 variable_b 未由复选框定义，在这种情况下，预期的行为是

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?

我觉得我可能可以创建很多if / else逻辑来处理潜在的未定义的 req.query 变量和所产生的SQL查询，但这似乎很杂乱且不美观。

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.

推荐答案

此问题与此处记录的问题相同： https://github.com/vitaly-t/pg-promise/issues/442

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

基本上， pg-promise 查询格式化引擎会根据您的格式化参数生成SQL。它不会对生成的SQL进行任何语法验证。

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

您正在生成 IN（），这是无效的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.

示例：

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 */});
});

还有其他解决方案，例如 pg-promise 非常通用，不会限制您的处理方式。

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

例如，代替此：

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

您可以执行以下操作：

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

将产生相同的结果。无论您喜欢哪个！ ;）

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

这篇关于创建SQL SELECT查询并处理潜在的未定义值的最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！