Unable to read encrypted db in command line

The encrypted db can be read/written in C program using sqlite3_key(). However command line use failed:

.\sqlcipher\sqlite3.exe data.db
SQLite version 3.36.0 2021-06-18 18:36:39 (SQLCipher 4.5.0 community)
Enter “.help” for usage hints.
sqlite> PRAGMA key = “x’xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx’”;
sqlite> .schema
Error: file is not a database

The sqlite3.exe is built from community repo of sqlcipher together with the sqlite3.lib being used in C.

Hey @joggx,

Thank you for you interest in SQLCipher and for posting to the discussion forum.

A few preliminary suggestions/questions:

  • Try opening the database using DB Browser for SQLite: https://sqlitebrowser.org (there’s a raw key dropdown when you’re presented with the SQLCipher encryption prompt):

  • Try using the PRAGMA key = ... syntax in your C program and see if it works there.

  • In the post here, it appears as if you’ve used curly double quotes for the PRAGMA key = ... although I’m not sure if that’s just the post formatting.

  • Post your example C code where you’re using sqlite3_key() (redacting the key)

I downloaded the Windows version and run ‘DB Browser for SQLCipher.exe’, click ‘Open Database’, enter the raw key (with anything else intact) then click OK, the same dialog pops out again. If I close it, it says ‘Could not open database file. Reason: invalid format’.

Regarding the curly double quotes, I did use ASCII quote in command line.

Below is the code to call sqlite3_key().

int sqlite3_set_enckey(struct db_t *db, const u_char *enc_key, size_t len_enc_key)
	return sqlite3_key(db->db, enc_key, (int)len_enc_key);

Could you try the above recommendation.

Also show what the caller of sqlite3_set_enckey looks like, what are you passing in for the len_enc_key?

use sqlite3_exec() to execute statement:

PRAGMA key = “x’xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx’”

get error ‘file is not a database’.
len_enc_key is simply the length of byte array representing the raw key (32 bytes).

If you’d like to use a raw key (without key derivation) using sqlite3_key you’ll want to pass in a hex string in SQLITE BLOB format and then specify the length of the entire key. Here’s an example:

    const char *key = "x'2DD29CA851E7B56E4697B0E1F08507293D761A05CE4D1B628663F411A8086D99'";
    sqlite3_key(db, key, (int)strlen(key));

OK but I think it is different from sqlite3’s docs.

   int sqlite3_key_v2(
      sqlite3 *db,         /* The connection from sqlite3_open() */
      const char *zDbName, /* Which ATTACHed database to key */
      const void *pKey,    /* The key */
      int nKey             /* Number of bytes in the key */

I think if you want to mandate this format, it shouldn’t return 0 given a binary array.

Thanks for reminding me anyway. I can now use like

PRAGMA hexkey='796f75722d7365637265742d6b6579';
to read the db.

OK but I think it is different from sqlite3’s docs.

The documentation you reference is for SEE (SQLite Encryption Extension: Documentation 5.0 C Interface) and isn’t SQLCipher documentation.

I think if you want to mandate this format, it shouldn’t return 0 given a binary array.

It is possible to pass a byte array to sqlite3_key() but without the hex string in BLOB format it will go through key derivation.

This post may be helpful to clarify the different options: Technical Guidance: Using Random Values as SQLCipher Keys - Zetetic