是否有可能在SQLite的查询参数表和列名？ [英] Is it possible to parameterize table and column names in SQLite queries?

问题描述

我试着SQLite中从C＃进行参数化查询，以及使用该方法的IM是随着

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

        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"));

和调用它是这样的：

        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;

但IM得到一个错误沿着线'@search'不是一个正确的列名或类似的东西。如果我使用一个常数列名，只使用它的工作参数的数据，但我不希望当我需要通过不同的列名来搜索创建10个不同的命令。

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.

什么是这里的问题？

推荐答案

一般之类的东西列名（或表名）就可以不是进行参数设置 - 而事实上，有不同的指数意味着它的有无的是一个不同的计划内。所以，你将不得不使用串联 - 但小心白名单已知的列名，以防止SQL注入：

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:

    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;

这篇关于是否有可能在SQLite的查询参数表和列名？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！