Passing C# String as SQL parameter for SELECT WHERE IN

Question

I am writing a function to take as many multiple selected items from a Listbox and pass the vaules to a SQL Query to INSERT values into a table after selecting the filtered values from another table. The code I have typed is below and it doesn't seem to work (the problem is with the way I'm passing the string to the query.

string lbSites = "";

protected void Button1_Click1(object sender, EventArgs e)
{
    string cns = "server=abc;database=testDB;Trusted_Connection=True";
    using (SqlConnection con = new SqlConnection(cns))
    {
        using (SqlCommand command = con.CreateCommand())
        {
            command.CommandText = "INSERT INTO Activity (Hostname,Site,Status,System_Dept,Business_Dept)"
                + "SELECT * FROM Inventory WHERE Site IN ("+lbSites+");"
                ; 
            con.Open();
            command.Parameters.AddWithValue("@lbSites", lbSites);
            command.ExecuteNonQuery();
            con.Close();
        }
    }
}

protected void ListBox1_SelectedIndexChanged(object sender, EventArgs e)
{
    if (ListBox1.Items.Count > 0)
    {
        for (int i = 0; i < ListBox1.Items.Count; i++)
        {
            if (ListBox1.Items[i].Selected)
            {
                lbSites += "'" + ListBox1.Items[i].Value + "', ";
            }
        }
    }
}

Answer 1

You should not directly pass values like this to SQL, as it leaves you open to a SQL Injection Attack.

Instead, you can figure out how many parameters you need, and then insert those parameter names into your query.

This approach will work for up to approximately 2,000 values (assuming SQL Server). If you need to pass more values, you will either need to break up the queries into sub-sets, or use parameter-value tables.

Example (not tested, so may have some bugs):

// Get your selected items:
var items = ListBox1.Items.Where(i=>i.Selected).Select(i=>i.Value).ToArray(); 

// Create a series of parameters @param0, @param1, @param2..N for each value.
string paramNames = string.Join(", ", Enumerable.Range(0,items.Count()).Select(e=>"@param"+e)); 

// Build the command text and insert the parameter names. 
string commandText = "INSERT INTO Activity (Hostname,Site,Status,System_Dept,Business_Dept)" 
                + "SELECT * FROM Inventory WHERE Site IN ("+ paramNames +")";

command.CommandText = commandText; 

// Now add your parameter values:  this binds @param0..N to the values selected. 

for(int param=0;param<items.Count();param++)
{
   command.Parameters.AddWithValue("@param" + param, items[param]); 
}