SQLite Forum

AddWithValue does not work

AddWithValue does not work

(1) By anonymous on 2020-04-27 16:27:21 [source]

Hello guys,
I'm writing a database in C# and I ran into a weird Problem.
This is my code: using (SQLiteConnection db = new SQLiteConnection(@"Data Source = " + mypathtodb)) { SQLiteCommand insertCommand = new SQLiteCommand("", db); if (string.IsNullOrEmpty(inputText[2])) { insertCommand.CommandText = "INSERT INTO [@Table] VALUES (@Value);"; } else { insertCommand.CommandText = "INSERT INTO [@Table] VALUES (" + inputText[2] + "@Value);"; } //inputText[2] is a fix statement wich I use only in some cases so I don't need to check on SQL-Injection insertCommand.Parameters.AddWithValue("@Table", inputText[0]); insertCommand.Parameters.AddWithValue("@Value", inputText[1]); try { db.Open(); Console.WriteLine(inputText[0]); Console.WriteLine(inputText[1]); Console.WriteLine(inputText[2]); Console.WriteLine(insertCommand.CommandText); insertCommand.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(ex); } db.Close(); }
In my console I get this output: tblTable "someValue" "a-internal-prepared-SQL-statement", INSERT INTO [@Table] VALUES (("a-internal-prepared-SQL-statement"), @Value); code = Error (1), message = System.Data.SQLite.SQLiteException (0x800007BF): SQL logic error no such table: @Table bei System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain) bei System.Data.SQLite.SQLiteCommand.BuildNextCommand() bei System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index) bei System.Data.SQLite.SQLiteDataReader.NextResult() bei System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave) bei System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior) bei System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior) bei System.Data.SQLite.SQLiteCommand.ExecuteNonQuery() bei myClass.myFunction in pathToProject.myProject.myClass.cs:Zeile 47. As you can see in the fourth row "AddWithValue" does not exchange "@Table" and "@Value" with the values I want to.
Why does this happen?
I don't understand.

(2) By Larry Brasfield (LarryBrasfield) on 2020-04-27 16:48:38 in reply to 1 [link] [source]

You cannot parameterize schema names. Only values in expressions can be parameterized.

(3) By anonymous on 2020-04-27 18:28:34 in reply to 2 [link] [source]

Ok. I see.
I changed it so I can pass the value directly into the string.
But @Value ist still not right. INSERT INTO [myTable] VALUES ((some_Subquery), @Value); is what I get as output.

(4) By Larry Brasfield (LarryBrasfield) on 2020-04-27 19:47:07 in reply to 3 [link] [source]

It's difficult to see what you intend, precisely. So I am going to offer just these impressions:

  1. You seem to expect that the effect of supplying parameter values for a parameterized SQL statement, (ultimately held internally as a parsed statement object, representing the structure of your SQL), to show up as the text you wrote with the parameters substituted. It does not work that way.

  2. You claim an intent to avoid SQL injection woes, yet you concatenate inputs to your code (that you excerpted) with some SQL-fragments, and submit the result to the SQLite SQL parser as a statement.

I do not see enough of your code (after presumably ridding it of the would-be-parameterized table name) to venture what you are doing wrong now. But I think your expectation needs adjustment. Read up on what parameterization does and how that is supported in Data.SQLite (which I assume you are using.) Then, I suspect, you will be able to write the C# that makes happen what you rightly think can happen and want to happen. If not, more questions are welcome, if they are on-topic here.

(5) By anonymous on 2020-04-30 15:29:44 in reply to 4 [link] [source]

You got that right. That's exactly what I was thinking.
Thank you. I will look that up