想要将列表项转换为sqlparameter的... [英] Want to convert list items into sqlparameter's...

问题描述

我在列表产品ID中有一个产品ID的列表；
我想在IN子句中的asp.net c#查询格式中使用它.

我尝试过的事情:

我可以将其转换为单个字符串形式的逗号分隔值列表，并在如下所示的查询中使用.
例如
从SELECT中选择*，其中empID在(" + strProductID +)中"；
并使用DataReader我可以执行查询，但是我又不能直接在不安全的查询中使用此"strProductID" ...
因此，我想将列表的每个值转换为sqlparameter，以便从sqlinjection安全.

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.

推荐答案

您的问题有几个可能的答案.如果产品ID的列表来自数据库，则最好将查询更改为使用JOIN或EXISTS进行子选择(使用任何WHERE子句将产品ID限制为所需的ID) .

如果该列表可能很长，并且不是来自数据库，那么我建议您使用带有表值参数的存储过程，然后再执行上面建议的相同技术.有关表值参数的更多信息，请参见:

使用表值的参数(数据库引擎)| Microsoft文档 [

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}"); } 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 }

"p{index}", list[index]); // Do what you want with the command }

这篇关于想要将列表项转换为sqlparameter的...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！