Add List to a mysql parameter

Question

I have this question about the MySqlParameter from the .NET connector.

I have this query:

SELECT * FROM table WHERE id IN (@parameter)

And the MySqlParameter is:

intArray = new List<int>(){1,2,3,4};

...connection.Command.Parameters.AddWithValue("parameter", intArray);

This is possible? Is possible to pass an array of int to a single MySqlParameter? The other solution will be convert the array of int to a string such like "1,2,3,4", but this, when i pass it to the MySqlParameter and this is recognized as a string, it puts in the sql query like "1\,2\,3\,4" and this do not return the expected values.

@ UPDATE: Seems like the mysql connector team should work a little bit harder.

Answer 1

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

I ran into this last night. I found that FIND_IN_SET 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 limitations (I found your post looking for an alternate solution), but this may work for you.

Answer 2

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

Answer 3

you are going to have to iterate over your 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 thought more about this I'll go back to my original answer (below) which is to use parameters. Optimisations of built queries and whatever the database engine can muster 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() + ")";

Answer 4

You have a few options here (in order of 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 your database, user action, or machine-generated 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 it to a separate table on each individual user action, and then use a sub query.

     An example of this is a shopping cart. A user might select several items to purchase. Rather than keep these in the app and need to add all the items to an order in one go when they check out, add each item to a table in the db as the user 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 I mean definitive) work on the subject is here:

http://www.sommarskog.se/arrays-in-sql.html

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

Answer 5

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

Answer 6

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

See FIND_IN_SET() vs IN() .

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

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

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

Answer 7

I don't think there's a way you can add them like that, but perhaps you could iterate through the list and generate the query 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 even use a differently typed list and still reap the benefits of parameterized queries. However, I don't know if there would be performance issues with larger lists but I suspect that would be the case.

Answer 8

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

Instead of

SELECT * FROM table WHERE id IN (@parameter)

You have to do this:

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

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

It's a kludge, but it works.

Answer 9

Based on the Richard answer, I came up with a similar 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++}";
        parameters.Add($"@{parameter}");
        command.Parameters.AddWithValue(parameter, element);
    }

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

    return command;
}

And 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 empty list in order to avoid the IN ().