Saturday 3 August 2019

Encrypt data and store in Database in ASP.Net using C# and VB.Net

In this article I will explain with an example, how to encrypt data and store in Database in ASP.Net using C# and VB.Net.
This article will illustrate how to encrypt data before storing in Database using AES Symmetric key (Same key) algorithm and also decrypt it back when fetching the data from Database in ASP.Net using C# and VB.Net.
 
 
Database
I have created a new database named UsersDB which consist of one table named Users with the following schema.
The Password Column has been defined as NVARCHAR type so that it can easily store encrypted password containing special characters.
Encrypt data and store in Database in ASP.Net using C# and VB.Net
Note: The attached sample contains the SQL Script file to create the database and the table.
 
 
HTML Markup
The HTML Markup consists of two TextBoxes, a Button and a GridView control.
The GridView has been assigned OnRowDataBound event handler.
<table border="0" cellpadding="0" cellspacing="0">
    <tr>
        <td>
            Username:
        </td>
        <td>
            <asp:TextBox ID="txtUsername" runat="server" Text="" />
        </td>
    </tr>
    <tr>
        <td>
            Password:
        </td>
        <td>
            <asp:TextBox ID="txtPassword" runat="server" TextMode="Password" />
        </td>
    </tr>
    <tr>
        <td>
        </td>
        <td>
            <asp:Button ID="btnSubmit" OnClick="Submit" Text="Submit" runat="server" />
        </td>
    </tr>
</table>
<hr />
<asp:GridView ID="gvUsers" runat="server" AutoGenerateColumns="false" HeaderStyle-BackColor="#3AC0F2"
    HeaderStyle-ForeColor="White" RowStyle-BackColor="#A1DCF2" OnRowDataBound = "OnRowDataBound">
    <Columns>
        <asp:BoundField DataField="Username" HeaderText="Username" />
        <asp:BoundField DataField="Password" HeaderText="Encrypted Password" />
        <asp:BoundField DataField="Password" HeaderText="Desrypted Password" />
    </Columns>
</asp:GridView>
 
 
Namespaces
You will need to import the following namespaces.
C#
using System.IO;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Security.Cryptography;
 
VB.Net
Imports System.IO
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Security.Cryptography
 
 
AES Algorithm Encryption and Decryption functions
The following functions Encryption and Decryption will be used to perform encryption and decryption.
Note: The following functions have been explained in the article AES Encryption Decryption (Cryptography) Tutorial with example in ASP.Net using C# and VB.Net.
 
C#
private string Encrypt(string clearText)
{
    string EncryptionKey = "MAKV2SPBNI99212";
    byte[] clearBytes = Encoding.Unicode.GetBytes(clearText);
    using (Aes encryptor = Aes.Create())
    {
        Rfc2898DeriveBytes pdb = new Rfc2898DeriveBytes(EncryptionKey, new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
        encryptor.Key = pdb.GetBytes(32);
        encryptor.IV = pdb.GetBytes(16);
        using (MemoryStream ms = new MemoryStream())
        {
            using (CryptoStream cs = new CryptoStream(ms, encryptor.CreateEncryptor(), CryptoStreamMode.Write))
            {
                cs.Write(clearBytes, 0, clearBytes.Length);
                cs.Close();
            }
            clearText = Convert.ToBase64String(ms.ToArray());
        }
    }
    return clearText;
}
 
private string Decrypt(string cipherText)
{
    string EncryptionKey = "MAKV2SPBNI99212";
    byte[] cipherBytes = Convert.FromBase64String(cipherText);
    using (Aes encryptor = Aes.Create())
    {
        Rfc2898DeriveBytes pdb = new Rfc2898DeriveBytes(EncryptionKey, new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
        encryptor.Key = pdb.GetBytes(32);
        encryptor.IV = pdb.GetBytes(16);
        using (MemoryStream ms = new MemoryStream())
        {
            using (CryptoStream cs = new CryptoStream(ms, encryptor.CreateDecryptor(), CryptoStreamMode.Write))
            {
                cs.Write(cipherBytes, 0, cipherBytes.Length);
                cs.Close();
            }
            cipherText = Encoding.Unicode.GetString(ms.ToArray());
        }
    }
    return cipherText;
}
 
VB.Net
Private Function Encrypt(clearText As StringAs String
    Dim EncryptionKey As String = "MAKV2SPBNI99212"
    Dim clearBytes As Byte() = Encoding.Unicode.GetBytes(clearText)
    Using encryptor As Aes = Aes.Create()
        Dim pdb As New Rfc2898DeriveBytes(EncryptionKey, New Byte() {&H49, &H76, &H61, &H6E, &H20, &H4D, _
         &H65, &H64, &H76, &H65, &H64, &H65, _
         &H76})
        encryptor.Key = pdb.GetBytes(32)
        encryptor.IV = pdb.GetBytes(16)
        Using ms As New MemoryStream()
            Using cs As New CryptoStream(ms, encryptor.CreateEncryptor(), CryptoStreamMode.Write)
                cs.Write(clearBytes, 0, clearBytes.Length)
                cs.Close()
            End Using
            clearText = Convert.ToBase64String(ms.ToArray())
        End Using
    End Using
    Return clearText
End Function
 
Private Function Decrypt(cipherText As StringAs String
    Dim EncryptionKey As String = "MAKV2SPBNI99212"
    Dim cipherBytes As Byte() = Convert.FromBase64String(cipherText)
    Using encryptor As Aes = Aes.Create()
        Dim pdb As New Rfc2898DeriveBytes(EncryptionKey, New Byte() {&H49, &H76, &H61, &H6E, &H20, &H4D, _
         &H65, &H64, &H76, &H65, &H64, &H65, _
         &H76})
        encryptor.Key = pdb.GetBytes(32)
        encryptor.IV = pdb.GetBytes(16)
        Using ms As New MemoryStream()
            Using cs As New CryptoStream(ms, encryptor.CreateDecryptor(), CryptoStreamMode.Write)
                cs.Write(cipherBytes, 0, cipherBytes.Length)
                cs.Close()
            End Using
            cipherText = Encoding.Unicode.GetString(ms.ToArray())
        End Using
    End Using
    Return cipherText
End Function
 
 
Encrypting and storing data in Database Table
When the Submit Button is clicked, the Username and Password will be inserted into the SQL Server database table.
The Username is inserted directly but the Password is first encrypted using the Encrypt function (discussed earlier) and then it is inserted.
C#
protected void Submit(object sender, EventArgs e)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("INSERT INTO Users VALUES(@Username, @Password)"))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@Username", txtUsername.Text.Trim());
            cmd.Parameters.AddWithValue("@Password", Encrypt(txtPassword.Text.Trim()));
            cmd.Connection = con;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
    Response.Redirect(Request.Url.AbsoluteUri);
}
 
VB.Net
Protected Sub Submit(sender As Object, e As EventArgs)
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As New SqlConnection(constr)
        Using cmd As New SqlCommand("INSERT INTO Users VALUES(@Username, @Password)")
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@Username", txtUsername.Text.Trim())
            cmd.Parameters.AddWithValue("@Password", Encrypt(txtPassword.Text.Trim()))
            cmd.Connection = con
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using
    Response.Redirect(Request.Url.AbsoluteUri)
End Sub
 
 
Decrypting the data stored in Database Table
Inside the Page Load event, the GridView control is populated with the records from the Users table.
Then inside the OnRowDataBound event handler, the encrypted Password is fetched and decrypted using the Decrypt function (discussed earlier).
C#
protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM Users"))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    DataTable dt = new DataTable();
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = con;
                    sda.SelectCommand = cmd;
                    sda.Fill(dt);
                    gvUsers.DataSource = dt;
                    gvUsers.DataBind();
                }
            }
        }
    }
}
 
protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        e.Row.Cells[2].Text = Decrypt(e.Row.Cells[2].Text);
    }
}
 
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgsHandles Me.Load
    If Not Me.IsPostBack Then
        Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using con As New SqlConnection(constr)
            Using cmd As New SqlCommand("SELECT * FROM Users")
                Using sda As New SqlDataAdapter()
                    Dim dt As New DataTable()
                    cmd.CommandType = CommandType.Text
                    cmd.Connection = con
                    sda.SelectCommand = cmd
                    sda.Fill(dt)
                    gvUsers.DataSource = dt
                    gvUsers.DataBind()
                End Using
            End Using
        End Using
    End If
End Sub
 
Protected Sub OnRowDataBound(sender As Object, e As GridViewRowEventArgs)
    If e.Row.RowType = DataControlRowType.DataRow Then
        e.Row.Cells(2).Text = Decrypt(e.Row.Cells(2).Text)
    End If
End Sub
 
 
Screenshots
The encrypted Passwords in SQL Server Database Table
Encrypt data and store in Database in ASP.Net using C# and VB.Net
 
The GridView displaying both encrypted and decrypted Passwords
Encrypt data and store in Database in ASP.Net using C# and VB.Net

No comments:

Post a Comment