Has anyone tried to encrypt a business object before persisting it? We have a WinForms app using CSLA 3.7 and we have a customer who likes to play in the database. (Yes, I know this is bad, but we aren't allowed to lock them out of it).
I'm not too familiar with encryption and was wondering if anyone has done this and if it is easy.
Thanks for any insight,
Do you read property values and updated these straight into table columns (fields)?
Are you serializing the data object into a 'BLOB' that then gets stored in a table?
The answer to your question really depends on your architecture.
Let me know what you're doing in a little more detail, and perhaps I can help out.
The first scenario. We are updating them straight into the table columns.
Appreciate the quick response :)
Obviously, you need to be able to store the actual values into the fields in the table, as you want to be able to use SQL queries to filter/group/join, etc. So encrypting the actual data in the table would not be sensible, as you couldn't then use the database engine to get back the data you want. (Well this isn't entirely true - but if my suggestion below isnt any good - i can tell you how to do it).
Of course, if there is data that you don't ever need to be able to filter/group/join on, then you could encrypt that before writing it to the field. You'd typically do this with any security related data, such as user passwords.
But i think you are looking at a situation where a client/user has direct access to the tables and is messing up the data, so another option is to add another field to each table called something like 'checksum'. Then, whenever you update data into the table from your business object (DataPortal_Update/DataPortal_Insert), you can update this checksum field at the same time. The checksum should be derived from critical data in your business object, and can be easily created using standard .Net cryptography, such as an MD5 hash. Depending on the size of your object, you could serialize the object (in memory) and run a checksum over the entire object. Or, if the object writes to multiple tables, then you'll need to checksum the row of data - you can do this the same way.
Then, when you read the object back from the table (DataPortal_Fetch) and have populated all the fields, run the checksum again and compare it against the checksum from the table. If they differ, then the data has been modified externally, in which case you can handle that as an exception and/or invalidate the data and/or pop a message box to the user telling them not to mess with data otherwise this kind of thing happens.
Does that help?
Actually I think this does help and may work.
I would be interested in knowing how you would approach the first scenario that actually stored the encrypted value in the database.
Thank you so much for the detailed response. It really helps.
Before deciding to encrypt data in the database, you need to think of the consequences of doing so.
Since 2005, Microsoft SQL Server has built in cell level encryption - but you lose all query optimisations on encrypted cells, and performance will degrade significantly as more data is added, and with any joins. MS SQL Server 2008 has introduced database encryption, but that has drawbacks too - and if your user/client has administrative access, then either of these options might be able to be worked around. If you want more info on this, check out this (extensive) MS article:
Alternatively, you could implement your own encryption in your data layer - but that will certainly mean that you can't use traditional SQL queries - for example, if you wanted to find all records with the letter x in the field, you would have to load the entire table of data into memory, decrypt it, then run an in-memory query across the data (perhaps using LINQ), to then populate your business objects with the results. Unless you have very small amounts of data, this is just not feasible.
MySQL has a variety of ENCRYPT/ENCODE options that you can use in your insert/update/select queries, however I've never used these. Your database would still suffer performance issues with this approach.
As a last thought - perhaps you could ask your client/user why they are playing in the database. Maybe there is an opportunity for some additional features that you could add to your product that would mean they don't need to do that anymore?
That all makes sense. Thanks again.
Copyright (c) Marimer LLC