使用C#在SQL内联查询中将多个值传递给单个参数 [英] Passing multiple values to single parameter in SQL inline query using C#
问题描述
我是编码的新手,正在寻求有关如何在嵌入式SQL查询中将多个值传递给单个参数的帮助。我已经对以下查询进行了框架设计,但听说这可能会导致SQL注入问题。请帮助我如何使用基于SQL查询的参数构建下面的框架。
I am new to coding and looking for some help on how to pass multiple values to a single parameter in an inline SQL query. I have framed the below query, but I heard this could result in SQL-injection issue. Kindly help on how can I frame the below by using parameter based in the SQL query.
string query = "Select ID, email FROM DBTABLE WHERE email in (";
var stringBuiler = new StringBuilder();
using (StringReader stringReader = new StringReader(DownloadIDtextBox.Text))
{
string line;
string prefix = "";
while ((line = stringReader.ReadLine()) != null)
{
stringBuiler.Append(prefix);
prefix = ",";
stringBuiler.Append("'" + line + "'");
}
}
query += stringBuiler.ToString() + ")";
SqlDataAdapter da = new SqlDataAdapter(query, Connection);
DataTable dt = new DataTable();
da.Fill(dt);
只想提一下ID是GUID格式。
Just want to mention that ID is GUID format.
推荐答案
如果您手动进行操作,则该过程(基本上)将是:
If you are doing it manually, the process would be (basically):
var stringBuiler = new StringBuilder("Select ID, email FROM DBTABLE WHERE email in (");
// create "cmd" as a DB-provider-specific DbCommand instance, with "using"
using (...your reader...)
{
int idx = 0;
...
while ((line = stringReader.ReadLine()) != null)
{
// ...
Guid val = Guid.Parse(line);
// ...
var p = cmd.CreateParameter();
p.Name = "@p" + idx;
p.Value = val;
if (idx != 0) stringBuiler.Append(",");
stringBuiler.Append(p.Name);
cmd.Parameters.Add(cmd);
idx++;
}
}
cmd.CommandText = stringBuiler.Append(")").ToString();
并使用 that ...的意思是:您 don'使用内联SQL-您使用完全参数化的SQL。不过,ORM / micro-ORM系列中的工具在这里极大都可以提供帮助-使它成为一线工具。
and use that... meaning: you don't use inline SQL - you use fully parameterized SQL. There are tools in the ORM/micro-ORM families that will help immensely here, though - making it a one-liner.
这篇关于使用C#在SQL内联查询中将多个值传递给单个参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!