0

Duplicate of this question, as it was not answered to satisfaction. Delete where in clause in sqlite

In virtually the exact same scenario as the original poster, Cholo, I am attempting a simple parameterised Delete statement.

string UIDcsv = "1, 2, 3, 4, 5, 6";
Console.WriteLine(UIDcsv);

var sqlite_DeleteEmployee = new SQLiteCommand(sqlite_Conn);
sqlite_DeleteEmployee.CommandText = "DELETE FROM myTable WHERE UID IN (@x);";
sqlite_DeleteEmployee.Parameters.AddWithValue("@x", UIDcsv);
sqlite_DeleteEmployee.Prepare();
sqlite_DeleteEmployee.ExecuteNonQuery();

Like Cholo found in the linked 'Accepted Answer', the code runs fine but does not actually execute this statement. This has been frustrating me all evening!

Now I've written this, I'm looking at rest of my code and realising that this C# Sql command generator is more than likely auto-appending quotation marks around @xyz in the command, turning the list of UIDs into a single string. How do I stop this happening?

Output of the WriteLine:

1, 2, 3, 4, 5, 6
mannington
  • 25
  • 4
  • 1
    The [duplicate](https://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause) show proper way to parameterize IN. I believe it also applies to SQLite. If that approach does not work - [edit] the question with what you properly tried and link to the duplicate along with information how it did not work. – Alexei Levenkov Apr 23 '20 at 23:35

1 Answers1

-3

I think the problem is the value of the variable. Try:

sqlite_DeleteEmployee.CommandText = "DELETE FROM myTable WHERE UID IN (" + UIDcsv +");";

Update: As John commented, this will allow SQL injection if the "UIDcsv" has some kind of user input. Other solutions are:

1) using "OR" insted on "IN" (UID = @ID1 OR UID = @ID2 ...)

2) use a for-loop to add the values in the "IN" condition (for each value you add a parameter and add a "IN" value with a sequencial number (@ID1, @ID2, etc)

Afonso
  • 323
  • 4
  • 14
  • 3
    Please do not promote SQL Injection vulnerabilities in your answers. – ProgrammingLlama Apr 23 '20 at 23:49
  • I would have to know the full scope to advise against SQL Injection, but you are right. Since he named the var as "IDs", I will assume that it isn't an user input but a selection of id's from models. If that's the case you can use string.join to get the UIDcsv, but, as I said, I don't know the full scope of the application. Another solution would be to replace the "ID in (...)" for OR's. – Afonso Apr 24 '20 at 00:15
  • It's (probably) fine in this user's case, but with the way the site works, it likely won't be only OP that uses the code you have provided. Someone else might come along and use it for different purposes. – ProgrammingLlama Apr 24 '20 at 00:26