Custom Data Adapter for database's Data Security (Advance Concept)


Custom Data Adapter to show how to achieve data security for the data base tables using a previously written PBKDF2 key from the registry

This is an updated version of my earlier post:


http://dotnetspider.com/resources/40730-Custom-Data-Adapter-for-Data-Integrity.aspx


Where I showed how you can do basic encryption and decryption (text manipulation) for a table in database by creating a Custom Adapter.

Let me tell you the new scenario:

Table contains 100 or "n" rows, where "n" is a finite number

I want to encrypt, using a registry key value stored in DPAPI format, my tables data in such a way :
For eg: One of my row is:

"menu_id" "menu_name" "Starters" "Deserts"
47 Burger Chips icecream


Should be converted into:

"menu_id" "menu_name" "Starters"
47 5gLDGUGu+N+0k9f4yqF4+VTQn04Jwne9IQPTCNZG1dk= VRaBRulC206ioPskGq2kw7wNVOhhlcKshMTIPOA5/iY=


And on decryption it will be converted, into earlier format
And should affect multiple rows of the table.

The concept is almost the same as it was in my other post:
http://dotnetspider.com/resources/40730-Custom-Data-Adapter-for-Data-Integrity.aspx

Create a Custom Data Adapter having its own fill(decryption) and update(encryption) method

So lets begin
And again here I will be using MySql Connectors for .Net

For information on how to connect to MySql database using Visual Studio go here:-

MySQL Connector for .Net:-
http://dotnetspider.com/resources/40729-MySql-Connector-For-net.aspx


Create a Custom Data Adapter


The same that I have used earlier
Create a new constructor for custom dataadapter class

#region New DataAdapter Constructor
///NEW CONSTRUCTOR ///
///takes parameter as the name of the table and the connection ///
///build a select command based on the table name ///
///Store the table name in a private string member called "data_table_name" ///

private string data_table_name;//to store the data table name

public CustomDataAdapter(string My_table_name, MySqlConnection My_connection): this()
{
data_table_name = My_table_name;
string my_command_text = "SELECT * FROM " + My_connection.Database.ToString() + "." + data_table_name;
SelectCommand = new MySqlCommand(my_command_text, My_connection);
}

#endregion

The new constructor gets the name of the table and a connection

Read from Registry


How to write to a registry in a DPAPI format ?

http://dotnetspider.com/resources/40748-Networking-Threading-DPAPI-Sample-App.aspx


I am reading the same key that had been created and written to the registry, that I showed in the article mentioned.

So the read from registry method:-

#region Read from Registry

///
/// Read the Required Key, used for encryption/decryption of fields, from the registry
/// The key was written to the registry by the " P2P App"
///

private static string Read_from_registry()
{
byte[] encryptedUser = Microsoft.Win32.Registry.GetValue(@"HKEY_LOCAL_MACHINE\SOFTWARE\Mydba33",
"Deployed Key", null) as byte[];

//Since Stored as DPAPI
string decrtpted_registry = UTF8Encoding.UTF8.GetString(ProtectedData.Unprotect(
encryptedUser,
null,
DataProtectionScope.LocalMachine));

return decrtpted_registry;
}

#endregion


Its reading this key:-
readreg

The read from registry method returns the key in string format.

Encryption of data base / Update the data base


Let me show you the example first

This is the normal look of the MySQL Database table and when the datagrid is filled with the table.
normalsql

normalform

And here what happens after the Update button is clicked.
updateform

update sql

The code behind of the Update Method in custom adapter class:-

#region Update Method

public new int Update(DataTable dataTable)
{
// Create a Rijandael
RijndaelManaged aes2 = new RijndaelManaged();

//Read the registry Key
string rfc2898 = Read_from_registry();

//Initialiase the Rijandael objects
aes2.KeySize = aes2.LegalKeySizes[0].MaxSize;
aes2.BlockSize = aes2.LegalBlockSizes[0].MaxSize;
aes2.Key = Convert.FromBase64String(rfc2898);
aes2.IV = Convert.FromBase64String(rfc2898);

//create an encryptor
ICryptoTransform encryptTransf = aes2.CreateEncryptor();

//encrypt it
foreach (DataRow row in dataTable.Rows)
{
if (row.RowState != DataRowState.Deleted)
{
row["menu_name"] = encrypt((string)row["menu_name"], encryptTransf);
row["starter"] = encrypt((string)row["starter"], encryptTransf);
row["mains"] = encrypt((string)row["mains"], encryptTransf);
row["dessert"] = encrypt((string)row["dessert"], encryptTransf);
}
}
return base.Update(dataTable);
}

#endregion


The code behind of the encrypt() method:

public string encrypt(string data, ICryptoTransform encryptTransf)
{
//read the data to be encrypted
byte[] utfdata = Encoding.UTF8.GetBytes(data);

// Output stream, can be also a FileStream
MemoryStream encryptStream = new MemoryStream();
CryptoStream encryptor = new CryptoStream(encryptStream, encryptTransf, CryptoStreamMode.Write);

encryptor.Write(utfdata, 0, utfdata.Length);
encryptor.FlushFinalBlock();
encryptor.Close();
// Showing our encrypted content
byte[] encryptBytes = encryptStream.ToArray();

return Convert.ToBase64String(encryptBytes);
}


Decryption of data base / fill the data


Let me show you the example

This is the normal look of the MySQL Database table and when the datagrid of form is filled with the table using Decrypt Button.
decryptform

update sql

The code behind of the Fill Method in custom adapter class and when the Decrypt Button is clicked:-

#region Fill

public new int Fill(DataTable dataTable)
{
dataTable.TableName = data_table_name;
RijndaelManaged aes = new RijndaelManaged();
string rfc2898 = Read_from_registry();

aes.BlockSize = aes.LegalBlockSizes[0].MaxSize;
aes.KeySize = aes.LegalKeySizes[0].MaxSize;
aes.Key = Convert.FromBase64String(rfc2898);
aes.IV = Convert.FromBase64String(rfc2898);

//Create a decryptor
ICryptoTransform decryptTrans = aes.CreateDecryptor(aes.Key, aes.IV);

foreach (DataRow row in dataTable.Rows)
{
row["menu_name"] = decrypt(row["menu_name"].ToString(), decryptTrans);
row["starter"] = decrypt(row["starter"].ToString(), decryptTrans);
row["mains"] = decrypt(row["mains"].ToString(), decryptTrans);
row["dessert"] = decrypt(row["dessert"].ToString(), decryptTrans);
}

base.Fill(dataTable);
return dataTable.Rows.Count;
}

#endregion


The code behind of the decrypt() method:

public string decrypt(string data1, ICryptoTransform decryptTrans)
{
//read the encrypted text
byte[] encryptBytes_x = Convert.FromBase64String(data1);

// Output stream, can be also a FileStream
MemoryStream decryptStream = new MemoryStream();
CryptoStream decryptor = new CryptoStream(decryptStream, decryptTrans, CryptoStreamMode.Write);

decryptor.Write(encryptBytes_x, 0, encryptBytes_x.Length);
decryptor.Flush();
decryptor.Close();

// Showing our decrypted content
byte[] decryptBytes = decryptStream.ToArray();

return (UTF8Encoding.UTF8.GetString(decryptBytes));
}



THE END


Please view my application attached for a better understanding.
In the application attached I have also made the use of MySql Connector for .net

Any doubts, give a response

Cheers
Paul

Reference: http://dotnetspider.com/resources/40730-Custom-Data-Adapter-for-Data-Integrity.aspx


Attachments

  • custom data adapter advance (40752-29923-customdataadapter_adv.rar)
  • Comments



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: