Build fails with SQLCipher due to calls not yet available in SQLCipher

[jimt2000]

commit 336faa1 on Oct 8 added calls to sqlite3_expanded_sql and sqlite3_trace_v2.

These are available in sqlite3 version 3.14, but not earlier versions. If you use SQLCipher upon which SQLiteCipher.swift is dependent, you get link errors because the version of sqlite3 used by SQLCipher is earlier than 3.14.

[jberkel]

I've got no experience with SQLCipher. Will it eventually catch up with a more recent version of SQLite? Not sure what would be a good solution in this case. More conditionals would make the build even more complicated.

[leminhtuan2015]

I am sorry, I am a new dev. May you please help me how can I use SQLiteCipher.swift (https://github.com/stephencelis/SQLiteCipher.swift)

My purpose is make a key for access my sqlite database, I want to prevent someone access to my database.

I am waiting for your help or any suggestion about this problem.

Thank sir so much.

[jberkel]

@leminhtuan2015 SQLiteCipher is deprecated. The upcoming 0.11.1 release of SQLite.swift will make the integration easier (at least if you use CocoaPods in your project).

[leminhtuan2015]

@jberkel Thank sir so much, I am waiting for the upcoming 0.11.1 release so that I can use SQLiteCipher.swift , and by the way, I am using your SQLite.swift by CocoaPods, that is so great

Thank you sir!

[jberkel]

It's been released now, please give it a try:

https://github.com/stephencelis/SQLite.swift/blob/master/Documentation/Index.md#using-sqliteswift-with-sqlcipher

[leminhtuan2015]

@jberkel Hello sir, I am trying your sqlcipher function:

let db = try Connection("path/to/db.sqlite3")
try db.key("secret")

But i seem not working for me, SO please give me some ideal.

I already have a "encrypted.sqlite" and I encrypted my sqlite file by using sqlcipher on MacOs terminal, the method I do is that:

https://discuss.zetetic.net/t/how-to-encrypt-a-plaintext-sqlite-database-to-use-sqlcipher-and-avoid-file-is-encrypted-or-is-not-a-database-errors/868

I use "encrypted.sqlite" in my IOS swift app, when I connect to the "encrypted.sqlite" by using "try db.key("secret")" nothing happen, I cannot connect to database anymore

Please help. Thank Sir so much.

[jberkel]

@leminhtuan2015 I've added some tests to make sure this functionality works as expected.

Have a look at
https://github.com/stephencelis/SQLite.swift/blob/master/SQLiteTests/CipherTests.swift#L75

Do you use the exact same passphrase for encrypting and decrypting?

Also make sure to read the API docs at:
https://github.com/stephencelis/SQLite.swift/blob/master/SQLite/Extensions/Cipher.swift

[leminhtuan2015]

Hi Sir.

• I make sure the key for encrypting sqlite file and the key when I call "db.key("mykey")" totally the same
• I make sure I already install pod 'SQLite.swift/SQLCipher', '~> 0.11.1' last version

Below are all the things I did, Please tell me if I wrong anything.

Step 1: Create a "encrypted.sqlite" in the terminal of Mac OSX (macOS Sierra 10.12.1)

$ sqlcipher encrypted.sqlite
sqlite> pragma key = 'mykey';
sqlite> CREATE TABLE word (id TEXT, w TEXT, m Text);
sqlite> INSERT INTO word (id,w,m) VALUES ('1','1','world');

=> After that I got a "encrypted.sqlite" file.

Step 2: Copy "encrypted.sqlite" to my bundle of IOS Swift app.

Step 3: Use SQLite.swift/SQLCipher

do{
  let db = try Connection("\(Constant.DOCUMENT_PATH)/\(Config.DATABASE_NAME)")
  print("Connected ok")
  try! db.key("mykey")
  print("key ok")
  let wordFilter = wordTable
    .select(idExpression, wordExpression, meanExpression)
    .filter(wordExpression.like("\(textSearch)"))
    .limit(10)
} catch {
    print("Error")
}

• I use the exact same passphrase for encrypting and decrypting : "mykey"

NOTICE: "(Constant.DOCUMENT_PATH)/(Config.DATABASE_NAME)" is the path to "encrypted.sqlite" and my everything work well before I use pod 'SQLite.swift/SQLCipher',

And now when run project i just only see the log in console of XCode is "Connected ok" and I Can NOT see "key ok" neither "Error"

Please give me some ideal, Thank Sir so much.

[jberkel]

Strange, looks like you've done everything correctly – if there's a problem decrypting the database the code should throw.

Maybe try stepping through the code in a debugger? I would also remove the do ... catch since it could obscure the error somehow.

[leminhtuan2015]

Hi Sir. I am so sorry. Because of I run my project on simulator so that I got above problem.
I do not know why on simulator nothing happen when i call try db.key("mykey")
And now i run my project on iphone device and I got error ""The operation couldn\342\200’t be completed. (SQLite.Result error 0.)

Please give me some advances, Thank sir so much

[jberkel]

Can you post the full error output?

[leminhtuan2015]

Hi Sir. I am new at Swift. The way i did is that :

do{
let db = try Connection("(Constant.DOCUMENT_PATH)/(Config.DATABASE_NAME)")
print("Connected Ok")
try db.key("mykey") // Throw error here
print("Key Ok")
....
} catch let error as NSError {
print(error)
}

And i got all in console:

Connected Ok
Error Domain=SQLite.Result Code=0 "(null)"

[jberkel]

Remove the the catch statement, and run again

[leminhtuan2015]

Sorry Sir. When i remove catch statement i can not run project because i got error ": Errors thrown from here are not handled"

When i use try! db.key("testkey")

i got => fatal error: 'try!' expression unexpectedly raised an error: not an error: file /Library/Caches/com.apple.xbs/Sources/swiftlang/swiftlang-800.0.58.6/src/swift/stdlib/public/core/ErrorType.swift, line 178

Sorry but please help solve it

[jberkel]

Sorry I'm not sure how your code ends up in this error condition. The result code is 0, which means "no error" (that's what it says "raised an error: not an error")

I'm not sure what else you can try. Does everything work as expected with non-encrypted databases?

[leminhtuan2015]

Yes sir, Everything work great when i use non-encrypted databases I really need cipher my database, I tried so hard to this expect and this is the final step, please help me, Thank sir a lot.

[jberkel]

Maybe try using db.run("PRAGMA key = 'key'") instead of db.key(...)

[leminhtuan2015]

Hi Sir, I just find out that the error occur depending on the devices

When I use non-encrypted databases => my app work great on all Simulator, Iphone 4s(IOS version 9.3.5) and Iphone 5s (version 10.1.1)

But when I use encrypted databases ( I use try! db.key("testkey") ) =>

• On Simulator: Nothing happen
• On Iphone 5s I got : Error Domain=SQLite.Result Code=0 "(null)"
• And nearest : 5 minus ago i try on Iphone 4s: My app keep crashing

[jberkel]

On simulator, nothing happens means it works? Which simulator did you try?

[leminhtuan2015]

I try on Simulator Iphone 5s Version 10.0 (SimulatorApp-700.14 )

[leminhtuan2015]

Sorry sir, nothing happens NOT means it work, It mean i got no error and still can not connect to database.

[jberkel]

What do you mean you can't connect to the database?

[leminhtuan2015]

I try on Simulator Iphone 5s Version 10.0 => My app not crashing and i got no error, But my app not print "Key Ok" in console as below:

do{
let db = try Connection("(Constant.DOCUMENT_PATH)/(Config.DATABASE_NAME)")
print("Connected Ok")
try db.key("mykey") // Throw error here
print("Key Ok")
....
} catch let error as NSError {
print(error)
}

My app just only print "Connected Ok" and Not print "Key Ok"

and with the same above code:

• My app keep crashing on Iphone 4s(IOS version 9.3.5)
• i got "Error Domain=SQLite.Result Code=0 "(null)" on Iphone 5s (version 10.1.1)

Sorry for taking so much time of Sir , but Just only sir can help me.

[jberkel]

Does it print Error Domain... on the simulator?

[leminhtuan2015]

Sir. It is NOT print anything on simulator BUT "Connected Ok"

[jberkel]

Try the following in the simulator:

do {
  let db = try Connection("(Constant.DOCUMENT_PATH)/(Config.DATABASE_NAME)") 
  print("Connected Ok")
  try db.key("mykey") // Throw error here
  fatalError("Key Ok")
} catch let error as NSError {
  fatalError("error: \(error)")
}
fatalError("last")

[leminhtuan2015]

I tried and above code got error in catch block:
"fatal error: error: Error Domain=SQLite.Result Code=0 "(null)"

[leminhtuan2015]

Sorry sir, I tried and above code on simulator i got NO error

Because github auto remove splash on comment :

"\(Constant.DOCUMENT_PATH)/(Config.DATABASE_NAME)" Github comment not show "\"

=> I paste your code on my app > i got "Error Domain..." BUT now I try with correct path to database and got NO error But still can not get print "Key ok"

[leminhtuan2015]

Sorry sir, I tried and above code on simulator i got NO error

Because github auto remove splash on comment :

"\(Constant.DOCUMENT_PATH)/(Config.DATABASE_NAME)" Github comment not show "\"

Sir 's code is: "(Constant.DOCUMENT_PATH)/(Config.DATABASE_NAME)"

=> I paste your code on my app > i got "Error Domain..." BUT now I try with correct path to database and got NO error But still can not get print "Key ok"

[jberkel]

You need to figure out what your code is doing. Don't rely on print(...) for debugging, it might not get printed for some other reasons.

Remove the do... catch as I said earlier, so your program fails early. Use a debugger to step through the code to see what it does. I don't really know what else to suggest.

[leminhtuan2015]

I really thank Sir for supporting. I will try debug, Really thank sir.

[leminhtuan2015]

Hi Sir. When i use debug i got error code 21 on this function (the function of SQLite.swift)
// MARK: - private
private func _key(keyPointer: UnsafePointer, keySize: Int) throws {
try check(sqlite3_key(handle, keyPointer, Int32(keySize)))
try execute(
"CREATE TABLE "SQLCipher.swift" ("cipher key check");\n" +
"DROP TABLE "SQLCipher.swift";"
)
}

[jberkel]

OK, this is helpful. Looks like the error code got obscured, 21 is SQLITE_MISUSE, which means an API method gets called with wrong parameters, or from the wrong context.

https://sqlite.org/rescode.html#misuse

And the debugger screenshot seems to indicate that you're calling the db code from another thread than the main thread (Thread 20).

Are you opening the db as a result of async work such as a web request?

For now, to keep things simple I would open the db as early as possible in your application, for instance in the ApplicationDelegate#didFinishLaunchingWithOptions, or in the viewDidLoad method of your view controller.

[leminhtuan2015]

Hi Sir, Good morning. Sir are right. I am calling the database method from async

DispatchQueue.global(qos: .background).async {
  // QUERY
  let db = try Connection("\(Constant.DOCUMENT_PATH)/\(Config.DATABASE_NAME)")
  try db.key("mykey")
...
}

Because i thought when I use background thread for query database so that will not affect my UI.

• Now I remove DispatchQueue.global(qos: .background).async {... and calling the query in main thread but the same error occur again (both when i call query method in ApplicationDelegate#didFinishLaunchingWithOptions, or in the viewDidLoad)

[jberkel]

So it must be something else. One thing you can try is to set a symbolic breakpoint to
sqlite3MisuseError and then run your app.

The debugger should stop where the error occurs.

[leminhtuan2015]

So sorry sir. I add a new symbolic breakpoint to sqlite3MisuseError But debugger not stoping . May sir please TeamViewer help me at weekend. Please!!!

[jberkel]

Where is the database located on the device? Is it in a writable location?
If it's not a writable it might currently fail. I've just pushed a fix for this.

Try changing your Podfile to

pod 'SQLite.swift/SQLCipher', :git => 'https://github.com/stephencelis/SQLite.swift.git'

Run pod install, and try again.

[leminhtuan2015]

Oh WOW. Thank Sir so much. It work great now.

So Could Sir please tell me what did you fix to make it work?

Will Sir update to pod 'SQLite.swift', '~> 0.11.2' ?

Really really...Thank Sir!!!,

[jberkel]

Glad it works now, that was a tricky one :)

The valid key check wrote to the database to make sure the key is correct (it performed a CREATE TABLE / DROP TABLE). In your case this caused an error since your database is not writable (it is probably shipped inside the application package). There's no need to write to the database though, any normal select works the same.

There will be a new release soon, but not sure exactly when.

[leminhtuan2015]

Thank Sir so much. By the way, It is still working great when I query or make connection in background thread.

Please! May I have your email or skype to keep contact. So So thank sir !

[jberkel]

Good to hear! In general, accessing the db from a background thread should work since access to the database is automatically serialized with a queue by SQLite.swift. There are some functions (like key / rekey) which are not serialized, that's why you should be a bit careful.

Drop me a mail if you want: jan (at) berkel (dot) fr.
Good luck with your project!

[leminhtuan2015]

Thank Sir so much!!! Have a nice day!!!

[noufal-kmc]

Hi,

I have just went through the above discussion but I couldn't fix the same issue.

It will be helpful if you have any solution on below :

Issue :

connectionListDB = try Connection(path)
//Encryption
try connectionListDB?.key("secret")

I'm invoking method containing above 2 lines from AppDelegate (tried from both main and global thread) but the app is stay at second line of above codes and show the below error. No crashes, instead app is just stay at Launch screen. If I comment second line for encryption, everything works fine including all operation with SQLite.Swift.

Error log:
[] nw_coretls_read_one_record tls_handshake_process: [-9802]

NSURLSession/NSURLConnection HTTP load failed (kCFStreamErrorDomainSSL, -9802)

Note : I have enabled Allow Arbitrary Loads in my info.list and I also tried by removing this key from plist.

Regards,
Noufal