Databases not keying properly in iOS 10?

[tcoyle]

After building my app in iOS, I have quite a big problem.

I use the following code to create my databases and secure them with a key

if (sqlite3_open([writableDBPath UTF8String], &database) == SQLITE_OK) {

        const char*key = [keyname UTF8String];

        if(sqlite3_exec(database, [[NSString stringWithFormat:@"PRAGMA key = '%@'",keyname] UTF8String], NULL, NULL, NULL) != SQLITE_OK) {
            NSLog(@"Could not key database: %s",sqlite3_errmsg(database));
        }

        if(sqlite3_prepare_v2(database, sql, -1, &createStmt, NULL) == SQLITE_OK){
            sqlite3_step(createStmt);
        }
        
        sqlite3_finalize(createStmt);
        sqlite3_close(database);
        
    } else {
        NSAssert1(0, @"Failed to create the database'%@'.", @"Error");
    }

This code still works on iOS 10 but, it seems the databases is never secured.

I use the following code to check the key of the database to make sure it is correct - on iOS 10 this returns returns successful no matter what key I specify. Any random string works.

sqlite3_stmt *statement = nil;
NSString *test = @"SELECT count(*) FROM sqlite_master";
const char *sql = [test UTF8String];
sqlite3_key(database, key, (int)strlen(key));

if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) != SQLITE_OK) {
    return NO;
} else {
    return YES;
}

As a further test I tried to query my db’s using random strings (not the keys I created them with) - and was able to get unencrypted data back every time. What could be happening here?? This is only on iOS 10 and everything works as expected on both iOS 8 and 9

[sjlombardo]

Hey @tcoyle - We have a few important questions here:

1. How you are integrating SQLCipher, e.g. are you using the commercial edition static library, sqlcipher.xcodeproj, cocoapods, or other?

2. Is the database being created on the iOS device, or are you creating the database separately and only opening it on the iOS device?

3. When you say it is working with iOS 8 and 9, is it the exact same software build, or are you deploying different versions (e.g. built with different versions of X Code)?

4. What version of Xcode are you using to build the app?

5. In the first code block, why are you using PRAGMA key instead of sqlite3_key used in the second code block? Have you tried changing the code that generates the database to use sqlite3_key?

6. Do you have a small reproducible sample app that demonstrates this issue?

[tcoyle]

Hey thanks for the quick reply!

1. I am using sqlcipher.xcodeproj (cocoapods is available?!?)

2. I am creating the database in the app itself

3. Yes same build. If I build and deploy to an ios8 or ios9 device it works properly but deploying to an ios10 device causes this issue

4. Xcode 8 (latest GM build)

5. I tried alternating between PRAGMA and sqlite3_key in both places trying to see if it made a difference that’s all. (It didn’t)

6. I will put one together as soon as I return to my desk.

[sjlombardo]

@tcoyle An example would be very helpful. It would also be great to know what you get when you call PRAGMA cipher_version as well!

[tcoyle]

@sjlombardo

SQLTest.zip (31.6 KB)

Here is a sample project that reproduces the issue - I removed SQLCipher from the zip file because it made the file too large to upload but - I replicated it using a fresh clone from git - as described here: https://www.zetetic.net/sqlcipher/ios-tutorial/

In this project all I am doing is creating a DB with a key, then opening that database and trying to query it with a different key - which it allows. This needs to be tested in the GM build of Xcode 8 pointing to an iOS 10 enabled device/simulator.

Thanks!

[sjlombardo]

@tcoyle Thanks for providing the sample. I have run it on this side. In this example, I believe the problem is that you are constructing the database path in two different ways. In createDatabase you do the following:

NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:[databaseName stringByAppendingString:@".db"]];

which results in a database path like this

/var/mobile/Containers/Data/Application/2965DCDC-BBAB-48AB-BF6F-0CB12D4C0B7D/Documents/TestDatabase.db

However, later in openDatabase the path is constructed like this (note the omission of the trailing.db):

NSString *path = [documentsDirectory stringByAppendingPathComponent:dbName];

This results in an entirely different database path

var/mobile/Containers/Data/Application/2965DCDC-BBAB-48AB-BF6F-0CB12D4C0B7D/Documents/TestDatabase

Thus, the sqlite3_key operation used in verifyKey is actually operating on a database file that doesn’t exist, and thus the prepare statement succeeds (it is essentially creating the file from scratch using that key).

When the reference code is corrected to normalize the way that the database paths are constructed (i.e. so that the openDatabase is operating on the same encrypted database file created by createDatabase) then verifyKey method returns false when passed an incorrect key.

This was verified on a device running the latest iOS 10 seed, compiled using the latest XCode 8.

Can you please look through your application code and determine if this same logical error is also affecting your application?

[tcoyle]

That was indeed the problem in the sample project. However, I am not making the same mistake in my actual project. I will need to investigate even further and get back to you. I haven’t updated it in well over a year so that may be cause for concern. Clearly it works in an empty project with a new build so there must be a cause somewhere on my end.

[tcoyle]

@sjlombardo It appears as though the inclusion of pods that use sqlite is breaking SQLCipher in iOS 10.

I use a number of pods, and included the same pods in my test project and reproduced the issue. I narrowed it down to 2 specific pods, both of which individually cause the issue - and I believe both use SQLite. For example: Google Analytics. Removed either of these pods fixes the issue.

Is there anyway around this?

[sjlombardo]

Hello @tcoyle - thanks for providing additional details here, we’ve been looking into this further. It appears that when the GA pod is installed it is modifying the project file in such a way that the system sqlite3 library is linked before sqlcipher, resulting in the behavior you are seeing. I would like to have you try the following:

1. Open up your XCode workspace for your application, and switch to the Build Settings view for your target
2. Navigate to the Other Linker Flags settings
3. At the Target level, edit the Linker Flags (i.e. double click on it) and add -lsqlcipher before/above $(inherited)

Once this is complete, attempt to rebuild and run. Please let me know if that change to force the linking of sqlcipher first allows your program to function as expected.

[tcoyle]

@sjlombardo after doing as you said, the build now fails with the following error

ld: library not found for -lsqlcipher
clang: error: linker command failed with exit code 1 (use -v to see invocation)

[sjlombardo]

@tcoyle Thanks for trying that. It’s unusual that you are seeing that error. Might I ask:

1. Is that within your application or the SQLTest version you provided earlier in the thread?
2. Are you using a recent version of the sqlcipher.xcodeproj from the SQLCipher repo?
3. Could you provide me with the resolved Other Linker Flags value in your application?

[tcoyle]

@sjlombardo It works in my SQLTest project, but not in my actual project. I can’t seem to figure out why. I installed the same Podfile on my SQLTest project and they are both using the same and latest version of sqlcipher.xcodeproj - not making much sense. I have tried cleaning the project and everything but same linker error overtime - thoughts on possible causes I could investigate?

Here is the other linker flags

-lsqlcipher (inherited) -ObjC -l"AFNetworking" -l"AGWindowView" -l"GGLAnalytics" -l"GGLCore" -l"GSDK_Overload" -l"GTMSessionFetcher_core" -l"GTMSessionFetcher_full" -l"GTMStackTrace" -l"GTM_AddressBook" -l"GTM_DebugUtils" -l"GTM_GTMURLBuilder" -l"GTM_KVO" -l"GTM_NSData+zlib" -l"GTM_NSDictionary+URLArguments" -l"GTM_NSScannerJSON" -l"GTM_NSStringHTML" -l"GTM_NSStringXML" -l"GTM_Regex" -l"GTM_RoundedRectPath" -l"GTM_StringEncoding" -l"GTM_SystemVersion" -l"GTM_UIFont+LineHeight" -l"GTM_core" -l"GTM_iPhone" -l"GoogleAnalytics" -l"MBProgressHUD" -l"Masonry" -l"ProtocolBuffers" -l"RNCryptor" -l"SSKeychain" -l"c++" -l"sqlite3" -l"stdc++" -l"uservoice-iphone-sdk" -l"xml2" -l"z" -framework "AVFoundation" -framework "Accelerate" -framework "AddressBook" -framework "AssetsLibrary" -framework "AudioToolbox" -framework "CFNetwork" -framework "CoreData" -framework "CoreFoundation" -framework "CoreGraphics" -framework "CoreImage" -framework "CoreLocation" -framework "CoreMedia" -framework "CoreMotion" -framework "CoreTelephony" -framework "CoreText" -framework "Crashlytics" -framework "Fabric" -framework "Foundation" -framework "GLKit" -framework "ImageIO" -framework "JavaScriptCore" -framework "MediaPlayer" -framework "MessageUI" -framework "MobileCoreServices" -framework "OpenGLES" -framework "PSPDFKit" -framework "QuartzCore" -framework "QuickLook" -framework "Security" -framework "SystemConfiguration" -framework "UIKit" -force_load (PODS_ROOT)/GoogleUtilities/Libraries/libGTM_NSData+zlib.a

[Steve_Djuroska]

The problem with iOS 10 and SQLite seems to be wider spread as my app (built in Xamarin, using SQLite .NET plugin) stopped working after the iOS 10 upgrade.

[sjlombardo]

@Steve_Djuroska can you provide some additional
Details? Is your app using SQLCipher with SQLite.NET or just the standard SQLite? What is the behavior you are seeing? Does it an existing app that is not work on iOS 10 or is it rebuilt against the latest SDK?

[Steve_Djuroska]

Hi.
I’m not using SQLCipher. It’s a read only database with no sensitive data.
It’s an existing app already on sale that worked fine under iOS 9.xx and earlier.
Now, it stops responding on phones running 10 after firing two successful QueryAsync calls. At the 3rd call the ListView shows up empty, controls not-responding. iOS is not killing it.
I am in the process of updating XCode, macOS and Xamarin. Once done I will test it against the latest SDK to see the logs.

[tcoyle]

The issue I’m experiencing does not happen to existing apps running on iOS 10 that were compiled before iOS 10. Those apps still work fine, it is when I build new apps in Xcode 8 and run them on iOS 10 that I experience my issue. Even still, the apps compiled in Xcode 8 still work properly on iOS 8 and 9.

[sjlombardo]

@tcoyle Could you please try the following at your earliest convenience?

1. Remove the previous target-level configuration mentioned above (i.e. -lsqlcipher)
2. Open up the project build settings (i.e. the global project settings, not the target)
3. Locate the Other Linker Flags settings and add the following $(BUILT_PRODUCTS_DIR)/libsqlcipher.a
4. Navigate down to the Target Build Settings, and verify that the resolved target-level Other Linker Flags shows the path to the libsqlcipher.a library first.
5. Clean the Project, and attempt to rebuild

[tcoyle]

@sjlombardo Hey Stephen

I did what you requested, build still fails but with a new error:

clang: error: no such file or directory: '...../Build/Products/DebugDEV-iphoneos/libsqlcipher.a'

[sjlombardo]

@tcoyle are you sure that you have SQLCipher integrated into that project using the sqlcipher.xcodeproj? In that case, there should be a libsqlcipher.a generated as part of the build process. Could you provide a full build log?

[tcoyle]

@sjlombardo yes followed to specifications. I did the same on my sample project but its working there for some reason. Without the -lsqlcipher it builds fine.

I messaged you the build log.