加密和解密数据库中的数据

引言

您可以在将数据存储到数据库之前轻松地对其进行加密，并在检索时自动解密，从而在需要时以纯文本形式查看。这在将密码存储在数据库中时特别有用，出于安全原因，不应从后端数据库读取该数据。

在这种情况下，我们基于两篇较早的文章。第一篇是Anh Trinh的文章，发表于2007年1月4日：《在保存到数据库之前加密密码》。第二篇是Jim Murphy的文章，发布在2003年11月25日的Iron Speed Designer技术论坛上：《密码数据库加密》。

这两种方法都有特定的用途。Trinh先生的文章使用一个简单的哈希函数来加密，但没有反转该函数来查看密码的方法。在某些情况下这可能是可取的，例如当数据不需要将来查看时。Murphy先生的文章对于数据查看来说更实用。

本文融合了这两种技术，并为我们这些刚接触Iron Speed Designer和Visual Basic .NET的人提供了简单的解释。

在我们的场景中，我们研究了如何在将密码保存到数据库之前对其进行加密，并提供了一种方法，供授权人员查看或编辑解密后的密码。此示例可以扩展到需要加密的数据库中的其他字段。

密码表的数据库结构可以使用以下 SQL 脚本创建，或者您也可以自己创建

CREATE TABLE [dbo].[passwd] (
    [id] [int] IDENTITY (1, 1) NOT NULL ,
    [UserId] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Password] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Application] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Comment] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [RoleID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [LastChange] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Procedure

• 步骤 1：基于上述密码表创建一个 Iron Speed Designer 应用程序。
• 步骤 2：使用 Murphy 先生的文章作为指导，从 此处 下载并构建 Kryptonite DLL，并在 Visual Studio .NET 2003 中将其作为一个单独的项目。
• 步骤 3：将新的 DLL 复制到应用程序的 bin 文件夹中。

  ...\<App Folder>\Bin

• 步骤 4：在 Visual Studio .NET 2003 中打开 Iron Speed Designer 加密项目。添加对在步骤 1 中创建的 Kryptonite DLL 的引用。添加对 .NET -> System.Security 的引用。保存项目，但不要构建它！
• 步骤 5：在 Iron Speed Designer 中重新打开加密项目。将以下内容添加到 Public Class passwdRecordControl 的底部，位于

  .NET Framework 1.1

  ...\<App Folder>\Shared\AddPasswordPage.Conrtols.vb

  .NET Framework 2.0 / 3.0

  ...\<App Folder>\App_Code\Shared\AddPasswordPage.Conrtols.vb

  Visual Basic .NET

  Public Overrides Sub GetUIData()
      ' Set the over ride
      MyBase.GetUIData()
      Dim oKryptonite As New Kryptonite.SymmCrypto
      (Kryptonite.SymmCrypto.Providers.TripleDES) 
      ‘ Declare the Encryption DLL
      Dim record As passwdRecord = Me.GetRecord
      Dim password As String = record.Password
      record.Password = oKryptonite.Encrypt(password)
  End Sub

• 步骤 6：将以下代码添加到 Public Class passwdRecordControl 的底部，位于

  .NET Framework 1.1

  ...\<App Folder>\Shared\EditPasswordPage.Controls.vb

  .NET Framework 2.0 / 3.0

  ...\<App Folder>\App_Code\Shared\EditPasswordPage.Controls.vb

  此代码从数据库中检索密码，解密它，并在编辑时将其加密形式保存回数据库。

  Visual Basic .NET

  Public Overrides Sub GetUIData()
      MyBase.GetUIData()
      Dim oKryptonite As New _
          Kryptonite.SymmCrypto(Kryptonite.SymmCrypto.Providers.TripleDES)
      Dim record As passwdRecord = Me.GetRecord
      Dim password As String = record.Password
      record.Password = oKryptonite.Encrypt(password)
  End Sub
   
  Public Overrides Sub LoadData()
      ' Copy of the LoadData() with the Decryption added
      ' to reveal the current text password
   
          If Not Me.RecordUniqueId Is Nothing AndAlso _
                 Me.RecordUniqueId.Trim <> "" Then
              Me.DataSource = PasswdTable.GetRecord(Me.RecordUniqueId, True)
              Return
          End If
   
          Dim wc As WhereClause = Me.CreateWhereClause()
          If wc Is Nothing Then
              Me.DataSource = New PasswdRecord()
              Return
          End If
   
          ' Retrieve the record from the database.
          Dim recList() As PasswdRecord = PasswdTable.GetRecords(wc, Nothing, 0, 2)
          If recList.Length = 0 Then
              Throw New Exception(Page.GetResourceValue(
                  "Err:NoRecRetrieved", "mvhpass"))
          End If
   
          Me.DataSource = PasswdTable.GetRecord(recList(0).GetID.ToXmlString(), True)
   
      End Sub
   
      ' Populate the UI controls using the DataSource.
      ' To customize, override this method in passwdRecordControl.
      Public Overrides Sub DataBind()
   
          MyBase.DataBind()
   
          ' Make sure that the DataSource is initialized.
          If Me.DataSource Is Nothing Then
              Return
          End If
   
          ' For each field, check to see if a value
          ' is specified. If a value is specified,
          ' then format the value for display.
          ' If no value is specified, use the default value
          (formatted).
          ' Copied and added here to override the password field
   
          If Me.DataSource.IsCreated OrElse Me.DataSource.Application0Specified Then
   
              Dim formattedValue As String = _
                  Me.DataSource.Format(PasswdTable.Application0)
              formattedValue = HttpUtility.HtmlEncode(formattedValue)
              Me.Application1.Text = formattedValue
          Else
   
              Me.Application1.Text = PasswdTable.Application0.Format
              (PasswdTable.Application0.DefaultValue)
          End If
   
          If Me.Application1.Text Is Nothing _
              OrElse Me.Application1.Text.Trim() = "" Then
              Me.Application1.Text = " "
          End If
   
          If Me.DataSource.IsCreated OrElse Me.DataSource.CommentSpecified Then
   
              Dim formattedValue As String = 
                  Me.DataSource.Format(PasswdTable.Comment)
              formattedValue = HttpUtility.HtmlEncode(formattedValue)
              Me.Comment.Text = formattedValue
          Else
   
              Me.Comment.Text = PasswdTable.Comment.Format
              (PasswdTable.Comment.DefaultValue)
          End If
   
          If Me.Comment.Text Is Nothing _
              OrElse Me.Comment.Text.Trim() = "" Then
              Me.Comment.Text = " "
          End If
   
          If Me.DataSource.IsCreated OrElse Me.DataSource.LastChangeSpecified Then
   
              Dim formattedValue As String = Me.DataSource.Format(
                  PasswdTable.LastChange)
              formattedValue = HttpUtility.HtmlEncode(formattedValue)
              Me.LastChange.Text = formattedValue
          Else
   
              Me.LastChange.Text = PasswdTable.LastChange.Format
              (PasswdTable.LastChange.DefaultValue)
          End If
   
          If Me.LastChange.Text Is Nothing _
              OrElse Me.LastChange.Text.Trim() = "" Then
              Me.LastChange.Text = ""
          End If
   
          If Me.DataSource.IsCreated OrElse Me.DataSource.PasswordSpecified Then
   
              Dim formattedValue As String = 
                  Me.DataSource.Format(PasswdTable.Password)
              'add decryption code here to change the formatted value
              Dim oKryptonite As New
              Kryptonite.SymmCrypto(Kryptonite.SymmCrypto.Providers.TripleDES)
              formattedValue= oKryptonite.Decrypt(formattedValue)
              formattedValue = HttpUtility.HtmlEncode(formattedValue)
   
   
              'end additions
              Me.Password.Text = formattedValue
          Else
   
              Me.Password.Text = PasswdTable.Password.Format
              (PasswdTable.Password.DefaultValue)
          End If
   
          If Me.Password.Text Is Nothing _
              OrElse Me.Password.Text.Trim() = "" Then
              Me.Password.Text = " "
          End If
   
          If Me.DataSource.IsCreated OrElse Me.DataSource.UserId0Specified Then
   
              Dim formattedValue As String = _
                  Me.DataSource.Format(PasswdTable.UserId0)
              formattedValue = HttpUtility.HtmlEncode(formattedValue)
              Me.UserId1.Text = formattedValue
          Else
   
              Me.UserId1.Text = _
                 PasswdTable.UserId0.Format(PasswdTable.UserId0.DefaultValue)
          End If
   
          If Me.UserId1.Text Is Nothing _
              OrElse Me.UserId1.Text.Trim() = "" Then
              Me.UserId1.Text = " "
          End If
   
          Me.IsNewRecord = True
          If Me.DataSource.IsCreated Then
              Me.IsNewRecord = False
   
              Me.RecordUniqueId = Me.DataSource.GetID.ToXmlString()
          End If
   
              ' Load data for each record and table UI control.
              ' Ordering is important because child controls get 
              ' their parent ids from their parent UI controls.
   
      End Sub

• 步骤 7：将以下代码添加到 ShowPasswordPage.Controls.vb 文件中。此代码用于检索从数据库中解密的密码，以便授权用户查看。

  Visual Basic .NET

  Public Overrides Sub LoadData()
      ' Copy of the LoadData() with the Decryption
      ' added to reveal the current text password
   
   
          If Not Me.RecordUniqueId Is Nothing AndAlso _
                 Me.RecordUniqueId.Trim <> "" Then
              Me.DataSource = PasswdTable.GetRecord(Me.RecordUniqueId, True)
              Return
          End If
   
          Dim wc As WhereClause = Me.CreateWhereClause()
          If wc Is Nothing Then
              Me.DataSource = New PasswdRecord()
              Return
          End If
   
          ' Retrieve the record from the database.
          Dim recList() As PasswdRecord = PasswdTable.GetRecords(wc, Nothing, 0, 2)
          If recList.Length = 0 Then
              Throw New Exception(Page.GetResourceValue(
                  "Err:NoRecRetrieved", "mvhpass"))
          End If
   
   
          Me.DataSource = PasswdTable.GetRecord(recList(0).GetID.ToXmlString(), True)
   
      End Sub  
      ' Populate the UI controls using the DataSource.
      ' To customize, override this method in
      passwdRecordControl.
      Public Overrides Sub DataBind()
   
          MyBase.DataBind()
   
          ' Make sure that the DataSource is initialized.
          If Me.DataSource Is Nothing Then
              Return
          End If
   
   
          ' For each field, check to see if a value
          ' is specified. If a value is specified,
          ' then format the value for display. If no value
          ' is specified, use the default value (formatted).
          ' Copied and added here to override the password field
   
          If Me.DataSource.IsCreated OrElse _
             Me.DataSource.Application0Specified Then
   
              Dim formattedValue As String = _
                  Me.DataSource.Format(PasswdTable.Application0)
              formattedValue = HttpUtility.HtmlEncode(formattedValue)
              Me.Application1.Text = formattedValue
          Else
   
              Me.Application1.Text = PasswdTable.Application0.Format
              (PasswdTable.Application0.DefaultValue)
          End If
   
          If Me.Application1.Text Is Nothing _
              OrElse Me.Application1.Text.Trim() = "" Then
              Me.Application1.Text = " "
          End If
   
          If Me.DataSource.IsCreated OrElse Me.DataSource.CommentSpecified Then
   
              Dim formattedValue As String = 
                  Me.DataSource.Format(PasswdTable.Comment)
              formattedValue = HttpUtility.HtmlEncode(formattedValue)
              Me.Comment.Text = formattedValue
          Else
   
              Me.Comment.Text = PasswdTable.Comment.Format
              (PasswdTable.Comment.DefaultValue)
          End If
   
          If Me.Comment.Text Is Nothing _
              OrElse Me.Comment.Text.Trim() = "" Then
              Me.Comment.Text = " "
          End If
   
          If Me.DataSource.IsCreated OrElse Me.DataSource.LastChangeSpecified Then
   
              Dim formattedValue As String = Me.DataSource.Format(
                  PasswdTable.LastChange)
              formattedValue = HttpUtility.HtmlEncode(formattedValue)
              Me.LastChange.Text = formattedValue
          Else
   
              Me.LastChange.Text = PasswdTable.LastChange.Format
              (PasswdTable.LastChange.DefaultValue)
          End If
   
          If Me.LastChange.Text Is Nothing _
              OrElse Me.LastChange.Text.Trim() = "" Then
              Me.LastChange.Text = " "
          End If
   
          If Me.DataSource.IsCreated OrElse Me.DataSource.PasswordSpecified Then
   
              Dim formattedValue As String = 
                  Me.DataSource.Format(PasswdTable.Password)
              'add decryption code here to change the formatted value
              Dim oKryptonite As New ryptonite.SymmCrypto
              (Kryptonite.SymmCrypto.Providers.TripleDES)
              formattedValue= oKryptonite.Decrypt(formattedValue)
              formattedValue = HttpUtility.HtmlEncode(formattedValue)
   
   
              'end additions
              Me.Password.Text = formattedValue
          Else
   
              Me.Password.Text = PasswdTable.Password.Format
              (PasswdTable.Password.DefaultValue)
          End If
   
          If Me.Password.Text Is Nothing _
              OrElse Me.Password.Text.Trim() = "" Then
              Me.Password.Text = " "
          End If
   
          If Me.DataSource.IsCreated OrElse Me.DataSource.UserId0Specified Then
   
              Dim formattedValue As String = _
                  Me.DataSource.Format(PasswdTable.UserId0)
              formattedValue = HttpUtility.HtmlEncode(formattedValue)
              Me.UserId1.Text = formattedValue
          Else
   
              Me.UserId1.Text = _
                 PasswdTable.UserId0.Format(PasswdTable.UserId0.DefaultValue)
          End If
   
          If Me.UserId1.Text Is Nothing _
              OrElse Me.UserId1.Text.Trim() = "" Then
              Me.UserId1.Text = " "
          End If
   
          Me.IsNewRecord = True
          If Me.DataSource.IsCreated Then
              Me.IsNewRecord = False
   
              Me.RecordUniqueId = Me.DataSource.GetID.ToXmlString()
          End If
   
              ' Load data for each record and table UI control.
              ' Ordering is important because child controls get
              ' their parent ids from their parent UI controls.
   
      End Sub

• 步骤 8：构建并运行应用程序。

  备注：通常，如果将代码添加内容放置在每个 Controls.vb 文件的相应代码自定义部分，则该项目将在添加或保存编辑后的记录时提供密码字段的加密。编辑页面也使用 Show 函数，因此在编辑覆盖中需要添加到 Show 函数的代码。