title: Encrypting SQLite
body: <p>I am going to write my own encryption, but would like to discuss some internals. Should be employed on several mobile platforms - iOS, Android, WP7 with desktop serving more or less as a test platform.</p>
  
  <p>Let's start first with brief characteristics of existing solutions:</p>
  
  <ul>
  <li><p>SQLite standard (commercial) SEE extension - I have no idea how it works internally and how it co-operates with mentioned mobile platforms.</p></li>
  <li><p>System.data.sqlite (Windows only): RC4 encyption of the complete DB, ECB mode. They encrypt also DB header, which occasionally (0.01% chance) leads to DB corruption.*) Additional advantage: They use SQLite amalgamation distribution.</p></li>
  <li><p>SqlCipher (openssl, i.e. several platforms): Selectable encryption scheme. They encrypt whole DB. CBC mode (I think), random IV vector. Because of this, they must modify page parameters (size + reserved space to store IV). They realized the problems related to unencrypted reading of the DB header and tried to introduce workarounds, yet the solution is unsatisfactory. Additional disadvantage: They use SQLite3 source tree. (Which - on the other hand - enables additional features, i.e. fine tuning of the encryption parameters using special pragmas.)</p></li>
  </ul>
  
  <p>Based on my own analysis I think the following could be a good solution that would not suffer above mentioned problems:</p>
  
  <ul>
  <li>Encrypting whole DB except the DB header.</li>
  <li>ECB mode: Sounds risky, but after briefly looking at the DB format I cannot imagine how this could be exploited for an attack. </li>
  <li>AES128?</li>
  <li>Implementation on top of the SQLite amalgamation (similarly as system.data.sqlite)</li>
  </ul>
  
  <p>I'd like to discuss possible problems of this encryption scheme.</p>
  
  <p>*) Due to SQLite reading DB header without decryption. Due to RC4 (a stream cipher) this problem will manifest at the very first use only. AES would be a lot more dangerous as every "live" DB would sooner or later face this problem.</p>
  
  <hr>
  
  <p>EDITED - case of VFS-based encryption</p>
  
  <p>Above mentioned methods use codec-based methodology endorsed by sqlite.org. It is a set of 3 callbacks, the most important being this one:</p>
  
  <pre><code>void *(*xCodec)(void *iCtx, void *data, Pgno pgno, int mode)
  </code></pre>
  
  <p>This callback is used at SQLite discretion for encrypting/decrypting data read from/written to the disk. The data is exchanged page by page. (Page is a multiple of 512 By.)</p>
  
  <p>Alternative option is to use VFS. VFS is a set of callbacks used for low-level OS-services. Among them there are several file-related services, e.g. xOpen/xSeek/xRead/xWrite/xClose. In particular, here are the methods used for data exchange</p>
  
  <pre><code>int (*xRead)(sqlite3_file*, void*, int iAmt, sqlite3_int64 iOfst);
  int (*xWrite)(sqlite3_file*, const void*, int iAmt, sqlite3_int64 iOfst);
  </code></pre>
  
  <p>Data size in these calls ranges from 4 By (frequent case) to the DB page size. If you want to use a block cipher (what else to use?), then you need to organize underlying block cache. I cannot imagine an implementation that would be as safe and as efficient as SQLite built-in transactions.</p>
  
  <p>Second problem: VFS implementation is platform-dependent. Android/iOS/WP7/desktop all use different sources, i.e. VFS-based encryption would have to be implemented platform-by-platform.</p>
  
  <p>Next problem is a more subtle: Platform may use VFS calls to realize file locks. These uses must not be encrypted. More over, shared locks must not be buffered. In other words, encryption at the VFS level might compromise locking functionality.</p>
  
  <hr>
  
  <p>EDITED - plaintext attack on VFS-based encryption</p>
  
  <p>I realized this later: DB header starts with fixed string "SQLite format 3" and the header contains a lot of other fixed byte values. This opens the door for known plaintext attacks (KPA).</p>
  
  <p>This is mainly the problem of VFS-based encryption as it does not have the info that the DB header is being encrypted.</p>
  
  <p>System.data.sqlite has also this problem as it encrypts (RC4) also the DB header. </p>
  
  <p>SqlCipher overwrites hdr string with salt used to convert password to the key. Moreover, it uses by default AES, hence KPA attack presents no danger.</p>