Tutorial: iPhone SQLite Encryption With SQLCipher
By 
Mobile devices are notoriously difficult to secure. With a small footprint it is dangerously easy to leave your phone in the back of a taxi, or forget to pick it up from a table after dinner at a restaurant. iPhone and iPod Touch devices are no exception – most applications store their data in an easily readable format. Anyone with access to your device can browse through your personal information. Even worse, since the devices back up to your computer, any knowledgeable person with access to your workstation can read your data as well.
This is balanced by immense convenience. An iPhone makes a perfect digital wallet: a single device with all your important information literally at your fingertips. When we set out to write Strip, our iPhone Password Manager and Data Vault, we wanted to build a system that we could trust to store Passwords, Credit Card Numbers, Passport Identifiers, SSNs and bank account information. Most of the other applications in the App store used undocumented encryption or weaker field level security. We wanted to encrypt EVERY bit of Strip’s data on the device. Naturally the ideal approach would be to have the encryption managed transparently, so Strip didn’t need to know about the details.
Enter SQLCipher, a specialized build of the excellent SQLite database that performs transparent and on-the-fly encryption. Using SQLCipher, an application uses the standard SQLite API to manipulate tables using SQL. Behind the scenes the library silently manages the security aspects, making sure that data pages are encrypted and decrypted as they are written to and read from storage. SQLCipher uses the widely trusted and peer-reviewed OpenSSL library for all cryptographic functions.
SQLite is already the predominant API for persistent data storage on the iPhone so the upside for development is obvious. As a programmer you work with a stable and well documented API. All security concerns are cleanly decoupled from application code and managed by the underlying framework. The framework code of the SQLCipher and OpenSSL projects are both open source, so users can be confident that an application isn’t using insecure or proprietary security code.
Using SQLCipher in an iPhone application is straightforward once you setup your project properly. The easiest way to integrate SQLCipher into an application is to use XCode project references as described here.
Get OpenSSL
SQLCipher relies on OpenSSL for several encryption requirements including the AES-256 algorithm, pseudo random number generation, and PBKDF2 key derivation. OpenSSL isn’t a framework that is usable directly on the iPhone so we will setup our project to build and link against it as a static library.
Navigate to the OpenSSL download page. Download the source file of the latest stable version (0.9.8k as of this writing) and extract it to a folder on your system. Make a note of the source directory path for later.
OpenSSL can be a tricky system to compile properly from source. It’s even more complex when you’re dealing with multiple target architectures, targeting i386 for the simulator but armv6 for a device. Luckily we’ve built a handy XCode project template to make it easy called openssl-xcode. The project actually relies on the OpenSSL configure and make system to build the libraries. However, it automatically detects the appropriate build settings for architecture (i386, ppc, arv6), build tools, and SDK. This makes it ideal for inclusion in an iPhone project. Just git clone or download openssl-xcode from GitHub and move the openssl.xcodeproj file into the OpenSSL source directory.
Get SQLCipher
SQLCipher is a standalone package that includes the entire SQLite source distribution. You can git clone or download the latest version from the SQLCipher repository on GitHub. Again, make note of the source directory path you use for later in the setup process.
XCode Setup
SQLCipher uses project references to manage build dependencies and static linking. In order to allow linking you must setup your XCode environment to use a central build location. We will also need to configure Source Trees OpenSSL and SQLCipher respectively.
- Open XCode, Choose the XCode Menu, and then Preferences.
- On the Building tab, Change the “Place Build Products In” to “Customized location” and pick a location for the build directory.
- Switch to the Source Tree tab.
- Add a SQLCIPHER_SRC that references the path to the SQLCipher sourcecode
- Add a OPENSSL_SRC to reference the path to the OpenSSL sourcecode
Project Setup
Now that we have XCode global preferences setup we will move on to the project configuration.
Open your XCode application project, and click on the top level Project item. Hit option-command-a to add a resource. Navigate to the OpenSSL directory and choose the openssl.xcodeproj to add the reference. When the add resource window comes up make sure that “Copy items” is not checked, and add it to the appropriate target. Repeat the same, this time choose the sqlcipher.xcodeproj file in SQLCipher source directory.
Let’s change the Path Types as a convenience to multi-developer teams. Using paths relative to the Source Trees we defined will allow each developer to place the SQLCipher and OpenSSL in any locations and the build will still work.
- Hit option-i on the sqlcipher.xcodeproj reference and change the Path Type to “Relative to SQLCIPHER_SRC”.
- Hit option-i on the openssl.xcodeproj reference and change the Path Type to “Relative to OPENSSL_SRC”
- Hit option-command-a to add another resource
Now we will add build dependencies to ensure that the SQLCipher is compiled before the application code. Open the Info panel for your Application Target and Choose the General Tab. We will add two dependencies, one for OpenSSL crypto, and one for SQLCipher.
Then switch to the Build tab to add our source paths to the include directories to make relevant header files available. Make sure that the Configuration is set to “All Configurations”. Look for the “Header Search Paths” setting and add references to $(SQLCIPHER_SRC) and $(OPENSSL_SRC). Check “recursive” on both.
Finally, we will tell XCode to link against the built libraries. Expand the sqlcipher and openssl .xcodeproj references and select libsqlcipher.a and libcrypto.a. Drag and drop them on “Link Binary With Libraries” under your application target.
Building SQLCipher
At this point you should be able to build your XCode project without errors. Note that the first time you build your application for a particular architecture (Simulator for instance), it will take much longer than usual. This is because SQLCipher and OpenSSL are compiled from source for the specific architecture. You can keep track of the status under Build Results. Subsequent builds for the same platform will be much quicker since the libraries don’t need to be recompiled.
In Code
Now that you’ve incorporated the SQLCipher library into your project you can start using the system immediately. Telling SQLCipher to encrypt a database is as easy as opening a database and using “PRAGMA key” or the sqlite3_key function.
#import <sqlite3.h>
...
sqlite3 *db;
if (sqlite3_open(@"/path/to/database", &db) == SQLITE_OK) {
sqlite3_exec(db, "PRAGMA key = 'BIGsecret', NULL, NULL, NULL);
if (sqlite3_exec(db, (const char*) "SELECT count(*) FROM sqlite_master;", NULL, NULL, NULL) == SQLITE_OK) {
// password is correct, or, database has been initialized
} else {
// incorrect password!
}
}
The call to sqlite3_key or PRAGMA key should occur as the first operation after opening the database. In most cases SQLCipher uses PBKDF2, a salted and iterated key derivation function, to obtain the encryption key. Alternately, can tell SQLCipher to use a specific binary key in blob notation (note that you must provide exactly 256 bits of key material):
PRAGMA key = "x'2DD29CA851E7B56E4697B0E1F08507293D761A05CE4D1B628663F411A8086D99'";
Once the key is set SQLCipher will automatically encrypt all data in the database! If you don’t set a key then SQLCipher will operate identically to a standard SQLite database.
Note: In the interest of brevity and clarity this section demonstrates the use of the API to set a passphrase and an encryption key using a static value. In a real application the passphrase or key data should be collected from an external source, like a “Secure” UITextField, and then passed to SQLCipher. An application should never hard-code its key, as this would be very easy to crack.
Verify
After your application is wired up to use SQLCipher you should take a quick peek at the resulting data files to make sure everything is in order. An ordinary SQLite database will look something like the following under hexdump. Note that the file type, schema, and data are clearly readable.
new-host-2:sqlcipher sjlombardo$ hexdump -C plaintext.db 00000000 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00 |SQLite format 3.| 00000010 04 00 01 01 00 40 20 20 00 00 00 04 00 00 00 00 |.....@ ........| ... 000003b0 00 00 00 00 24 02 06 17 11 11 01 35 74 61 62 6c |....$......5tabl| 000003c0 65 74 32 74 32 03 43 52 45 41 54 45 20 54 41 42 |et2t2.CREATE TAB| 000003d0 4c 45 20 74 32 28 61 2c 62 29 24 01 06 17 11 11 |LE t2(a,b)$.....| 000003e0 01 35 74 61 62 6c 65 74 31 74 31 02 43 52 45 41 |.5tablet1t1.CREA| 000003f0 54 45 20 54 41 42 4c 45 20 74 31 28 61 2c 62 29 |TE TABLE t1(a,b)| ... 000007d0 00 00 00 14 02 03 01 2d 02 74 77 6f 20 66 6f 72 |.......-.two for| 000007e0 20 74 68 65 20 73 68 6f 77 15 01 03 01 2f 01 6f | the show..../.o| 000007f0 6e 65 20 66 6f 72 20 74 68 65 20 6d 6f 6e 65 79 |ne for the money|
Fire up your SQLCipher application in simulator you can look for your database files under /Users/<username>/Library/Application Support/iPhone Simulator/User/Applications/<HEX app id>/Documents. Try running hexdump on your application database. With SQLCipher you should get output that looks completely random, with no discerning characteristics at all:
new-host-2:Documents sjlombardo$ hexdump -C encrypted.db 00000000 84 d1 36 18 eb b5 82 90 c4 70 0d ee 43 cb 61 87 |.?6.?..?p.?C?a.| 00000010 91 42 3c cd 55 24 ab c6 c4 1d c6 67 b4 e3 96 bb |.B<?U$???.?g??.?| 00000020 8e df fa bc c3 9c 92 8a 4e 40 59 6f b5 95 f3 80 |.????...N@Yo?.?.| ... 00000bd0 91 16 9e 89 d9 4e ac f7 1c c9 d1 d7 aa bb a7 51 |....?N??.?????Q| 00000be0 dc 77 5c 6c de c6 d3 be 43 49 48 3e f3 02 94 a9 |?wl???CIH>?..?| 00000bf0 8e 99 ee 28 23 43 ab a4 97 cd 63 42 8a 8e 7c c6 |..?(#C??.?cB..|?| 00000c00
Conclusion
SQLCipher is an easy way to incorporate full database encryption into an iPhone application. For more information on SQLCipher and the underlying security features please check out the SQLCipher project site. To see it in action please try Strip and Strip Lite in the iTunes App Store!
Why did you decide to use OpenSSL instead of the system crypto APIs? As you point out, OpenSSL is hard to build, and it’s also pretty large, adding to the app binary size. I don’t think there’s anything in it you’d need that isn’t already in CommonCrypto, CommonDigest or Security.framework.
The issue of key management deserves some mention. The data is encrypted, but now there’s a key to keep safe. It’s tempting but insecure to hardcode the key into the app’s binary (it just takes one hacker to dig through the app to find it and publish it, and all your security is gone.)
At the other extreme, asking the user for a passphrase on every launch is theoretically very safe, except that users are very bad at choosing secure passwords, and even worse when they have to type them in on an onscreen keyboard whenever they use the app. The temptation would be to use just four or five letters, which is very easy to break.
The best solution might be to have the app make up a random key, and then store it in the system keychain. It can then get the key back without user intervention, and the keychain is designed to be resistant to attack. (It is, apparently, encrypted with a hardware key built into the CPU.)
A simple API for storing keys in the keychain is Buzz Andersen’s utility library: https://github.com/ldandersen/scifihifi-iphone/tree/master/security
Hi Jens, thanks for taking the time to leave such detailed thoughts and feedback in your comments – I’ll try my best to address your points.
Our original goal for SQLCipher was not to write an iPhone or Mac specific library, dispite the fact that Strip is an iPhone application. In fact much of SQLCipher’s early stage development was done on Linux. We chose OpenSSL’s crypto framework for it’s broad platform support and widespread availability. For this reason it’s possible for SQLCipher to be used on Mac OS X, Linux, Windows, or other systems just as with the iPhone. On the many platforms where OpenSSL is already available SQLCipher can be dynamically linked. Static linking is a particular requirement for the iPhone. While it does increases the size of the application binary, using CommonCrypto or Security.framework would sacrifice the ease of use on other platforms.
That said, we are definitely interested in minimizing application size. We have some work ongoing now to reduce size by excluding unused algorithms at build time. We’d also like to factor out the dependency on OpenSSL eventually, which would open the door to Apple specific variants built using CommonCrypto. The increase in application size is a trade off for the time being though.
Key management is an interesting topic as well. I completely agree that hardcoding a key in a binary is completely unsuitable for anything but rudimentary obfuscation of data. Therefore SQLCipher supports two methods of dynamic key management.
When initialized with a passphrase SQLCipher uses PBKDF2 to strengthen and derive the encryption key. The salt is uniquely generated for each database. This means that the same passphrase will generate two distinct keys when used with two different databases. The intent of PBKDF2 is to provide elevated resistance to brute force and dictionary attacks against weak passwords. Of course there is still no substitute for a strong password!
SQLCipher also provides the ability for an application to provide a raw encryption key directly. This mode is perfectly compatible with your suggestion to use the Keychain to store a random encryption key. The application author would only need to write the Keychain specific code to extract the key, and then pass it into SQLCipher where it would be used directly.
Very nice write-up and timely too.
But an issue to keep in mind is that — as already announced — Core Data is coming to iPhone. It’s very handy technology that makes it easy and efficient to write database-backed apps. But it’s also likely to be linked to the standard SQLite libraries. Add-ons like encryption or full-text searching may not work with it (at least out-of-the-box).
Something to keep in mind when choosing what technology to go with.
Hi Ramin,
You’re absolutely correct – using SQLCipher and foregoing use of Core Data can incur a significant overhead for a developer. It’s not the hardest thing to do, of course, but I really wish we had Core Data available to us for Strip and our other SQLCipher-based apps.
I’ve been actively working on figuring out what’s possible with extending or plugging into Core Data over the last few days, to see if it can be used with SQLCipher, and we’re still not convinced that it can be done, it looks like Core Data is still loading the in-house dynamic sqlite libs. We’re trying to think of ways to get Core Data to use our static lib, and we’re open to suggestions.
Being able to get Core Data to link against a modified version of SQLite would enable things like FTS, for sure, but it’s not enough when it comes to SQLCipher. Because SQLCipher needs to initialize and key (or re-key) the database used by Core Data, we’d need to open up the private SQLite storage class (let’s guess that it’s NSSQLiteStore or something) and modify the methods where it initializes and opens the database to accept and use a key.
Unfortunately, the only part of the NSPersistentStore API that’s really useful for rolling your own storage is the NSAtomicStore. NSSQLiteStore (or whatever it’s called) is not public and so over-riding some of its methods or extending it with a category could be done, but wouldn’t be reliable over time. It may or may not get your app bounced from the App Store, too. Not sure about that, curious if anybody knows the answer (anecdotes are a reasonable substitute).
I did look into creating a flat-file alternative as Dan suggested in the podcast (using NSAtomicStore), and I took the CustomAtomicStoreSubclass example project and was able to add encryption hooks into it. This, however, could be particularly unsuitable for use in iPhone applications depending on the size of the application’s object graph, because an atomic store slurps the whole data store into memory at once, and writes out the entire store and all changes at once (typically on app start and app close). For many applications this may be just fine. Once I have this cleaned up I’ll post it to our blog over on zetetic.net.
That’s actually why the SQLite capability of Core Data is so game-changing – it’s the only Core Data store that provides for record-by-record management of the data store. It’s actually surprising that 1) it’s the only one and 2) it’s a private API.
In summary, for right now we’re out of luck using SQLCipher with Core Data unless we hook into private Core Data APIs, which we’ll be looking into.
It’s important to mention that SQLCipher vs Core Data isn’t an apples-to-apples comparison because Core Data doesn’t directly address security. If your application requires enhanced security and encryption then the available Core Data providers would be unsuitable anyway.
I suspect that using SQLCipher with the lower level SQLite API and a few model abstractions would be a quicker and less complex approach than attempting to bolt field level encryption and key management onto objects at a level above CoreData.
Of course, if some of the work Billy mentioned bears fruit we may have the best of both worlds in the future!
Thanks for this tutorial very easy to follow! I am however running into problems at the point at which you say you can compile now and it should be good.
I compile and it gives me a bunch of errors. here’s some examples.
setenv YACC /Developer/usr/bin/yacc
/bin/sh -c “”/Users/kevinh/Documents/iPhone Development/build/openssl.build/Debug-iphoneos/make.build/Script-9069D13B0FCE35730042E34C.sh””
make: *** No rule to make target `clean’. Stop.
/Users/kevinh/Documents/iPhone Development/build/openssl.build/Debug-iphoneos/make.build/Script-9069D13B0FCE35730042E34C.sh: line 7: ./config: No such file or directory
make: *** No targets specified and no makefile found. Stop.
usage: cp [-R [-H | -L | -P]] [-f | -i | -n] [-pvX] source_file target_file
cp [-R [-H | -L | -P]] [-f | -i | -n] [-pvX] source_file … target_directory
That is during compiling of openssl
SQLcipher gave a lot of warnings and errors as well. I do think I have performed every step listed here. I’ve been through it a few times.
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c:10208:25: error: openssl/evp.h: No such file or directory
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c:10209:26: error: openssl/rand.h: No such file or directory
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c:10210:26: error: openssl/hmac.h: No such file or directory
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c:10927: error: syntax error before ‘EVP_CIPHER’
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c:10927: warning: no semicolon at end of struct or union
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c:10934: error: syntax error before ‘}’ token
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c:10934: warning: type defaults to ‘int’ in declaration of ‘cipher_ctx’
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c:10934: warning: data definition has no type or storage class
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c:10942: error: syntax error before ‘cipher_ctx’
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c:10942: warning: no semicolon at end of struct or union
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c:10943: warning: type defaults to ‘int’ in declaration of ‘write_ctx’
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c:10943: warning: data definition has no type or storage class
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c:10944: error: syntax error before ‘}’ token
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c:10944: warning: type defaults to ‘int’ in declaration of ‘codec_ctx’
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c:10944: warning: data definition has no type or storage class
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c:10983: error: syntax error before ‘*’ token
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c: In function ‘cipher_ctx_set_pass’:
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c:10984: error: ‘ctx’ undeclared (first use in this function)
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c:10984: error: (Each undeclared identifier is reported only once
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c:10984: error: for each function it appears in.)
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c:10985: error: ‘nKey’ undeclared (first use in this function)
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c:10986: error: ‘zKey’ undeclared (first use in this function)
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c: At top level:
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c:11002: error: syntax error before ‘*’ token
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c: In function ‘cipher_ctx_init’:
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c:11003: error: ‘iCtx’ undeclared (first use in this function)
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c:11004: error: ‘ctx’ undeclared (first use in this function)
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c:11007: error: ‘EVP_MAX_KEY_LENGTH’ undeclared (first use in this function)
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c: At top level:
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c:11015: error: syntax error before ‘*’ token
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c: In function ‘cipher_ctx_free’:
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c:11016: error: ‘ctx’ undeclared (first use in this function)
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c:11016: error: ‘iCtx’ undeclared (first use in this function)
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c: At top level:
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c:11031: error: syntax error before ‘*’ token
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c: In function ‘cipher_ctx_copy’:
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c:11032: error: ‘target’ undeclared (first use in this function)
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c:11035: error: ‘source’ undeclared (first use in this function)
/Users/kevinh/Downloads/sjlombardo-sqlcipher-0acb34a113d953af42db98e310d63b9c3ce147b3/sqlite3.c:11038: error: ‘EVP_MAX_KEY_LENGTH’ undeclared (first use in this function)
any help would be appreciated. im currently using sqlite in my project and would love to get encryption working as well.
thank you!
Hi,
I’ve been playing a bit with this project and it works flawlessly. It’s a little bit messy to set up, but once is there it works as a charm! Thanks for it!
One easy question. I am a little bit surprised that the indexes of my DB are being used and operations perform really fast. Are indexes encrypted at all?
Another easy one: is decrypted data written to disk at any point or it only stays in memory?
Again many thanks!!
@Kevin – We spoke about this offline in email, but to summarize the OpenSSL compiler errors were caused importing the openssl.xcode project from the wrong location. For folks reading along, just make sure that you add the openssl.xcode project from within the OpenSSL source directory (the one containing the OpenSSL distribution files). This will ensure that the build scripts, source code, and include files are all available.
@Jose – I’m glad it’s working well for you. It’s cool to hear you noticed the speed, too! All of the indexes and data are encrypted, but SQLCipher is still pretty fast. We estimate an impact of about 15-30% above normal SQLite for most operations, often less if indexes are used. We’ll be releasing a performance test app soon with more statistics soon.
Decrypted data is only stored in memory. As pages are read from disk they are decrypted and stored in page cache. The pages are then encrypted before they are written back to disk. The benefit of this approach is that it can still takes advantage of SQLite’s page cache to improve performance.
I ran into similar linking errors recently, running through the process again for another project. Basically, I had missed a step, although at some point after I had all the settings right it still wouldn’t build because I needed to run a Clean Build. This build process *does* work, you probably missed a step.
Common mistakes for me, having done this a few times:
* Setting a project reference to openssl-xcode.proj inside the openssl-xcode project, instead of inside the OpenSSL source dir.
* Forgetting to set the relative path types for the source trees.
* Building the SQLCipher dependency before building the OpenSSL dependency.
@Stephen,
amazing piece of code mate. Fast and secure! I’m actually quite impressed with the speed. It’s difficult to appreciate the difference of encrypted vs raw and I’m using quite a large db. The only speed reduction which seems notable is actually random access (SELECT * FROM WORDS WHERE ROWID=?1). I guess this is probably because it require intense paging. Do you know of any way to speed this up, maybe using a different sql sentence?
Again cheers a lot for sqlcipher!
Jose
PS: By the way, for sorting out header files location use OTHER_CFLAGS in project settings and add “-I/path/to/openssl/include”. That did the trick for me.
For reference, developers can file problems with SQLCipher using the Issues page for the project over on Github. We’re using Github to handle any patching, contributions, and issue-tracking.
@Jose You are absolutely correct that the query is causing a full scan. In an SQLite Database rowid is not indexed. That kind of search without an indexe will be much slower because SQLCipher can’t be selective about which pages it needs to read and decrypt.
The best approach is to ensure sure your tables are created with an INTEGER PRIMARY KEY column, or some other unique key, along with an index. If a similar search were performed based on a unique index you’d probably only expect a 5-10% impact with SQLCipher. If you give that a try I’d be curious to see what kind of performance you see!
@Stephen I tried using a manually indexed integer column (I always thought rowid was indexed in the first place) and the results are similar. After a little bit of debugging, I found out that the time is not spent searching but on the first call to sqlite3_prepare_v2, which takes over 2 seconds. I guess that it is in this call that the whole DB is getting decrypted… Do you decrypt the whole DB into memory on the first call to prepare_v2? Any ideas on how to try reducing this ‘prepare’ time?
Cheers!
@Jose SQLCipher doesn’t read the whole database into memory because that would make it unsuitable for reduced memory environments like the iPhone. Rather, I think the 2 second performance hit you’re seeing on the first operation is related to key derivation.
SQLCipher uses PBKDF2 to derive an encryption key from a passphrase. PBKDF2 strengthens a key and makes it more resistant to dictionary attacks by combining a salt (uniquely generated per database), with the passphrase and then running it through multiple hash iterations. The intent of the iterations is to slow key derivation so it becomes more expensive to run a dictionary attack. By default SQLCipher uses 4000 iterations. This takes 1.5 – 2 seconds on the iPhone 3G’s hardware. The delay manifests as a startup cost for the first database operation while the key is initialized.
The impact of key derivation should be acceptable for most uses as part of the application login or startup time. However, if you’d like to eliminate it you can pass SQLCipher raw key data, like
PRAGMA key = "x'2DD29CA851E7B56E4697B0E1F08507293D761A05CE4D1B628663F411A8086D99'";. To speed up the key derivation you can reduce the default number of iterations at compile time by changing the PBKDF2_ITER define. Finally, the latest version of the codebase in GitHub also has experimental support for run time definition of iterations viaPRAGMA kdf_iter = 1000;, when run immediately after the key is set and before the first database operation.@Stephen Thanks mate!! You were absolutely right. Using a raw key got rid of the initial delay. Now the app launches at full speed!!
Thanks a lot! Awesome project!
I just wanted to post an update about my experiences with this library. The Zetetic support team has been fantastic helping me get the library up and running in my iphone app. There were a few hiccups albeit they were my fault and not the libraries.
It is working wonderfully now and I am very impressed with it. Highly recommend this to anybody considering an iphone app or game. It’s seamless and the only draw back is you can not easily “browse” your database files with SQLite Database Browser apps if they are encrypted.
I tend to work the kinks out with unencrypted database files, then i’ll enabled encryption before it’s pushed live.
Cheers to the team and specificaly Stephen for all his assistance. Keep up the good work guys!
> It’s seamless and the only draw back is you can not easily “browse” your database files with SQLite Database Browser apps if they are encrypted.
Perhaps they can be patched? 😉 Thanks all the kind words, Kevin, we appreciate it. Glad to hear things are going smoothly.
I have a strange problem that i am hoping someone else had it and may help out. The instructions above are perfect, the build is ok from the first time with no errors (except that single unused “err” variable warning inside sqlite3.c)
The problem when using the PRAGMA Key for the first time to encrypt the DB (immediately after the first open of the DB) it does not encrypt the database, in fact, it continue as if the DB was encrypted before and i did not pass the correct key because subsequent calls or statements does not return SQLITE_OK, when commenting out the PRAGRA key, the database works fine and select statements return records.
My understanding of the above is that the first time use of the PRAGMA will encrypt the database if it was not encrypted. I went through a lot of checking just to make sure everything ok, i did the above again also to make sure, the build always ok. My procedure is to copy the DB from the bundle to the Documents so its writable, and then open it and exec PRAGMA then after the PRAGMA, select statement to populate a table.
Here are some few points regarding my env:
1) Using XCode 3.1
2) Developing an iPhone 3.0 ONLY (so the SDK base is 3.0 and not 2.2.1)
3) unlike the comment above regarding the extra time for build, it takes long time to build every time i change from SIM to DEVICE, even though the previous time while i was in SIM it took longer time and then subsequent build are fine with no time, however, changing to DEVICE will make it take a long time again for the first time. I am not sure if this is relevant but thought to mention it anyway
4) i tried both Open and Open_v2, same result
5) I had a large database initially (over 7MB with over 200 tables) then when changed back to a small test DB with single table, 3 records. Same thing
6) I did not add the standard sqlite3 lib and still i get no errors regarding the commands so this shows the SQLcipher is linked correctly
7) when i try PRAGMA rekey = the simulator app crashes on that call, this is relevant report generated:
0 0x000f00b3 EVP_CIPHER_key_length + 9
1 0x0000bf1b codec_set_cipher_name + 141 (sqlite3.c:11193)
2 0x0000c481 sqlite3CodecAttach + 506 (sqlite3.c:11318)
3 0x0000c664 sqlite3_rekey + 145 (sqlite3.c:11382)
4 0x0005f027 sqlite3Pragma + 13530 (sqlite3.c:74031)
5 0x00077f49 yy_reduce + 12063 (sqlite3.c:88570)
6 0x00078943 sqlite3Parser + 222 (sqlite3.c:88893)
7 0x000798d2 sqlite3RunParser + 701 (sqlite3.c:89715)
8 0x00060238 sqlite3Prepare + 511 (sqlite3.c:74704)
9 0x000606b9 sqlite3LockAndPrepare + 116 (sqlite3.c:74786)
10 0x000607f0 sqlite3_prepare + 59 (sqlite3.c:74845)
11 0x0005aaea sqlite3_exec + 163 (sqlite3.c:71584)
8) When using REKY with no initial key (as mentioned here:
https://github.com/sjlombardo/sqlcipher/tree/master
DB continue normal and operates ok but it not encrypted.
Any help will be appreciated.
@Jim – Thanks for trying out SQLCipher, and sorry for the delayed response. The first issue you’re running into is that PRAGMA key can’t be used to encrypt an existing database. PRAGMA key, and it’s sqlite3_key function counterpart, are used to prepare the key information in the database handle. If you want to encrypt a database with a key you’ll need to run it as the first operation on a new database.
When you run PRAGMA key on an existing plaintext SQLite database, SQLCipher will still treat the database as if it were encrypted. This will include reading salt from the first 16 bytes of the database file, deriving the provided key and attempting to decrypt pages as they are read from storage. Of course, since the data is plain text the decryption options will fail.
The second issue is an oversight in the documentation on my part for which I apologize. Rekeying a standard database is not supported at this time. The upstream SQLite source introduced a regression several versions ago that prevented the reserved page size from being modified during a VACUUM operation. SQLCipher uses CBC mode encryption by default and requires a reserved data segment at the end of each page to store the initialization vector. Because we can’t modify the reserve size of an database we can’t encrypt a pre-existing plaintext database using the rekey operation. Unfortunately I didn’t update the README to reflect that rekey on an existing database is unsupported. I’ve made the change now.
As for your options moving forward, my recommendation would be to start off with an encrypted database from the onset if at all possible. When you first open the database run PRAGMA key and any further writes will be fully encrypted as you desire. As the comments in this thread have show there is a really small overhead.
If you can’t start off using encryption from the start you could take a standard database, ATTACH an encrypted DB, and then copy your tables and data between the two. This is effectively what a rekey operation on an unencrypted database would do anyway.
Finally, we have a potential workaround under development that would allow rekeying of a standard database. However, we’re still working on ironing out some kinks and optimizing it. I can’t commit to an exact timeframe but we will be adding support in the future.
If you have any further questions or comments we can take the discussion over to the GitHub issue you opened. Thanks!
Thanx Stephan,
Issue resolved and i also closed the issue at GitHub. I followed your suggestion and we created a new encrypted DB and copied the data over.
I guess all the other users tried to create new DB directly or tried with existing one and when failed, tried with new DB and worked as none of them left any comment that it does not work on existing DB as mentioned.
It never crossed our minds to try with a new db as we have a 7MB existing db with 200 tables that we needed to encrypt so we kept trying to encrypt an existing db.
great package with great support. Thanx again.
Any thoughts on encryption/decryption of sqlite for Core Data at app start/finish, before loading the ? Granted it’s far from ideal and wouldn’t keep out determined individuals, but it would hinder to easiest access – unzipping the .ipa from your desktop’s iTunes folder.
This does prove problematic if your app crashes and doesn’t have time to properly delete the decrypted database. Just planting seeds. Maybe someone has a better plan for the interim.
@Greg – Core Data is a challenging subject. It would definitely be possible to wire up an application to decrypt an SQLite database before handing it off to Core Data, and then re-encrypt it after the application closes. The downside is that it will leave the data completely unencrypted while the app is open. In addition, it would result in the unecrypted data being written to flash, where it could remain for some time unless securely overwritten. Finally, running a full decrypt / encrypt cycle would slow down application start time with mid-to-large sized databases.
If your database are small, using a custom NSAtomicStore might work as @Billy recommends earlier in the comments. However, this is probably unsuitable for large databases.
If that won’t work out then it really comes down to a development compromise between convenience of the Core Data API vs. the security of using a package like SQLCipher with direct access to the SQLite API. In the case of SQLCipher access some well-composed model abstractions make development a lot easier.
If you really feel strongly about wanting to see SQLCipher work with Core Data, please consider voting for this over at the Unofficial SDK feedback project – it would provide the best of both worlds:
https://sdkfeedback.mobileorchard.com/pages/21612-iphone-sdk/suggestions/235403-allow-use-of-core-data-with-custom-sqlite-builds-open-sqlite-data-store-api
I have the same issue as @Jim did i.e. have an existing database of about 10MB. I was wondering about the process of migrating the data to an encrypted DB? is there a tool for it or is it done programatically?
Furthermore, I uses Lita or SQLite Manager (a Firefox plug in) to browse my DB, is there a way that these tool work after the encryption? I see there is an option in Lita for (Re)encrypt DB which says it uses SimpleEncryptionKeyGenerator class??
I have the same problem too, I also have an existing database that I am trying to encrypt so that it can be used on the iPhone.
Any ideas anyone?
p.s. brilliant tutorial! 🙂
@Jim, @Yasir, @Ambrose – We’ve pushed an experimental branch up to the SQLCipher repository that supports rekeying plaintext databases. It allows you to open up the plaintext database and run “PRAGMA rekey=’passphrase’;” or call sqlite3_rekey() to encrypt it. The code is up under the “rekey-plaintext” branch in GitHub, check it out here:
https://github.com/sjlombardo/sqlcipher/tree/rekey-plaintext
If you have any feedback or run into trouble let us know in GitHub issues (https://github.com/sjlombardo/sqlcipher/issues), or on the recently setup mailing list (https://groups.google.com/group/sqlcipher).
ATTENTION Ye SQLCipher Developers:
We just started a mailing list to help disseminate answers to the questions we get asked. Please sign up for news & updates, to share tips with other developers, and to ask for help!
@Yasir, @Ambrose, would you mind reposting your questions over on SQLCipher Users?
To answer your questions briefly:
The easiest way to migrate an existing SQLite DB to SQLCipher is probably to start a new DB in SQLCipher (remember to set the key first), ATTACH the existing DB, then copy your tables over. Not pretty, I know, but you can’t key an existing database.
To use SQLCipher with any kind of manager software would require you to do some hacking on the software to prompt you for a key (or look in some keychain for it), and then issue the sqlite3_key method to unlock the DB before working with it.
I’ve changed two methods to fix a bug. If you try to save accents you will see there a problem when you read the data. With these codes it fix this problem :
static int singleRowCallback(void *queryValuesVP, int columnCount, char **values, char **columnNames) {
NSMutableDictionary *queryValues = (NSMutableDictionary *)queryValuesVP;
int i;
for(i=0; i<columnCount; i++) {
[queryValues setObject:values[i] ? [NSString stringWithUTF8String:values[i]] : [NSNull null]
forKey:[NSString stringWithFormat:@"%s", columnNames[i]]];
}
return 0;
}
static int multipleRowCallback(void *queryValuesVP, int columnCount, char **values, char **columnNames) {
NSMutableArray *queryValues = (NSMutableArray *)queryValuesVP;
NSMutableDictionary *individualQueryValues = [NSMutableDictionary dictionary];
int i;
for(i=0; i<columnCount; i++) {
[individualQueryValues setObject:values[i] ? [NSString stringWithUTF8String:values[i]] : [NSNull null]
forKey:[NSString stringWithFormat:@"%s", columnNames[i]]];
}
[queryValues addObject:[NSDictionary dictionaryWithDictionary:individualQueryValues]];
return 0;
}
Another thing, the base is opened and closed for each request. If you want to improve the speed why don’t create an singleton ? I’ve tried it’s working.
@Oliver – thanks for the checking out SQLCipher! I’m not entirely sure what changes you are pointing out with that posted code. SQLCipher is a low level interface – that code looks like it’s straight from an application. SQLCipher has no trouble storing or retrieving accented characters. UTF8 is the default encoding, just like standard SQLite. There isn’t anything different between the API code for SQLCipher and the SQLite library, save for the encryption functions.
As for your second post about a singleton: as a data access framework, SQLCipher doesn’t impose any restrictions on how it’s used. If an application insists on opening and closing the database for each call, it is free to do so. I’d agree that is a poor design, given the startup overhead of opening the file, deriving the key, and parsing the schema. It would be much better for an application to hold open a single database handle for it’s entire lifetime and close it in applicationWillTerminate. That is the way I’d imagine most database-enabled applications should work.
If you have any other questions, please don’t hesitate to join and post to the SQLCipher mailing list.
Hi Guys,
I am child to iPhone development .Its a Great tutorial.I used this for encrypting the database of the my iPhone App. But it didnt get encrypted .I am Stuck in this issue for the whole day. Can any one help.?
Your Step you mentioned in tutorial:
” Open the Info panel for your Application Target and Choose the General Tab. We will add two dependencies, one for OpenSSL crypto, and one for SQLCipher.”
When i am doing this . i didnt see the green rigth mark in crypto. i dont know whats the reason.
How to set the “pragma key” . in your code you Quotes(“) at one end but its not closed.
Brilliant explanation, having trouble with the Pragma statement which I will sort out. My question is, do you have to declare this as encryption to Apple when submitting an app?
If so, how long does it take to get a certificate?
@Jeremy – Yes, you would need to declare this as encryption when you submit to Apple if you wish to export it outside of the US and Canada. I should point out, however, that you must declare and have mass market certification no matter what libraries you use. This is direct from Apple’s legal team: you must have CCATS certification if you are using any data encryption, including the iPhone’s internal libraries, CommonCrypto, the iPhone Keychain, or 3rd party libraries like SQLCipher and OpenSSL.
The classification process will take you between 30 and 50 days. If you’re interested in details I’d really suggest you take a look at our practical guide to the process that includes stepwise instructions and document templates:
https://www.zetetic.net/blog/2009/08/03/mass-market-encryption-commodity-classification-for-iphone-applications-in-8-easy-steps/
Hello Stephen
Great tutorial.
I am using this library for encrypting the DB for my iPhone app.I am having some linker errors.
“_sqlite3CodecGetKey”, referenced from:
_attachFunc in libsqlcipher.a(sqlite3.o)
_sqlite3RunVacuum in libsqlcipher.a(sqlite3.o)
“_sqlite3_activate_see”, referenced from:
_sqlite3Pragma in libsqlcipher.a(sqlite3.o)
“_sqlite3CodecAttach”, referenced from:
_attachFunc in libsqlcipher.a(sqlite3.o)
_attachFunc in libsqlcipher.a(sqlite3.o)
“_sqlite3_key”, referenced from:
_sqlite3Pragma in libsqlcipher.a(sqlite3.o)
_sqlite3Pragma in libsqlcipher.a(sqlite3.o)
“_sqlite3_rekey”, referenced from:
_sqlite3Pragma in libsqlcipher.a(sqlite3.o)
_sqlite3Pragma in libsqlcipher.a(sqlite3.o)
ld: symbol(s) not found
collect2: ld returned 1 exit status
Could you please throw some light on this? Really appreciate it.
Thanks
Ashwini
Hi Steve,
Your effort looks amazing. But like you posted earliar, Coredata and sqlite dont go along well..
Since im looking to keep my app 2.0 compatible, i have a question for you.
My app depends on SQLite, infact my app only READs from DB. and doesnt write to it.
Since the db is already made, Would I have to encrypt each row and write to db BEFORE using it in my app? Cuz we’re not going to write anything at app runtime.
oh man, that was “Stephen!” my bad
I’d also like to know if theres a php library for this? cuz for the entries??
@Raheel – If you use SQLCipher you wouldn’t need to do any per-row encryption. You could simply prepare the database ahead of time before using it in your application. However, I would caution you that the structure you describe would require your app to know the key at run time (most likely embedded). That will be fairly insecure as an attacker could just extract the key from your app to access the database.
In response to your second question, it is possible to use SQLCipher with PHP. You just need to point it at SQLCipher with custom build options when you compile PHP. Here is a link to the mailing list entry of another user that had success with that approach https://groups.google.com/group/sqlcipher/browse_thread/thread/6d77e4ef2a1530d7
Hi, what program or command line tool can be used to encrypt database for using with SQLCipher?
@Alex – when you clone the project you can run configure and make to generate a sqlcipher compatible sqlite3 binary. There is more info in the README, on the SQLCipher site and on this mailing list post.
Also, please post any further technical questions to the mailing list .
Yes, my design would require the key to be embedded. But i’ve got little choice here, the whole data is store in sqlite db. Every touch probably has something to do with sql query.
Thats the reason this encryption is so important to me. Any competitor, if wishes, can not just get my data strings etc.. but also my sql table design that would provide vital clues to how my app functions.
In such a scenario, i’d really like your opinion on the best possible way to get it. I’m not trying to protect my app against crackers or hacks, Im trying to protect it against competitors who can imitate my app in the future.. and specially if my db is fully accessible,, then its all over!
I’ve seen many apps using some other apps “copyrighted” material.
Hello, thanks for a wonderful tutorial. Everything compiles and runs okay, but nothing appears to happen when i try to rekey a database. Do I have to remove the original libsqlite3.0dylib library from my app?
Also, same question as above: Is there a way to encrypt a database via command line or other tool? I want a fully encrypted database for my app, there will be no writing, updates or inserts by the user.
Thanks!
Daniel
Oops, update to previous post.
Got everything to compile fine (problem was a space in my pathname).
Only issue now: I can use sqlite3_rekey to encrypt the entire database.
Then I quit the app, comment out the sqlite3_rekey and try to run using sqlite3_key right after opening the database. Any sql calls then yield a ‘database is malformed’ or ‘database is encrypted’ error. any ideas?
Actual code I use is:
sqlite3_rekey(database, “1234”, 4); to encrypt (which works) , and
sqlite3_key(database, “1234”, 4); to try and decrypt.
thanks again!
Daniel
@raheel – You’re free to use SQLCipher with an key embedded in your application. However, this will only obfuscate the key. A determined individual could inspect your application binary to pull out the key and access the data in the database. Basically that approach is better than nothing, but only provides limited security.
@daniel – please check out my response to @alex above regarding the command line tool. Basically you can make a sqlite3 command line tool from the SQLCipher source when compiled as described in the README.
With regard to rekeying a database you need to make sure you are using the experimental “rekey-plaintext” branch in github. Check out this comment for more information.
Closing the comments. For answers to your questions join the SQLCipher Google Group: https://groups.google.com/group/sqlcipher
Hi! i’ve implemented that in a Visual Studio console project, but it looks like sqlite3_exec(db, “PRAGMA key = ‘BIGsecret’, NULL, NULL, NULL); code line is being ignored because it creates or opens the database but never encrypt the file