How does sqlite handle encryption in iOS?

Question

There appears to be some default support for sqlite encryption in iOS but I cannot find any documentation for how it works. In a fresh iOS project I created a new db and added the key pragma before creating a table.

import SQLite3

...

sqlite3_open_v2(docStr, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nil)
sqlite3_exec(db, "PRAGMA key = 'abc123';", nil, nil, nil)
sqlite3_exec(db, "CREATE TABLE Breed (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, favorite INTEGER NOT NULL DEFAULT 0 ); INSERT INTO Breed(name) VALUES ('Beagle');", nil, nil, nil);

Checking the db on the file system it then appears to be encrypted. As I mentioned this is a clean project, I haven't added SQLCipher or any other libs. Is there a way to know what extension/lib is using the pragma so I can know if it makes sense to use?

Running on iOS 13.2.2 on iPhone 11 simulator

Answer 1

SQLite offers encryption extensions and of them is the sqlite3-see-cccrypt.c:

This file is a drop-in replacement for the public-domain "sqlite3.c" filef, adding support for the AES-128 and AES-256 encryption algorithms, in OFB mode, using the external CCCrypt encryption. CCCrypt is the default encryption library on MacOS and iOS, and so this implementation of SEE is recommended for those platforms.

The see-ccrypt.c module normally only does AES128 encryption. However, when see-cccrypt is compiled with -DCCCRYPT256, it will use AES256 if and only if the key is exactly 32 bytes long.

I don't know if this is what Apple does use or not.

When you create a database with abc123 key the following happens:

* frame #0: 0x00007fff52a041fb libcommonCrypto.dylib`CCCrypt
  frame #1: 0x00007fff21cfca21 libsqlite3.dylib`sqliteCodecCCCrypto + 305
  frame #2: 0x00007fff21bc76d3 libsqlite3.dylib`pager_write_pagelist + 243
  ...  

CCCrypt is utilized. Page size is 4096 bytes. CCrypt function signature is:

CCCryptorStatus
CCCrypt(CCOperation op, CCAlgorithm alg, CCOptions options, const void *key, size_t keyLength,
    const void *iv, const void *dataIn, size_t dataInLength, void *dataOut, size_t dataOutAvailable,
    size_t *dataOutMoved)

This function is called with:

• op set to 0 (encrypt)
• alg set to 0 (AES 128),
• key is abc123abc123abc1 (initial key, copied to fit 16 bytes),
• dataIn is a garbage I don't recognize yet (not a plaintet SQLite page),
• dataInLength is 4096,
• dataOutMoved is set to 4096 on the return.

Also iv value is:

0x01 0x00 0x00 0x00 0xc0 0xa2 0xe8 0xa8
0x44 0x49 0xef 0xa0 0xa4 0x7b 0xec 0x5f

I don't know SQLite internals, but it looks that the first four bytes represents page number and the rest is some random stuff. CCCrypt is called for every page and consequent pages iv starts with 0x02 0x00 0x00 0x00, 0x03 0x00 0x00 0x00, etc.

When you look at the encrypted sqlite file, last 12 bytes of the iv are stored at the end of each page:

% hexdump -C db-enc.sqlite| grep "44 49 ef"
00000ff0  e2 a1 77 2a c0 a2 e8 a8  44 49 ef a0 a4 7b ec 5f
          ^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
           |- ?        |- last 12 bytes of iv

• 0xff0 = 4080
• 4080 + 16 = 4096 (= page size)

One would say that AES 128 is used, but ... When I open already existing database, CCCrypt is called again, but with the operation set to 0 (encrypt). In other words, CCCrypt is never called with the decrypt operation.

The library is stored:

/Applications/Xcode-beta.app/Contents/Developer/Platforms/
  iPhoneOS.platform/Library/Developer/CoreSimulator/
  Profiles/Runtimes/iOS.simruntime/Contents/Resources/
  RuntimeRoot/usr/lib/libsqlite3.dylib

Symbols to focus on:

• _sqlite3CodecAttach
  • _sqliteCodecCCCrypto
  • _sqliteCodecCCCryptoSizeChng
  • _sqliteCodecCCCryptoFree
• _loadKeyCCCrypt

Related external symbols:

                     _CCCrypt:
00000000001ce000         extern function code                                   ; in /usr/lib/libSystem.B.dylib, CODE XREF=imp___stubs__CCCrypt, DATA XREF=_CCCrypt_ptr
                     _CC_SHA256_Final:
00000000001ce008         extern function code                                   ; in /usr/lib/libSystem.B.dylib, CODE XREF=imp___stubs__CC_SHA256_Final, DATA XREF=_CC_SHA256_Final_ptr
                     _CC_SHA256_Init:
00000000001ce010         extern function code                                   ; in /usr/lib/libSystem.B.dylib, CODE XREF=imp___stubs__CC_SHA256_Init, DATA XREF=_CC_SHA256_Init_ptr
                     _CC_SHA256_Update:
00000000001ce018         extern function code    

dlopen calls:

• Opens /usr/lib/libcompression.dylib for compression streams

---

That's what I've found so far. If anyone wants to continue, feel free, I don't have more time for this rabbit hole :)

Answer 2

I tried to run your code in a Simulator, and the database seems to be encrypted.

Then I tried to list all exported sqlite3_ functions:

(lldb) image lookup -r -s "sqlite3_"

....
Summary: libsqlite3.dylib`_sqlite3_lockstate        Address: libsqlite3.dylib[0x0000000000010ee0] (libsqlite3.dylib.__TEXT.__text + 66704)
Summary: libsqlite3.dylib`_sqlite3_purgeEligiblePagerCacheMemory        Address: libsqlite3.dylib[0x000000000002a590] (libsqlite3.dylib.__TEXT.__text + 170816)
Summary: libsqlite3.dylib`_sqlite3_system_busy_handler        Address: libsqlite3.dylib[0x0000000000038e40] (libsqlite3.dylib.__TEXT.__text + 230384)
Summary: libsqlite3.dylib`sqlite3_activate_see        Address: libsqlite3.dylib[0x0000000000016750] (libsqlite3.dylib.__TEXT.__text + 89344)
Summary: libsqlite3.dylib`sqlite3_aggregate_context        Address: libsqlite3.dylib[0x00000000000169d0] (libsqlite3.dylib.__TEXT.__text + 89984)
Summary: libsqlite3.dylib`sqlite3_aggregate_count        Address: libsqlite3.dylib[0x000000000001df70] (libsqlite3.dylib.__TEXT.__text + 120096)
...

One of those is sqlite3_activate_see so it must be from SQLite Encryption Extensions.

See docs: https://www.sqlite.org/see/doc/release/www/readme.wiki

I could not find other proofs of SSE being used in iOS. As sqlite3.h says

#define SQLITE_SOURCE_ID      "2019-04-15 14:49:49 378230ae7f4b721c8b8d83c8ceb891449685cd23b1702a57841f1be40b5daapl"

It points to https://sqlite.org/src/info/378230ae7f4b721c, but the original hash is

378230ae7f4b721c8b8d83c8ceb891449685cd23b1702a57841f1be40b5db63e

and sqlite3.h from iOS SDK has

378230ae7f4b721c8b8d83c8ceb891449685cd23b1702a57841f1be40b5daapl

... If the source code has been edited in any way since it was last checked in, then the last four hexadecimal digits of the hash may be modified.

sqlite3.h has aapl suffix which means that the source code has been modified. One of this mods could be SSE integration.