Introduction :
My Objective in this series of articles is to provide basic and general blocks for any asp.net application which helps the development more faster and easier.
Creating a .net application is easy when you are following an approach of a coder who only knows how to code the things but if you think yourself a bit more than a typical code wake up and write some thing which full fills the need of application and solve all the issues. There is a class file which is called SqlHelper very popular among the vb.net developers which helps you to access sql server database, that is good I also used that class for a long time but when I feel the importance of Stored Procedures I decide to write my own class which will have all the things inside.
The class file is very secure and you just don't need to replace ' with ' ' for keeping your application secure from sql injection.
Actually the thing which I am going to show you is a cobinationof two namespacess Called "Data Access Tools" (DAT) and "Security Tools" (ST). DAT is responsible for accessing every kind of data either from database or web.config and ST is responsible for taking care of database level security.
Data Access Tools:
It has the following methods.
- RunSpDtNoParam (Used to run stored procedure with out parameter and return datatablle means use Data Adapter)
- RunSpDt (Used to run stored procedure with parameter and return datatablle means use Data Adapter)
- RunSpDrNoParam (Used to run stored procedure with out parameter and return Data reader)
- RunSpDr (Used to run stored procedure with parameter and return Data reader)
- RunSpNonQuery (Used to insert / update or delete by stored procedure)
- RunQueryDt (Used to run inline query and return data table)
- RunQueryDr (Used to run inline query and return data reader)
- RunQueryNonQuery (Used to insert / update or delete by Inline query)
The following class have comments so there will be no problem to understrand the code.
Namespace DataAccessTools
Public Class Dal
Dim ObjWebConfigAccess As New WebConfigAccess
Dim ObjValidateData As New SecurityTools.ValidateData
Public Function RunSpDtNoParam(ByVal SP_name As String) As DataTable
Dim ObjCon As SqlConnection
Try
Dim ObjCom As SqlCommand
Dim ObjDataAdapter As New SqlDataAdapter
Dim Return_Dt As New DataTable
ObjValidateData.DetectSqlInjection(SP_name, True)
ObjCon = New SqlConnection(ObjWebConfigAccess.GetAddKeyValue("ConnectionString"))
ObjCom = New SqlCommand
ObjCon.Open()
ObjCom.CommandType = CommandType.StoredProcedure
ObjCom.CommandText = SP_name
ObjCom.Connection = ObjCon
ObjDataAdapter = New SqlDataAdapter(ObjCom)
ObjDataAdapter.Fill(Return_Dt)
Return Return_Dt
Catch ex As Exception
Throw ex
Finally
ObjCon.Close()
End Try
End Function
Public Function RunSpDt(ByVal sp_name _
As String, ByVal param() As _
SqlParameter) As DataTable
Dim ObjCon As SqlConnection
Try
Dim ObjCom As SqlCommand
Dim ObjDataAdapter As New SqlDataAdapter
Dim Return_Dt As New DataTable
ObjValidateData.DetectSqlInjection(sp_name, True)
ObjCon = New SqlConnection(ObjWebConfigAccess.GetAddKeyValue("ConnectionString"))
ObjCom = New SqlCommand
ObjCon.Open()
ObjCom.CommandType = CommandType.StoredProcedure
ObjCom.CommandText = sp_name
Dim a As Integer
For a = 0 To param.Length - 1
ObjValidateData.DetectSqlInjection(param(a).Value, True)
ObjValidateData.DetectSqlInjection(param(a).ParameterName, True)
ObjCom.Parameters.Add(param(a))
Next
ObjCom.Connection = ObjCon
ObjDataAdapter = New SqlDataAdapter(ObjCom)
ObjDataAdapter.Fill(Return_Dt)
Return Return_Dt
Catch ex As Exception
Throw ex
Finally
ObjCon.Close()
End Try
End Function
Public Function RunSpDrNoParam(ByVal _
sp_name As String) As SqlDataReader
Dim ObjCon As SqlConnection
Try
Dim ObjCom As SqlCommand
Dim ObjDataReader As SqlDataReader
Dim Return_Dt As New DataTable
ObjValidateData.DetectSqlInjection(sp_name, True)
ObjCon = New SqlConnection(ObjWebConfigAccess.GetAddKeyValue("ConnectionString"))
ObjCom = New SqlCommand
ObjCon.Open()
ObjCom.CommandType = CommandType.StoredProcedure
ObjCom.CommandText = sp_name
ObjCom.Connection = ObjCon
ObjDataReader = ObjCom.ExecuteReader(CommandBehavior.CloseConnection)
Return ObjDataReader
Catch ex As Exception
Throw ex
Finally
ObjCon.Close()
End Try
End Function
Public Function RunSpDr(ByVal sp_name As String,_
ByVal param() As SqlParameter) As SqlDataReader
Dim ObjCon As SqlConnection
Try
Dim ObjCom As SqlCommand
Dim ObjDataReader As SqlDataReader
Dim Return_Dt As New DataTable
ObjValidateData.DetectSqlInjection(sp_name, True)
ObjCon = New SqlConnection(ObjWebConfigAccess.GetAddKeyValue("ConnectionString"))
ObjCom = New SqlCommand
ObjCon.Open()
ObjCom.CommandType = CommandType.StoredProcedure
ObjCom.CommandText = sp_name
Dim a As Integer
For a = 0 To param.Length - 1
ObjValidateData.DetectSqlInjection(param(a).Value, True)
ObjValidateData.DetectSqlInjection(param(a).ParameterName, True)
ObjCom.Parameters.Add(param(a))
Next
ObjCom.Connection = ObjCon
ObjDataReader = ObjCom.ExecuteReader(CommandBehavior.CloseConnection)
Return ObjDataReader
Catch ex As Exception
Throw ex
Finally
ObjCon.Close()
End Try
End Function
Public Function RunSpNonQuery(ByVal Sp_name _
As String, ByVal Param() _
As SqlParameter) As Long
Dim ObjCon As SqlConnection
Try
Dim ObjCom As SqlCommand
Dim Results As Long
ObjValidateData.DetectSqlInjection(Sp_name, True)
ObjCon = New SqlConnection(ObjWebConfigAccess.GetAddKeyValue("ConnectionString"))
ObjCom = New SqlCommand
ObjCon.Open()
ObjCom.CommandType = CommandType.StoredProcedure
ObjCom.CommandText = Sp_name
Dim a As Integer
For a = 0 To Param.Length - 1
ObjValidateData.DetectSqlInjection(Param(a).Value, True)
ObjValidateData.DetectSqlInjection(Param(a).ParameterName, True)
ObjCom.Parameters.Add(Param(a))
Next
ObjCom.Connection = ObjCon
Results = ObjCom.ExecuteNonQuery
Return Results
Catch ex As Exception
Throw ex
Finally
ObjCon.Close()
End Try
End Function
Public Function RunQueryDt(ByVal Sql_Query As String) As DataTable
Dim ObjCon As SqlConnection
Try
Dim ObjCom As SqlCommand
Dim ObjDataAdapter As New SqlDataAdapter
Dim Return_Dt As New DataTable
ObjValidateData.DetectSqlInjection(Sql_Query, True)
ObjCon = New SqlConnection(ObjWebConfigAccess.GetAddKeyValue("ConnectionString"))
ObjCom = New SqlCommand
ObjCon.Open()
ObjCom.CommandType = CommandType.Text
ObjCom.CommandText = Sql_Query
ObjCom.Connection = ObjCon
ObjDataAdapter = New SqlDataAdapter(ObjCom)
ObjDataAdapter.Fill(Return_Dt)
Return Return_Dt
Catch ex As Exception
Throw ex
Finally
ObjCon.Close()
End Try
End Function
Public Function RunQueryDr(ByVal Sql_Query As String) As SqlDataReader
Dim ObjCon As SqlConnection
Try
Dim ObjCom As SqlCommand
Dim ObjDataReader As SqlDataReader
Dim Return_Dt As New DataTable
ObjValidateData.DetectSqlInjection(Sql_Query, True)
ObjCon = New SqlConnection(ObjWebConfigAccess.GetAddKeyValue("ConnectionString"))
ObjCom = New SqlCommand
ObjCon.Open()
ObjCom.CommandType = CommandType.Text
ObjCom.CommandText = Sql_Query
ObjCom.Connection = ObjCon
ObjDataReader = ObjCom.ExecuteReader(CommandBehavior.CloseConnection)
Return ObjDataReader
Catch ex As Exception
Throw ex
Finally
ObjCon.Close()
End Try
End Function
Public Function RunQueryNonQuery(ByVal Sql_Query As String) As Long
Dim ObjCon As SqlConnection
Try
Dim ObjCom As SqlCommand
Dim Results As Long
ObjValidateData.DetectSqlInjection(Sql_Query, True)
ObjCon = New SqlConnection(ObjWebConfigAccess.GetAddKeyValue("ConnectionString"))
ObjCom = New SqlCommand
ObjCon.Open()
ObjCom.CommandType = CommandType.Text
ObjCom.CommandText = Sql_Query
ObjCom.Connection = ObjCon
Results = ObjCom.ExecuteNonQuery
Return Results
Catch ex As Exception
Throw ex
Finally
ObjCon.Close()
End Try
End Function
End Class
End Namespace
Security Tools:
There is only one class in this namespace which is known as "Validate Data". It has the following methods.
- DetectSqlInjection (will call from DAT namespace and is responsible for catch any kind of sql injection keyword)
- CheckAndFireSqlInjectionException (This method is used to fire the exception, may be some one want to perform any action in it)
- GetSqlInjectionKeywords (This method will get all the injection keywords specified in web.config file)
For example:
Namespace SecurityTools
Public Class ValidateData
Public Sub DetectSqlInjection(ByVal Input_Sql As Object, _
Optional ByVal IgnoreColor As Boolean = False)
If IsDBNull(Input_Sql) = False Then
Dim SqlInjectionCharacter() As Char = GetSqlInjectionKeywords()
Dim a As Integer
For a = 0 To SqlInjectionCharacter.Length - 1
If IgnoreColor = True Then
If SqlInjectionCharacter(a) <> "'" Then
CheckAndFireSqlInjectionException(Input_Sql, SqlInjectionCharacter(a))
End If
Else
CheckAndFireSqlInjectionException(Input_Sql, SqlInjectionCharacter(a))
End If
Next
End If
End Sub
Protected Sub CheckAndFireSqlInjectionException(ByVal _
InputSql As String, ByVal CharToMap As Char)
If InputSql.IndexOf(CharToMap) <> -1 Then
Throw New CustomExceptions.Exception_SqlInjection
End If
End Sub
Public Function GetSqlInjectionKeywords() As Char()
Dim ObjWebConfigAccess As New DataAccessTools.WebConfigAccess
Dim Keywords As String = ObjWebConfigAccess.GetAddKeyValue("SqlInjectionKeywords")
Return Keywords.ToCharArray()
End Function
End Class
End Namespace
Well above file can help you alot and in many cases, I have created DLL of this namespace and just add reference whenever I start some thing from the start.
I have keep the above file for download for more help contact me : aghausman@gmail.com
Happy Programming :)