如何使用 Postgres/NpgSQL 动态查询列名

发布于 2024-10-31 04:51:59 字数 659 浏览 1 评论 0原文

我有一个过滤器对象来查询包含许多列的表，而不是像这样编写覆盖所有列的条件（允许可选过滤）：

WHERE ((:value0 IS NULL) OR (column_name0 = :value0)) AND ((:value1 IS NULL) OR (column_name1 = :value1)) AND... etc

对于每一列。相反，理想情况下，我希望能够将字段名称作为参数传递：

WHERE :column_name0 = :value0 AND column_name1 = :value1 AND... etc

这是不可能的，因为在解析时需要这些列（类似于这个答案此处给出）。

你如何克服这个问题？ - 我真的不想在添加或删除新列时维护 SQL（就像在我的第一个示例中必须做的那样），我认为这将是将列名直接构建到命令字符串中对我来说是危险的，因为这可能允许 SQL 注入。

请注意，此代码位于 Web 服务后面。

原文

I have a filter object to query a table with many columns, and rather than write a condition covering all columns (allowing for optional filtering) like this:

WHERE ((:value0 IS NULL) OR (column_name0 = :value0)) AND ((:value1 IS NULL) OR (column_name1 = :value1)) AND... etc

for every column. Instead, I'd ideally I'd like to be able to pass in the field name as a parameter:

WHERE :column_name0 = :value0 AND column_name1 = :value1 AND... etc

which isn't possible as the columns are required at parse time (similar to this answer given here).

How do you overcome this? - I don't really want to have to maintain the SQL when new columns are added or removed (as you would have to in my first example) and I think it would be dangerous for me to construct the column names into the command string directly as this might allow for sql injection.

Note that this code is behind a web service.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

月下客 2024-11-07 04:51:59

只需确保最终用户无法直接提供列名称，并且手动构建查询时应该是安全的。如果您需要找出哪些列名在运行时有效，您可以使用以下查询：

SELECT column_name
FROM information_schema.columns
WHERE table_schema='public' AND table_name='yourtablename'

Just make sure end users cannot provide the column names directly and you should be safe when constructing the query manually. If you need to find out what column names are valid on runtime you can use the following query:

SELECT column_name
FROM information_schema.columns
WHERE table_schema='public' AND table_name='yourtablename'

回复收藏 0 原文

倾城泪 2024-11-07 04:51:59

我认为最简单的解决方案是动态构建 SQL 语句。

如果您对用户提供的数据使用参数，则不可能进行 SQL 注入。

回复收藏 0 原文

勿挽旧人 2024-11-07 04:51:59

例子：

NpgsqlCommand command = new NpgsqlCommand(SQL, Connection);
        Npgsql.NpgsqlDataReader Resource = command.ExecuteReader();

        while (this.Resource.Read())
        {
            for (int i = 0; i < this.Resource.FieldCount; i++)
            {
                string field = this.Resource.GetName(i).ToString();
            }
        }

example:

NpgsqlCommand command = new NpgsqlCommand(SQL, Connection);
        Npgsql.NpgsqlDataReader Resource = command.ExecuteReader();

        while (this.Resource.Read())
        {
            for (int i = 0; i < this.Resource.FieldCount; i++)
            {
                string field = this.Resource.GetName(i).ToString();
            }
        }

回复收藏 0 原文

~没有更多了~