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

**URL:** <https://discuss.zetetic.net/t/migration-old-unencrypted-core-database-to-encrypted-core-data-base-using-sqlcipher/4560>\
**Category:** SQLCipher\
**Created:** [August 19, 2020, 3:56pm UTC](https://discuss.zetetic.net/t/migration-old-unencrypted-core-database-to-encrypted-core-data-base-using-sqlcipher/4560 "2020-08-19T15:56:22Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![Hitesh\_Landge](https://sea2.discourse-cdn.com/flex016/user_avatar/discuss.zetetic.net/hitesh_landge/32/1448_2.png) [@Hitesh\_Landge](https://discuss.zetetic.net/u/Hitesh_Landge)\
**Post date:** [August 19, 2020, 3:56pm UTC](https://discuss.zetetic.net/t/migration-old-unencrypted-core-database-to-encrypted-core-data-base-using-sqlcipher/4560/1 "2020-08-19T15:56:22Z")

</div>

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](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

```auto
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.

---

<div class="post-metadata">

**Author:** ![mmoore](https://sea2.discourse-cdn.com/flex016/user_avatar/discuss.zetetic.net/mmoore/32/858_2.png) [@mmoore](https://discuss.zetetic.net/u/mmoore)\
**Post date:** [August 19, 2020, 5:14pm UTC](https://discuss.zetetic.net/t/migration-old-unencrypted-core-database-to-encrypted-core-data-base-using-sqlcipher/4560/2 "2020-08-19T17:14:02Z")

</div>

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()](https://www.zetetic.net/sqlcipher/sqlcipher-api/#sqlite3_key) on the encrypted database when re-opening it when setting the version:

> [@Hitesh\_Landge](#):
>
> ```auto
> sqlite3_open([tempFile UTF8String], &encrypted_DB);
> [self setVersion:encrypted_DB version:(int)version];
> sqlite3_close(encrypted_DB);
> 
> ```

---

<div class="post-metadata">

**Author:** ![Hitesh\_Landge](https://sea2.discourse-cdn.com/flex016/user_avatar/discuss.zetetic.net/hitesh_landge/32/1448_2.png) [@Hitesh\_Landge](https://discuss.zetetic.net/u/Hitesh_Landge)\
**Post date:** [August 19, 2020, 6:55pm UTC](https://discuss.zetetic.net/t/migration-old-unencrypted-core-database-to-encrypted-core-data-base-using-sqlcipher/4560/3 "2020-08-19T18:55:59Z")

</div>

@mmoore  
How I can use sqlite3\_key() ?

---

<div class="post-metadata">

**Author:** ![Hitesh\_Landge](https://sea2.discourse-cdn.com/flex016/user_avatar/discuss.zetetic.net/hitesh_landge/32/1448_2.png) [@Hitesh\_Landge](https://discuss.zetetic.net/u/Hitesh_Landge)\
**Post date:** [August 19, 2020, 6:59pm UTC](https://discuss.zetetic.net/t/migration-old-unencrypted-core-database-to-encrypted-core-data-base-using-sqlcipher/4560/4 "2020-08-19T18:59:26Z")

</div>

@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);

---

<div class="post-metadata">

**Author:** ![mmoore](https://sea2.discourse-cdn.com/flex016/user_avatar/discuss.zetetic.net/mmoore/32/858_2.png) [@mmoore](https://discuss.zetetic.net/u/mmoore)\
**Post date:** [August 19, 2020, 7:14pm UTC](https://discuss.zetetic.net/t/migration-old-unencrypted-core-database-to-encrypted-core-data-base-using-sqlcipher/4560/5 "2020-08-19T19:14:27Z")

</div>

> [@Hitesh\_Landge](#):
>
> How I can use sqlite3\_key() ?

There’s a snippet of example code using sqlite3\_key() near the bottom of this page: [SQLCipher Community Edition - iOS and macOS Tutorial - Zetetic](https://www.zetetic.net/sqlcipher/ios-tutorial/)

> [@Hitesh\_Landge](#):
>
> 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);

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

---

<div class="post-metadata">

**Author:** ![mmoore](https://sea2.discourse-cdn.com/flex016/user_avatar/discuss.zetetic.net/mmoore/32/858_2.png) [@mmoore](https://discuss.zetetic.net/u/mmoore)\
**Post date:** [August 19, 2020, 11:08pm UTC](https://discuss.zetetic.net/t/migration-old-unencrypted-core-database-to-encrypted-core-data-base-using-sqlcipher/4560/6 "2020-08-19T23:08:54Z")

</div>

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](https://github.com/project-imas/encrypted-core-data/issues/293)

---

<div class="post-metadata">

**Author:** ![Hitesh\_Landge](https://sea2.discourse-cdn.com/flex016/user_avatar/discuss.zetetic.net/hitesh_landge/32/1448_2.png) [@Hitesh\_Landge](https://discuss.zetetic.net/u/Hitesh_Landge)\
**Post date:** [August 20, 2020, 7:31am UTC](https://discuss.zetetic.net/t/migration-old-unencrypted-core-database-to-encrypted-core-data-base-using-sqlcipher/4560/7 "2020-08-20T07:31:14Z")

</div>

@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](https://github.com/project-imas/encrypted-core-data/issues/331)

---

<div class="post-metadata">

**Author:** ![mmoore](https://sea2.discourse-cdn.com/flex016/user_avatar/discuss.zetetic.net/mmoore/32/858_2.png) [@mmoore](https://discuss.zetetic.net/u/mmoore)\
**Post date:** [August 20, 2020, 2:42pm UTC](https://discuss.zetetic.net/t/migration-old-unencrypted-core-database-to-encrypted-core-data-base-using-sqlcipher/4560/8 "2020-08-20T14:42:16Z")

</div>

@Hitesh_Landge

I would recommend examining the db on disk using a tool like [DB Browser for SQLite](https://sqlitebrowser.org) (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).

---

<div class="post-metadata">

**Author:** ![Hitesh\_Landge](https://sea2.discourse-cdn.com/flex016/user_avatar/discuss.zetetic.net/hitesh_landge/32/1448_2.png) [@Hitesh\_Landge](https://discuss.zetetic.net/u/Hitesh_Landge)\
**Post date:** [August 20, 2020, 3:34pm UTC](https://discuss.zetetic.net/t/migration-old-unencrypted-core-database-to-encrypted-core-data-base-using-sqlcipher/4560/9 "2020-08-20T15:34:29Z")

</div>

@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.

---

<div class="post-metadata">

**Author:** ![mmoore](https://sea2.discourse-cdn.com/flex016/user_avatar/discuss.zetetic.net/mmoore/32/858_2.png) [@mmoore](https://discuss.zetetic.net/u/mmoore)\
**Post date:** [August 20, 2020, 4:31pm UTC](https://discuss.zetetic.net/t/migration-old-unencrypted-core-database-to-encrypted-core-data-base-using-sqlcipher/4560/10 "2020-08-20T16:31:31Z")

</div>

@Hitesh_Landge

> [@Hitesh\_Landge](#):
>
> I checked my both SQLite file **unencrypted** & **encrypted** both looks same.  
> Both files have same table & column name and same data.

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`

---

<div class="post-metadata">

**Author:** ![Hitesh\_Landge](https://sea2.discourse-cdn.com/flex016/user_avatar/discuss.zetetic.net/hitesh_landge/32/1448_2.png) [@Hitesh\_Landge](https://discuss.zetetic.net/u/Hitesh_Landge)\
**Post date:** [August 20, 2020, 4:34pm UTC](https://discuss.zetetic.net/t/migration-old-unencrypted-core-database-to-encrypted-core-data-base-using-sqlcipher/4560/11 "2020-08-20T16:34:27Z")

</div>

@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.

---

<div class="post-metadata">

**Author:** ![mmoore](https://sea2.discourse-cdn.com/flex016/user_avatar/discuss.zetetic.net/mmoore/32/858_2.png) [@mmoore](https://discuss.zetetic.net/u/mmoore)\
**Post date:** [August 20, 2020, 4:49pm UTC](https://discuss.zetetic.net/t/migration-old-unencrypted-core-database-to-encrypted-core-data-base-using-sqlcipher/4560/12 "2020-08-20T16:49:47Z")

</div>

@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!

---

<div class="post-metadata">

**Author:** ![Hitesh\_Landge](https://sea2.discourse-cdn.com/flex016/user_avatar/discuss.zetetic.net/hitesh_landge/32/1448_2.png) [@Hitesh\_Landge](https://discuss.zetetic.net/u/Hitesh_Landge)\
**Post date:** [August 21, 2020, 5:47am UTC](https://discuss.zetetic.net/t/migration-old-unencrypted-core-database-to-encrypted-core-data-base-using-sqlcipher/4560/13 "2020-08-21T05:47:11Z")

</div>

@mmoore  
Thanks for the support.