FMDB & SQLCipher not encrypting

Question

In Xcode I am trying to get FMDB to use SQLCipher to encrypt a database. In my project I already have a compiling version of SQLCipher which I already have proved is working via sqlite3 calls. I have a unit test that creates the database and 1 table then inserts a row. Everything works using FMDB except the database is still unencrypted.

-(id)initWithDatabaseFilename:(NSString*)filename{
NSString *databasePath = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0]
                          stringByAppendingPathComponent: filename];

self.databasePath = databasePath;
NSFileManager *fileManager = [NSFileManager defaultManager];
if (![fileManager fileExistsAtPath:databasePath]) {
    database = [FMDatabase databaseWithPath:databasePath];
    [database setLogsErrors:YES];
    [database setTraceExecution:NO];
    BOOL keyCheck = [database setKey:@"B!GSecret"];
    NSLog(@"Database is encrypted: %d",keyCheck);
    NSLog(@"database created");
} else {
    NSLog(@"Didnt need to create database");
}
   [self createTable];

return self;

}

-(void)createTable{

BOOL tableExists = [self.database tableExists:kTASKTableName];

if(!tableExists){
    [database open];
    [database executeUpdate:@"CREATE TABLE TEST(TEST_PK integer primary key autoincrement, TITLE text, OTHERTITLE text, TESTVAL text, COMPLETED integer, CREATION_DATE double)"];

    [database close];
}

}

-(BOOL) addTasks:(NSArray*) tasks{
BOOL insertSuccess = NO;

if([self.databasePath isEqualToString:@""]){
    NSLog(@"Database has not yet been initialized");
}
[database open]; 
for(TESTOBJ *to in tasks){


    [database executeUpdate:@"insert into TEST(TITLE, OTHERTITLE, TESTVAL) VALUES (?,?,?)",
     to.title,to.otherTitle,to.testVal,nil];
}
[database close];

return insertSuccess;

}

Answer 1

Sorted the issue by adding

[database setKey:@"B!GSecret"];

after each database open statement.

Answer 2

Since this is where google usually take people, if anyone is having problems encrypting a alreadt existing unencrypted database, I was able to figure out, after a painful research, how to do it and created a tutorial on:

http://www.guilmo.com/fmdb-with-sqlcipher-tutorial/

But the most important parts are, Opening your existing DB and attaching a new encrypted one. Then setting the key in your FMDB connections.

SQLCipher - Encrypting the database

// Import sqlite3.h in your AppDelegate
#import <sqlite3.h>

// Set the new encrypted database path to be in the Documents Folder
NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentDir = [documentPaths objectAtIndex:0];
NSString *ecDB = [documentDir stringByAppendingPathComponent:@"encrypted.sqlite"];

// SQL Query. NOTE THAT DATABASE IS THE FULL PATH NOT ONLY THE NAME
const char* sqlQ = [[NSString stringWithFormat:@"ATTACH DATABASE '%@' AS encrypted KEY 'secretKey';",ecDB] UTF8String];

sqlite3 *unencrypted_DB;    
if (sqlite3_open([self.databasePath UTF8String], &unencrypted_DB) == SQLITE_OK) {

    // Attach empty encrypted database to unencrypted database
    sqlite3_exec(unencrypted_DB, sqlQ, 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);

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

self.databasePath = [documentDir stringByAppendingPathComponent:@"encrypted.sqlite"];

Note that we set 2 parameters in SQL Query, the DATABASE and the KEY. The DATABASE should be the full path to the encrypted database you want to create, in this case, string ecDB, and the KEY parameter is the key that’s going to be use to ENCRYPT your database, so choose a strong one

Now on your FMDB functions, call [db setKey:@"strongKey"] after every time you open the db.

// FMDatabase Example
FMDatabase *db = [FMDatabase databaseWithPath:[self getDatabasePath]];
[db open];
[db setKey:@"secretKey"];


// FMDatabaseQueue Exmple
FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:[self getDatabasePath]];

[queue inDatabase:^(FMDatabase *db) {
    [db setKey:@"secretKey"];
    ...
}];

Let me know if you have any questions!

Answer 3

In addition to the excellent answer from @gmogames, see below a Swift example showing how to create from scratch an encrypted SQLCipher database, without having to convert an existing unencrypted database.

The trick is to create the file using appropriate sqlite3_open (or sqlite3_open_v2) command, to set the key using sqlite3_key and to populate the database before closing it (adding a table for example will be enough). If no table or content is created before closing the database, the file will be empty (size 0) and unencrypted.

You could then open the newly encrypted database with FMDB and set the key using customerDB.setKey(secretKey).

A common mistake is to try to create an encrypted database from scratch with FMDB and setting its key using customerDB.setKey(secretKey). Such procedure is suitable to open an existing encrypted SQLCipher database, but not to create one. A SQLCipher encrypted database can only be created using sqlite3 commands as below, and then opened with FMDB.

var rc: Int32
var db: OpaquePointer? = nil
var databasePath: String = ""    // Initialize to your database path
var secretKey: String = "Thisisasecretkey"

rc = sqlite3_open_v2((databasePath as NSString).utf8String, &db, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE | SQLITE_OPEN_FULLMUTEX, nil)
if (rc != SQLITE_OK) {
    let errmsg = String(cString: sqlite3_errmsg(db))
    NSLog("Error opening database: \(errmsg)")
    return
}
rc = sqlite3_key(db, secretKey, Int32(secretKey.utf8.count))
if (rc != SQLITE_OK) {
    let errmsg = String(cString: sqlite3_errmsg(db))
    NSLog("Error setting key: \(errmsg)")
}
// create a table to populate database and ensure it is saved in encrypted form
let sql = "CREATE TABLE IF NOT EXISTS TEST_TABLE (ID INTEGER PRIMARY KEY AUTOINCREMENT, INFO_TEXT TEXT default '');"
if (sqlite3_exec(db, sql, nil, nil, nil) != SQLITE_OK) {
   print("error")
}
sqlite3_close(db)
print("encrypted db created")
// Now, we open the newly created encrypted database
customerDB = FMDatabase(path: databasePath)
if customerDB.open() {
    // We set secret key
    customerDB.setKey(secretKey)
    // We perform needed operations on the database using FMDB requests
    // ...
}