Command line Decryption of a SQLite Database Encrypted with GRDB + SQLCipher

[franciskelly-okta]

I'm wondering if anyone can advise me on how to use SQLCipher on the command line or a Python script using the sqlcipher library to decrypt a database encrypted with GRDB + SQLCipher. I can write a simple Swift app that takes the hex key and the path to database file and I can successfully dump the database to the console. But If I try either the ATTACH command or the PRAGMA key = command to try to decrypt the database from the command line (using the sqlcipher command), I get the "file is not database" error (error 26).

My assumption is that GRDB has some settings baked in that are different than SQLCipher defaults and that is causing the problem for me. I'm using GRDB 6.20.2 and SQLCipher 4.5.5.

I can make do with my Swift app, but it would be nice to be able to use command line tools as well.

Any help would be greatly appreciated!

[groue]

Hello @franciskelly-okta,

If I try either the ATTACH command or the PRAGMA key = command to try to decrypt the database from the command line (using the sqlcipher command), I get the "file is not database" error (error 26).

This "file is not a database" error is indeed the sign of a database that could not be decrypted.

There are at least two tests (1, 2) in GRBD that asserts that a database encrypted from GRDB apis can be attached with ATTACH DATABASE ... AS encrypted KEY 'mySecret', so I'm optimistic that you will solve your problem.

My assumption is that GRDB has some settings baked in [...]

When GRDB is compiled with SQLCipher, it always runs PRAGMA cipher_version anytime it opens a new connection, as recommended by the SQLCipher FAQ. That's the only thing GRDB does on its own.

Now, both SQLCipher itself, and apps, can do stuff.

The version of SQLCipher is important. For example, SQLCipher 4 has to be given special instructions to decode databases encrypted with SQLCipher 3 (see PRAGMA cipher_compatibility).

Also, apps can instruct GRDB to configure SQLCipher:

var config = Configuration()
config.prepareDatabase { db in
    // The minimum statement that encrypts:
    try db.usePassphrase("secret")

    // Apps can invoke other SQLCipher config that impact your use case:
    try db.execute(sql: """
        PRAGMA cipher_page_size = ...;
        PRAGMA kdf_iter = ...;
        PRAGMA cipher_plaintext_header_size = ...;
        PRAGMA cipher_salt = ...;
        ...
        """)
}

Note that some apps don't use the usePassphrase GRDB api, and directly call PRAGMA key or call low-level C apis. In this regard, take care of knowing whether the database was encrypted with a passphrase, or a key (see the link on PRAGMA key).

var config = Configuration()
config.prepareDatabase { db in
    // Some people prefer that for security reasons because they
    // can manage the lifetime of the key bytes.
    sqlite3_key(db.sqliteConnection, ...)
}

I guess you need to gather more information about your encrypted database.

[franciskelly-okta]

This gives me a lot to work with - thank you very much! I will let you know what I figure out.