Transcript - AndrewCraze.com

SQLCipher on
Objective-C
Andrew Craze
@AndrewCr
acraze at dxysolutions.com
Agenda
• What are SQLite and SQLCipher?
• Why not just use Core Data?
• How do I set up and use SQLCipher?
• What other concerns are there?
What is SQLite?
• Portable (C) implementation of SQL
• Open-source
• Available on/for:
• Mobile: iOS, Android, .NET
• Desktop: OSX, Win/.NET, Linux
• Web: Ruby, PHP, Python, Java, .NET
• Underpins CoreData on iOS
What is SQLCipher?
• SQLite extension for encryption
• 256-bit secure encryption using
OpenSSL
• Open source (BSD License)
• Pay-support option, binaries, too
SQLite/SQLCipher vs. CoreData
CoreData has:
• Great built-in support
• Update mechanism
• Abstraction
• But, encryption only when entire device
is locked
SQLite/SQLCipher vs. CoreData
SQLite has:
• Better data-aggregation & searching
• Transactions
• Cross-platform compatibility
• Strong encryption
• But, poor object support
Setting up SQLCipher
2 options: buy or build
Buy:
1. Cough up $150
2. Add lib to project (includes OpenSSL)
3. Add header paths
4. Add c flag: SQLITE_HAS_CODEC
Setting up SQLCipher
Build:
1. Download or clone sqlcipher
2. Download OpenSSL
3. Download or clone OpenSSL-Xcode
4. Add source path in XCode: OPENSSL_SRC
5. Add the subprojects
6. Add header paths
7. Add c flag: SQLITE_HAS_CODEC
Getting the tools
Setting the Source
Path
Setting the Search
Path
Setting the C Flags
Using SQLCipher
After opening the DB, set the key
const char* key = [@”MyKey123" UTF8String];
sqlite3_key(db, key, strlen(key));
or execute this SQL statement
PRAGMA key = 'MyKey123';
or with FMDB
database.key = @"MyKey123";
(That’s it.)
Unencrypted
hexdump
Encrypted hexdump
Using SQLite with FMDB
FMDatabase* db = [FMDatabase databaseWithPath:path];
BOOL fOpened = [db open];
NSString* sql= @"SELECT id, friendlyName FROM Device WHERE
serialNumber = ?;”;
NSArray* args = [NSArray arrayWithObjects:serialNumber, nil];
FMResultSet* rs = [db executeQuery:sql withArgumentsInArray:args];
if ([rs next]) {
NSString* friendlyName = [rs
stringForColumn:@"friendlyName"];
// …
}
BOOL fClosed = [db close];
Other issues
• Distributing the key with the database
• Performance cost
• Not compatible with CoreData
• Pragma for Android compatibility
• Export restrictions
Compatibility with Android
After setting the key, execute
NSString* pragmaSql = @"PRAGMA cipher_use_hmac = OFF;”;
BOOL fPragmaSucceeded = [ret executeUpdate:pragmaSql];
Export Restrictions
• You’ll have to answer “Yes” to Crypto
• App must be reviewed by the
Department of Commerce
• App is a “Mass-market encryption item”
• Walkthroughs of the process online
• Not rocket surgery, but it takes time
Handy Reference Links
•
•
•
•
•
•
•
SQLite API (c/c++)
http://sqlite.org/c3ref/intro.html
FMDB (Obj-C wrapper for SQLite)
https://github.com/ccgus/fmdb
SQLCipher main page
http://sqlcipher.net
OpenSSL source
http://www.openssl.org/source/
Nice SQLCipher tutorial
http://www.mobileorchard.com/tutorial-iphone-sqlite-encryption-with-sqlcipher/
Comparison of CoreData and “Traditional” Databases
http://www.cocoawithlove.com/2010/02/differences-between-core-data-and.html
Walk-through of export-license process
http://zetetic.net/blog/2009/08/03/mass-market-encryption-commodity-classification-for-iphone-applicationsin-8-easy-steps/
and an update
http://tigelane.blogspot.com/2011/01/apple-itunes-export-restrictions-on.html
Questions, Maybe Answers
Andrew Craze
@AndrewCr
http://blog.andrewcraze.com
acraze at dxysolutions.com
A Quick Pitch for
Speakers
• You learn something new at least once
every week-or-two anyway
• It’s not that painful 8)
• It’s the best way I know to get ahead