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.

In line ATTACH DATABASE, does the encrypted database have to be of type .db? Can it be .sqlite to match my original database?
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)?
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));
    }
}

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. — Hot Licks
– Hot Licks, Commented Jan 19, 2012 at 20:47

slava · Accepted Answer · 2012-04-03 16:35:58Z

6

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

answered Apr 3, 2012 at 16:35

slava

3765 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

welsonla Over a year ago

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

Stephen Lombardo · Accepted Answer · 2012-01-20 15:43:51Z

5

Does the encrypted database have to be of type .db? - No, you can use any extension that you want.
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
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.
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.

answered Jan 20, 2012 at 15:43

Stephen Lombardo

1,5238 silver badges7 bronze badges

1 Comment

Kevin_TA Over a year ago

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?

Hot Licks · Accepted Answer · 2012-01-19 20:43:49Z

0

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.

answered Jan 19, 2012 at 20:43

Hot Licks

47.8k19 gold badges96 silver badges156 bronze badges

Collectives™ on Stack Overflow

Attaching Encrypted Database to Unencrypted Database Using SQLCipher

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related