I'd like to take a second and say good bye to two things:

1. ADO 2.x (whatever version they are on)

2. SQL Injection

That's right people SQL Injection Ain't effecting or affecting US anymore!!!

We have the dang cure and it comes in the form of ADO.NET's SQLCommand Object.

Simply put, we no longer have to construct queries in the matter of:

SQL = "SELECT * FROM Users WHERE NickName = '" & trim(request("NickName")) & "'"

Which is Susceptible to SQL Injection which can then basically either return something

to the user that we don't want them to see, or Update/Insert/Delete a mass amount of rows which

we obviously do not want them to do!!!!

Using the SQLCommand Object now will save us from injection, and also the dreaded problem of

Converting (') Into ('') when receiving data from the client.

Here is a code snippet i put together to show you how to use this beast!  The example is using

a classic "Ad-Hoc" query. To use a stored procedure wouldn't change much.

' ... In your webform

Public Class WebForm1 : Inherits System.Web.UI.Page

      Dim m_dsUser as new DataSet

      ' Gets fired on every page load

      Sub Page_Load()

            Dim sLastName as String = request.params("LastName")

            Dim sFirstName as string = request.params("FirstName")

            Dim sQuery as string = ""

            Dim arrParamList as new ArrayList

            Dim param as new SqlParameter

            ' Create the SQL Injection-safe query.

            sQuery = "SELECT * FROM Users WHERE FirstName = @FirstName AND LastName = @LastName"

            ' Fill the proper key, value combo for the struct structParameter

            ' Then add this to the arraylist

            param.ParameterName = "@FirstName"

            param.Value = sFirstName

            arrParamList.add(param)

            param.ParameterName = "@LastName"

            param.Value = sLastName

            arrParamList.add(param)

            ' Send the query off to the shared method that will return us a

            ' DataSet representing the User requested.

            m_dsUser = DAL.GetUser(sQuery, arrParamList)

            '-- Do something with the DataSet like bind to a DataGrid. 

      End Sub

'...

End Class

'.... DataAccessLayer.vb

Public Shared Class DAL

      ' GetUser for Ad-Hoc Queries

      Public Shared Function GetUser(ByVal SQL as string, ByVal ParamList as ArrayList) as DataSet

            Dim _DataSet As New DataSet

            Dim cmd As New SqlCommand

            Dim adapt As New SqlDataAdapter

            Dim Param as new SqlParameter

            Dim Item as new ListItem

            cmd.Connection = New SqlConnection(ConfigurationSettings.AppSettings.Get("ConnectString"))

            cmd.CommandType = CommandType.Text

            cmd.CommandText = SQL

            For Each Item in ParamList.Count

                  Param = CType(Item, SqlParameter)

                  cmd.Parameters.Add(Param)

            next Param

            cmd.Connection.Open()

            adapt.SelectCommand = cmd

            adapt.Fill(_DataSet)

            ' Garbage Cleaning

            cmd.Connection.Dispose()

            adapt.Dispose()

            return _DataSet

      End Function     

End Class

Notice the Use of the "@SomeVarName"?  This tells our command object to expect some parameter in it's position and it is smart enough, or dumb enough to not care what it is, it will query on it the proper way.

This cures all!

What does this all mean?

1. A More Secure Site.

2. With the use of a common Database-type code base, less code to write I.E. Code reuse.

3. Money and Time saved in many ways shapes and/or forms which i think would make some people happy.

JR