Attaching Encrypted Database to Unencrypted Database Using SQLCipher

Question

I'm trying to add the contents of an unencrypted sqlite3 database to an encrypted one using SQLCipher. I've based what I am trying to do off of this and this. A few things however remain unclear to me.

1. In line ATTACH DATABASE, does the encrypted database have to be of type .db? Can it be .sqlite to match my original database?

2. Does said encrypted database have to already exist? If so, where should it be in the app? Do I have to provide a path to the file (documents directory, etc)?

3. Where can I find the successfully encrypted database? Where will it be saved?

Here is my code:

+ (void)encryptDB
{
    sqlite3 *unencrypted_DB;
    NSString *path_u = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0] 
                  stringByAppendingPathComponent:@"dict.sqlite"];

    if (sqlite3_open([path_u UTF8String], &unencrypted_DB) == SQLITE_OK) {
        NSLog(@"Database Opened");
        // Attach empty encrypted database to unencrypted database
        sqlite3_exec(unencrypted_DB, "ATTACH DATABASE 'dict_encrypted.sqlite' AS encrypted KEY '1234';", NULL, NULL, NULL);

        // Create new tables within encrypted database to match those in unencrypted database
        sqlite3_exec(unencrypted_DB, "CREATE TABLE encrypted.t1(A,B,C);", NULL, NULL, NULL);

        // Copy items from unencrypted database into encrypted database
        sqlite3_exec(unencrypted_DB, "INSERT INTO encrypted.t1 SELECT * FROM t1;", NULL, NULL, NULL);

        // Detach encrypted database
        sqlite3_exec(unencrypted_DB, "DETACH DATABASE encrypted;", NULL, NULL, NULL);

        NSLog (@"End database copying");
        sqlite3_close(unencrypted_DB);
    }
    else {
        sqlite3_close(unencrypted_DB);
        NSAssert1(NO, @"Failed to open database with message '%s'.", sqlite3_errmsg(unencrypted_DB));
    }
}

Dumpcart

I guess I'm curious as to why your second reference says to use this technique rather than just REKEY to encrypt an existing DB. Our SQLCipher implementations handle REKEY of an existing DB just fine. – Hefner

Answer 1

1. Does the encrypted database have to be of type .db? - No, you can use any extension that you want.
2. Does said encrypted database have to already exist? - No the database does not need to exist already. When you attach a database that doesn't exist it will create a new one
3. Do I have to provide a path to the file (documents directory, etc)? - Yes, you do need to provide the path to the database, otherwise the current working directory for the app will be used. On iOS you should generally provide the full path to the file name including the app document directory.
4. Where can I find the successfully encrypted database? - The encrypted database will be located wherever you tell attach to put it. Per #3, if you are building for iOS, include the full path to the document directory in the file name and then the encrypted database will be located there.

Xanthochroid

Excellent answer. Thank you very much. My other question was should I encrypt the database in a separate program and just add this database to the app that I want to use it? – Dumpcart

Answer 2

With the latest version of the SQLCipher we have sqlcipher_export() function that can make our code shorter and not connected to the existing database structure:

+ (void)encryptDB
{
    sqlite3 *unencrypted_DB;
    NSString *path_u = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0] 
                         stringByAppendingPathComponent:@"dict.sqlite"];

    if (sqlite3_open([path_u UTF8String], &unencrypted_DB) == SQLITE_OK) {
        NSLog(@"Database Opened");
        // Attach empty encrypted database to unencrypted database
        sqlite3_exec(unencrypted_DB, "ATTACH DATABASE 'dict_encrypted.sqlite' AS encrypted KEY '1234';", 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);

        NSLog (@"End database copying");
      sqlite3_close(unencrypted_DB);
    }
    else {
        sqlite3_close(unencrypted_DB);
        NSAssert1(NO, @"Failed to open database with message '%s'.", sqlite3_errmsg(unencrypted_DB));
    }
}

Slier

"ATTACH DATABASE 'dict_encrypted.sqlite' AS encrypted KEY '1234'; the dict_encrypted.sqlite should to replace a path of your Document. – Gourmand

Answer 3

Well, I've never done ATTACH DATABASE, and the version of SQLCiper I've used is likely different from yours, but (for the implementations of SQLCipher I've dealt with), the file open logic is standard SQLite, and behaves the same way as without SQLCipher.

So you should not need a suffix of .db or anything else specific -- you can use whatever you want.

Until you issue a REKEY any new database is perfectly "normal", and will create/delete in the "normal" fashion for SQLite.

Re ATTACH DATABASE, I suspect it would be best to create the DB (and REKEY it) first, then attach it.

Hefner