Using the SQLite Online Backup API


#1

Hi, is it possible to use the SQLite Online Backup API with an encrypted database?

The code in Example 1: Loading and Saving In-Memory Databases works fine when I
I use it with an unencrypted db file. Unfortunately, when the db is encrypted, it crashes on hitting line 3 of the snippet below (sqlite3_backup_step):

 pBackup = sqlite3_backup_init(pTo, "main", pFrom, "main");
    if( pBackup ){
      (void)sqlite3_backup_step(pBackup, -1);
      (void)sqlite3_backup_finish(pBackup);
    }

Shouldn’t such a process be content-agnostic? Do you have any idea on how to copy an encrypted database into memory (fast)?


#2

Hello @classintouch - thanks for taking a look at SQLCipher. Historically the backup API has not been supported with SQLCipher, with the preferred approach being use of an attached database to copy back and forth, or the use of sqlcipher_export(). That said, we’d need to take a closer look into this in order to determine what is causing the crash, and whether this use case could be supported.

In the mean time, one option / workaround would be to open your in memory database and then attach the encrypted database.

Once you have the database attached you could copy data from the attached database into the memory database, e.g. insert into X select * from attached.X (assuming the same schema).

The final thing that I would mention is that SQLCipher is typically not used in the manner you are describing, in terms of copying all data from an encrypted database into memory. SQLCipher performs cryptographic operation on a page-by-page basis, so there is no need to decrypt the entire database and load it into memory, except for very special circumstances. In most cases, leaving the database on disk, increasing the page cache and taking note of design guidelines for performance will yield good speed even for encrypted databases.


#3

Hello @classintouch - we’ve been looking into this a bit further. Just to clarify:

  1. are you loading data from an encrypted database to memory, or from memory to an encrypted database?
  2. is your application receiving an error on sqlite3_backup_step, or actually crashing?
  3. what version of SQLCipher are you using?