Hi everyone,

I am using SQLite Database version 3.35.5 for a WPF app developed with Visual Studio 2019. I would like to protect the data and encrypt the database and decrypt it only when using the app. Is there an easy way to encrypt and decrypt the database?

Is there an easy way to encrypt and decrypt the database?

The official way is the sqlite3 Encryption Extension (SEE):

https://www.sqlite.org/see

but that's a commercial product. There have previously been mentions of one or two FOSS options and maybe another forum-goer can enlighten us about those.

If you're looking for free options, there's SQLCipher, for example. Although all the cryptography does is take the problem of storing the large secret (the database) and transform it into the problem of storing a small secret (the password) somewhere.

Since your program needs the password to operate, it's probably possible for a sufficiently interested reverse engineer running your program to extract that password and then decrypt the database. You could try to mitigate it further by limiting the users' ability to interact with the program by giving them locked hardware (kiosk computers), using their computers to hide secrets from them (trusted computing, remote attestation) or hosting it on the Internet (service as a software substitute).

Others have told you how to make SQLite decrypt just the data it is using, but you may be asking instead if you can encrypt the entire database when you need to.

Each database for SQLite, if no connection has it open, keeps all its data in one file. It's not a folder of many files, or one file for each table, just one file for the whole database. So while it's not being used you can rename, encrypt, compress, duplicate the file or anything else you want to do with it.

For this to work properly it's important that all software which accesses the database uses the SQLite API to close its connections properly instead of just quitting. Otherwise some of the data (recent changes and more) may remain in a journal file.

@anonymous, Thanks for your answer. I have been trying to install and get to know SQLCipher for the last few days. For my application is sufficient. Encrypting the database with a password was quite simple.

Now I have the problem to decrypt the database in Visual Studio when I establish the connection. In some forum it says that the password should be given in the ConnectionString as follows (I use: System.Data.SQLite Version 1.0.115.5 )

connectionString="Data Source=DatabaseName.db;Password=MyPassword"

But this did not work. I always get an error. After searching for a while I found in some forum that the key "Password" of "System.Data.SQLite" is not supported anymore.

I don't know anymore what is right and what is wrong. Do you know how to decrypt the database in VS so that the database can be used by the app?

Does PRAGMA key work for you?

SQLCipher is a C library. You can use it for free in applications written in C or other languages that let you call C entry points. SQLCipher for Windows System.Data.SQLite is a commercial C# library.

It's probably possible to take the System.Data.SQLite sources, add a bunch of functions corresponding to SQLCipher C functions related to encryption and build your own version of System.Data.SQLite with SQLCipher support.

Alternatively, you can use P/Invoke in your C# program to call C functions straight from SQLCipher. Be very careful, because C# can't protect you from mistakes in the management of C resources.

Now I have the problem to decrypt the database in Visual Studio when I establish the connection

and

I found in some forum that the key "Password" of "System.Data.SQLite" is not supported anymore.

I'm not very familiar with VS and only answering for the lack of other replies.

Note that the encryption extension you use (or any of them really) requires that the database, once encrypted, be opened/read with the same encryption extension in place to facilitate decryption of the data.

That is, Visual Studio's standard DB reader connection probably does not support (or at least, does not include by default) the encryption engine you've used to encrypt the database, so it wouldn't know how to decrypt it, regardless of giving the correct keys/pragmas/etc.

Perhaps simply building a VS-happy DLL will do the trick - I am not familiar enough with VS to speculate, but somebody in the Visual Studio community may have done this before (in fact it is rather unlikely that no-one did), so you are more likely to get an answer from a VS forum in this regard.

PRAGMA key does not work either. I really have a big problem. The developed app is useless if I can't decrypt the database to access the data.

I have posted the question also in microsoft forum here, but I still I have not got any helpful answer

You can use DB Browser for SQLite (the SQLCipher version ... there are two .exe's in the package) to open the database and decrypt it. You can then save it decrypted to use in development.

system.data.sqlite which is a library to access the database does support encryption, but a different format to that used by sqlCipher - one cannot access the other.

sqlCipher do sell a library you can use, but being a commercial product, its not free.

system.data.sqlite does support encryption up to version 112 - after that it was removed due to a change in the actual sqlite version it uses. I think its on version 115 at the moment, so depends if your application uses any new featuers of sqlite... can it stick with the old version? Or can you/want to use a commercial third-party solution to use encryption.

For development I use DB Browser for SQLite for decryption. But in the long run it is not a practical solution. I use only the standard functions (INSERT, SELECT, DELETE, SET). So if an old SQLite version would solve my problem, I will then use an old version. But the old versions are not available anymore. I can download/use in Visual Studio only the versions 1.0.115, 1.0.115.5 and 1.0.116. For example, version 1.0.112 is not available in VS.

I haven't got my development box up and running currently, as I'm testing on a Linux box and tinkering. However, the URLs for system.data.sqlite are quite friendly.
Download 112 from here: https://system.data.sqlite.org/downloads/1.0.112.0/sqlite-netFx46-binary-bundle-x64-2015-1.0.112.0.zip and https://system.data.sqlite.org/downloads/1.0.112.0/sqlite-netFx46-binary-bundle-Win32-2015-1.0.112.0.zip

You just need three files - the System.Data.SQLite.dll with the app .exe and two other two dlls in the x86 and x64 directory. Yell back if you need more info on that. I'll try this myself later to confirm. The only drawback is that DB4S (DB Browser for SQLite) won't be able to open the encrypted database - the encryption formats between sqlCipher and system.data.sqlite are different. You'll have to code a database viewer once you encrypt the database to view data in it (eg, check data in fields) and code any database changes. Urgh.

When I said "the old versions are not available anymore", it was referring to NuGetPackages in VS.

I was able to install version 112 via the Package Manager Console and configured the StringConnection as follows.

"connectionString="Data Source= MyDatabaseName.db;Password=aes256:MyPassword;"

But I get the following error:

"Cannot use "Password" connection string property library was not built with encryption support, please see "https://sqlite.org/see" for more information"

The error is very confusing. If version 112 is supposed to support decryption, what am I doing wrong?

Interesting - SEE is an extra extension. Is this on a bog standard 'new' project, or one that's maybe got in a bit of a pickle with references? I've my development box back. I'll have a test. I must admit I've not used system.data.sqlite for encryption in the past, as it caused too much development problems for the solution it provided.

It is a new WPF project(Net 5.0) developed with Visual Studio 2019

OK, I got this working easily using v112 of system.data.sqlite Please note the URLs I posted are the wrong ones - they're the bundle ones, so simply remove the word 'bundle' from the links. Sorry about that.

I added a reference to the system.data.sqlite.dll then added the imports line. That was it. This was also useful. https://stackoverflow.com/questions/41425759/set-password-for-sqlite-v3-database

Open the database then use 'changePassword'.
This was the database before - human readable (just) https://i.imgur.com/plSVTfO.png

But now its scrambled. https://i.imgur.com/VdmaZrw.png

Again, I won't be able to open this in DB4S (normal or sqlCipher version) until its decrypted (set the password to "")

This may be a 'not enough info' post, but yell back if not, as its working here and I can go through more info.

I am so frustrated. It does not work for me. I get the following error when I use "SetPassword" as from your posted link.

'SQLiteConnection' does not contain a definition for 'SetPassword' and no accessible extension method 'SetPassword' accepting a first argument of type 'SQLiteConnection' could be found (are you missing a using directive or an assembly reference?

I installed System.Data.SQLite V112 via NuGet Packege Manager. And in my C# code I use the Packege by "using System.Data.SQLite;"

Do I really need to download the package manually from the posted link? Isn't it enough if I download it via NuGet Package Manager? What am I doing wrong?

Do I really need to download the package manually from the posted link?

Its only two files, and you only need three files in total. I don't know what other files its downloaded. I remember struggling for ages trying to get a NuGet package working, and found it easier to just download the files, add a reference to them then add a simple 'imports' statement.

I can give you the project I just created so you can compare that against your project - it might be that you've got v112.2 and not v112.1 or something - I really have no idea. All I can suggest is trying a basic project using the files I suggested.

Happy to help where I can.

If you hover your mouse over 'SetPassword', does Intellisense pop up anything?

https://i.imgur.com/Y3BcqSq.png

yes, it would be very friendly if you make your project available. I can then compare it with my project

I get the following two error:

Error CS1061 'SQLiteConnection' does not contain a definition for 'SetPassword' and no accessible extension method 'SetPassword' accepting a first argument of type 'SQLiteConnection' could be found (are you missing a using directive or an assembly reference?)

MyProject depends on System.Data.SQLite (>= 1.0.112.1) but System.Data.SQLite 1.0.112.1 was not found. An approximate best match of System.Data.SQLite 1.0.112.2 was resolved.

Aah, that makes sense then. You're not referencing the v112 file, and instead VS is finding the closest match, which is vXYZ - could be anything.

I've created a project from scratch. See if you can at least create a project which uses the v112 files. Heres a PDF http://e.pc.cd/HAPotalK

And here's a link to the project (VB) files http://e.pc.cd/mIPotalK

Obviously beware of anything you download off the Internet. These files are clean but upload them to VirusTotal first. VirusTotal reports 5 vendors flag the project as malicious https://www.virustotal.com/gui/file/428a8a212f5b1c7a002420c745108cf805ceb532a1b8bc8b6bf54aa2f96964a2?nocache=1

Neither System.Data.SQLite nor Visual Studio support SQLCipher. Visual Studio only hosts your application so if you want to open database files encrypted by SQLCipher, you will need to provide your application with the code to do so, as presumably it will eventually head out to the big wide world outside of Visual Studio.

System.Data.SQLite documentation of the Connection class states

    password - Using this parameter requires that the legacy CryptoAPI based codec 
    (or the SQLite Encryption Extension) be enabled at compile-time for both the 
    native interop assembly and the core managed assemblies; otherwise, using this 
    parameter may result in an exception being thrown when attempting to open the 
    connection

So if you really need encryption, you can use either of those encryption APIs, although the SQLite Encryption Extension is not free, and you must encrypt with the API you intend to decrypt with. The SQLCipher option is either open source C code which you can compile yourself and write managed code to access, or a System.Data.SQLite based option which as has been mentioned is a commercial library.

Your previous reference to the Microsoft forum is not related as the error mentioned is from Microsoft.Data.SQLite not System.Data.SQLite. However, that documentation comes with

    Warning
    Password has no effect when encryption isn't supported 
    by the native SQLite library

You could probably conclude that encryption is a bit tricky. As an alternative, depending on your target users, you could simply prevent the user connecting to the unencrypted database if they don't provide valid credentials to the interface. This obviously wouldn't prevent access by other means.

I tried to follow your instructions. But that does not work. I think it worked for you only because you created a VB and not a WPF project. I suspect if you did the same for a WPF project, it wouldn't work.

Maybe you can check this?

Sure. Can try. However, the key is in the 'SQLite 1.0.112.1 was not found. An approximate best match of System.Data.SQLite 1.0.112.2 was resolved' error - if you're not going to use v112 then you're not going to get the same results.

By 'that does not work', what didn't work? Reminds me of when I tried to help my Mum install WhatsApp on her phone via a telephone call and all I was getting was, "What do I click on now? I can see two buttons." While the information she provided was technically accurate, it was not overly helpful.

Worked with no issues.

https://i.imgur.com/FfTL6kQ.png

Well, apart from I couldn't do much with VS after the program had started, but that's more to do with Visual Studio. As shown, Intellisense liked the 'setPassword' option.

can you please post the whole project?

When I try to add the SQLite.Interop.dll as reference to the project, I get the following error:

"The reference is invalid or unsupported"

You should not (be trying to) add a reference to that DLL. You should add a reference to the .Net library module, System.Data.SQLite.dll, but not to its internal component, SQLite.Interop.dll .

so it works now but only if I use an absolute path. If I use a relative path, I get the following error: "SQL logic error no such table: TableName"

Does anyone know why it doesn't work with relative path and how to solve it? I have to use relative path.

I did add the whole project. The link I've posted previously.

Why are you adding a reference to SQLite.Interop.dll ? It was a waste of time me writing instructions in a PDF which I also linked to.

Sorry I couldn't help.