it doesn't work with table or parameter names, only with values

Do you know any open source projects which use sqlite or any database api which I can refer source code from.

Firefox, Chromium, various open-source languages/libraries/frameworks like Python, PHP, Django, System.Data.SQLite, or GUI tools like DB Browser for SQLite, SQLiteStudio, Sqliteman. Just google "sqlite site:github.com" and you'll find a ton of stuff.

How to add password protection to sqlite or should I use another database api for password protection.

msmshazan
How to add password protection to sqlite or should I use another database api for password protection.

The difference between sqlite and other databases is that there's a database server which can enforce access, and with sqlite you embed the entire database engine into your app. This also means the database file is on the same computer as the client instead of elsewhere.

If you want to add password protection to sqlite, you'd basically have to encrypt it. You can do that as sqlite allows overriding its I/O, but you'll have to add a few custom bits and bobs.

And because sqlite will run db in same machine where user enters password that means that once user knows password, he will have full access to database contents (create/insert/update/delete, anything).

You mean i should use openssl to encrypt the database and decrypt it when using the database.

Plus how should I intigrate sqlite to an imgui based app.
I know it has to do something with threads but still no idea.

No, what that means is you need to understand what is your attacker and what against are you guarding.
Are you protecting against accidentally putting sqlite in backup where it could leak to somebody else or to internet? Then transparent sqlite db file encryption is OK.
Are you protecting data at rest? Then same encryption scheme is OK.
Are you protecting against user itself - to forbid him/her to manipulate sqlite db file outside of your application? You cannot guard against this with sqlite, unless you make wrappers and/or use it remotely (but at that point it would be easier to take something else like mysql/postgresql).

I am not trying to protect the database from the user because if the user has access to the db then I don't mind.

So i'm not protecting against your 3rd case only case 1,2.

Any good encryption libraries because trying to compile openssl on windows is insane and it feels heavy.

You could use libsodium by DJB.

You would have to override sqlite's I/O functions so that file access goes through your crypto layer. You'll probably want to use AES in CTR mode so you can have random access to the encrypted data at a block-level granularity instead of having to decrypt everything preceding. Whether that's an acceptable trade-off compared to CBC mode is for you to determine, but most whole disk encryption methods use some variant or other of CTR mode to satisfy being able to have random access without speed cratering.


If all of this is too much work or you believe it to be too complicated, you could look into licensing SQLite Encryption Extension from sqlite's primary author, or a comparable extension from someone else. SQLCipher has both open source and commercial options.

I'm not familiar with either of these products, so I can't really recommend one or the other and there are certainly more implementations of the same functionality out there as well.

Hope that helps.

I have used sqlite vfs I/O API to implement transparent encryption on page level. SQLite asks VFS to do I/O on individual pages when it reads or writes. Typically these pages are 4KB. I have plugged in AES CBC there with additional HMAC for authentication (using libsodium here instead is also a good option). It's a bit of work, but everything works pretty well and is very flexible

SQLiteCipher integrates openssl in a bit deeper level - it modifies sqlite internal code to encrypt pages before they are to vfs. It's also a good solution, but a bit harder to modify if you need some customization - it will require understanding internal sqlite structure.

Alternatively for your use case you can consider using file encryption from OS. That can include Full Disk Encryption solutions like BitLocker or VeraCrypt. Or encryption on file level - ecryptfs.

What about mbedtls or should i go libsodium anyways I'll try both and see which works and has less build hassels.

I think I'll encrypt the whole file because its easy but there's a problem i want a way to find out if the file is a database file or not before encryption. Maybe i can encrypt all rest of the file except the header. I just wanted a way that the person who has the key can do anything with the file!!!..But didn't think encryption is so complicated.
But anyways because i am still learning its fine.

Where can I get an overview about encryption because i don't want a deep understanding but when Martin's says the terms aes,CBC,hmac and I have no clue about those.

Those are very two different libraries. libsodium is just a crypto library with easy to use API. mbedtls is like OpenSSL - full blown TLS library. You can use crypto primitives from mbedtls or openssl, but then you must make sure you use them correctly.

SQLite file has a well known and constant header. To determine if file is encrypted (and if password is right or not), just check the header. If it is plain, the file is not encrypted. If after decryption it is what it should be, then password is correct. Otherwise password was wrong.

This cryptography course on Coursera is pretty decent for understanding crypto basics: https://www.coursera.org/learn/crypto The author is very well known in cryptography and software security community,

mmozeiko
SQLite file has a well known and constant header. To determine if file is encrypted (and if password is right or not), just check the header. If it is plain, the file is not encrypted. If after decryption it is what it should be, then password is correct. Otherwise password was wrong.

Alternatively, use a HMAC in addition to just encrypting. If this hash (which takes as is input the block to hash as well as the password or a derivative of it) matches, you know both that was encrypted by you, and that the block has the content you expect it to have.

That's also a cheap way to know if you can open the file or not, without having to decrypt it. If your encryption scheme is such that you sign each encrypted block with a HMAC, then you check the signature before decrypting the block in question. When the block in question contains the header, this gives you an early out.

Either the hash matches and you can decrypt an open the database, or the hash is wrong, meaning someone/something corrupted the database or the wrong password was supplied. You're going to want to sign each ciphertext block anyway, if for no other reason than to make sure you don't end up with garbage after decryption (unless you encrypted garbage by design ;-) ).

Yes, you absolutely want to use some kind of MAC in addition to just encryption. That's what I did before - two posts above :)
It can be HMAC, GCM mode or Poly1305 in case of libsodium. Doesn't really matter which.