Sql injection and data adapters

[Anon]

Hi all.


My question, basically, is: if I do this, am I open to a sql injection
attack?


DataAdapter.SelectCommand = new SqlCommand("SELECT * FROM Users WHERE
UserId = 1",ConnectionObject) //Let's assume for a moment that that
sql string is not dynamically constructed and not susceptible.. I am
asking more about what comes next..


SqlCommandBuilder CmdBuilder = new SqlCommandBuilder (DataAdapter);
DataAdapter.UpdateCommand = CmdBuilder.GetUpdateCommand();


DataSet ds = new DataSet();
DataAdapter.Fill(ds,"mytable");

ds.tables[0].Rows["Name"] = MALICIOUS SQL INJECTION ATTEMPT; // let's
say this is where the end user could slip malicious string he wanted
into

DataUpdater.Update(ds);

if (ds.haschanges)
{
ds.AcceptChanges();
}



I'm hoping that the fact that DataAdapter.UpdateCommand is essentially
a parameterized sql string that an attempted sql injection attack
placed into here: ds.tables[0].Rows["Name"] would always fail.




Is that right or am I wide open here?


Thank you in advance for your help and I look forward to hearing from
anyone who knows the answer to this..

Regards,

Jeff

 

[Robert Simpson]

CommandBuilders use parameterized queries when generating
insert/update/delete statements, so your answer is NO, you will not be
subject to a SQL injection attack from them. In a parameterized query, the
SQL statement with its parameter markers are parsed, and then the parameters
are pushed separately. The values inside the parameters are not parsed by
the SQL query parser, so there's nowhere in the codepath for an injection to
occur.

Of course, if you're passing a SQL clause in a parameter, and calling
sp_executesql() on the parameter's value, then yes of course you could open
yourself to all sorts of attacks. But that's a separate issue.

Robert

 

[Miha Markic [MVP C#]]

Hi Jeff,

SqlCommandBuilder is responsible for building DataAdapter (not DataAdapter
by itself).
And yes it uses only parametrised statements.

 

[Anon]

Hi Robert.

Thanks for the great explanation. I never understood how that worked
under the covers. That puts me at ease

CommandBuilders use parameterized queries when generating
insert/update/delete statements, so your answer is NO, you will not be
subject to a SQL injection attack from them. In a parameterized query, the
SQL statement with its parameter markers are parsed, and then the parameters
are pushed separately. The values inside the parameters are not parsed by
the SQL query parser, so there's nowhere in the codepath for an injection to
occur.

Of course, if you're passing a SQL clause in a parameter, and calling
sp_executesql() on the parameter's value, then yes of course you could open
yourself to all sorts of attacks. But that's a separate issue.

Robert

Hi all.


My question, basically, is: if I do this, am I open to a sql injection
attack?


DataAdapter.SelectCommand = new SqlCommand("SELECT * FROM Users WHERE
UserId = 1",ConnectionObject) //Let's assume for a moment that that
sql string is not dynamically constructed and not susceptible.. I am
asking more about what comes next..


SqlCommandBuilder CmdBuilder = new SqlCommandBuilder (DataAdapter);
DataAdapter.UpdateCommand = CmdBuilder.GetUpdateCommand();


DataSet ds = new DataSet();
DataAdapter.Fill(ds,"mytable");

ds.tables[0].Rows["Name"] = MALICIOUS SQL INJECTION ATTEMPT; // let's
say this is where the end user could slip malicious string he wanted
into

DataUpdater.Update(ds);

if (ds.haschanges)
{
ds.AcceptChanges();
}



I'm hoping that the fact that DataAdapter.UpdateCommand is essentially
a parameterized sql string that an attempted sql injection attack
placed into here: ds.tables[0].Rows["Name"] would always fail.




Is that right or am I wide open here?


Thank you in advance for your help and I look forward to hearing from
anyone who knows the answer to this..

Regards,

Jeff

 

[msnews.microsoft.com]

There is no way to SQL inject using the Update method. The SQL string
literal, if you append values, can be injected, but the queries created by
the CommandBuilder are parameterized.

If you use SQL built on the fly (dynamic SQL), use parameters and you are
fine on that end, as well.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think Outside the Box!
*************************************************