Close modal

Blog Post

Encrypting SQLite with swift.

Development
Wed 04 April 2018
0 Comments


I'm fully aware that the keychain is the most convenient and accepted method of storing stuff securely on iOS, however sometimes there are reasons you don't want to store stuff there, and storing securely in SQLite is possible without too much headache thanks to some libraries.

Dependencies

Chiefly we will be using the SQLite wrapper for swift and the community open source version of SQLCipher, which actually work together as a plugin module.

First create a project, and use a podfile something like the following:

platform :ios, '11.0'

target 'MY_APP' do
  #Frameworks, yes! Warnings, no - they create noise for our warnings
  use_frameworks!
  inhibit_all_warnings!

  # Pods for MY_APP
  pod 'SQLite.swift/SQLCipher'

end

Run pod init, edit the podfile as above and run pod update.

Interfaces

Let's create some basic protocols and data structures, this is the boiler-plate kind of example you would see anywhere for a list of users with fields: id, name and token (some sort of API or oAuth token perhaps).

struct User {
    var id: Int64
    var name: String
    var token: String?
}

protocol Store {
    func addUser(newName: String, newToken: String?)
    func updateUser(withId: Int64, setName newName: String, setToken newToken: String)
    func deleteUse(withId: Int64)
    func allUsers() -> [User]
}

Now that we have protocols and structures, you should be able to write your unit tests... I'm going to skip over that for brevity and assume you know how to write them and why they are important (see my other articles. Here's an implementation of our data store protocol that securely stores the data using SQLCipher, notice how there's no implementation structures passed in or out of the data store - for all we know it could be stored in volatile memory or even JSON (the virtues of abstraction).

SecureStore implementation

import SQLite

class SecureStore: Store {
    var db: Connection
    let users = Table("users")
    let id = Expression<Int64>("id")
    let name = Expression<String>("name")
    let token = Expression<String?>("token")

    init() throws{
        //Setup the data connection
        let documentsPath = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true)[0]
        db = try Connection("\(documentsPath)/db.sqlite3")
        //Encrypt it
        try db.key("secret")

        //Create the table if it does not exist
        try! db.run(users.create(ifNotExists: true) { t in
            t.column(id, primaryKey: true)
            t.column(name)
            t.column(token, unique: true)
        })

    }

    func addUser(newName: String, newToken: String?) {
        //Insert a new user
        let insert = users.insert(name <- newName, token <- newToken)
        _ = try! db.run(insert)
    }

    func updateUser(withId: Int64, setName newName: String, setToken newToken: String) {
        let theUser = users.filter(id == withId)
        try! db.run(theUser.update(token <- newToken))
        try! db.run(theUser.update(name <- newName))
    }

    func deleteUse(withId: Int64) {
        let theUser = users.filter(id == withId)
        try! db.run(theUser.delete())
    }

    func allUsers() -> [User] {
        var results = [User]()
        if let users = try? db.prepare(users) {
            for user in  users{
                results.append(User(id: user[id], name: user[name], token: user[token]))
            }
        }

        return results
    }

}

As you can see, it's fairly basic, however the points to note are that the magic happens with try db.key("secret") which sets the cnryption key. Remove this line (as we see later) and it just becomes a regular unencrypted SQLite store.

Applied usage

Rather trivially, but important, let's use this:

let store = try! SecureStore()
//Add a user if there are not any
if store.allUsers().count == 0 {
    store.addUser(newName: "Sarah", newToken: "PWR5y-PnpKz-FVSqu-m6S6p")
    store.addUser(newName: "Marcus", newToken: "gd5g4-gd4kS-Gj6Dk-fPdGk")
    store.addUser(newName: "Jane", newToken: "4tYKb-4QfZQ-t9HRm-3pbvzS")
}
for user in store.allUsers() {
    print(user)
}

Unsurprisingly, it will print something like:

User(id: 1, name: "Sarah", token: Optional("PWR5y-PnpKz-FVSqu-m6S6p"))
User(id: 2, name: "Marcus", token: Optional("gd5g4-gd4kS-Gj6Dk-fPdGk"))
User(id: 3, name: "Jane", token: Optional("4tYKb-4QfZQ-t9HRm-3pbvzS"))

Analysis

Why don't you grab the SQL file from the simulator (or device), and try to open it in an SQLite browser. A good one is DB Browser for SQLite

Here's the comparison of running it with try db.key("secret") and without:

  • 4.0K db_crypt.sqlite3
  • 12K db_plain.sqlite3

I think we can ignore the size difference as there's padding of 0 inside the non encrypted version. Fire them up in a hex editor for some fun.

The plain format starts with: SQLite format 3 as expected, but what about the version that has an encryption key set? In this case it is ....|.7~..{...d, looks like gibberish, which is great, because that's what anyone without the encryption key will see too. Using DB Browser AND entering the key you can browser the contents of the encrypted version too.

Summary

Encryption might be hard to master if you have to implement the ciphers and decoding manually, especially when combining it with SQLite, however as demonstrated here there are some very good libraries that have been created by the open source community to make light work of this. Having the ability to define and manage your own file and schema AND have security against tampering or dissemination of the contents (either by non-secure iOS backup or rooted Android devices, etc). This enables you to maintain the security of the data being stored, and thus maintain the integrity of the overall experience for all users.