如何在Npgsql中提供查询的表名作为命令参数？ [英] How can I provide the table name for a query as command parameter in Npgsql?

问题描述

我想提供查询的表名作为命令参数，例如：

I want to provide the table name for a query as command parameters, like so:

public class Foo
{
    private const String myTableName = "mytable";

    public void Bar()
    {
        NpgsqlCommand command = new NpgsqlCommand("SELECT * from :tableName", connection);
        command.Parameters.Add(new NpgsqlParameter("tableName", DbType.String));
        command.Parameters[0].Value = myTableName;
    }
}

这似乎导致以下查询： SELECT * from E'mytable' 会导致错误（注意单引号）。

This seems to result in this query: "SELECT * from E'mytable'" which results in an error (mind the single quotes).

我真的需要吗为此做字符串连接？从安全的角度来看这没关系，因为用户不能更改表名，但是用于创建SQL查询的字符串连接总是让我感到毛骨悚然...

Do I really need to do string concatenation for this? It doesn't matter from a security standpoint, since the table name can not be changed by the user but string concatenation for creating SQL queries always gives me the creeps...

感谢
Eric

Thanks, Eric

推荐答案

表名不能作为参数发送。表名是在解析时解析的，因为规划和此类操作需要它们。仅在执行程序（或必要时优化程序）时替换参数。

Table names cannot be sent as parameters. The table names are resolved at parse time, since they are needed for planning and such things. Parameters are only substituted at executor (or optimizer if necessary) time.

是的，您将需要使用字符串替换。当然，只要表名来自您的类中的const，这不是安全问题（甚至不是成为一个安全问题）。

So yeah, you will need to use string replacement for it. It's not a security issue (or even the risk of becoming one) as long as the table name comes from a const in your class, of course.

但是如果您这样做根据用户输入构造表名，您需要非常小心。但是通常，如果您需要根据用户输入来构造表名，则数据库中的某些内容首先会被不良设计，并且应予以修复（是的，当然还有例外）。

But if you do construct the table name from user input, you need to be very careful. But usually if you need to construct the table name from user input, something is badly designed in the database in the first place and should be fixed (yes, there are of course exceptions to that).

这篇关于如何在Npgsql中提供查询的表名作为命令参数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！