Not able to export an encrypted database with COLLATE to plaintext

SQLCipher Issues
1

Hi there,
I am trying to create a decrypted copy of an encrypted database. The database has the following cipher settings:

PRAGMA cipher_default_compatibility = 3;
PRAGMA kdf_iter = 64000;
PRAGMA cipher_page_size = 1024;
PRAGMA cipher_use_hmac = 1;
PRAGMA cipher_plaintext_header_size = 0;
PRAGMA cipher_hmac_algorithm = HMAC_SHA1;
PRAGMA cipher_kdf_algorithm = PBKDF2_HMAC_SHA1;

The database schema has a few COLLATE sequences, which are causing issues while executing the sqlcipher_export function.

ATTACH DATABASE 'plaintext.db' AS plaintext KEY '';  
SELECT sqlcipher_export('plaintext');

The following error is thrown:

Runtime error: no such collation sequence: CUSTOM_COLLATE

@developernotes @sjlombardo @mmoore Please provide some insights!

2

Hi @Shyamal_Shah

How are you defining the custom collating sequences?

Any custom collating sequences (which are referenced in table schema definitions) will need to be available to the database connection at the time you run sqlcipher_export.

Without it being available, you would run into a similar error message as this when trying to create a new table definition referencing the custom collating sequence (when it’s not available).

If you’re defining the collating sequences using sqlite3_create_collation notice that the collating sequence is only available until the connection is closed:

Collating functions are deleted when they are overridden by later calls to the collation creation functions or when the database connection is closed using sqlite3_close().

Because of that, without seeing more code, I’d speculate that you could be:

  1. Opening the database connection.
  2. Create the collation when creating the table.
  3. Closing the database connection and re-opening it without defining sqlite3_create_collation.
  4. Attempting to sqlcipher_export

Any collation sequences which would need to be available when creating the table schema need to be available when using sqlcipher_export as well, so you’ll need to make the custom collation sequences available to the connection prior to performing sqlcipher_export

3

Hi @mmoore,
Thanks for the quick response and the explanation. You are correct the collating sequence was not available while calling the sqlcipher_export. The database in question is created in an Android application and I think it will not be possible to create the same collation sequence with SQLite command line interface.
Is there any workaround for that?

4

Hello @Shyamal_Shah - The main workaround would be to recreate your custom collate for the platform where you are running the command line interface. If you build it as a loadable module you can load it into the shell and then use it during export. Another option would be to run the export on android where you have the collation available.