Can't open an encrypted database in C

SQLCipher
1

Simply i have an encrypted database, and my code should filter out the key from a text file.
i’ve done all that, but when i run my code it says “file is not a database”.

After lots of reserch i beleive the problem wil be in how the ‘key’/‘password’ generated from the text file are encoded.

beacuse passing the key manully using sqlcipher from the command line decrypt the databse and opens it, works fine!

What i’ve done:

Opening the database file :

int rc = sqlite3_open("/path/to/database.sqlite", &db);

Storing the PRAGMA statment for setting the key in a variable to use it :

snprintf(keyQ, sizeof(keyQ), "PRAGMA key=\"x'%s'\";", subbuff);

knowing that the variable subbuff is actully the key as a char array.

Setting the sqlite3_exec calls:

rc = sqlite3_exec(db,keyQ, NULL, 0, NULL);

rc = sqlite3_exec(db, "PRAGMA cipher_plaintext_header_size = 32;", 0, 0, NULL);

rc = sqlite3_exec(db, "PRAGMA cipher_compatibility = 3;", 0, 0, NULL);

knowing that the error occurs only when i pass in an actual qurery like the following:

char *sql = "SELECT * FROM table;";
rc = sqlite3_exec(db, sql, callback , 0, &errMsg);
if i comment the last line, no error happen.

i’ve tried lots and lots of solutions without any results.
please help :frowning:

2

The problem may be that you are using cipher_plaintext_header_size. That PRAGMA allocates an unencrypted portion at the start of the database some of which is where the salt would normally be stored. You would need to use PRAGMA cipher_salt to ensure that the salt is configured correctly as well. Please review the documentation for PRAGMA cipher_plaintext_header_size for details.

3

Thank you so much for the quick response
I did add PRAGMA cipher_salt = "x'01010101010101010101010101010101'";
same error : file is not a database

4

could it be that the password are actually changed after being encoded? cuz the error “file is not a database” will occurr when the key is wrong.
the key is a char array containg the right key.

5

The Password on a database can’t change after it is created without using PRAGMA rekey. However, you are storing / retriving the key material from. If that value changes it would be unable top open the database. Also, make sure you are creating the databse from scratch the first time (i.e. that database.sqlite does not already exist).

Without additional information I’m not sure what else could be amiss here, but it is very likely a problem with the application logic. If you can produce a small standalone app that reproduces the problem it might be helpful for troublshooting.

6

Thanks again for the reply, here’s a demonstration of what i’m facing:

extern char **environ;
char fString[2000];

int callback(void *, int, char **, char **);

int callback(void *NotUsed, int argc, char **argv, char **colName) {
    char buff[7000];
    for (int i = 0; i < argc; i++) {
        snprintf(buff, sizeof(buff), "%s = %s \n ", colName[i], argv[i]);
        strcat(fString, buff);
    }
    strcat(fString, "@--------@\n");
    return 0;
}



int main () {
    sqlite3 *db;
    char *errMsg = 0;
    
    int rc = sqlite3_open("/path/to/encrypted/db.sqlite", &db);
    
    rc = sqlite3_key(db, "0f9424100af49c8c1ce0d2c79b2958f67d9f4aa056b1c216ed82171e41c1310966d65af32844bc2974b5a7788cb90f38", strlen("0f9424100af49c8c1ce0d2c79b2958f67d9f4aa056b1c216ed82171e41c1310966d65af32844bc2974b5a7788cb90f38"));
    
// error not occure here 
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Error in key --- %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return 1;
    }

/*
 The following lines are the another approch for giving the key as PRAGMA statment. 
eather way it failed with same error message.
*/

//    rc = sqlite3_exec(db,"PRAGMA key=\"x'0f9424100af49c8c1ce0d2c79b2958f67d9f4aa056b1c216ed82171e41c1310966d65af32844bc2974b5a7788cb90f38'\";", 0, 0, NULL);
//    rc = sqlite3_exec(db, "PRAGMA cipher_plaintext_header_size = 32;", callback, 0, NULL);
//    rc = sqlite3_exec(db, "PRAGMA cipher_salt = \"x'01010101010101010101010101010101'\";", callback, 0, NULL);
//    rc = sqlite3_exec(db, "PRAGMA cipher_compatibility = 3;", callback, 0, NULL);
    
    char *sql = "SELECT * FROM table;";
    
    rc = sqlite3_exec(db, sql, callback , 0, &errMsg);
    
// The error happen here
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Error opening the database --- %s\n", sqlite3_errmsg(db));
        sqlite3_close(db); // <- will return 
        return 1;
    }
    sqlite3_close(db);
    return 0;
}

Sorry for the inconvenience…

knowing that performing the PRAGMA statments using sqlcipher tool from the command line works perfectly with the same database and password.

7

In the example you are referencing, does db.sqlite already exist? Is it a plaintext SQLite database?

8

yes it already exist, and it’s a plaintext database.
the ‘key’ for the database is given as a plaintext in sqlite3_key() function.

9

The problem is that sqlite3_key will not encrypt an existing plaintext database. You will need to use a migration method:

SQLCipher API - Zetetic

10

Thanks for all your help, i might not explaining the problem that i’m facing correctly :

the database (sqlite file) is already exist, and it is an encrypted database.

and i have the key for the database, and according to your post here

The same process also works in reverse to create a plaintext, fully decrypted, copy of an encrypted SQLCipher database that can be opened with standard SQLite.

$ ./sqlcipher encrypted.db
sqlite> PRAGMA key = 'testkey';
sqlite> ATTACH DATABASE 'plaintext.db' AS plaintext KEY '';  -- empty key will disable encryption
sqlite> SELECT sqlcipher_export('plaintext');
sqlite> DETACH DATABASE plaintext;

I implement the same:
after int rc = sqlite3_open()

rc = sqlite3_exec(db, "PRAGMA key=\"x'theKeyForDB'\";", NULL, NULL, &errMsg);
rc = sqlite3_exec(db, "ATTACH DATABASE 'plaintext.sqlite' AS plaintext KEY '';", NULL, NULL, &errMsg);
rc = sqlite3_exec(db, "SELECT sqlcipher_export('plaintext');", NULL, NULL, &errMsg);
rc = sqlite3_exec(db, "DETACH DATABASE plaintext;", NULL, NULL, &errMsg);

And this is the error messages that i got after each sqlite3_exec():

attach error:  file is not a database
export funciton error:  no such function: sqlcipher_export
cant perform query: file is not a database

The database that i want to decrypt is signal application database in ios, using the CLS for sqlcipher works perfect.

11

Hello @mjeed - thanks for getting back with these additional details and explanation. To recap, you are trying to open a preexisting encrypted database created using Signal with a known key. I think it would be worthwhile to determine if your application is actually properly linking SQLCipher. Can you please execute the following in the application and capture the value from the result set?

PRAGMA cipher_version;

This should report the SQLCipher version. If there is no result set then SQLCipher is not properly linked. If a version is returned, you should compare it against the version returned from the command like SQLCipher you’ve been able to use to successfully open the database to make sure they have the same major version number.