使用ASP.NET 2.0创建审计功能 [英] Creating audit functionality using ASP.NET 2.0

问题描述

您好b $ b

您已经为审计示例做得很好。



但它仅适用于gridview。 />


我想要一样但是我的单一形式。 INSERT / UPDATE / DELTE。



请帮忙。



问候

Manish



我的尝试：

 Imports System 
 Imports System.Data 
 Imports System.Data.SqlClient 
 Imports System.Configuration 
 Imports System.Web 
 Imports System.Web.Configuration 
 Imports System.Collections.Generic 
 Imports System.Collections.Specialized 
 
 Public Class CurrentFields 
 
'Private Shared ReadOnly _connectionString As String 
 
 Private _colName As String 
 
 Private _colValue As String 
 
'Public Shared Sub New（）
 Public _connectionString As String = WebConfigurationManager.ConnectionStrings（POManagerConnectionString）。 ConnectionString 
'结束子
 
公共属性CurrColName As String 
获取
返回_colName 
结束获取
 
设置（ByVal值）作为字符串）
 _colName = value 
结束集
结束物业
 
公共物业CurrColValue As String 
获取
返回_colValue 
结束获取
 
 Set（ByVal value As String）
 _colValue = value 
 End Set 
 End Property 
 
公共函数GetCurrentFields（ByVal WhereField As String，ByVal ID As Integer，ByVal TableName As String）As List（Of CurrentFields）
 Dim results As List（Of CurrentFields）= New List（Of CurrentFields）（）
 Dim ds As DataSet = New DataSet（）
 Dim dt As DataTable = Nothing 
 Dim dad As SqlDataAdapter = Nothing 
 Dim strSelectSql As String =SELECT * FROM& TableName& 哪里& WhereField& =& ID 
'strSelectSql + =WHERE& WhereField = ID 
尝试
 Dim conn As SqlConnection = New SqlConnection（_connectionString）
 dad = New SqlDataAdapter（strSelectSql，conn）
 dad.Fill（ds）
 dt = ds.Tables（0）
 For Each row As DataRow in dt.Rows 
 For Each column As DataColumn in dt.Columns 
 Dim currFlds As CurrentFields = New CurrentFields（）
 currFlds.CurrColName = column.ColumnName 
 currFlds.CurrColValue = row（column）.ToString（）
 results.Add（currFlds）
 Next 
 Next 
 Catch 
结束尝试
 
返回结果
结束功能
结束等级

< br $>

 Imports System 
 Imports System.Data 
 Imports System.Data.SqlClient 
 Imports System.Configuration 
 Imports System.Web 
 Imports System.Web.Con figuration 
 Imports System.Web.Security 
 Imports System.Web.UI 
 Imports System.Web.UI.WebControls 
 Imports System.Web.UI.WebControls.WebParts 
 Imports System.Web.UI.HtmlControls 
 
 Public Class InsertAuditData 
 
 Public Shared Sub insertAuditChanges（ByVal TableName As String，ByVal Operation As String，ByVal PerformedBy As String，ByVal FieldName As String，ByVal OldValue As String，ByVal NewValue As String）
 Dim strConn As String = WebConfigurationManager.ConnectionStrings（POManagerConnectionString）。ConnectionString 
使用conn As SqlConnection = New SqlConnection（strConn）
 Dim strUpdate As String =INSERT INTO AUDIT_Table（PageName，Operation，OccurredAt，PerformedBy，FieldName，OldValue，NewValue）VALUES
 strUpdate + =（'& TableName& '，'&操作& '，'+ DateTime.Now& '，'& PerformedBy& '，'& FieldName& '，'& OldValue& '，'& NewValue& '）
 Dim cmd As SqlCommand = New SqlCommand（strUpdate，conn）
 conn.Open（）
 cmd.ExecuteNonQuery（）
 End using 
 End Sub 
结束等级

当我保存数据时我把这个代码但myDE和e.NewValues有问题，因为它不是gridview ???它与gridview一起工作正常。

'开始审核
'-------------- -------------------------------------------------- ------------------------ 
 Dim ID As String = txtPOID.Text 
 Dim currFlds As CurrentFields = New CurrentFields（）
 currFieldList = currFlds.GetCurrentFields（POID，ID，PurchaseOrder）
 Session（CurrData）= currFieldList 
 
 
 currFieldList = CType（Session（ CurrData），List（Of CurrentFields））
 
 
'Dim myDE As DictionaryEntry 
'myDE.Value = currFlds.CurrColValue 
 
 For each myDE作为DictionaryEntry在e.NewValues中
 Dim i As Integer = 0 
 Dim key As String = myDE.Key.ToString（）
 For each currFld As CurrentFields in currFieldList 
 if currFld。 CurrColName = key然后
退出
结束如果
 
i + = 1 
下一个
 
如果myDE.Value IsNot Nothing则
 Dim newVal As String = myDE.Value.ToString（）
如果currFieldList（i）.CurrColValue<> newVal然后
 InsertAuditData.insertAuditChanges（编辑采购订单，更新，会话（用户名），密钥，currFieldList（i）.CurrColValue，newVal）
结束如果
其他
如果currFieldList（i）.CurrColValue<> 然后
 InsertAuditData.insertAuditChanges（编辑采购订单，更新，会话（用户名），currFieldList（i）.CurrColName，currFieldList（i）.CurrColValue，）
结束如果
结束如果
下一个
 

解决方案

 Dim strSelectSql as  String  =   SELECT * FROM &安培; TableName&   where& WhereField&   =& ID 

 Dim strUpdate As  String  =   INSERT INTO AUDIT_Table（PageName，Operation，OccurredAt，PerformedBy，FieldName，OldValue，NewValue）VALUES 
 strUpdate + = < span class =code-string> （'& TableName&  '，'& Operation&  ' ，' + DateTime.Now&  '，'& PerformedBy& amp; ;  '，'& FieldName&   '，'& OldValue&  '，'& NewValue&  '） 

不是你问题的解决方案，而是你遇到的另一个问题。

永远不要通过连接字符串来构建SQL查询。迟早，您将使用用户输入来执行此操作，这会打开一个名为SQL注入的漏洞，这对您的数据库很容易并且容易出错。

名称中的单引号你的程序崩溃。如果用户输入像Brian O'Conner这样的名称可能会使您的应用程序崩溃，那么这是一个SQL注入漏洞，崩溃是最少的问题，恶意用户输入，并且它被提升为具有所有凭据的SQL命令。

SQL注入 - 维基百科 [ ^ ]

SQL注入 [ ^ ]

按示例进行SQL注入攻击 [ ^ ]

PHP：SQL注入 - 手册 [ ^ ]

SQL注入预防备忘单 - OWASP [ ^ ]

Hi
You have done good job for audit example.

But its working only on gridview.

I want same but work into my single form. INSERT/UPDATE/DELTE.

Please help.

Regards
Manish

What I have tried:

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Web
Imports System.Web.Configuration
Imports System.Collections.Generic
Imports System.Collections.Specialized

Public Class CurrentFields

    'Private Shared ReadOnly _connectionString As String

    Private _colName As String

    Private _colValue As String

    'Public Shared Sub New()
    Public _connectionString As String = WebConfigurationManager.ConnectionStrings("POManagerConnectionString").ConnectionString
    'End Sub

    Public Property CurrColName As String
        Get
            Return _colName
        End Get

        Set(ByVal value As String)
            _colName = value
        End Set
    End Property

    Public Property CurrColValue As String
        Get
            Return _colValue
        End Get

        Set(ByVal value As String)
            _colValue = value
        End Set
    End Property

    Public Function GetCurrentFields(ByVal WhereField As String, ByVal ID As Integer, ByVal TableName As String) As List(Of CurrentFields)
        Dim results As List(Of CurrentFields) = New List(Of CurrentFields)()
        Dim ds As DataSet = New DataSet()
        Dim dt As DataTable = Nothing
        Dim dad As SqlDataAdapter = Nothing
        Dim strSelectSql As String = "SELECT * FROM " & TableName & " where " & WhereField & "=" & ID
        ' strSelectSql += " WHERE " & WhereField = ID
        Try
            Dim conn As SqlConnection = New SqlConnection(_connectionString)
            dad = New SqlDataAdapter(strSelectSql, conn)
            dad.Fill(ds)
            dt = ds.Tables(0)
            For Each row As DataRow In dt.Rows
                For Each column As DataColumn In dt.Columns
                    Dim currFlds As CurrentFields = New CurrentFields()
                    currFlds.CurrColName = column.ColumnName
                    currFlds.CurrColValue = row(column).ToString()
                    results.Add(currFlds)
                Next
            Next
        Catch
        End Try

        Return results
    End Function
End Class

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Web
Imports System.Web.Configuration
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls

Public Class InsertAuditData

    Public Shared Sub insertAuditChanges(ByVal TableName As String, ByVal Operation As String, ByVal PerformedBy As String, ByVal FieldName As String, ByVal OldValue As String, ByVal NewValue As String)
        Dim strConn As String = WebConfigurationManager.ConnectionStrings("POManagerConnectionString").ConnectionString
        Using conn As SqlConnection = New SqlConnection(strConn)
            Dim strUpdate As String = "INSERT INTO AUDIT_Table (PageName, Operation, OccurredAt, PerformedBy, FieldName, OldValue, NewValue) VALUES "
            strUpdate += "('" & TableName & "', '" & Operation & "', '" + DateTime.Now & "' ,'" & PerformedBy & "' , '" & FieldName & "', '" & OldValue & "', '" & NewValue & "')"
            Dim cmd As SqlCommand = New SqlCommand(strUpdate, conn)
            conn.Open()
            cmd.ExecuteNonQuery()
        End Using
    End Sub
End Class

When i save data then i put this code but something wrong with myDE and e.NewValues because its not gridview ??? It's working fine with gridview.

' Start Audit
      ' ----------------------------------------------------------------------------------------
      Dim ID As String = txtPOID.Text
      Dim currFlds As CurrentFields = New CurrentFields()
      currFieldList = currFlds.GetCurrentFields("POID", ID, "PurchaseOrder")
      Session("CurrData") = currFieldList


      currFieldList = CType(Session("CurrData"), List(Of CurrentFields))


      'Dim myDE As DictionaryEntry
      'myDE.Value = currFlds.CurrColValue

      For Each myDE As DictionaryEntry In e.NewValues
          Dim i As Integer = 0
          Dim key As String = myDE.Key.ToString()
          For Each currFld As CurrentFields In currFieldList
              If currFld.CurrColName = key Then
                  Exit For
              End If

              i += 1
          Next

          If myDE.Value IsNot Nothing Then
              Dim newVal As String = myDE.Value.ToString()
              If currFieldList(i).CurrColValue <> newVal Then
                  InsertAuditData.insertAuditChanges("Edit Purchase Order", "UPDATE", Session("Username"), key, currFieldList(i).CurrColValue, newVal)
              End If
          Else
              If currFieldList(i).CurrColValue <> "" Then
                  InsertAuditData.insertAuditChanges("Edit Purchase Order", "UPDATE", Session("Username"), currFieldList(i).CurrColName, currFieldList(i).CurrColValue, "")
              End If
          End If
      Next

解决方案

Dim strSelectSql As String = "SELECT * FROM " & TableName & " where " & WhereField & "=" & ID

Dim strUpdate As String = "INSERT INTO AUDIT_Table (PageName, Operation, OccurredAt, PerformedBy, FieldName, OldValue, NewValue) VALUES "
            strUpdate += "('" & TableName & "', '" & Operation & "', '" + DateTime.Now & "' ,'" & PerformedBy & "' , '" & FieldName & "', '" & OldValue & "', '" & NewValue & "')"

Not a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]

这篇关于使用ASP.NET 2.0创建审计功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！