Wrox Programmer Forums
Go Back   Wrox Programmer Forums > C# and C > C# 2005 > C# 2005
|
C# 2005 For discussion of Visual C# 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the C# 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 18th, 2009, 02:39 PM
Authorized User
 
Join Date: Jul 2009
Posts: 20
Thanks: 0
Thanked 1 Time in 1 Post
Default SQL-Injection and multiple parameters when iterating a ListBox for example

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
 
Old October 19th, 2009, 04:43 AM
samjudson's Avatar
Friend of Wrox
 
Join Date: Aug 2007
Posts: 2,128
Thanks: 1
Thanked 189 Times in 188 Posts
Default

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.
__________________
/- Sam Judson : Wrox Technical Editor -/

Think before you post: What have you tried?
 
Old October 19th, 2009, 05:23 AM
Authorized User
 
Join Date: Jul 2009
Posts: 20
Thanks: 0
Thanked 1 Time in 1 Post
Default

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.

Last edited by 4thhorseman; October 19th, 2009 at 05:29 AM..
 
Old October 19th, 2009, 05:25 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 996
Thanks: 2
Thanked 11 Times in 11 Posts
Send a message via Yahoo to melvik
Default

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 & ....
__________________
Always,
Hovik Melkomian.
 
Old October 19th, 2009, 05:35 AM
samjudson's Avatar
Friend of Wrox
 
Join Date: Aug 2007
Posts: 2,128
Thanks: 1
Thanked 189 Times in 188 Posts
Default

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();
}
__________________
/- Sam Judson : Wrox Technical Editor -/

Think before you post: What have you tried?





Similar Threads
Thread Thread Starter Forum Replies Last Post
sql injection trufla Classic ASP Basics 2 June 16th, 2008 02:54 PM
SQl Injection through ASP and MS SQl 2000 cancer10 Classic ASP Databases 1 October 27th, 2007 03:21 AM
Pass Multiple Parameters from C# to SQL RS ms_code_bsuter BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 2 July 25th, 2005 06:34 PM
SQL Injection cygnusx04 Classic ASP Databases 1 November 6th, 2004 11:06 AM
What SQL Injection is ? minhtri Classic ASP Basics 2 October 20th, 2004 10:11 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.