// FMDB with SQLCipher Tutorial – Guilmo

How To cryptodb

Published on February 20th, 2014 | by Guilherme Mogames

24

FMDB with SQLCipher Tutorial


After long hours of suffering, searching for a simple example on how to get my current SQLite database and encrypt using SQLCipher and FMDB, I was able to work on a code that is simple and it works!

The code below shows how you can get a current SQLite database and encrypt using fmdb + sqlcipher. You can use a database with empty tables, an empty database and even a working database with table and data already stored in them. Please try the solution below and tell me in the comments if it worked for you or if you had any problems with it.

Getting Started

First thing you’ll need and this is very important, is to use Cocoapods to load the correct libraries and configuration. By using Cocoapods, it will install FMDB + SQLCipher in seconds for you.
If you don’t know what Cocoapods is (???), I’ve been meaning to write a simple tutorial, but while that does not happen, check this link

Open your Podfile and add the following line

pod 'FMDB/SQLCipher'

If you already had FMDB in your Podfile, remove the line. This pod already have the FMDB dependency and it will install FMDB automatically

Now run pod update (or pod install if first time installing the pods for your project) in the terminal to install the new pods.
After the pods are installed, open your .xcworkspace

Creating a Copy of the Database File in the Documents Folder

If you already know how to do this or are already doing, you can skip this part as this does not change from the default behavior

In my game, I already had a default database with all the tables that I needed inside my project Resources folder, once I run the app I check to see if the database is in the Documents folder, if it’s not, I copy the file from the Resources folder to the Documents folder.

In the AppDelegate I have a NSString property called databasePath which I use throughout the application and in application:didFinishLaunchingWithOptions method I set this property with my default database path by doing:

- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions
{
...

    NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentDir = [documentPaths objectAtIndex:0];
    self.databasePath = [documentDir stringByAppendingPathComponent:@"gameDefault.sqlite"];
    
    [self createAndCheckDatabase];

...
}

You’ll notice that I also call a separate method createAndCheckDatabase which actually do the verification I told above and create a copy of the database file in case it does not exist in the Documents folder.

-(void) createAndCheckDatabase
{
    BOOL success;
    
    NSFileManager *fileManager = [NSFileManager defaultManager];
    success = [fileManager fileExistsAtPath:self.databasePath];
    
    if(success) return; // If file exists, don't do anything

    // if file does not exist, make a copy of the one in the Resources folder
    NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"gameDefault.sqlite"]; // File path
   
    [fileManager copyItemAtPath:databasePathFromApp toPath:self.databasePath error:nil]; // Make a copy of the file in the Documents folder

}

After you’ve done this, you’ll have your database in the Documents folder where it will be accessible to the application to read/write.

Working the magic of SQLCipher

So, in order for you to have an encrypted database, you’ll need to create a new blank encrypted database and then ATTACH you existing database to it. By attaching, you will have everything that you had in your unencrypted database in the new encrypted one.

Let’s start by importing SQLite to your AppDelegate.m file. At the top of you file add

#import <sqlite3.h>

Now inside of the createAndCheckDatabase method, add the following below what we already have

    // 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"];

Breaking down this code

  1. In the first 3 lines we get the Documents Directory path and set the NSString ecDB to that path with the new database name.
  2. Then we create the SQL Query we’re going to execute first to do the ATTACH. Note that we set 2 parameters in there, 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
  3. We then connect to the unencrypted database and run 3 SQL Queries, the ATTACH, the EXPORT which will export the new encrypted database with your data to the path you chose and finally the DETACH to detach the database from the unencrypted file.
  4. And the last command is the one which will set my AppDelegate databasePath property to the new Encrypted Database we created. In my case this is very important because in my classes, I call AppDelegate to get the path to open the database

This part is now complete and you have your encrypted database. If you go to your Application Documents folder you will now see 2 files, gameDefault.sqlite and encrypted.sqlite.
Keep both there, but make sure you are using the encrypted.sqlite one.

Using FMDB to open Encrypted Database

Now comes the easy part. You should already have all your methods to INSERT, UPDATE, CREATE, etc… in your code, so you should need just to add a single line of code after every time you open the database.

[db setKey:@"secretKey"]

.
I’m going to show you 2 examples, one using FMDatabase and another using FMDatabaseQueue. In the FMDatabase function, you alway call a [db open] method to open your db, and in FMDatabaseQueue you don necessarily open the database directly, you call a queue method that opens the db for you.

If you try to open your encrypted db it’s not going to work because you need your secret key. so add the setKey method after you open your db

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

    // FMDatabaseQueue
    FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:[self getDatabasePath]];
    
    [queue inDatabase:^(FMDatabase *db) {
        [db setKey:@"secretKey"];
        ...
    }];
 

And you’re done! that should convert your existing DB into an encrypted db.

FYI: I tried opening my encrypted database in several sqlite browser programs and none could open because it was encrypted, but the code is able to insert, update, delete, etc… to your db just fine.
So if you’re still in development and need frequent access to your DB for debugging purposes, leave the encryption part to the end.

I really want to know if you’re able to use this and if you have/had any problems, so please drop a comment below once you try it out!

Thanks

.gm.

Tags: , , , , , , , ,


About the Author

Founder of the Guilmo.com website and freelance iOS developer for over 2 years. Creator of one of the most used financial app in Brazil and also creator of the iPhone game FlyingMoo. Over 10 years of experience with IT and over 4 years as a Scrum Master for a large multinational IT company.



24 Responses to FMDB with SQLCipher Tutorial

  1. Idrish Sorathiya says:

    Tried several sqlite browser programs but not able to open database even after adding key. I assume that after entering key I should be able to open db. However, I am able to open it through code with use of key. Do you have any idea regarding this?

  2. tyegah says:

    Hello,

    Thank you so much for sharing this tutorial. This is just what I need right now. I will definitely try this one out and maybe later I’ll tell you the result. Once again thank you. Cheers! 🙂

  3. Idrish, only the code can open the db. After it’s encrypted, no program can decrypt the db to allow you to manage or see the data.
    I had the same problems, but never found a way to do it.

  4. mangesh says:

    After Encdoing I need to use “encrypted.sqlite” ? what about path it will always return ” gameDefault.sqlite” from if(success) return; // If file exists, don’t do anything

    • Guilherme Mogames says:

      Hi There,

      So, in order for the DB to be read, it needs to be in the documents folder. My function just checks if the DB file in the project is already in the Documents folder. If it is, I don’t have to copy, that’s why it if(success) return;

      Thanks!

  5. mahir says:

    not working

    i have a encrypted.sqlite
    i opened other programs with password but follow code not working. please help me

    this part comes true

    self getDatabasePath

    for example /var/mobile/Containers/Data/Application/138534FC-1727-4458-BE50-6CE120E06717/Documents/encrypted.sqlite

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

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

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

    }];

  6. Nguyen says:

    Hi man!

    Great tutorial, i have a question for you, Can we use SQLCipher (FMDB/SQLCipher) with FTS (FMDB/FTS)?

    Thank so much.

  7. Girish says:

    Hi,
    I want Encrypt DB but I have Some different situation.
    I will be adding records in db after installation. At runtime (Network hit – save in database).
    I think what given is by assuming preloaded data. can you guide or any idea how to achieve the same ?

  8. PBhatt says:

    Sorry, Not working for me . encrypted.sqlite gets created , But my app lost all old data . One more thing to add I am not using FMDB to access db , I am using coredata to operate DB using passphrase key which worked fine if installed fresh , Please guide me what can be the possible reason.

  9. David says:

    Hi Girish,

    Good tutorial, thanks for publishing it. Just wanted to know if it is ok to have the secret key hardcoded or assigned to a static var to open the db. Isn’t is vulnerable to a strings pull-out from the binary?

  10. Pranav J dev says:

    Very good tutorial. But i have some problem with this. I am using FMDB and i followed your tutorial and its creating two db. But I can open encrypted.sqlite with SQLiteBrowser. Please help me

  11. Pranav J dev says:

    I am using swift and following is the code i
    var db: COpaquePointer = nil;
    let databasePath = FileUtils.getPath(“app.db”)
    var ecDB = NSSearchPathForDirectoriesInDomains(NSSearchPathDirectory.DocumentDirectory, NSSearchPathDomainMask.UserDomainMask, true)[0].stringByAppendingPathComponent(“encrypted.sqlite”)

    let result = String.fromCString(“ATTACH DATABASE \(ecDB) AS encrypted KEY TaP”)

    if (sqlite3_open(databasePath, &db) == SQLITE_OK) {

    // Attach empty encrypted database to unencrypted database
    sqlite3_exec(db, result!, nil, nil, nil);
    // export database
    sqlite3_exec(db, “SELECT sqlcipher_export(‘encrypted’);”, nil, nil, nil);

    // Detach encrypted database
    sqlite3_exec(db, “DETACH DATABASE encrypted;”, nil, nil, nil);

    sqlite3_close(db);
    }
    else {
    sqlite3_close(db);
    sqlite3_errmsg(db);
    }

  12. saitjr says:

    After encrypt, I use inDatabase is correct, but when I use inTransaction, the console shows that “Unknown error calling sqlite3_step (26: file is encrypted or is not a database) eu”.

    Have you met this error before?

  13. YouSeoung Kim says:

    Hello,

    After updating pod, I got the following error messages:

    Undefined symbols for architecture arm64:
    “_sqlite3_rekey”, referenced from:
    -[FMDatabase rekeyWithData:] in libPods-FMDB.a(FMDatabase-6A561930B34D098C.o)
    “_sqlite3_key”, referenced from:
    -[DSDictSqliteManager createDecryptedDB:] in DSDictSqliteManager.o
    -[FMDatabase setKeyWithData:] in libPods-FMDB.a(FMDatabase-6A561930B34D098C.o)

    And I can’t find the “sqlite3.c” file in my Pods project file.
    This file added in my previous Pod project file.

    Please, help me how to solve this problem!!!

    • YouSeoung,

      You need to add sqlite as a framework just like you would add CoreTelephony or any other framework. Just go to link binaries and search for sqlite3 and you should be good

  14. Sachin says:

    Hi ,

    We are using FMDB with sqlcipher in our iOS app.
    We have a singleton class to access FMDataBaseQueue and this queue exists through the lifecycle of app.
    We have this block of code:
    self.dbQueue inTransaction:^(FMDatabase *db, BOOL *rollback){
    [db setKey:”dummykey”];
    //Do Some DataBase Updates or Queries
    }

    We have more than 100 methods/functions written with similar blocks which are used in different context and to interact with different tables.
    My question here is : Should we make set the key everytime in these blocks , since I believed that when I initialised FMDataBaseQueue , it opened up the DB Connection and gets closed only when this FMDataBaseQueue gets destroyed. So I think we can set the key only once, after I initialise the DataBaseQueue.
    Knowledge me more if am wrong..

    • Hi Sachin,

      Every time you start a transaction that requires opening the database you need to set the key.
      Imagine that your db is a locked safe, right now you want to go grab something out of it, you have to type in your password, open the door and get the item, then you close the door after done. When you need to get something else, you have to repeat the steps.

      The setKey is used to unlock the encryption during that read. Now, if your code keeps the safe open during it’s full session, I imagine you don’t need to set the key every time you call your functions, only once should be enough.

      Let me know what worked for you.

      Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to Top ↑