Add List<int> 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.

Solution 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.

Solution 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.

Solution 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() + ")";

Solution 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.

Solution 5

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

View more solutions

Comments

[Phoenix_uy]

You can do the IN thing... do the "where id IN (1,2,3,4)" is the same of "where id = 1 or id = 2 or id = 3 or id = 4", at least this works for me :)

[Vladislav Rastrusny]

@user710422 AS I can see in your example there is no parameters to bind. And we talk about them only.

[Phoenix_uy]

Maybe i explained wrong.. when i said "convert the array to string and pass it" i dont mean "SELECT * FROM table WHERE id IN(" + strings + ")"...

[Jamie Treworgy]

I know, and I'm saying it's not possible to do it the way you want.

[BrokenGlass]

that whole loop and check can be replaced with string.Join(",", intArray)

[Adam Straughan]

well, yes if you wanted short readable code :-) rather than in my case forgotten about that method

[Joel Coehoorn]

smells of sql injection. Even if you're using a relatively "safe" type in like an int sequence, imo it's a bad habit.

[BrokenGlass]

I wasn't the down voter btw - while longer than needed this is still an answer that applies to the question

[Adam Straughan]

@Joel Coehoorn, bad habit yes, however if you are making an active design decision then it is a solid solution (if longer than required as pointed out)

[Jamie Treworgy]

"smells of sql injection" i agree, and i dislike this, but it's also the only way to solve the problem in some situations without doing something silly like iterating "id=@parm_x.." over and over. There is absolutely no risk when using strongly typed numeric data. The key to avoiding problems like sql injection isn't just blind faith in doing things a certain way - it's also knowing what you are doing.

[Phoenix_uy]

The thing as i said... i am currently using the string s = string.Join(",", intArray) but when i pass command.Parameter.AddWithValue("parameter", s) in the query, mysql connector pass the parameter like "1\,2\,3" and the select query doesn't work as the expected.

[Adam Straughan]

a string containg a comma delimited list of numbers cannot be the value for a parameter in any SQL driver I know. Note in my sample I am setting the whole SQL value, there are no parameters. As discussed elsewhere you cannot pass and array in with the SQL. I will update my answer with more detail

[Nick Hanshaw]

The previous comment is misleading... Mud is using a parameterized query to achive the goal. Parameterized queries are literally what you use to prevent sql injection in strings without having to manually scrub the contents of the string. This is a great example of achieving the goal.

[Colin Young]

@NickHanshaw You are of course correct. I believe I may have mixed this up with a comment on the original question about ORMs and string concatenation. In any case, I'm deleting my comment to avoid any confusion. Good catch, thanks. Indexes are still an issue, but that might be okay depending on the table.