0

I am trying to perform a query that reads a list of projects from a database where the project's state is within a given list. I have written this query (using C#, also):

string statesList = "(1, 2, 3, 4, 5)"; // This is built inside a function, it could include any or all of these values, e.g. (1, 3, 4);

cmd.CommandText = "SELECT p.Name, p.State_Id FROM Projects p
    WHERE p.State_Id IN @states";
cmd.Parameters.AddWithValue("@states", statesList);

When I run this query, I am given this error:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''(1, 2, 3, 4, 5)'' at line 1".

I'm stumped. Can anyone see what I'm missing?

slava
  • 1,901
  • 6
  • 28
  • 32
AdamMc331
  • 16,492
  • 10
  • 71
  • 133
  • 1
    You can't use an parameter for the list of the IN() operator. You can use a loop to get those parameters (you need 5 in your example). Don't forget your parentheses around this list too. – VMai Aug 12 '14 at 13:39
  • You can't use Parameters for `IN` values. You have two options. Either concatenated your string `statesList` with your `CommandText` or See [this Answer](http://stackoverflow.com/a/337792/961113) for manually adding parameters for each value. – Habib Aug 12 '14 at 13:40
  • See http://stackoverflow.com/questions/337704/parameterize-a-sql-in-clause – dee-see Aug 12 '14 at 13:40
  • Oh, I never knew that. What do you think would be the best way to write a loop like that? – AdamMc331 Aug 12 '14 at 13:40
  • possible duplicate of [C# Parameterized Query MySQL with \`in\` clause](http://stackoverflow.com/questions/650455/c-sharp-parameterized-query-mysql-with-in-clause) – VMai Aug 12 '14 at 13:40
  • I had no idea the parameters were the issue. It didn't even occur to me. Sorry for duplicate. – AdamMc331 Aug 12 '14 at 13:42
  • Hi what about Python? Can be parameterize IN clause with Python code? I have a similar question: https://stackoverflow.com/questions/67925365/parameterize-an-mysql-in-clause-in-python-code – wawawa Jun 11 '21 at 08:20

2 Answers2

3

You have the misconception that using parameterized queries works the same as a string replacement. The resulting query is not

SELECT p.Name, p.State_Id FROM Projects p WHERE p.State_Id IN (1,2,3,4,5)

You can not use a parameterized query the way you do here.

Thorsten Dittmar
  • 55,956
  • 8
  • 91
  • 139
  • Thank you! I had no idea that paramaters weren't allowed for the in clause. All I did was concatenate the command text: `cmd.CommandText = "SELECT p.Name, p.State_Id FROM projects p WHERE p.State_Id IN "; cmd.CommandText += statesList;` I'll be back in 10 minutes to accept this. – AdamMc331 Aug 12 '14 at 13:42
1

Your resulting query is:

SELECT p.Name, p.State_Id
FROM Projects p
WHERE p.State_Id IN ('1, 2, 3, 4, 5');

Which is the same as:

WHERE p.State_Id = '1, 2, 3, 4, 5' <-- a single string value

If you don't care about indexes, you can do what you want as:

SELECT p.Name, p.State_Id
FROM Projects p
WHERE find_in_set(p.State_Id, @states) > 0

Note: you should remove the spaces after the commas for this.

Alternatively, do a direct substitution of the list into the string. Unfortunately, I don't think there is a better, convenient solution.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks! Indexes is important, because I want a query that can select any number of project states based on command line parameters. The first one works fine, I already have a function that builds the string. The error was in using a paramatized value for the list. – AdamMc331 Aug 12 '14 at 13:49