[ACCEPTED]-Add List<int> to a mysql parameter-mysql-parameter

Accepted answer
Score: 17

when i pass it to the MySqlParameter and 8 this is recognized as a string, it puts 7 in the sql query like "1\,2\,3\,4" and this 6 do not return the expected values.

I ran 5 into this last night. I found that FIND_IN_SET 4 works here:

SELECT * FROM table WHERE FIND_IN_SET(id, @parameter) != 0
intArray = new List<int>(){1,2,3,4};
conn.Command.Parameters.AddWithValue("parameter", string.Join(",", intArray));

Apparently this has some length 3 limitations (I found your post looking for 2 an alternate solution), but this may work 1 for you.

Score: 5

Parameters don't work with IN. I have always 7 embedded such things as a string in the 6 query itself. While that is generally considered 5 bad form because SQL injection, if you are 4 constructing the query from a strongly typed 3 numeric list, then there should be no possibility 2 of any external input corrupting it in a 1 meaningful way.

Score: 3

you are going to have to iterate over your 6 array and create the list yourself

// no parameters
var sb = new StringBuilder();
for(int i=0;i<intArray.Length;i++)
    sb.Append(intArray[i] + ",");// no SQL injection they are numbers
if (sb.Length>0) {sb.Length-=1;}
string sql = "SELECT * FROM table WHERE id IN (" + sb.ToString() + ")";

UPDATE: Having 5 thought more about this I'll go back to 4 my original answer (below) which is to use 3 parameters. Optimisations of built queries 2 and whatever the database engine can muster 1 are up to you.

// no parameters
var sb = new StringBuilder();
for(int i=0;i<intArray.Length;i++)
    sb.AppendFormat("p{0},", i);// no SQL injection they are numbers
    connection.Command.Parameters.AddWithValue("p"+i, intArray[i]);
if (sb.Length>0) {sb.Length-=1;}
string sql = "SELECT * FROM table WHERE id IN (" + sb.ToString() + ")";
Score: 3

You have a few options here (in order of 15 preference):

  1. Use a database that supports table valued parameters. This is the only way to get the exact syntax you want.
  2. The data has to come from somewhere: either 14 your database, user action, or machine-generated 13 source.

    • If the data is already in your database, use a subquery instead.
    • For other machine generated data, use BULK INSERT, SqlBulkCopy, or your database's preferred bulk import tools.
    • If it's created by the user, add 12 it to a separate table on each individual user action, and then use a 11 sub query.

      An example of this is a shopping 10 cart. A user might select several items 9 to purchase. Rather than keep these in 8 the app and need to add all the items to 7 an order in one go when they check out, add 6 each item to a table in the db as the user 5 selects or changes it.

  3. Have an sql user defined function that unpacks a string parameter into a table and returns that table as a set you can use with an IN() expression. See the linked article below for more detailed information on how this works.
  4. Build a string list or parameter list dynamically on the client (as shown in other answers). Note that this is my least preferred option, as the code it creates tends to be crazy-vulnerable to sql injection issues.

The definitive (and 4 I mean definitive) work on the subject is here:


The 3 article long, but in a good way. The author 2 is a sql server expert, but the concepts 1 on the whole apply to MySQL as well.

Score: 1

As I know you cannot provide any array as 2 a parameter to prepared statement. IN() doesn't 1 support parameters as an array.

Score: 0

I don't think there's a way you can add 8 them like that, but perhaps you could iterate 7 through the list and generate the query 6 dynamically.

For example:

var intArray = new List<int>(){1,2,3,4};
if (intArray.Count > 0) {
    var query = "SELECT * FROM table WHERE id IN (";
    for (int i = 0; i < intArray.Count; i++) {
        //Append the parameter to the query
        //Note: I'm not sure if mysql uses "@" but you can replace this if needed
        query += "@num" + i + ",";
        //Add the value to the parameters collection
        ...connection.Command.Parameters.AddWithValue("num" + i, intArray[i]);
    //Remove the last comma and add the closing bracket
    query = query.Substring(0, query.Length - 1) + ");";
    //Execute the query here

This way, you could 5 even use a differently typed list and still 4 reap the benefits of parameterized queries. However, I 3 don't know if there would be performance 2 issues with larger lists but I suspect that 1 would be the case.

Score: 0

This doesn't work well for huge lists, but 5 it is the only thing I have found that works 4 if you have to pass a list in as a parameter.

Instead 3 of

SELECT * FROM table WHERE id IN (@parameter)

You have to do this:

FROM table
WHERE INSTR(','+@parameter+',', ','+CAST(the_column AS CHAR) + ',')

Then you can pass 2 in your list with string.Join(",", intArray)

It's a kludge, but it 1 works.

Score: 0

Answer from Mud only works for the first 9 int in the parameter list. This means '2,1,3,4' won't 8 work if id is 1 for example.

See FIND_IN_SET() vs IN() .

No comment 7 possible by now but also see answer from 6 Matt Ellen. Would edit his answer but can't. INSTR 5 doesn't seem to work in a WHERE case with 4 more than one id (returns only on result).

But 3 replacing INSTR with LOCATE make his solution work 2 (with String.Join(",", intArray) as parameter added) ... UP VOTE from 1 me:

LOCATE(CONCAT(',' , CAST(id AS CHAR) , ',') , CONCAT(',' , CAST(@paramter AS CHAR) , ',')) <> 0
Score: 0

Based on the Richard answer, I came up with a similar 3 approach avoiding the SQL Injection:

private MySqlCommand GetCommandWithIn(string sql, string sqlParam, ICollection<string> list)
    var command = new MySqlCommand(string.Empty, connection);
    var i = 0;
    var parameters = new List<string>(list.Count);

    foreach (var element in list)
        var parameter = $"p{i++}";
        command.Parameters.AddWithValue(parameter, element);

    command.CommandText = sql.Replace(sqlParam, string.Join(',', parameters));

    return command;

And 2 you can call it with:

var types = new List<string> { "Type1", "Type2" };
var sql = "SELECT * FROM TABLE WHERE Type IN (@Type)"
using var command = GetCommandWithIn(sql, "@Type", types);

Remember to check for 1 empty list in order to avoid the IN ().

More Related questions