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 + "', ";
            }
        }
    }
}

your code has a serious security problem called SQL injection - please read up on parameterized queries... — Yahia, May 29 '12 at 01:53
your LINQ and Lambda skills seem ok (not to sure about your SQL tho), why didn't you go for a LINQ to SQL option? — Jeremy Thompson, May 29 '12 at 02:51

score 3 · Answer 1 · answered May 29 '12 at 02:02

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

score 0 · Accepted Answer · answered May 29 '12 at 01:54

0

The value of lbSites is lost everytime you've posted back. Keep it in your ViewState.

Besides, you don't need command.Parameters.AddWithValue("@lbSites", lbSites); since there's no @lbSites parameter in your sql.

answered May 29 '12 at 01:54

Jeffrey Zhao

4,923
4
30
52

score 0 · Answer 3 · answered Sep 28 '16 at 14:52

0

try this 
SELECT M.REG_NO, T.TYPE_ID 
    FROM MAIN AS M 
        INNER JOIN CLASSIFICATION AS C 
            ON M.REG_NO = C.REG_NO
        INNER JOIN TYPE AS T 
            ON T.TYPE_ID = C.TYPE_ID
    WHERE (@Types) like .LIKE '%,' +T.TYPE_ID+ ',%'

answered Sep 28 '16 at 14:52

Mohamad Mahmoud Darwish

3,865
9
51
76

Passing C# String as SQL parameter for SELECT WHERE IN

3 Answers3