SQL Server and ADO.Net best method

[Fred Flintstone]

What's the difference between these two methods?

1 - Parameterrized SQL queries:

Dim CommandObject As New Data.SqlClient.SqlCommand
With CommandObject
.Connection = myConnection
.Parameters.Clear()
.Parameters.Add("@TextField", SqlDbType.NVarChar,
50).Value = TextField
.Parameters.Add("@NumField", SqlDbType.Int, 50).Value =
NumField
.Parameters.Add("@BitField", SqlDbType.Int, 50).Value =
BitField
.CommandText = "INSERT [Table1]([TextField], [NumField],
[BitField]) VALUES(@TextField, @NumField, @BitField);"
.ExecuteNonQuery()
End With

2 - Dataset method

myDataRow = myDataSet.Tables(DataSetName).NewRow
myDataRow("TextField") = TextField
myDataRow("NumField") = NumField
myDataRow("BitField") = BitField
myDataSet.Tables(DataSetName).Rows.Add(myDataRow)
mySqlDataAdapter.Update(myDataSet, DataSetName)
Apparently, if I use the data set method, terrorists will fly planes
into buildings and life will end in a spectacular universal explosion.
(or something horrible, I'm not sure exactly, something to do with
people inserting SQL commands into our data streams?) So I've been
recommended method 1 as 'you MUST do it this way!!!1one'.

So my question is then, what's wrong with the dataset method? It it
exposes such a massive security risk, why is it there in the first
place?

I'm just trying to find the best method for doing database management
in ADO.Net and I'm getting conflicting messages. Any advice
appreciated, thanks!

[Kerry Moorman]

Fred,

SQL injection attacks can occur when an sql command is built up as a string
by concatenating sql keywords with data from a textbox, for example.

Using parameters and assigning data to the parameters prevents such attacks.

You are not showing your dataadapter's Insert, Update and Delete commands,
which are being called when the dataadapter's Update method is called.

If you examine the adapter's Insert, Update and Delete commands you should
see that they are using parameters, just like your first example.

So either of the 2 techniques you show should be capable of preventing sql
injection attacks.

Kerry Moorman
"Fred Flintstone" wrote:

What's the difference between these two methods?

1 - Parameterrized SQL queries:

Dim CommandObject As New Data.SqlClient.SqlCommand
With CommandObject
.Connection = myConnection
.Parameters.Clear()
.Parameters.Add("@TextField", SqlDbType.NVarChar,
50).Value = TextField
.Parameters.Add("@NumField", SqlDbType.Int, 50).Value =
NumField
.Parameters.Add("@BitField", SqlDbType.Int, 50).Value =
BitField
.CommandText = "INSERT [Table1]([TextField], [NumField],
[BitField]) VALUES(@TextField, @NumField, @BitField);"
.ExecuteNonQuery()
End With

2 - Dataset method

myDataRow = myDataSet.Tables(DataSetName).NewRow
myDataRow("TextField") = TextField
myDataRow("NumField") = NumField
myDataRow("BitField") = BitField
myDataSet.Tables(DataSetName).Rows.Add(myDataRow)
mySqlDataAdapter.Update(myDataSet, DataSetName)
Apparently, if I use the data set method, terrorists will fly planes
into buildings and life will end in a spectacular universal explosion.
(or something horrible, I'm not sure exactly, something to do with
people inserting SQL commands into our data streams?) So I've been
recommended method 1 as 'you MUST do it this way!!!1one'.

So my question is then, what's wrong with the dataset method? It it
exposes such a massive security risk, why is it there in the first
place?

I'm just trying to find the best method for doing database management
in ADO.Net and I'm getting conflicting messages. Any advice
appreciated, thanks!

[Fred Flintstone]

"So either of the 2 techniques you show should be capable of
preventing sql injection attacks."

Now I'm really confused. The gotdotnet message boards are telling me
the opposite; that I MUST use parameters because a dataset IS
vulnerable to injection attacks.

*sigh* I'm using a data set now, I'm just going to stick with it.
Frankly, I don't see how "select delete * from sales from sales" is
going to wipe my tables.

Thanks for the response! :)

On Tue, 28 Mar 2006 11:00:04 -0800, Kerry Moorman
<Ke**********@discussions.microsoft.com> wrote:

Fred,

SQL injection attacks can occur when an sql command is built up as a string
by concatenating sql keywords with data from a textbox, for example.

Using parameters and assigning data to the parameters prevents such attacks.

You are not showing your dataadapter's Insert, Update and Delete commands,
which are being called when the dataadapter's Update method is called.

If you examine the adapter's Insert, Update and Delete commands you should
see that they are using parameters, just like your first example.

So either of the 2 techniques you show should be capable of preventing sql
injection attacks.

Kerry Moorman
"Fred Flintstone" wrote:

What's the difference between these two methods?

1 - Parameterrized SQL queries:

Dim CommandObject As New Data.SqlClient.SqlCommand
With CommandObject
.Connection = myConnection
.Parameters.Clear()
.Parameters.Add("@TextField", SqlDbType.NVarChar,
50).Value = TextField
.Parameters.Add("@NumField", SqlDbType.Int, 50).Value =
NumField
.Parameters.Add("@BitField", SqlDbType.Int, 50).Value =
BitField
.CommandText = "INSERT [Table1]([TextField], [NumField],
[BitField]) VALUES(@TextField, @NumField, @BitField);"
.ExecuteNonQuery()
End With

2 - Dataset method

myDataRow = myDataSet.Tables(DataSetName).NewRow
myDataRow("TextField") = TextField
myDataRow("NumField") = NumField
myDataRow("BitField") = BitField
myDataSet.Tables(DataSetName).Rows.Add(myDataRow)
mySqlDataAdapter.Update(myDataSet, DataSetName)
Apparently, if I use the data set method, terrorists will fly planes
into buildings and life will end in a spectacular universal explosion.
(or something horrible, I'm not sure exactly, something to do with
people inserting SQL commands into our data streams?) So I've been
recommended method 1 as 'you MUST do it this way!!!1one'.

So my question is then, what's wrong with the dataset method? It it
exposes such a massive security risk, why is it there in the first
place?

I'm just trying to find the best method for doing database management
in ADO.Net and I'm getting conflicting messages. Any advice
appreciated, thanks!