Click here to Skip to main content
15,119,954 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a list of prduct ID's in List PrductID;
which i want to use in asp.net c# query formation in IN clause.

What I have tried:

i can convernt that list into comma seperated values in the form of single string and use in query like below.
e.g.
"SELECT * from Emp where empID in ("+strProductID+")";
and with DataReader i can execute the query but again i can't user this 'strProductID' directly in query which is not secure...
so i want to convert each value of list into sqlparameter so that it will be secure from sqlinjection.
Posted
Updated 29-Jul-18 11:00am
Comments
Gerry Schmitz 29-Jul-18 7:44am
   
Where did you get your "list of product ID's"?
Mike V Baker 29-Jul-18 12:44pm
   
Create a SP in the sql server database that takes the list of products id's in a single param?

1 solution

There are a few possible answers to your question. If the list of product IDs are coming from a database, you're best bet is to change your query to either use a JOIN or an EXISTS with a sub-select (using whatever WHERE clause limits the product IDs to the desired ones).

If the list can be long, and is not coming from a database, I would suggest using a stored procedure with a table valued parameter and then pursue the same technique suggested above. For more information on table valued parameters, see:

Use Table-Valued Parameters (Database Engine) | Microsoft Docs[^]

While generally not a good approach, if you know your list will be short and merely want to construct a command with parameters (to avoid SQL injection), something like the following should work:

var builder = new StringBuilder(1024);
int count = list.Count;

builder.Append("SELECT * from Emp where empID in (");
for (int index = 1; index <= count; index++)
{
  if (index > 1)
    builder.Append(',');

  builder.Append($"@p{index}");
}
builder.Append(")");

using (var command = new SqlCommand(builder.ToString(), connection))
{
  SqlParameterCollection parameters = command.Parameters;
  for (int index = 1; index <= count; index++)
    parameters.AddWithValue($"p{index}", list[index]);

  // Do what you want with the command
}
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900