Migration Old unencrypted Core database to Encrypted Core Data base(using SQLCipher)

Hello Team,

I already have a live app on apple app-store which is using CoreData. Now I want to encrypt my coredata using sqlcipher.
For that I am using EncryptedCoreData (https://github.com/project-imas/encrypted-core-data).
I am successfully able to implement the same with my project and it working fine for fresh installed applications but it is creating issue when I am trying to update my application over the app which is using normal coredata (without encryption).

- (NSPersistentStoreCoordinator *)persistentStoreCoordinator
{
    if (persistentStoreCoordinator_ != nil) {
        return persistentStoreCoordinator_;
    }
    else if (!storeUrl)
        return nil;
    
    NSError *error = nil;
    persistentStoreCoordinator_ = [[NSPersistentStoreCoordinator alloc] initWithManagedObjectModel:self.managedObjectModel];
    NSDictionary *options = @{          EncryptedStorePassphraseKey : @"123456",
                                        EncryptedStoreFileManagerOption : [EncryptedStoreFileManager defaultManager],
                                        NSMigratePersistentStoresAutomaticallyOption : @YES
                                        };

    NSPersistentStore *store = [persistentStoreCoordinator_
                                addPersistentStoreWithType:EncryptedStoreType
                                configuration:nil
                                URL:storeUrl
                                options:options
                                error:&error];
    
    if (!store && error)
    {
        [self encryptDB:[storeUrl absoluteString] url:storeUrl];

        NSError *error1 = nil;
        NSPersistentStore *store1 = [persistentStoreCoordinator_
                                    addPersistentStoreWithType:EncryptedStoreType
                                    configuration:nil
                                    URL:storeUrl
                                    options:options
                                    error:&error1];
    }
    
    return persistentStoreCoordinator_;
}

//encryptDB used for encriptt the existing DB with password
- (void)encryptDB:(NSString*)path_u url:(NSURL*)url
{
    sqlite3 *unencrypted_DB;
    sqlite3 *encrypted_DB;
    NSString *tempFile = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0]
                        stringByAppendingPathComponent:@"encrypted.sqlite"];
    
    NSURL *tempUrl = [NSURL fileURLWithPath:tempFile];

    if (sqlite3_open([path_u UTF8String], &unencrypted_DB) == SQLITE_OK) {
        NSLog(@"Database Opened");
        // Attach empty encrypted database to unencrypted database
        
        NSString *strEncryptedPath = [NSString stringWithFormat:@"ATTACH DATABASE '%@' AS encrypted KEY '123456';",tempFile];
        
        sqlite3_exec(unencrypted_DB, [strEncryptedPath UTF8String], NULL, NULL, NULL);

        // export database
        sqlite3_exec(unencrypted_DB, "SELECT sqlcipher_export('encrypted');", NULL, NULL, NULL);

        // Detach encrypted database
        sqlite3_exec(unencrypted_DB, "DETACH DATABASE encrypted;", NULL, NULL, NULL);
        
        int version = [self queryUserVersion:unencrypted_DB];
        sqlite3_close(unencrypted_DB);
        NSLog (@"End database copying");

        
    if (sqlite3_open([tempFile UTF8String], &encrypted_DB) == SQLITE_OK) {
              const char* key = [@"123456" UTF8String];
              sqlite3_key(encrypted_DB, key, (int)strlen(key));
             [self setVersion:encrypted_DB version:(int)version];
      }
     sqlite3_close(encrypted_DB);
        
        NSError *error = nil;
       [[NSFileManager defaultManager] removeItemAtURL:url error:&error];

         BOOL result = [[NSFileManager defaultManager] moveItemAtURL:tempUrl toURL:url error:&error];
        if(!result)
            NSLog(@"Error: %@", error);
    }
    else {
        sqlite3_close(unencrypted_DB);
        //NSAssert1(NO, @"Failed to open database with message '%s'.", sqlite3_errmsg(unencrypted_DB));
    }
}
-(void)setVersion: (sqlite3*) db version:(int)version {
    
    // get current database version of schema
    static sqlite3_stmt *stmt_version;
    
    if(sqlite3_prepare_v2(db, "PRAGMA user_version;", -1, &stmt_version, NULL) == SQLITE_OK) {
        while(sqlite3_step(stmt_version) == SQLITE_ROW) {
            db = sqlite3_bind_int( stmt_version, 1, version ); // Bind first parameter.
        }
    } else {
        NSLog(@"%s: ERROR Preparing: , %s", __FUNCTION__, sqlite3_errmsg(db) );
    }
    sqlite3_finalize(stmt_version);
}
-(int)queryUserVersion: (sqlite3*) db {
    // get current database version of schema
    static sqlite3_stmt *stmt_version;
    int databaseVersion;

    if(sqlite3_prepare_v2(db, "PRAGMA user_version;", -1, &stmt_version, NULL) == SQLITE_OK) {
        while(sqlite3_step(stmt_version) == SQLITE_ROW) {
            databaseVersion = sqlite3_column_int(stmt_version, 0);
            NSLog(@"%s: version %d", __FUNCTION__, databaseVersion);
        }
        NSLog(@"%s: the databaseVersion is: %d", __FUNCTION__, databaseVersion);
    } else {
        NSLog(@"%s: ERROR Preparing: , %s", __FUNCTION__, sqlite3_errmsg(db) );
    }
    sqlite3_finalize(stmt_version);

    return databaseVersion;
}

Following code I am using for fetch the existing data

NSFetchRequest *request = [[NSFetchRequest alloc] init];
    NSEntityDescription *entity = [NSEntityDescription entityForName:@"HistoryEvent" inManagedObjectContext:dbDataFile.managedObjectContext];
    [request setEntity:entity];
NSMutableArray* andPredicateArray = [[NSMutableArray alloc] init];
    if (guid)
    {
        [andPredicateArray addObject:
         [NSPredicate predicateWithFormat:@"guid = %@", guid]];
    }
    NSPredicate* predicate = [NSCompoundPredicate andPredicateWithSubpredicates:andPredicateArray];
    [request setPredicate:predicate];
    
    [request setFetchLimit:limit];
NSMutableArray* sortDescriptors = [[NSMutableArray alloc] init];
        [sortDescriptors addObject:
         [[NSSortDescriptor alloc] initWithKey:@"creationDate" ascending:[isAscendingCreationDatesNum boolValue]]];
[request setSortDescriptors:sortDescriptors];
    NSError *error = nil;
    NSMutableArray *mutableFetchResults = [[dbDataFile.managedObjectContext executeFetchRequest:request error:&error] mutableCopy];
    if (!mutableFetchResults && errorMessage && error)
    {
		NSLog(@"Error to fetch”); //I am not getting any error here
    }
    
    return mutableFetchResults; //Count is zero

here I am getting empty array.
I don’t know what is the wrong with this code.
Code is working fine If I installed the fresh application and creating the DB.
but when I am trying to override the existing application I am not getting any data.

Hey @Hitesh_Landge

Thanks for your interest in SQLCipher. At least one issue I see with the code you posted is that you’re not using sqlite3_key() on the encrypted database when re-opening it when setting the version:

@mmoore
How I can use sqlite3_key() ?

@mmoore
I also tried by commenting this three lines

sqlite3_open([tempFile UTF8String], &encrypted_DB);
[self setVersion:encrypted_DB version:(int)version];
sqlite3_close(encrypted_DB);

There’s a snippet of example code using sqlite3_key() near the bottom of this page: https://www.zetetic.net/sqlcipher/ios-tutorial/

Are you checking the result codes in the situation where encrypting a plaintext database fails? Do you receive any error messages in the logs?

One other thing I’d like to mention: While we don’t develop/support Encrypted Core Data, you may want to have a look at this GitHub issue which mentions you most likely need to roll your own migration when migrating a database from Core Data to Encrypted Core Data as ECD uses different column prefixes than Core Data: https://github.com/project-imas/encrypted-core-data/issues/293

@mmoore
I tried sqlite3_key() but no luck

if (sqlite3_open([tempFile UTF8String], &encrypted_DB) == SQLITE_OK) {
    const char* key = [@"123456" UTF8String];
    sqlite3_key(encrypted_DB, key, (int)strlen(key));
    [self setVersion:encrypted_DB version:(int)version];
}
sqlite3_close(encrypted_DB);

also created a issue at https://github.com/project-imas/encrypted-core-data/issues/331

@Hitesh_Landge

I would recommend examining the db on disk using a tool like DB Browser for SQLite (using SQLCipher 3 default settings as that’s what Encrypted Core Data uses) to confirm that it is indeed being encrypted properly. If it is being encrypted properly then I suspect the issue lies within the database column/table prefix naming being different as noted in the issue I linked above, which points to you needing to establish your own custom migration (to change the prefix to what Encrypted Core Data is expecting).

@mmoore
Thanks for replay.
I checked my both SQLite file unencrypted & encrypted both looks same.
Both files have same table & column name and same data.

@Hitesh_Landge

That is precisely the problem. Now compare that structure to the database created by Encrypted Core Data when there is no non-encrypted database present. Notice how the table names are prefixed with ecd and the column names aren’t prefixed with Z

@mmoore
Following are my few observation
Case 1:- Installed a fresh iOS application without applying encryption.Structure of the table is like
CREATE TABLE ZMYTABLENAME ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZLINE INTEGER, ZCREATIONDATE TIMESTAMP, ZAPIVERSION VARCHAR, ZEVENTDESCRIPTION VARCHAR, ZFILE VARCHAR )

Case 2:- Override the application using the encrypted database with the same shared code than the structure of the table is same as above like an unencrypted database.(My database is encrypted successfully because it is asking for a password while opening it.)

Case 3:- Now I uninstalled the application and installed a fresh app with encrypted database then structure of the table is like
CREATE TABLE ecdMyTableName ('__objectid' integer primary key, 'creationDate', 'apiVersion', 'eventDescription', 'line', 'file')

Using Case-1 & Case-3, I am able to Read & Write the data
but using Case-2 unable to fetch old data.

@Hitesh_Landge

Correct, this is why a custom migration is required. The table/column structure expected by Encrypted Core Data is different from the default that Core Data uses. This is unrelated to SQLCipher at all. We can’t provide any additional guidance as we don’t support/develop the Encrypted Core Data project.

If you continue further with Encrypted Core Data, making the necessary changes for the support you need, please keep us in the loop!

@mmoore
Thanks for the support.