0

I'm trying to parameterize some SQL statements as best I can, but in some cases I need to include some raw SQL text that is generated elsewhere in the code.

In one case, I'm trying to replace code that looks like this:

using (SQLiteCommand cmd = dbConnection.CreateCommand())
{
    cmd.CommandTimeout = 0;
    cmd.CommandText = String.Format("CREATE VIEW {0} AS {1}", viewName, sqlQuery);

    cmd.ExecuteNonQuery();
}

with something that looks like this:

using (SQLiteCommand cmd = dbConnection.CreateCommand())
{
    cmd.CommandTimeout = 0;
    cmd.CommandText = "CREATE VIEW [@view] AS @sql";
    cmd.Parameters.AddWithValue("@view", viewName);
    cmd.Parameters.AddWithValue("@sql", sqlQuery);

    cmd.ExecuteNonQuery();
}

However, this generates the following error:

System.Data.SQLite.SQLiteException (0x80004005): SQL logic error or missing database
near "@sql": syntax error
   at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
   at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
   at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)
   at System.Data.SQLite.SQLiteDataReader.NextResult()
   at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
   at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior)
   at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()

On the other hand, the following code does not generate the error:

using (SQLiteCommand cmd = dbConnection.CreateCommand())
{
    cmd.CommandTimeout = 0;
    cmd.CommandText = String.Format("CREATE VIEW [@view] AS {0}", sqlQuery);
    cmd.Parameters.AddWithValue("@view", viewName);

    cmd.ExecuteNonQuery();
}

Is there something wrong with my syntax, or is it just not possible to use a parameter there? I'm thinking it may be the latter, since it should probably be another parameterized query, but my searches don't come up with any examples of people trying to put a SELECT statement into a parameter.

James T
  • 607
  • 6
  • 9
  • You cannot use parameters for this. They are used to represent values not to express column or table or view names (system names). The important thing here is: Do you get your strings from user inputs? – Steve Nov 07 '16 at 17:49
  • @Steve Good to know. There is user input, so it looks like I'm going to need to find a way to parameterize it. We restrict how it's entered, but I'm not confident that it can prevent SQL injection. – James T Nov 07 '16 at 17:51
  • General way of understanding: with parametrised query can be done precompilation - optimization (in sqlite probaly not, but in general yes), engine select optimal idexes etc ... So table names and similar cannot be parameters. Only "pure values" - in terms of programming languages – Jacek Cz Nov 07 '16 at 17:55

0 Answers0