8

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?

Improve this question
6
  • Duplicate of stackoverflow.com/questions/337704/… (although that's SQL server instead of mysql; unlikely to be really different though) Commented Mar 16, 2009 at 13:43
  • The solution offered there was acknowledged to be incredibly slow, although it does answer the second half of my question. Commented Mar 16, 2009 at 13:46
  • There are lots of solutions offered there. The accepted answer isn't the most popular one. Commented Mar 16, 2009 at 13:50
  • True, but there's already been one answer here that wasn't in that question, so I guess it paid off to ask the question again. Commented Mar 16, 2009 at 13:51
  • 3
    There are non-trivial differences between Sql Server and MySql. I wouldn't trust an answer for one to apply to the other. Commented Mar 16, 2009 at 14:03

8 Answers 8

Reset to default
6

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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(...));
}
Improve this answer

2 Comments

I was just thinking that. But would I gain the performance benefits of having a stored parameterized query if I did that?
Ado.Net suggests best performance is by creating a new connection, using it once and throwing it away -- at least for other SQL engines; After all, the query engine could optimise based on the query string to reuse them...
3

You cannot use parameters for an IN clause.

Improve this answer

Comments

3

Old question, but in case anyone comes across this via Google, here's what I use:

int status = 4;  
string ids = "1,14,145,43";      

m.Parameters.AddWithValue("@Status", status);
m.Parameters.AddWithValue("@IDs", ids);

UPDATE TABLE_1 SET STATUS = @Status WHERE FIND_IN_SET(ID, @IDs) > 0;

Note: FIND_IN_SET is a mySQL specific function.

Credit, where credit is due: See this question: Add List<int> to a mysql parameter

Improve this answer

1 Comment

There is of course no SQL injection risk due to the parameters. I've removed that comment to avoid confusion. I believe I may have mixed up some ORM risks in the original question. There is still the index issue which could be significant depending on the size of your table.
0

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.

Improve this answer

2 Comments

That would be incredibly inefficient, considering that this query could, in some cases, have thousands of items in the IN clause.
Isn't there a limit to how many items you can have in an IN clause?
0

Since MySQL 4.0 you can use FIND_IN_SET function to create parametrized SQL with 'in clause'.

Your code:

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

Changed to use FIND_IN_SET:

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

Finally you can use variables to parametrize your query:

var s = "UPDATE TABLE_1 SET STATUS = 4 WHERE FIND_IN_SET(ID, ?)";
var params = "1, 14, 145, 43";

dataSource.Execute(s, params);

See the W3Schools reference and the MySQL Tutorial

Since FIND_IN_SET is a MySQL function it works with every language not just C#.

Improve this answer

Comments

0

I recently found out how slow find_in_set is, so I try to avoid it. I also try to avoid constructing my sql to help prevent injection attacks. I've found you can achieve what's required and use a parameterised query at the same time.

int[] ids = new int[] { 1, 2, 3};

command.CommandText = @"
  SET @temp = @ids;
  SELECT * FROM table_name WHERE table_name_id IN @temp;
";

command.Parameters.AddWithValue("ids", string.Join(",", ids));

Old question, but it confirmed my suspicion and I came up with this workaround.

Improve this answer

1 Comment

ids is going to be treated as a string by mysql, so only the first occurence of id in the string will be found
-1

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.