Encrypted .NET Serialization to SQLite Database

Then we must determine if this is a new product or if it already exists in the database, so that we can build the appropriate SQL statement. To do this, we attempt to retrieve just the ID column for the Product ID of interest. We then check the Dataset to see if its empty (eof). If it is, we build an INSERT statement. If not, we 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 we accomplish the encryption. The BLOB data will be encrypted and substituted deep within the SQLite engine as the Data column value. SQLitePlus provides us the high-level objects that give us the ability to encrypt, store, retrieve and decrypt our binary data.

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

OK. We have successfully created a SQLite database and table, and serialized a .NET class object - with encryption - into a BLOB field. Now lets look at how to recreate the same class object using .NET deserialization.

Deserialize Whole Objects using .NET Deserialization

Right, so now we want to re-create our class objects from the saved BLOB data. Its going to be very easy, as we see in the code below.

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 we must select our 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 we get it from the database. The first parameter is the name of the column, and the second is an optional parameter which 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 we check for EOF, and if no row was found we display a message and return. If we have a row, we access the Data column using the Dataset's Columns collection. We assign the value to an object first, so we can use the Convert.IsDBNull method to check for a NULL database value. If its not null, we then cast the value to a byte array and write it to a MemoryStream. Then we create a BinaryFormatter object and call its Deserialize method, casting the return value to a Product type, thus giving us a newly deserialized Product object. Lastly, we 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 we have seen how to save whole objects directly into a SQLite database file, with encryption, with very little programming effort. Now lets see how to use a custom BLOB serialization method, apart from any .NET mechanism.

Custom BLOB Serialization

This is our second method of serializing object data to SQLite database. We 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. We do the same ENCRYPT(?) bit, so what have we done different? Notice we have called Product.Serialize instead of BinaryFormatter.Serialize. Let's 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 we see that we explicitly write each member value that we want to serialize. We can also write a version number first, which makes it possible to add new fields later. We simply check the version number when deserializing to see if we should deserialize the new fields or not. Notice we use DateTime.ToAODate to convert mLastOrderDate to OLE date type.

Custom Deserialization

Again, the code is very similar to the .NET deserialization method. The only difference is the Product.Deserialize method call. Let's 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 we 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 straight-forward the code is. And see how we must convert our LastOrderDate field using DateTime.FromOADate, but after we have retrieved the date value as a double. The version number can be used later when you must add new fields and know if you should deserialize them or not.

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 its on to our final serialization method.

Save to Table Columns

We have seen how to save entire objects into encrypted BLOB fields. Now we 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 we are simply formatting a SQL string by using the object data members directly, and also we are wrapping them with ENCRYPT(). Notice that for the date field mLastOrderDate, we use the DateTime.ToOADate method to convert it to OLE date format, which is really the double data type.

Load Columns From Table

OK, so lets see how to load our object fields from the database. Here is the last code. Notice we just wrap each field name with DECRYPT(). But, notice how we 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 its just a simple matter of casting the column values to their field data types.

Conclusion

I have demonstrated how easy and straight-forward it is to use SQLitePlus for encrypted .NET object serialization to SQLite database files. We have learned 3 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

BrettG is a professional Windows programmer and owner of EzTools Software, maker of Windows programming tools (www.eztools-software.com)