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

Question

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

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服务后面。

- Mr Shoubs

---

为什么你经常需要添加或删除列？有什么充分的理由需要这样做吗？ - ypercubeᵀᴹ

@ypercube，我不需要经常这样做。我只是不想为数据库中的每个更改维护我的过滤器对象中的SQL，并同时提供一个灵活的对象来过滤数据。 - Mr Shoubs

Answer 1

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

SELECT column_name FROM information_schema.columns WHERE table_name='your_table_name';

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

- Eelke

---

+1 对于获取有效列名的想法。请注意，由于这是一个Web服务，客户端软件提供过滤信息，尽管已经实现了安全/身份验证，但仍有可能使用来自客户端以外的数据调用Web服务。 - Mr Shoubs

我将使用您的想法，在服务启动时将（允许的）列名存储在表名字典的列名字典中。客户端可以引用键而不是过滤对象中要使用的表或列的实际名称。 - Mr Shoubs

Answer 2

例子：

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

- Oberdan

---

这个解决方案适用于动态查询（表达式），但我无法从数值类型中获取精度和刻度，或从varchars中获取长度，有什么建议吗？ - Stavros Koureas

Answer 3

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

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

- Thomas Mueller

---

是的，即时处理是一个简单的解决方案，但由于这是一个Web服务，客户端传递过滤条件，包括列信息，因此仍可能会受到攻击。 - Mr Shoubs

你说得对，如果列名由用户提供，则存在风险。一个解决方案（不完美）是只允许字符（A-Z，a-z）和下划线（_）。可能还包括数字（0-9），但不能以数字开头。 - Thomas Mueller

客户端是客户端应用程序。虽然@Eelke提供了最好的答案来回答我的问题，但正确答案会得到加1分。 - Mr Shoubs