postgresql 如何使用 Postgres/NpgSQL 动态查询列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5584754/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to query column names dynamically using Postgres/NpgSQL
提问by Mr Shoubs
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.
你如何克服这个问题？- 我真的不想在添加或删除新列时维护 SQL（就像在我的第一个示例中那样），我认为将列名直接构造到命令字符串中对我来说是危险的因为这可能允许 sql 注入。
Note that this code is behind a web service.
请注意，此代码位于 Web 服务后面。
回答by Eelke
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'
回答by Thomas Mueller
I think the easiest solution is to construct the SQL statement on the fly.
我认为最简单的解决方案是动态构建 SQL 语句。
SQL injection is not possible if you use parameters for user provided data.
如果您对用户提供的数据使用参数，则 SQL 注入是不可能的。
回答by Oberdan
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();
}
}
