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

[sjlombardo]

We often field questions about how SQLCipher encryption works. In one common scenario, a developer wants to convert an existing standard SQLite database to an encrypted SQLCipher database. For example, this might be a requirement for an application that was not previously using SQLCipher, and must convert an insecure database to use SQLCipher full database encryption during an upgrade.

In such cases, developers may initially attempt to open a standard SQLite database and then call sqlite3_key or PRAGMA key, thinking that SQLCipher will open the existing database and rewrite it using encryption. That is not possible, however, and such attempts will be met with a “file is encrypted or is not a database” error (code 26 / SQLITE_NOTADB).

The reason is that SQLCipher encryption works on a per-page basis for efficiency, and sqlite3_key and PRAGMA key can only be used:

1. when opening a brand new database (i.e. for the first time), or
2. when opening an already encrypted databases.

It follows that the correct way to convert an existing database to use SQLCipher is to create a new, empty, encrypted database, then copy the data from the insecure database to the encrypted database. Once complete, an application can simply remove the original plaintext database and use the encrypted copy from then on.

SQLCipher has supported ATTACH between encrypted and plaintext databases since it’s initial release, so it has always been possible to do this conversion on an table-by-table basis. However, an even easier option was added to SQLCipher 2.0, in the form of the sqlcipher_export() convenience function.

The purpose of sqlcipher_export is a to duplicate the entire contents of one database into another attached database. It includes all database objects: the schema, triggers, virtual tables, and all data. This makes it very easy to migrate from a standard non-encrypted SQLite database to a SQLCipher encrypted database, or back in the other direction.

To use sqlcipher_export() to encrypt an existing database, first open up the standard SQLite database, but don’t provide a key. Next, ATTACH a new encrypted database, and then call the sqlcipher_export() function in a SELECT statement, passing in the name of the attached database you want to write the main database schema and data to.

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

Finally, securely delete the existing plaintext database, and then open up the new encrypted database as usual using sqlite3_key or PRAGMA key.

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;

It is also possible to use the sqlcipher_export() function in specialized cases to change or customized SQLCipher database settings. These advanced concepts are covered further in the documentation.

[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.