SQL paramaterization WHERE IN not working

Question

I have a specific question about the WHERE IN command inside of a parameterized sql query.

Current situation
All the normal queries are working, but on the following query example it goes wrong:

SELECT * FROM table WHERE Id IN(@Ids)

What does this query do?
This query is selecting all items in the table with the specific ids.

The error

# this generates the following string: "1,2,3,4", which gets parsed into the @Ids param
new OleDbParameter("Ids", String.Join(",", objects.Select(c => c.Id.ToString())));

This will generate the folling raw sql query:

# this is the converted sql query after performing the OleDbCommand.ExecuteNonQueryAsync()
# which is not working
SELECT * FROM table WHERE Id IN("1,2,3,4")
# this is the sql query that is working
SELECT * FROM table where Id IN("1", "2", "3", "4")

solution
as you can see, the first example is an array, but is not an array inside of the IN statement. How can I change my code so it will get the working sql query with parameterization?

score 0 · Answer 1 · answered Sep 17 '22 at 10:56

You could try this

var ids = new char[objects.Count()];
Array.ForEach(ids, x => x = '?');

var idsList = string.Join(',', ids);

var queryString = $"SELECT * FROM table WHERE Id IN ({idsList})"; 

var command = new OleDbCommand(queryString, connection); 

for (int i = 0; i < ids.Length; i++)
{
    command.Parameters.Add($"@p{i + 1}", OleDbType.VarChar, 5).Value = objects.ElementAt(i).Id; 
} 
  
OleDbDataReader reader = command.ExecuteReader();

SQL paramaterization WHERE IN not working

1 Answers1