ASP.NET 应用程序构建模块：第一部分

• 下载源代码文件 - 2 Kb

简介

在本系列文章中，我的目标是为任何 asp.net 应用程序提供基本和通用的模块，以帮助开发更快更容易。

当您遵循只知道如何编写代码的程序员的方法时，创建一个 .net 应用程序很容易，但是如果您认为自己比典型的代码编写者更优秀，请醒来并编写一些能够满足应用程序的需求并解决所有问题的东西。有一个名为 SqlHelper 的类文件在 vb.net 开发人员中非常流行，它可以帮助您访问 sql server 数据库，这很好，我也使用了这个类很长时间，但是当我感觉到存储过程的重要性时，我决定编写自己的类，其中包含所有内容。

该类文件非常安全，您只需不需要用 ' ' 替换 '，就可以确保您的应用程序免受 sql 注入的攻击。

实际上，我将要向您展示的是两个命名空间的组合，称为“数据访问工具”(DAT) 和“安全工具”(ST)。 DAT 负责访问来自数据库或 web.config 的每种数据，而 ST 负责处理数据库级别的安全性。

数据访问工具

它具有以下方法。

1. RunSpDtNoParam（用于运行没有参数的存储过程并返回 datatablle，意味着使用数据适配器）
2. RunSpDt（用于运行带有参数的存储过程并返回 datatablle，意味着使用数据适配器）
3. RunSpDrNoParam（用于运行没有参数的存储过程并返回 Data reader）
4. RunSpDr（用于运行带有参数的存储过程并返回 Data reader）
5. RunSpNonQuery（用于通过存储过程插入/更新或删除）
6. RunQueryDt（用于运行内联查询并返回数据表）
7. RunQueryDr（用于运行内联查询并返回数据读取器）
8. RunQueryNonQuery（用于通过内联查询插入/更新或删除）

以下类有注释，因此理解代码不会有问题。

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
'declairing variables
Dim ObjCom As SqlCommand
Dim ObjDataAdapter As New SqlDataAdapter
Dim Return_Dt As New DataTable
'========================================
'Checking For Sql Injection
ObjValidateData.DetectSqlInjection(SP_name, True)
ObjCon = New SqlConnection(ObjWebConfigAccess.GetAddKeyValue("ConnectionString"))
ObjCom = New SqlCommand
ObjCon.Open()
'============================================

'Setting Command Object
ObjCom.CommandType = CommandType.StoredProcedure
ObjCom.CommandText = SP_name
'==========================

'Setting opened connection with 
'Command Object and Filling datatable
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

'For Using Datatable and Stored Procedure
Public Function RunSpDt(ByVal sp_name _
       As String, ByVal param() As _
       SqlParameter) As DataTable
'declairing variables
Dim ObjCon As SqlConnection
Try
Dim ObjCom As SqlCommand
Dim ObjDataAdapter As New SqlDataAdapter
Dim Return_Dt As New DataTable
'========================================
'Checking For Sql Injection
ObjValidateData.DetectSqlInjection(sp_name, True)
ObjCon = New SqlConnection(ObjWebConfigAccess.GetAddKeyValue("ConnectionString"))
ObjCom = New SqlCommand
ObjCon.Open()
'============================================

'Setting Command Object
ObjCom.CommandType = CommandType.StoredProcedure
ObjCom.CommandText = sp_name
'==========================

'setting parameter in command object from parameter
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
'=====================================================

'Setting opened connection with 
'Command Object and Filling datatable
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
'For Using DataReader and Stored Procedure
Public Function RunSpDrNoParam(ByVal _
       sp_name As String) As SqlDataReader
'declairing variables
Dim ObjCon As SqlConnection
Try
Dim ObjCom As SqlCommand
Dim ObjDataReader As SqlDataReader
Dim Return_Dt As New DataTable
'========================================
'Checking For Sql Injection
ObjValidateData.DetectSqlInjection(sp_name, True)
ObjCon = New SqlConnection(ObjWebConfigAccess.GetAddKeyValue("ConnectionString"))
ObjCom = New SqlCommand
ObjCon.Open()
'============================================

'Setting Command Object
ObjCom.CommandType = CommandType.StoredProcedure
ObjCom.CommandText = sp_name
'==========================

'Setting opened connection with Command Object and Filling datatable
ObjCom.Connection = ObjCon
ObjDataReader = ObjCom.ExecuteReader(CommandBehavior.CloseConnection)

'============================================
Return ObjDataReader

Catch ex As Exception
Throw ex
Finally
ObjCon.Close()
End Try

End Function
'For Using DataReader and Stored Procedure
Public Function RunSpDr(ByVal sp_name As String,_
   ByVal param() As SqlParameter) As SqlDataReader
'declairing variables
Dim ObjCon As SqlConnection
Try
Dim ObjCom As SqlCommand
Dim ObjDataReader As SqlDataReader
Dim Return_Dt As New DataTable
'========================================
'Checking For Sql Injection
ObjValidateData.DetectSqlInjection(sp_name, True)
ObjCon = New SqlConnection(ObjWebConfigAccess.GetAddKeyValue("ConnectionString"))
ObjCom = New SqlCommand
ObjCon.Open()
'============================================

'Setting Command Object
ObjCom.CommandType = CommandType.StoredProcedure
ObjCom.CommandText = sp_name
'==========================

'setting parameter in command object from parameter
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
'===================================

'Setting opened connection with Command 
'Object and Filling datatable
ObjCom.Connection = ObjCon
ObjDataReader = ObjCom.ExecuteReader(CommandBehavior.CloseConnection)

'===================================
Return ObjDataReader

Catch ex As Exception
Throw ex
Finally
ObjCon.Close()
End Try

End Function
'For Using Long and Stored Procedure
Public Function RunSpNonQuery(ByVal Sp_name _
       As String, ByVal Param() _
       As SqlParameter) As Long

'declairing variables
Dim ObjCon As SqlConnection
Try
Dim ObjCom As SqlCommand
Dim Results As Long
'==================================
'Checking For Sql Injection
ObjValidateData.DetectSqlInjection(Sp_name, True)

ObjCon = New SqlConnection(ObjWebConfigAccess.GetAddKeyValue("ConnectionString"))
ObjCom = New SqlCommand
ObjCon.Open()
'=================================

'Setting Command Object
ObjCom.CommandType = CommandType.StoredProcedure
ObjCom.CommandText = Sp_name
'==========================

'setting parameter in command object from parameter
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
'================================
'Setting opened connection with Command Object and Filling datatable
ObjCom.Connection = ObjCon
Results = ObjCom.ExecuteNonQuery
'=================================
Return Results

Catch ex As Exception
Throw ex
Finally
ObjCon.Close()
End Try

End Function
'For Using Datatable and Sql Query
Public Function RunQueryDt(ByVal Sql_Query As String) As DataTable
'declairing variables
Dim ObjCon As SqlConnection
Try
Dim ObjCom As SqlCommand
Dim ObjDataAdapter As New SqlDataAdapter
Dim Return_Dt As New DataTable
'===============================
'Checking For Sql Injection
ObjValidateData.DetectSqlInjection(Sql_Query, True)
ObjCon = New SqlConnection(ObjWebConfigAccess.GetAddKeyValue("ConnectionString"))
ObjCom = New SqlCommand
ObjCon.Open()
'==============================

'Setting Command Object
ObjCom.CommandType = CommandType.Text
ObjCom.CommandText = Sql_Query
'==========================

'Setting opened connection with Command Object and Filling datatable
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
'For Using DataReader and Sql Query
Public Function RunQueryDr(ByVal Sql_Query As String) As SqlDataReader
'declairing variables
Dim ObjCon As SqlConnection
Try
Dim ObjCom As SqlCommand
Dim ObjDataReader As SqlDataReader
Dim Return_Dt As New DataTable
'========================================
'Checking For Sql Injection
ObjValidateData.DetectSqlInjection(Sql_Query, True)
ObjCon = New SqlConnection(ObjWebConfigAccess.GetAddKeyValue("ConnectionString"))
ObjCom = New SqlCommand
ObjCon.Open()
'========================================

'Setting Command Object
ObjCom.CommandType = CommandType.Text
ObjCom.CommandText = Sql_Query
'==========================

'Setting opened connection with Command Object and Filling datatable
ObjCom.Connection = ObjCon
ObjDataReader = ObjCom.ExecuteReader(CommandBehavior.CloseConnection)
'=======================================
Return ObjDataReader

Catch ex As Exception
Throw ex
Finally
ObjCon.Close()
End Try
End Function
'For Using Long and Sql Query
Public Function RunQueryNonQuery(ByVal Sql_Query As String) As Long
'declairing variables
Dim ObjCon As SqlConnection
Try
Dim ObjCom As SqlCommand
Dim Results As Long
'========================================
'Checking For Sql Injection


ObjValidateData.DetectSqlInjection(Sql_Query, True)
ObjCon = New SqlConnection(ObjWebConfigAccess.GetAddKeyValue("ConnectionString"))
ObjCom = New SqlCommand
ObjCon.Open()
'===============================

'Setting Command Object
ObjCom.CommandType = CommandType.Text
ObjCom.CommandText = Sql_Query
'==========================

'Setting opened connection with Command Object and Filling datatable
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

安全工具

此命名空间中只有一个类，称为“验证数据”。它具有以下方法。

1. DetectSqlInjection（将从 DAT 命名空间调用，并负责捕获任何类型的 sql 注入关键字）
2. CheckAndFireSqlInjectionException（此方法用于触发异常，可能有人想在其中执行任何操作）
3. GetSqlInjectionKeywords（此方法将获取在 web.config 文件中指定的所有注入关键字）
   例如

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

上面的文件可以为您提供很多帮助，并且在很多情况下，我已经创建了此命名空间的 DLL，并且只要我从头开始，就添加引用。
我已将以上文件保留为下载，以获取更多帮助，请联系我：aghausman@gmail.com

编程愉快 :)