Encrypting a database

[mshmyob]

This is NOT a question regarding coding, it is a question concerning methodology.

I need to encrypt a database. The database being used does not contain any encryption abilities like TDE for SQL Server. I have therefore worked out a method to do symetric encryption and key storage etc.

My question is which method should I use for the encryption/decryption and executing SQL statements.

I am thinking to encrypt the database and then decrypt it when I need to run an SQL statement against the data. After I execute the SQL statement and get my return set I can encrypt it again.

I see a problem with this method as when the database is in the decrypted state all the data is in clear text. If the system crashed when the SQL statement is being executed the database would be left in a clear text state until I could encrypt it again. This is not acceptable.

I am not sure what the proper method would be to encrypt/decrypt and execute SQL statements against the database.

Any ideas or suggestions.. especially from anyone who has attempted this before.

*****
New thought

I have thought about encrypting and decrypting just the data rather than the database file but am unsure what would happen if for instance I had a column with a data type of DATE. After I encrypt the date it would I assume no longer look like a date and how would the system accept it in a date column?
Thanks....

[NeoPa]

Are you aware that ACCDB databases are encrypted if you give them a database password?

[mshmyob]

Hey NEO,

Long time... I am not using a MS Access database. This is a pet project that I am working on. I am using an SQLite database and Python. There are addons to buy for encryption for SQLite but I do not want to pay for them. I have the code for Python to do all the encryption and decryption and I think I have a proper method to hide the keys but I am stuck on the concept of saving and retrieving the encrypted/decrypted data in the database.

[NeoPa]

I was trying to keep you happy, just minding my own business ;-)

If I'm honest what you plan sounds like it could be a bit of an overhead for every transaction, but it's not really an area I can help much with.

Best of luck anyway of course :-)

[Rabbit]

If you're encrypting field by field, you would have to set every field to a binary or text data type. You would need to use type conversion functions to interact with the data. You would also want to use a unique salt for each row and/or data element. NeoPa is right, it would probably introduce a lot of overhead.

If you really need the security, it's better and more secure to pay for it than trying to roll your own.

[NeoPa]

Rabbit:
If you really need the security, it's better and more secure to pay for it than trying to roll your own.

I have to say I agree with that. The ramifications of rolling your own are already (becoming) obvious to you. Far from trivial.

NB. I would say Rabbit is our security guru here as far as Access is concerned, and probably beyond that too.

[mshmyob]

Thanks. I am going to use pre built functions for encryption (the paid functions for Sqlite) do it field by field so I can just do that myself. I did find an article talking about binary fields and such. Just because this is my own pet project I would like to try just to get more knowledge.

Thanks for the feedback.

[NeoPa]

A very interesting project as far as exploring potentials is concerned. Just be very careful of promising results until you've ironed out all the complications ;-)

[mshmyob]

So assume I have a table with 5 columns

col1 - primary key - auto generated - Not encrypted
col2 - text - encrypted
col3 - text - encrypted
col4 - text encrypted
col5 - IV column - unique for each row - obviously not encrypted

Assume col4 has just contains single words. Now I execute the following simple query

SELECT * FROM tablename WHERE col4='Apple';

I can only think that I would decrypt each row and do a comparison for the word Apple. I have basically eliminated using indexes since I would have to go through each row and I also defeated the purpose of a SELECT statement since I am not returning a return set with the SELECT statement really but with my code that I need to create.

I assume I am thinking about this incorrectly since I don't see this working properly. What am I missing???

Confused....

[mshmyob]

Hey Neo, not promising anything to anybody. I am doing this all for myself to get a better understanding of encrypting a database without built in tools. I really want to know the details.

[mshmyob]

Actually with some more research I think I came up with the solution of how to query encrypted data without first decrypting and then iterating through the data with application code.

I can directly query the encrypted data with the concept of Blind Indexing. I think I will try this route.

Thanks

[Rabbit]

It will come down to how secure is secure enough.

Creating an index on a HMAC means loosening the security slightly to accommodate the increase in performance. The security weakness with the this approach is that the HMAC can't use a salt/nonce/initialization vector as part of its calculation. Therefore, the same inputs run through the HMAC result in the same output. Otherwise the index wouldn't work.

In practice, what this means for an attacker is that if they are able to figure out the value of one field on one account, then at a minimum, they now know that value maps to that HMAC throughout the rest of the records in that table for that field.

How much that small leak of information matters comes down to how sensitive it is to maintain the confidentiality of that piece of data.

For example, leaking data on their preference of deodorant and everyone else who likes that same deodorant probably isn't much of an issue. But leaking a zip code and everyone else that lives at that zip code might be more of an issue.

[mshmyob]

You are correct Rabbit but I want to see if I hash it with a hidden salt (I will encrypt The salt) and have it on a different server. That way if the database gets compromised all they will know is that one of the encrypted columns (they won't know which one) contains like data in some rows. But without the salt they will not be able to determine the prefix which is the actual data.