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

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.

A post was merged into an existing topic: “file is encrypted or is not a database” using node js sqlite3 package with SQLCipher

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

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

@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);
}