Is it possible to parameterize table and column names in SQLite queries?

Question

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

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.

What is the issue here?

If you are avoiding creating 10 different commands because of the inefficiency of tying each one to a SqliteConnection and building up their parameters and CommandText over and over, I'm just making sure that readers know that there is an alternative. SqliteCommand has a parameterless constructor of just new SqliteCommand(); and its Connection property is assignable at run time. So you can create and initialize many commands just once and then assign their Connection property to a SqliteConnection object that is repeatedly created and disposed at run time. — Gary Z, Jun 06 '18 at 18:53

score 14 · Accepted Answer · answered Nov 10 '10 at 05:58

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;

score 2 · Answer 2 · answered Nov 10 '10 at 06:00

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.

Is it possible to parameterize table and column names in SQLite queries?

2 Answers2

Linked

Related