What about encryption?

[Spacelapp]

How to open an encrypted database?

[groue]

Hello @Spacelapp.

Here is an answer - you'll rephrase your question if it is not the answer you expected:

If you are on iOS, do you know that iOS devices encrypt their content? Look for "Encryption and Data Protection" in https://www.apple.com/business/docs/iOS_Security_Guide.pdf

GRDB supports data protection classes:

var config = Configuration()
config.fileAttributes = [NSFileProtectionKey: NSFileProtectionComplete]
let dbQueue = DatabaseQueue(path: ".../db.sqlite", configuration: config)

Et voilà! Your database is encrypted.

[Spacelapp]

Hello @groue thx for the answer, but I mean this: https://www.sqlite.org/see/doc/trunk/www/readme.wiki Maybe interesting as well: https://www.zetetic.net/sqlcipher/ios-tutorial/ Best regards

[groue]

All right, @Spacelapp

Both techniques require a specific compilation of SQLite. Besides, SEE requires buying a license.

GRDB uses the free SQLite bundled by the hosting operation system.

So the answer is to your question is: you can't, in the current state of affairs.

[groue]

Besides, before I consider exploring this feature request, you have to explain why the encryption facilities provided by iOS don't fit your needs (assuming your code runs on iOS).

[Spacelapp]

I have a database with sensitive data, if someone extract the content of the iOS app and gets access to the .sqlite file, he should not be able to open it on a computer. "NSFileProtectionComplete" only secures the data if the device is locked or am I wrong?

[groue]

Yes, NSFileProtectionComplete secures the data when the device is locked. Do you know of a scenario where an attacker could access the unencrypted data?

We need to be specific here, before asking for useless free work. That is not something I like, as an open source developer who provides free software in my spare time. I want to focus on actually useful features.

There is one specific case I can talk about, where the attacker is the app itself. This happens when the application allows the user to sign in, and out. In such an app, Alice could sign in, store sensitive data, sign out. Later Bob signs in, on the same device. We don't want Bob to have access to Alice data. The app should not let this happen, but still, if the Alice's database is still there on the disk, one could imagine that the app could be tricked into loading Alice data while the Bob is signed in. My personal opinion on such apps is that they should erase data when the user signs out. In this case, an eventual Bob could just never access Alice's data. And this technique also works for data that is not stored in the database, such as images.

[Spacelapp]

Well this is my scenario (Latin Vocabulary App): I give my users the option to download a database to also use the app in offline mode. This database contains data which should only be used in the app (Vocabularies, Mnemonics, ...). So I don't want users to extract this database and copying it or whatever.

[groue]

OK, I get it :smile:

Well. How do you feel about this? Do you think you could contribute? If so, it looks like Zetetic's SQLCipher is the only reasonable option. Since SQLCipher requires a recompilation of SQLite, activating encryption is not a matter of setting a flag at runtime: we need a totally separate framework.

[groue]

Libera via delenda est!

[Spacelapp]

Well, you are right, but currently I don't have the time to contribute :( I could only contribute with money 😅

[groue]

OK. I must, in full honesty, link to SQLiteCipher.swift. It is a variant of SQLite.swift which already has support for SQLCipher.

I guess you already know about SQLite.swift, and I'm happy you have shown interest in GRDB. But now it all depends on your shipping schedule, and my own free time. I'm currently working on a NSFetchedResultsController for GRDB. I could start looking at SQLCipher next after.

[groue]

On a side note, @Spacelapp: I work at Pierlis, an iOS developers company. If you really want to use GRDB with SQLCipher before a fixed date, we could discuss an agreement.

[Spacelapp]

Well I tried now SQLite.swift (currently not working with SQLCipher...don't know why). I love the performance of GRDB so much! I also tried FMDB (works with SQLCipher), but this is horrible...so slow :-1: would be great to discuss an agreement!

[groue]

That would be great! Please send an email to gr [at] pierlis.com: we'll figure this out!

[groue]

Hello @Spacelapp, I've started looking at SQLCipher.

As I kind of expected, it is not trivial: https://github.com/sqlcipher/sqlcipher/issues/162, https://github.com/sqlcipher/sqlcipher/issues/163

Nothing blocking, but still it deserves investigation.

[groue]

@Spacelapp It has been shorter than I expected.

Will you try the SQLCipher branch?

Don't import the GRDB framework, but the GRDBCipher framework:

import GRDBCipher

You can't encrypt an existing database (ref), but you can open or reopen a new encrypted database:

var configuration = Configuration()
configuration.passphrase = "secret"
let dbQueue = DatabaseQueue(path: ".../db.sqlite", configuration: configuration) // Or DatabasePool

[Spacelapp]

WOW! :dancer: :clap: I will try it, but does it need iOS9+ ?

[groue]

No, it should not. Please be more precise (what is your integration technique, what OS do you target, etc.)

[Spacelapp]

I tried it over Cocoapods, but the line s.ios.deployment_target = '9.0' throws this error: ...dependency were found, but they required a higher minimum deployment target.

[groue]

CocoaPods support is not ready yet (if it gets ready eventually...).

I suggest you switch to manual integration during your testing phase. Make sure to use the GRDBCipher framework, not GRDB.

[Spacelapp]

Works as expected (simulator and device)! :clap: :clap: Great work!! Thank you so much! :+1: :dancer:

[groue]

Happy GRDB Lukas! This will ship in the main branch soon.

[groue]

@Spacelapp, we're done now.

GRDB v0.56.1 has shipped with SQLCipher support (documentation).

CocoaPods is really hard to support, so I suggest that you stay with manual integration (and be sure to checkout v0.56.1), or that you switch to Carthage. See https://github.com/groue/GRDB.swift#installation

[Lakshmipriya087]

How to use SPM for GRDBCipher?

[groue]

How to use SPM for GRDBCipher?

https://github.com/groue/GRDB.swift/issues/1244