How to encrypt a plaintext SQLite database to use SQLCipher (and avoid “file is encrypted or is not a database” errors)

[sjlombardo]

The latest guidance has been moved to the Encrypting Plaintext Databases documentation. This topic has been archived to preserve discussion.

[Gaurav_Rathi]

How can this be done in C? Everytime I run

sqlite3_prepare_v2(db_plaintext, “ATTACH DATABASE ‘encrypted.db’ AS encrypted KEY ‘testkey’”, -1, 0, 0);
application crashes

[sjlombardo]

@Gaurav_Rathi that is extremely unusual. Can you post a stack trace? What happens if you use sqlite3_exec instead?

[Gaurav_Rathi]

@sjlombardo Yes, that helped, here is my code in case someone else has the same issue:

void encryptPlainTextDatabase(const string& src_database_file, const string& license_key)
{
sqlite3 *db_plaintext;
int rc = sqlite3_open(src_database_file.c_str(), &db_plaintext);
if (rc != SQLITE_OK) {
qDebug()<<(“Failed to open database\n”); return;}
string lic_st(“PRAGMA cipher_license = '”+ license_key+“’ ;”);
//not sure if my code will work with open source version
rc = sqlite3_exec(db_plaintext, lic_st.c_str() , NULL, NULL, NULL);
if (rc != SQLITE_OK) {
qDebug()<<"Failed in license step with error code: "<<rc; return;
}
else
qDebug()<<“license step done”;
deleteFile(QString(“outDB.db3”));//If the encrypted file exits we will not be able to proceed, need to ensure
//output file does not already exist.
rc= sqlite3_exec(db_plaintext, “ATTACH DATABASE ‘outDB.db3’ AS encrypted KEY ‘testkey’”, 0, 0, 0);
if(rc != SQLITE_OK){
qDebug()<<“failed to attach to plaintext database”; return;
}
else
qDebug()<<“attaching done”;
rc = sqlite3_exec(db_plaintext, “SELECT sqlcipher_export(‘encrypted’)”,0,0,0);
if(rc != SQLITE_OK){
qDebug()<<“failed in doing sqlcipher_export() with: res:”<<rc; return;
}
else
qDebug()<<“sqlcipher_export() done”;
rc = sqlite3_exec(db_plaintext, “DETACH DATABASE encrypted;”, 0, 0, 0);
if(rc != SQLITE_OK){
qDebug()<<“failed to detach database with: res:”<<rc; return;
}
else
qDebug()<<“detaching done done”;
sqlite3_close(db_plaintext);
}

[Jolly]

@sjlombardo

./sqlcipher plaintext.db
sqlite> ATTACH DATABASE 'encrypted.db' AS encrypted KEY 'newkey';
sqlite> SELECT sqlcipher_export('encrypted');
sqlite> DETACH DATABASE encrypted;

Sorry to necro this thread, but this does not appear to work anymore?

I get “Runtime error: access to encrypted.

. is prohibited”

C:\temp>sqlcipher test.db
SQLite version 3.39.2 2022-07-21 15:24:47 (SQLCipher 4.5.2 zetetic)
Enter ".help" for usage hints.
sqlite> ATTACH DATABASE 'enc.db' as encrypted KEY 'test';
sqlite> SELECT sqlcipher_export('encrypted');
Runtime error: access to encrypted.data.z is prohibited

[sjlombardo]

@Jolly - yes, this method of migration still works and is extensively used and tested. I don’t believe we’ve ever had a report of that particular runtime error message either. It looks like you are using Commercial Edition and thus may be entitled to private priority support. Would you mind getting in touch with us at support@zetetic.net about this?

[Jolly]

It was just a silly oversight on my part, I needed to include the license key before trying to run any commands. This is the first time I’ve used the cmd line tool, have been using the .NET libraries for years now though and they work great.

The following worked:

C:\temp>sqlcipher test.db
SQLite version 3.39.2 2022-07-21 15:24:47 (SQLCipher 4.5.2 zetetic)
Enter ".help" for usage hints.
sqlite> PRAGMA cipher_license = 'license key goes here';
sqlite> ATTACH DATABASE 'enc.db' as encrypted KEY 'test';
sqlite> SELECT sqlcipher_export('encrypted');

[sjlombardo]

@Jolly glad to hear you got it working! As a tip, you can create a file called .sqliterc in your home directory on windows (C:\Users\username\.sqliterc) containing the PRAGMA cipher_license statement. That file is read and automatically executed each time the shell tool runs so the license will automatically be applied (and you don’t have to type or copy/paste it).

[Carson_H]

One way to lessen the confusion would be to re-word the sentence “Telling SQLCipher to encrypt a database is easy:” from SQLCipher Community Edition - iOS and macOS Tutorial - Zetetic. That, to me, sounds like the sqlite3_key function can encrypt a standard SQLite database.