C# Parameterized Query MySQL with `in` clause

Question

I am in the process of converting several queries which were hard-coded into the application and built on the fly to parameterized queries. I'm having trouble with one particular query, which has an in clause:

UPDATE TABLE_1 SET STATUS = 4 WHERE ID IN (1, 14, 145, 43);

The first parameter is easy, as it's just a normal parameter:

MySqlCommand m = new MySqlCommand("UPDATE TABLE_1 SET STATUS = ? WHERE ID IN (?);");
m.Parameters.Add(new MySqlParameter("", 2));

However, the second parameter is a list of integers representing the ids of the rows that need updating. How do I pass in a list of integers for a single parameter? Alternatively, how would you go about setting up this query so that you don't have to completely build it each and every time you call it, and can prevent SQL injection attacks?

Answer 1

You cannot use parameters for an IN clause.

Answer 2

i'd suggest creating a function (assuming that mysql supports user defined functions) to break the parameter apart to return a table.

Answer 3

Loop round your list of integers and perform individual updates.

MSSQL 2008 offers table-valued parameters to avoid this issue, I'm not aware of any similar functionality in MySQL.

Answer 4

You could build up the parametrised query "on the fly" based on the (presumably) variable number of parameters, and iterate over that to pass them in.

So, something like:

List foo; // assuming you have a List of items, in reality, it may be a List<int> or a List<myObject> with an id property, etc.

StringBuilder query = new StringBuilder( "UPDATE TABLE_1 SET STATUS = ? WHERE ID IN ( ?")
for( int i = 1; i++; i < foo.Count )
{   // Bit naive 
    query.Append( ", ?" );
}

query.Append( " );" );

MySqlCommand m = new MySqlCommand(query.ToString());
for( int i = 1; i++; i < foo.Count )
{
    m.Parameters.Add(new MySqlParameter(...));
}

Answer 5

This is not possible in MySQL. You can create a required number of parameters and do UPDATE ... IN (?,?,?,?). This prevents injection attacks (but still requires you to rebuild the query for each parameter count).

Other way is to pass a comma-separated string and parse it.