Encrypted .NET Serialization to a SQLite Database

This is a follow-on to my first article, ".NET Serialization to a SQLite Database." In that article, I pointed out that most non-enterprise applications need the ability to store data locally. Some need only simple storage, whereas others need a full SQL database capability for various needs. And, some need encryption as well. This article will show three different ways to serialize .NET objects to an encrypted BLOB column in a SQLite database file or as individual encrypted data fields.

Until I found SQLite, I had to use MS Access for my desktop application needs. On older computers, that meant installing MDAC (all of the ODBC/OLEDB drivers)—quite a large installer footprint. It was a happy day that I found out about SQLite, and equally important, SQLitePlus—a COM wrapper for SQLite enabling me to use it from higher level languages (for example, VB and .NET). In case you don't know anything about SQLite, it is a complete SQL database in a single file. And, it is very fast and efficient. But, it is all written in C and therefore not suitable to use from .NET. Therefore, you will use the SQLitePlus COM wrapper from EzTools Software (www.eztools-software.com).

First, a bit of background on SQLitePlus. It consists of two DLLs—the COM DLL, which provides the COM classes you use in your programs. They are the SqliteDb, Dataset, Columns, and Column classes. By using these COM class objects, you can create new database files, execute SQL commands, and get back result datasets. The other DLL is a "version DLL" that is a plain DLL (not COM) that encapsulates the SQLite database engine C code. The reason it is split into two DLLs is so that newer versions of the version DLL can be swapped-in without changing the COM DLL. For example, when a new version of the SQLite engine is released, you only need to update the version DLL rather than both. Also, you can make custom builds of the version DLL; for example, to add your own User Defined Functions. It's quite a nifty system.

These DLLs are included in the source code Zip file, but you must register the COM DLL to run the projects (using RegSvr32.exe). A .cmd file is included that does this for you (click on runme.cmd).

Note: You also can download the SQLitePlus Database Manager from the EzTools web site. It is very useful for visualizing your data as you experiment with the SQLitePlus COM DLL in your code. Here is the download link: www.eztools-software.com/downloads/sqliteplus.exe

After registering the SQLitePlus COM DLL, you are ready to start by adding a reference to the SQLitePlus COM DLL in your project. In the "Add Reference" dialog, scroll down and find "EzTools SqlitePus 3.5 COM DLL", as shown here:

Now, in MainFrm.cs, add this "using" statement near the top of the file:

using SQLITEPLUS35;

Okay, now you're ready to add a SqliteDb member variable that you can use for your serialization functions. Add this line near the top of the class definition:

SqliteDb mDB = new SqliteDb();

Now, scroll down to the form's Load event handler. Here, you will initialize SqliteDb, open the database, and set the cipher key.

String sErr;
ErrorCodeEnum eErr;

mDB.Init( "sqlt3317.dll", "demo", null );
mDB.Open( Application.StartupPath + @"\sqlite.db", -1, false );

// set cipher key after Init call
// cipher key up to 32 bytes (256 bytes);
mDB.CipherKey = "12345678";

Notice that I have specified the version DLL name in the Init method call. If the version DLL is not in the same folder as the COM DLL, you also must specify its path. The second parameter is for the license key. Just use "demo" (you can use SQLitePlus with no license, but with a 10-row limit for dataset results).

The next line is the Open method call. This actually creates or opens the database file. The first time you run the project, the database file (sqlite.db) will be created. Thereafter, it will be opened.

Next, you set the cipher key. This is necessary to use the SQLitePlus ENCRYPT and DECRYPT SQL extenstions.

Next, you create your database tables. I have created a helper function, TableExists, that checks the sqlite_master table for the table name. If it;s not there, you create the table.

Table Products1 is for BLOB serialization, so it has only the ID and BLOB Data column. Here is the table definition for Products1:

CREATE TABLE Products1(ID INTEGER PRIMARY KEY NOT NULL, Data BLOB)

Products2 is for field serialization, so it has individual fields for each data member. Here is the table definition:

CREATE TABLE Products2(ID INTEGER PRIMARY KEY NOT NULL,
                       Name BLOB_TEXT NOT NULL,
                       QtyPerUnit BLOB_TEXT NOT NULL,
                       Price BLOB_FLOAT NOT NULL,
                       LastOrderDate BLOB_DATE NOT NULL

This sort of table definition will look familiar to most programmers. Its a one-to-one mapping of the Product object fields to the table columns (see below for the class definition). However, there is a major difference: Notice the column data types are prefixed with BLOB. The reason is that SQLitePlus has a well-defined system for handling BLOB fields. It needs to know whether a field type is BLOB or not, and this is how it knows. It is also used for automatic decryption, which is another feature of SQLitePlus (you won't use it in this article).

Now that you have your database connection and the tables created, create your example class named Product. You must give it the Serializable Attribute so that you can use .NET Serialization. You then declare the data members. You'll add the serialization methods as you go. Here is the class definition with only the data members declared:

[System.Serializable]
public class Product
{
   public int mnID;
   public string msName,
                 msQtyPerUnit;
   public float mPrice;
   public DateTime mLastOrderDate;
}

I have deliberately declared members that use a few different data types to demonstrate how to use them with SQLitePlus. The members are public to simplify access, and consequently, code readability.

I create two Product objects in the code for your serialization purposes in CreateProducts.

void CreateProducts()
{
   mProduct1 = new Product();
   mProduct1.mnID = PROD_ID_1;
   mProduct1.mPrice = 97F;
   mProduct1.msName = "Mishi Kobe Niku";
   mProduct1.msQtyPerUnit = "18 - 500 g pkgs.";
   mProduct1.mLastOrderDate = DateTime.Today.AddDays( 90 );

   mProduct2 = new Product();
   mProduct2.mnID = PROD_ID_2;
   mProduct2.mPrice = 18F;
   mProduct2.msName = "Queso Cabrales";
   mProduct2.msQtyPerUnit = "1 kg pkg.";
   mProduct1.mLastOrderDate = DateTime.Today.AddDays( 120 );
}

Now, you are ready to serialize your data.

Serialize Whole Objects Using .NET Serialization

Your first serialization method is to use .NET serialization to persist whole objects to the database. Your table has only the ID and Data columns, the latter of which is defined as BLOB. You will now see how to save entire objects to this single column. Here is the code.

private void lnkSerializeWholeObject_LinkClicked( object sender,
   LinkLabelLinkClickedEventArgs e )
{
   // ensure we have an object
   CreateProducts();

   byte[] arData;

   using(MemoryStream stream = new MemoryStream())
   {
      BinaryFormatter formatter = new BinaryFormatter();
      formatter.Serialize( stream, mProduct1 );
      arData = stream.ToArray();
      stream.Close();
   }

   String sErr, sSql;
   ErrorCodeEnum eErr;
   SQLITEPLUS35.Dataset ds;
   object oKey = null;

   SQLITEPLUS35.VariantCollection paramsCol =
      new SQLITEPLUS35.VariantCollection();
   paramsCol.Add( arData, oKey );

   ds = mDB.Exec( "SELECT ID FROM Products1 WHERE ID=" + PROD_ID_1,
                  null, out eErr, out sErr );

   if(ds.eof)
      sSql = String.Format( "INSERT INTO Products1 (ID, Data)
                            VALUES({0}, ENCRYPT(?))", PROD_ID_1);
   else
      sSql = String.Format( "UPDATE Products1 SET Data=ENCRYPT(?)
                             WHERE ID={0}", PROD_ID_1);

   mDB.Exec( sSql, paramsCol, out eErr, out sErr );
}

The first thing you need is a MemoryStream and BinaryFormatter object. The latter is used to serialize your Product object into the former by using built-in .NET serialization. Then, you get the stream's contents as a byte array with the call arData = stream.ToArray().>

So far, so good. You now are ready to save the byte array as an encrypted BLOB into the SQLite Products1 table. To do this, you need a SQLitePlus VariantCollection object, which is used to hold BLOB parameters. You call the Add method, giving a null key because you don't care about ordering the collection in any way. Now, it's not obvious, but what happens here is the byte array is converted to a COM SAFEARRAY by the COM InterOp runtime, which is exactly what you need to happen to insert or update a BLOB field.

Then, you must determine whether this is a new product or if it already exists in the database, so that you can build the appropriate SQL statement. To do this, you attempt to retrieve just the ID column for the Product ID of interest. You then check the Dataset to see if it's empty (eof). If it is, you build an INSERT statement. If not, you build an UPDATE statement.

Now, here is the cool part. Notice here the ENCRYPT(?) in the SQL statements. This is the parameter substitution mechanism for SQLite and where you accomplish the encryption. The BLOB data will be encrypted and substituted deep within the SQLite engine as the Data column value. SQLitePlus provides you the high-level objects that give you the ability to encrypt, store, retrieve, and decrypt your binary data.

And of course, the last thing to do is execute the SQL statement with the SqliteDb.Exec method. You will note that the size of the byte array is 255 bytes.

Okay. You have successfully created a SQLite database and table, and serialized a .NET class object—with encryption—into a BLOB field. Now, look at how to re-create the same class object using .NET deserialization.

Encrypted .NET Serialization to a SQLite Database

Deserialize Whole Objects Using .NET Deserialization

Right, so now you want to re-create your class objects from the saved BLOB data. It's going to be very easy, as you see in the following code.

private void lnkDeserializeWholeObject_LinkClicked( object sender,
   LinkLabelLinkClickedEventArgs e )
{
   String sErr;
   ErrorCodeEnum eErr;
   SQLITEPLUS35.Dataset ds;

   ds = mDB.Exec( "SELECT ID, DECRYPT(Data,'B') AS Data
      FROM Products1 WHERE ID=" + PROD_ID_1, null, out eErr,
      out sErr );

   if(ds.eof)
   {
      MessageBox.Show( "Product not found!" );
      return;
   }

   object val = ds.Columns["Data"].Value;

   // check for NULL database value
   if(Convert.IsDBNull( val ))
      return;

   byte[] arData = (byte[]) val;

   using(MemoryStream stream = new MemoryStream())
   {
      stream.Write( arData, 0, arData.Length );
      stream.Seek( 0, SeekOrigin.Begin );

      BinaryFormatter formatter = new BinaryFormatter();
      mProduct1 = (Product) formatter.Deserialize( stream );
      // we could just assign PROD_ID_1, but I'm showing how to get
      // column data from a Dataset
      mProduct1.mnID = (int) ds.Columns[0].Value;
   }
}

First, you must select your Product row from the database into a Dataset object. Notice DECRYPT(Data,'B') in the SELECT statement. This is the key to decrypting the BLOB data on-the-fly as you get it from the database. The first parameter is the name of the column, and the second is an optional parameter that tells SQLitePlus the return data type (required for the lower-level SQLite code). In this case, B is for BLOB. Pretty cool, huh? Notice also that we use the AS Data clause. Use this technique if you need to have the column name returned in the Dataset, otherwise the column name would be returned exactly as in the SQL statement—DECRYPT(Data,'B').

Then, you check for EOF, and if no row was found, you display a message and return. If you have a row, you access the Data column using the Dataset's Columns collection. You assign the value to an object first, so you can use the Convert.IsDBNull method to check for a NULL database value. If it's not null, you then cast the value to a byte array and write it to a MemoryStream. Then, you create a BinaryFormatter object and call its Deserialize method, casting the return value to a Product type, thus giving you a newly deserialized Product object. Lastly, you assign the ID from the Dataset to show how to access other column data types. Notice I indexed by ordinal position rather than by column name (Columns[0] vs. Columns["ID"]). You can use either method.

Now, you have seen how to save whole objects directly into a SQLite database file, with encryption, with very little programming effort. Now, see how to use a custom BLOB serialization method, apart from any .NET mechanism.

Custom BLOB Serialization

This is our ysecond method of serializing object data to a SQLite database. I will show how to serialize the same object data, but in a more space-efficient way and without having to derive from and implement the ISerializable interface. Here is the code:

private void lnkCustomSerialization_LinkClicked( object sender,
   LinkLabelLinkClickedEventArgs e )
{
   // ensure we have an object
   CreateProducts();

   byte[] arData;

   using( MemoryStream stream = new MemoryStream() )
   {
     using( BinaryWriter bWriter = new BinaryWriter( stream ) )
      {
         mProduct2.Serialize( bWriter );
         arData = stream.ToArray();
      }
   }

   String sErr, sSql;
   ErrorCodeEnum eErr;
   SQLITEPLUS35.Dataset ds;
   object oKey = null;

   SQLITEPLUS35.VariantCollection paramsCol =
      new SQLITEPLUS35.VariantCollection();
   paramsCol.Add( arData, oKey );

   ds = mDB.Exec( "SELECT ID FROM Products1 WHERE ID=" + PROD_ID_2,
                  null, out eErr, out sErr );

   if(ds.eof)
      sSql = String.Format( "INSERT INTO Products1 (ID, Data)
                             VALUES({0},ENCRYPT(?))", PROD_ID_2 );
   else
      sSql = String.Format( "UPDATE Products1 SET Data=ENCRYPT(?)
                             WHERE ID={0}", PROD_ID_2 );

   mDB.Exec( sSql, paramsCol, out eErr, out sErr );

   arData = null;
}

The code is almost identical to the whole object serialization example. You do the same ENCRYPT(?) bit, so what have you done differently? Notice that you have called Product.Serialize instead of BinaryFormatter.Serialize. Now, define Product.Serialize:

public void Serialize( BinaryWriter bWriter )
{
   short nVer = 1;    // each object should have its own version

   bWriter.Write( nVer );
   bWriter.Write( msName );
   bWriter.Write( msQtyPerUnit );
   bWriter.Write( (double) mPrice );
   bWriter.Write( mLastOrderDate.ToOADate() );
}

Here, you see that you explicitly write each member value that you want to serialize. You can also write a version number first, which makes it possible to add new fields later. You simply check the version number when deserializing to see whether or not you should deserialize the new fields. Notice you use DateTime.ToAODate to convert mLastOrderDate to OLE date type.

Encrypted .NET Serialization to a SQLite Database

Custom Deserialization

Again, the code is very similar to the .NET deserialization method. The only difference is the Product.Deserialize method call. Look at the code:

private void lnkCustomDeserialization_LinkClicked( object sender,
   LinkLabelLinkClickedEventArgs e )
{
   if(mProduct2 == null)
      mProduct2 = new Product();
   else
      mProduct2.Reset();

   String sErr;
   ErrorCodeEnum eErr;
   Dataset ds = mDB.Exec( "SELECT ID, DECRYPT(Data,'B')
                           FROM Products1
                           WHERE ID=" + PROD_ID_2, null, out eErr,
                           out sErr );
   if(ds.eof)
   {
      MessageBox.Show( "Product not found!" );
      return;
   }
   object val = ds.Columns[1].Value;

   if(Convert.IsDBNull( val ))
      return;

   byte[] arData = (byte[]) val;

   using(MemoryStream stream = new MemoryStream())
   {
      stream.Write( arData, 0, arData.Length );
      stream.Seek( 0, SeekOrigin.Begin );

      using(BinaryReader bReader = new BinaryReader( stream ))
      {
         mProduct2.Deserialize( bReader );
         mProduct2.mnID = (int) ds.Columns[0].Value;
      }
   }
}

Notice again you used DECRYPT(Data,'B') to decrypt the data on-the-fly. Now, here is the Product.Deserialize method definition:

public void Deserialize( BinaryReader bReader )
{
   short nVer;

   nVer = bReader.ReadInt16();
   msName = bReader.ReadString();
   msQtyPerUnit = bReader.ReadString();
   mPrice = (float) bReader.ReadDouble();
   double date = bReader.ReadDouble();
   mLastOrderDate = DateTime.FromOADate( date );
}

Notice how straightforward the code is. And, see how you must convert your LastOrderDate field using DateTime.FromOADate, but after you have retrieved the date value as a double. The version number can be used later when you must add new fields and know whether or not you should deserialize them.

Custom deserialization is not quite as simple as .NET object serialization, but it is much more flexible. You have to define your own Serialize and Deserialize methods, but you have fine control over what goes in and comes out. And, there are some instances where it is quite preferable. For example, when using Reflection to deserialize objects from a dynamically loaded assembly, you will get a security exception, thus preventing you from deserializing your objects (you would need to write a special handler to overcome this issue). Additionally, the size of the binary data is only 35 bytes vs. the 255 bytes for the .NET serialization method (no doubt the extra 220 bytes are .NET header info).

Now, it's on to your final serialization method.

Save to Table Columns

You have seen how to save entire objects into encrypted BLOB fields. Now, I will show how to use traditional table columns using SQLitePlus, but with SQLitePlus' on-the-fly encryption. Here is the code for saving an object:

// ensure we have an object
CreateProducts(); String sErr, sSql; ErrorCodeEnum eErr; SQLITEPLUS35.Dataset ds; sSql = String.Format( "SELECT ID FROM Products2 WHERE ID={0}", PROD_ID_1 ); ds = mDB.Exec( sSql, null, out eErr, out sErr ); if(ds.eof) sSql = String.Format( "INSERT INTO Products2 (ID, Name, QtyPerUnit, Price, LastOrderDate) VALUES({0}, ENCRYPT('{1}'), ENCRYPT('{2}'), ENCRYPT({3}), ENCRYPT({4}))", PROD_ID_1, mProduct1.msName, mProduct1.msQtyPerUnit, mProduct1.mPrice, mProduct1.mLastOrderDate.ToOADate() ); else sSql = String.Format( "UPDATE Products2 SET Name=ENCRYPT('{0}'), QtyPerUnit=ENCRYPT('{1}'), Price=ENCRYPT({2}), LastOrderDate=ENCRYPT({3}) WHERE ID={4}", mProduct1.msName, mProduct1.msQtyPerUnit, mProduct1.mPrice, mProduct1.mLastOrderDate.ToOADate(), PROD_ID_1 ); mDB.Exec( sSql, null, out eErr, out sErr );

You can see that you are simply formatting a SQL string by using the object data members directly, and also you are wrapping them with ENCRYPT(). Notice that for the date field mLastOrderDate, you use the DateTime.ToOADate method to convert it to OLE date format, which is really the double data type.

Load Columns from the Table

See how to load your object fields from the database. Here is the last code. Notice you just wrap each field name with DECRYPT(). But, notice how you have an extra parameter—'F'—for Price and LastOrderDate. This tells SQLitePlus that it is a FLOAT (double) field. You must specify the field type if it is not the default Text data type.

private void lnkLoadFields_LinkClicked( object sender,
   LinkLabelLinkClickedEventArgs e )
{
   if(mProduct1 == null)
      mProduct1 = new Product();
   else
      mProduct1.Reset();

   String sErr, sSql;
   ErrorCodeEnum eErr;

   sSql = "SELECT DECRYPT(Name), DECRYPT(QtyPerUnit),
      DECRYPT(Price,'F'), DECRYPT(LastOrderDate,'F')
      FROM Products2 WHERE ID=" + PROD_ID_1;

   Dataset ds = mDB.Exec( sSql, null, out eErr, out sErr );
   if(ds.eof)
   {
      MessageBox.Show( "Product not found!" );
      return;
   }
   mProduct1.mnID = (int) ds.Columns["ID"].Value;
   mProduct1.msName = (string) ds.Columns[1].Value;
   mProduct1.msQtyPerUnit = (string) ds.Columns[2].Value;
   mProduct1.mPrice = Convert.ToSingle( ds.Columns[3].Value );
   double date = (double) ds.Columns[4].Value;
   mProduct1.mLastOrderDate = DateTime.FromOADate( date );
}

Notice how it's just a simple matter of casting the column values to their field data types.

Conclusion

I have demonstrated how easy and straightforward it is to use SQLitePlus for encrypted .NET object serialization to SQLite database files. You have learned three different ways to go about it, taking advantage of COM InterOp to easily and elegantly use the SQLitePlus COM DLL in a C# program and to encrypt and convert between data types seamlessly. I can't tell you how much I love using this system to get my coding work done super-fast with such cool database tools as SQLite and SQLitePlus.



About the Author

Brett Goodman

I've been a Windows software developer since 1988. I've written many programmer's tools and a few end user products.

Downloads

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: December 11, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT Market pressures to move more quickly and develop innovative applications are forcing organizations to rethink how they develop and release applications. The combination of public clouds and physical back-end infrastructures are a means to get applications out faster. However, these hybrid solutions complicate DevOps adoption, with application delivery pipelines that span across complex hybrid cloud and non-cloud environments. Check out this …

  • On-demand Event Event Date: October 29, 2014 It's well understood how critical version control is for code. However, its importance to DevOps isn't always recognized. The 2014 DevOps Survey of Practice shows that one of the key predictors of DevOps success is putting all production environment artifacts into version control. In this webcast, Gene Kim discusses these survey findings and shares woeful tales of artifact management gone wrong! Gene also shares examples of how high-performing DevOps …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds