Link to home
Start Free TrialLog in
Avatar of vdurbal
vdurbalFlag for United States of America

asked on

Manually encrypting/decrypting SQLite databases.

Hello.  I want to use SQLite in my VB.NET application.  In my application, the user opens a file and the database connection is kept open until the program is shut down, or another file is opened.  A major requirement I have, however, is that these files should be password protected so that they can only be opened by my application.  I have found some options for encrypting SQLite files (e.g. SQLiteCypher), but they are a bit pricey so I'm looking for other methods to get what I need.

One alternative I thought of would just be for me to manually encrypt and decrypt files using Rijndael (I found an example on CodeProject).  That is, when the file is created within my application, it is encrypted so that it cannot be opened by any normal SQLite viewer application.  However, when the file is opened in my program, it would first be decrypted, then connected to normally.  When the connection is closed, the application would then encrypt it again.  I have done a quick test on this and it seems to work, but I am wondering if I am missing, or not considering, something.  Is there any reason why this idea would be ill-advised?  Thank you.

Vijay
Avatar of Chinmay Patel
Chinmay Patel
Flag of India image

Hi Vijay,

As SQLLite is a file based database if someone determines the location of the file, they can easily copy it. Are you applying any locks on the file so it cannot be copied OR read by others(after decryption)?

Regards,
Chinmay.
You have to answer a few questions: How many users of your application do you plan? How much do they pay for it? What is your loss if 1% of them will hack your SQLite encryption?

If you just decrypt the file at the application start then it should be easy to intercept such operation for experienced computer user. More problematic part is the encryption at the program end... I can imagine many situations in which this operation fails and new data are lost.

BTW, the SQLCipher (https://www.zetetic.net/sqlcipher/) is open source so if you invest in its compilation then you don't need to pay for it.
SQLite Encryption Extension (https://github.com/shenghe/FreeSQLiteEncryption) is also free but again, it requires to learn how to compile it and incorporate it to SQLite.
A major requirement I have, however, is that these files should be password protected so that they can only be opened by my application.
This is obfuscation and not security. Cause the password could be easily (more or less) extracted from your application. The decrypted file can pretty easily be copied.

So this requirement is really non-sense. You must review it and redefine it. Most important is that you describe the targets of the desired measures.

Without knowing them, I would consider the following as the "common" approach:

Generate a public/private key pair during installation for machine-wide use or at program start for the current users use only. Store it in the Windows key store. Use an ORM which encrypts/decrypts when writing/reading data from your SQLlite database file.

The only circumstance where this kind of obfuscation makes "some" sense is where you only use streams. Thus the decrypted data does not exists as explicit file.
My thinking is that you just around the corner regarding the solution...if your application is strictly in house then use Rijndael  to encrypt your data...a class will encrypt/decrypt the data on the fly and that's about it...
The only issue would be when you are "calculating" field length ...eg  EXPERTS-EXCHANGE with a key 'John' gets
fnGkk4nrc5B9Mxj0D514K9JnyFy9GL8AcmgINPMQxMI=
Why not encrypt/decrypt vital data in SQL tables instead of encrypting file?
Avatar of vdurbal

ASKER

Thank you everyone for your comments and suggestions.  I guess I should clarify my situation a bit.  ste5an made the point that what I was looking for was more obfuscation than security.  He is correct in that.  We have around 1500 users of our program, but they are, on the whole, not terribly sophisticated in computer technology, nor is the incentive to break into these files terribly great.  We currently use MS Access database files with a simple password, which has worked fairly well, though I know that breaking these passwords is not difficult at all.  However, the password does limit casual opening of these files, which is preferable to having nothing.  Unfortunately, we need to have a standard password for all files of the type created by our program.

I was originally thinking of decrypting the database, then connecting to the database and leaving the connection open while the file was in use, only re-encrypting it when the user was finished.  However, I realized that this would leave the file in an unprotected state for too long.  So now I'm thinking of doing the following process for each interaction with the database file:

1.  Decrypt the file
2.  Open a connection to the db.
3.  Perform whatever action is needed on the db (e.g. query, update, etc.)
4.  Close the connection
5.  Encrypt the file

Our program interacts with the database a lot, however, so I'm worried about two things:

1.  How much of a performance hit will I incur in the decryption/encryption process, along with always opening/closing connections.

2.  More importantly, is there a significant risk of data being corrupted/lost when it is encrypted/decrypted?


Again, thank you all for your comments and suggestions.
Again: What are the goals here, what is your threat model? Why is Access and a simple password not longer sufficient?

For your questions:

1) Many of us use SSD's. This means that such an approach will wear out cells faster.
2) You need to juggle with the files. Save to a new file, keep the old for some time.

What's wrong with SQL Server Express and Always Encrypted?
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.