(untagged)

Encrypting and Decrypting Data to and from a Database

Iron Speed Designer

25 Apr 2008

Automatically decrypt data when retrieving it from a database.

This article is in the Product Showcase section for our sponsors at CodeProject. These articles are intended to provide you with information on products and services that we consider useful and of value to developers.

Introduction

You can easily encrypt data before storing it in a database and automatically decrypt it when retrieving, allowing it to be viewed in plain text when needed. This is particularly useful when storing passwords in the database, where for security reasons, that data should not be readable from the back-end database.

In this scenario, we build upon two earlier articles. The first is by Anh Trinh, published on January 4, 2007: Encrypting Passwords Before Saving to the Database. The second is by Jim Murphy, posted in the Iron Speed Designer Technical Forum on November 25, 2003: Password Database Encryption.

Both methods have specific uses. Mr. Trinh's article deals with encryption using a simple hash function, but has no method of reversing the function to view the password. This might be desirable in some instances, such as when the data has no requirement for future viewing. Mr. Murphy's article is more practical for data viewing.

This article blends the two techniques and provides a simple explanation for those of us new to Iron Speed Designer and Visual Basic .NET.

In our scenario, we look at how to encrypt a password before saving it to the database and provide a method of decrypting it for viewing or editing by authorized personnel. The example here could be extended for other fields in the database that require encryption.

The database structure for the password table can be created using the following SQL script, or by creating your own:

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

Step 1: Create an Iron Speed Designer application based on the above password table.
Step 2: Using Mr. Murphy's article as a guide, download and build the Kryptonite DLL from here in Visual Studio .NET 2003 as a separate project.
Step 3: Copy the new DLL to your application's bin folder.
```
...\<App Folder>\Bin
```
Step 4: Open the Iron Speed Designer encryption project in Visual Studio .NET 2003. Add a reference to the Kryptonite DLL just created in Step 1. Add a reference to .NET -> System.Security. Save the project, but do not build it!

Step 5: Reopen the encryption project in Iron Speed Designer. Add the following to the bottom of Public Class passwdRecordControl, located in:

.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

Step 6: Add the following code to the bottom of Public Class passwdRecordControl, located in:

.NET Framework 1.1:

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

.NET Framework 2.0 / 3.0:

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

This code retrieves the password from the database, decrypts it, and saves it back to the database in an encrypted form when edited.

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

Step 7: Add the following code to the ShowPasswordPage.Controls.vb file. This code provides for retrieving the password decrypted from the database for viewing by authorized users.

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

Step 8: Build and run the application.
Notes: In general, if the code additions are placed in the respective code customization sections of each Controls.vb file, the project will provide encryption of the password field when adding or saving an edited record. The Edit page also uses the Show functions, so the code added to the Show functions is necessary in the Edit overrides.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here