SQL-Injection and multiple parameters when iterating a ListBox for example

4thhorseman · October 18th, 2009, 02:39 PM

Hello everybody,

I deceided to use SQL-Injection as I heard of the security issues that arise.

My problem is that I want to select multiple items of an ListBox and use those as paramaters in a SQL INSERT statement. The thing is, that you can use a paramameter-name only one time in a select statement. I tricked the thing creating a 'new' command object everytime I retrieve another item of the ListBox.

This is not the finest method, I guess. Does anybody know how to solve this issue?

My code looks like this:
...
currentMessageId = ....
int id = 0;

for ( i = 0; i< listBoxUsers.SelectedItems.Count; i+)
{
sqlStr = "";
row = ((DataRowView)this.listBoxUsers.SelectedItems[i]).Row;
id = Convert.ToInt16(row[listBoxUsers.ValueMember]);
sqlStr = "INSERT INTO user_messages (M_Id, User_Id) VALUES (@messageId, @userId)";

command = new SqlCommand (sqlStr, sqlConnectionString);
command.Parameters.AddWithValue("messageId", currentMessageId);
command.Parameters.AddWithValue("userId", id);

command.CommandText = sqlStr;
command.ExecuteNonQuery();
}

is there a way to do an successful insert to the n:m related table without creating a new instance of the command class every time?

Best regards

samjudson · October 19th, 2009, 04:43 AM

Firstly, you are not 'using SQL Injection' but avoiding it...

If you create the SqlCommand outside of your loop, and then use SqlCommand.Parameter.Add to create a new SqlParameter object, then simply set its Value and execute the command inside the loop.

4thhorseman · October 19th, 2009, 05:23 AM

Ok,

It worked.

I placed the command object outside the loop and used the clear method of the command.parameters object to clear the variable-name.

Thank you.

melvik · October 19th, 2009, 05:25 AM

or u can use Dataset & DataAdapter
u'll add ur items in dataSet\DataTable\DataRow(s) & call Update() method of it
DataAdapter will all make its inserts & ....

samjudson · October 19th, 2009, 05:35 AM

I actually meant this:

Code:

currentMessageId = ....
string sqlStr = "INSERT INTO user_messages (M_Id, User_Id) VALUES (@messageId, @userId)";
 
 SqlCommand command = new SqlCommand (sqlStr, sqlConnectionString);
 command.Parameters.AddWithValue("messageId", currentMessageId);
 SqlParameter userParam = command.Parameters.Add("userId", SqlType.Int);
 
for (int i = 0; i< listBoxUsers.SelectedItems.Count; i+)
{
row = ((DataRowView)this.listBoxUsers.SelectedItems[i]).Row;
int id = Convert.ToInt16(row[listBoxUsers.ValueMember]);
userParam.Value = id;
command.ExecuteNonQuery();
}