asked on

c# using a TableAdaptor in a WebService

I'm trying to switch over to using a TableAdaptor instead of having the SQL directly in my cs page as I read somewhere this is bad practice due to SQL injection.

So i've added a Dataset to my project and added a TableAdaptor to it, but now in my code i'm trying to access this with no joy.
I've looked here
http://msdn.microsoft.com/en-us/library/ms233822(VS.80).aspx

and tryed using the Intellisense to bring up my TableAdaptor but it's nowhere to be seen.

Could you give me some examples on what I should be doing here?
Many Thanks.

SOLUTION

cauos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wht1986

you can still use 'inline' sql, just make sure you use parameters, and not appending to the command string

Bad inline:
SqlCommand cmd = new SqlCommand("Select * from TesTable where TestId = " + this.TextBox1.Text, conn);

Good:

SqlCommand cmd = new SqlCommand("Select * from TesTable where TestId = @TestID", conn);
cmd.Parameters.AddWithValue("@TestID", int.Parse(this.TextBox1.Text));

the use of parameters will prevent sql injection.

chuckdsc

andrewmilner,

There is a known bug in sending data from a web service,
check the MSDN blog where information is posted related to passing data from a web/wcf service

http://blogs.msdn.com/lifenglu/archive/2007/08/01/passing-datatable-across-web-wcf-services.aspx

andrewmilner

ASKER

How does the Parameters prevent SQL injection?

If I have
string StringFromAtextBox = TextBox1.Value

And I pass this as a parameter into an sql query then surely you can still enter sql in there?

andrewmilner

ASKER

Whats the int.Parse doing in the example above?

ASKER CERTIFIED SOLUTION

wht1986

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

andrewmilner

ASKER

Okay well using the parameters has gotton around the issue I had.
Many thanks for your help.