handmade.network » Forums » Learning DB Performance/Behavior
ratchetfreak
267 posts
#12087 Learning DB Performance/Behavior
3 weeks, 3 days ago

it doesn't work with table or parameter names, only with values
msmshazan
Shazan Shums
154 posts

Some day I will make quality software.
Programming FTW.

#12091 Learning DB Performance/Behavior
3 weeks, 3 days ago

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

Just being curious on programming....
mmozeiko
Mārtiņš Možeiko
1349 posts
1 project
#12092 Learning DB Performance/Behavior
3 weeks, 3 days ago Edited by Mārtiņš Možeiko on June 2, 2017, 6:27 p.m.

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.
msmshazan
Shazan Shums
154 posts

Some day I will make quality software.
Programming FTW.

#12120 Learning DB Performance/Behavior
3 weeks ago

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

Just being curious on programming....
Kelimion
Jeroen van Rijn
223 posts
3 projects

A big ball of Wibbly-Wobbly, Timey-Wimey _stuff_

#12121 Learning DB Performance/Behavior
3 weeks ago Edited by Jeroen van Rijn on June 5, 2017, 1:42 p.m.

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.
mmozeiko
Mārtiņš Možeiko
1349 posts
1 project
#12122 Learning DB Performance/Behavior
3 weeks ago

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).
msmshazan
Shazan Shums
154 posts

Some day I will make quality software.
Programming FTW.

#12124 Learning DB Performance/Behavior
3 weeks ago Edited by Shazan Shums on June 5, 2017, 3:21 p.m.

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.

Just being curious on programming....
mmozeiko
Mārtiņš Možeiko
1349 posts
1 project
#12125 Learning DB Performance/Behavior
3 weeks ago

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).
msmshazan
Shazan Shums
154 posts

Some day I will make quality software.
Programming FTW.

#12126 Learning DB Performance/Behavior
3 weeks ago

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.

Just being curious on programming....
Kelimion
Jeroen van Rijn
223 posts
3 projects

A big ball of Wibbly-Wobbly, Timey-Wimey _stuff_

#12127 Learning DB Performance/Behavior
3 weeks ago

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.
mmozeiko
Mārtiņš Možeiko
1349 posts
1 project
#12128 Learning DB Performance/Behavior
2 weeks, 6 days ago Edited by Mārtiņš Možeiko on June 5, 2017, 7:14 p.m.

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.
msmshazan
Shazan Shums
154 posts

Some day I will make quality software.
Programming FTW.

#12131 Learning DB Performance/Behavior
2 weeks, 6 days ago Edited by Shazan Shums on June 6, 2017, 7:36 a.m.

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.

Just being curious on programming....
mmozeiko
Mārtiņš Možeiko
1349 posts
1 project
#12133 Learning DB Performance/Behavior
2 weeks, 6 days ago

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,
Kelimion
Jeroen van Rijn
223 posts
3 projects

A big ball of Wibbly-Wobbly, Timey-Wimey _stuff_

#12135 Learning DB Performance/Behavior
2 weeks, 6 days ago

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 ;-) ).
mmozeiko
Mārtiņš Možeiko
1349 posts
1 project
#12137 Learning DB Performance/Behavior
2 weeks, 6 days ago Edited by Mārtiņš Možeiko on June 6, 2017, 6:15 p.m.

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.