1 Replies - 793 Views - Last Post: 18 March 2011 - 03:58 PM Rate Topic: -----

#1 knightmare2dream  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 51
  • Joined: 13-February 10

Parameterizing a generic object for stored procedure

Posted 18 March 2011 - 01:12 PM

I'm trying to reinforce our data lookups against sql injection and trying to maintain the original code as much as possible. Currently it appears that the procedure is passed a generic object as the set of parameters. I was wondering how I could perametize the words contained in the object like the example used on msdn(http://msdn.microsoft.com/en-us/library/ff648339.aspx) to protect against harmful injections. Any ideas?
using (SqlConnection connection = new SqlConnection(connectionString))
{
  DataSet userDataset = new DataSet();
  SqlDataAdapter myCommand = new SqlDataAdapter( 
             "LoginStoredProcedure", connection);
  myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
  myCommand.SelectCommand.Parameters.Add("@au_id", SqlDbType.VarChar, 11);
  myCommand.SelectCommand.Parameters["@au_id"].Value = SSN.Text;

  myCommand.Fill(userDataset);
}



OUR current code:
Public Function GetLookupList(ByVal LookupList As LookupLists, Optional ByVal objParms() As Object = Nothing) As DataTable

            Dim blnConnExists As Boolean = False
            Dim DS As New DataSet

            OpenConnection(_Db, blnConnExists)

                If System.Web.HttpContext.Current.Trace.IsEnabled Then System.Web.HttpContext.Current.Trace.Write("DataLookup", "Begin Get DataSet")

				If Not IsNothing(objParms) Then
					' ensure that the query strings do not have nonbreaking spaces ()
					' if present, replace with standard spaces ( )
					For i As Integer = 0 To objParms.Length - 1
						If (GetType(String) Is objParms(i).GetType) Then
							objParms(i) = objParms(i).Replace(Chr(160), Chr(32))
						End If
					Next
					DS = _Db.ExecuteDataSet("dbo." & LookupList.ToString, objParms)
				Else
					DS = _Db.ExecuteDataSet("dbo." & LookupList.ToString)
				

                If System.Web.HttpContext.Current.Trace.IsEnabled Then System.Web.HttpContext.Current.Trace.Write("DataLookup", "End Get DataSet")

                Return DS.Tables(0)

        End Function



Is This A Good Question/Topic? 0
  • +

Replies To: Parameterizing a generic object for stored procedure

#2 keakTheGEEK  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 107
  • View blog
  • Posts: 344
  • Joined: 23-February 10

Re: Parameterizing a generic object for stored procedure

Posted 18 March 2011 - 03:58 PM

The code that is in the definition of your GetLookupList function... specifically, the ExecuteDataSet method. That looks a lot like a function that is provided for you in the Microsoft.Practices.EnterpriseLibrary.Data Namespace. Are you using Microsoft Enterprise Library? (looks like you are).

The ExecuteDataSet method of the Microsoft.Practices.EnterpriseLibrary.Data Namespace (Microsoft Enterprise Library) takes an optional generic object array of parameter values. So, that ExecuteDataSet method already parameterizes each element in the object array for you.

If you want to take additional steps to protect from sql injection attacks even further, I recommend having a look at the Constrain Input (Step 1) and Additional Considerations sections of the link you provided in your post.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1