是否可以在SQLite查询中参数化表名和列名？

Im trying to perform a parameterized query in SQLite from C#, and the method im using is along the lines of creating a static command with

我试图在SQLite中使用C＃执行参数化查询，我使用的方法就是创建一个静态命令。

        SQLiteCommand cmd = new SQLiteCommand(
        "SELECT [ID]" +
            ",[email]" +
            ",[serializedata]" +
            ",[restrictions]" +
        " FROM " + UserTable +
        " WHERE @search = @searchparam", SQLConnection);

        cmd.Parameters.Add(new SQLiteParameter("@searchparam"));
        cmd.Parameters.Add(new SQLiteParameter("@search"));

and calling it like this:

并像这样调用它：

        Command.Parameters["@searchparam"].Value = searchdata;
        Command.Parameters["@search"].Value = search;
        SQLiteDataAdapter slda = new SQLiteDataAdapter(UserSelectUsernameCommand);
        DataSet ds = new DataSet();
        slda.Fill(ds);
        User[] array = new User[ds.Tables[0].Rows.Count];
        int index = 0;
        foreach (DataRow row in ds.Tables[0].Rows)
        {
            array[index] = new User(this, row);
            index++;
        }
        return array;

but im getting an error along the line of " '@search' is not a correct column name " or something like that. if i use a constant column name, and only use the data for parameters it works, but i dont want to create 10 different commands for when i need to search by different column names.

但我在“'@search'的行中得到错误不是一个正确的列名”或类似的东西。如果我使用一个常量列名，只使用它的工作参数数据，但我不想创建10个不同的命令，当我需要搜索不同的列名称。

What is the issue here?

这是什么问题？

2 个解决方案

#1

Generally things like column names (or table names) can not be parameterised - and the fact that there are different indices means that it will have to be a different plan internally. So you will have to use concatenation - but be careful to white-list the known column names to prevent sql injection:

通常，诸如列名（或表名）之类的东西不能参数化 - 并且存在不同索引的事实意味着它必须在内部是不同的计划。所以你必须使用连接 - 但要小心列出已知的列名以阻止sql注入：

    SQLiteCommand cmd = new SQLiteCommand(@"
    SELECT [ID],[email],[serializedata],[restrictions]
    FROM " + whiteListedUserTable + @"
    WHERE [" + whiteListedColumnName + @"] = @searchparam", SQLConnection);

    cmd.Parameters.Add(new SQLiteParameter("@searchparam"));
    ...
    Command.Parameters["@searchparam"].Value = searchdata;

#2

You cannot use a query parameter in that fashion -- to indicate a column name. You can only use it to supply values.

您不能以这种方式使用查询参数 - 以指示列名称。您只能使用它来提供值。

Consider something like this instead:

考虑这样的事情：

    SQLiteCommand cmd = new SQLiteCommand(
    "SELECT [ID]" +
        ",[email]" +
        ",[serializedata]" +
        ",[restrictions]" +
    " FROM " + UserTable +
    " WHERE [" + search + "] = @searchparam", SQLConnection);

    cmd.Parameters.Add(new SQLiteParameter("@searchparam"));

If you control all of the input to this function and none if it can be supplied by someone other than you, this should be safe. But if search comes from an untrusted third party, be sure to make the appropriate security checks on the value.

如果你控制了这个函数的所有输入，如果它可以由你以外的其他人提供，那么这应该是安全的。但如果搜索来自不受信任的第三方，请务必对该值进行适当的安全检查。

#1

    SQLiteCommand cmd = new SQLiteCommand(@"
    SELECT [ID],[email],[serializedata],[restrictions]
    FROM " + whiteListedUserTable + @"
    WHERE [" + whiteListedColumnName + @"] = @searchparam", SQLConnection);

    cmd.Parameters.Add(new SQLiteParameter("@searchparam"));
    ...
    Command.Parameters["@searchparam"].Value = searchdata;

#2