Passing multiple values to single parameter in SQL inline query using C#

Question

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

Just want to mention that ID is GUID format.

Possible duplicate of [SELECT \* FROM X WHERE id IN (...) with Dapper ORM](https://stackoverflow.com/questions/8388093/select-from-x-where-id-in-with-dapper-orm) — mjwills, Nov 08 '18 at 22:24
https://stackoverflow.com/questions/41676619/ado-net-insert-multiple-values-to-sql-parameter may also be worth a read. — mjwills, Nov 08 '18 at 22:29

score 3 · Answer 1 · answered Nov 08 '18 at 23:07

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();

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.

Passing multiple values to single parameter in SQL inline query using C#

1 Answers1

Linked