Converting sqlite to encrypted database:

Question

I've an existing sqlite database in my application. It running successfully. Now i need to encrypt the database using sqlcipher. My problem is at the time of converting sqlite to encrypt i got the following exception.

net.sqlcipher.database.SQLiteException: table android_metadata already exists
 at net.sqlcipher.database.SQLiteDatabase.native_rawExecSQL(Native Method)
 at net.sqlcipher.database.SQLiteDatabase.rawExecSQL(SQLiteDatabase.java:1851)
 at com.x.y.convert_sqlite_to_sqlcipher(Practitioner_menu.java:2626)
 at com.x.y$AdminProcess.doInBackground(Practitioner_menu.java:1659)
 at com.x.y$AdminProcess.doInBackground(Practitioner_menu.java:1)
 at android.os.AsyncTask$2.call(AsyncTask.java:287)
 at java.util.concurrent.FutureTask.run(FutureTask.java:234)
 at android.os.AsyncTask$SerialExecutor$1.run(AsyncTask.java:230)
 at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1080)
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:573)
 at java.lang.Thread.run(Thread.java:856)

My converting code:

File old_sqliteFile = getDatabasePath("old_db.sqlite");
File databaseFile = getDatabasePath("new_db.db");
        databaseFile.mkdirs();
        databaseFile.delete();
    database = SQLiteDatabase.openOrCreateDatabase(databaseFile,
                    "password", null);
database.rawExecSQL(String.format(
                    "ATTACH DATABASE '%s' AS encrypted KEY '%s'",
                    databaseFile.getAbsolutePath(), "password");
            database.rawExecSQL("select sqlcipher_export('encrypted')");
            database.rawExecSQL("DETACH DATABASE encrypted");
            database.close();

Please anyone help me to find out what i am doing wrong?

possible duplicate of [file is encrypted or is not a database (Exception net.sqlcipher.database.SQLiteException)](http://stackoverflow.com/questions/22062277/file-is-encrypted-or-is-not-a-database-exception-net-sqlcipher-database-sqlitee) — Maveňツ, Jul 24 '14 at 11:07
@maven i thought our questions are almost same but the error is different. How did you solve that issue? — Amsheer, Aug 27 '14 at 14:08

score 7 · Accepted Answer · answered Jul 24 '14 at 11:04

7

You are using databaseFile twice. Your openOrCreateDatabase() should be called for old_sqliteFile, not databaseFile.

Here is a method that will replace an unencrypted database file with an encrypted replacement:

  public static void encrypt(Context ctxt, String dbName,
                             String passphrase) throws IOException {
    File originalFile=ctxt.getDatabasePath(dbName);

    if (originalFile.exists()) {
      File newFile=
          File.createTempFile("sqlcipherutils", "tmp",
                              ctxt.getCacheDir());
      SQLiteDatabase db=
          SQLiteDatabase.openDatabase(originalFile.getAbsolutePath(),
                                      "", null,
                                      SQLiteDatabase.OPEN_READWRITE);

      db.rawExecSQL(String.format("ATTACH DATABASE '%s' AS encrypted KEY '%s';",
                                  newFile.getAbsolutePath(), passphrase));
      db.rawExecSQL("SELECT sqlcipher_export('encrypted')");
      db.rawExecSQL("DETACH DATABASE encrypted;");

      int version=db.getVersion();

      db.close();

      db=
          SQLiteDatabase.openDatabase(newFile.getAbsolutePath(),
                                      passphrase, null,
                                      SQLiteDatabase.OPEN_READWRITE);
      db.setVersion(version);
      db.close();

      originalFile.delete();
      newFile.renameTo(originalFile);
    }
  }

answered Jul 24 '14 at 11:04

CommonsWare

986,068
189
2,389
2,491

I have one issue in this. in any case below code failed db.rawExecSQL("SELECT sqlcipher_export('encrypted')"); i can't do anything further database corrupted. How to handle it. If these set of code run again still no luck. According to your answer this http://stackoverflow.com/questions/22062277/file-is-encrypted-or-is-not-a-database-exception-net-sqlcipher-database-sqlitee i thought this is corrupted. – Amsheer Aug 27 '14 at 14:03
Any easy way to see if a DB is already encrypted? `if (db.isEncrypted()){ db.encrypt(); }`? – Mar 10 '15 at 15:47
@user2676468: No, sorry. – CommonsWare Mar 10 '15 at 23:13
So how would you recommend doing this encrypt() call? Would I get an error or would I just do this in a DB onUpgrade call? – Mar 15 '15 at 00:24
@user2676468: You cannot do it *in* an `onUpgrade()` call on a `SQLiteOpenHelper`, as the database is already open there. While there is no `db.isEncrypted()` option, if you can open the database with regular SQLite (or with SQLCipher but using an empty passphrase), you know that the database is *not* encrypted. You know when to call `encrypt()` when you have an unencrypted database and the user indicates that she wants to switch to an encrypted database. – CommonsWare Mar 15 '15 at 00:35
empty passphrase as in "" as the argument? – Mar 15 '15 at 01:14
@user2676468: Yes, see the first `openDatabase()` call in the code snippet in the answer. – CommonsWare Mar 15 '15 at 10:57
" the user indicates that she wants to switch to an encrypted database." What would be the best solution if I don't want to give the user the option. I just want to do it for everyone? – Mar 16 '15 at 12:57
@user2676468: You know when to call `encrypt()` when you have an unencrypted database and you successfully get the passphrase from the user to use to encrypt the database. – CommonsWare Mar 16 '15 at 13:00
There was already a passphrase being used for the application. So it's not new to the user, but I'm still failing to understand how to check if it's an unencrypted db. If opening the db with a "" fails? Would that be best? – Mar 16 '15 at 14:21
@user2676468 "If opening the db with a "" fails? Would that be best?" -- yes. – CommonsWare Mar 16 '15 at 14:40
@user2676468: You can always positively determine an unencrypted database, because the empty string is always the empty string. You cannot positively identify an encrypted database for the reason that you describe. At this point, if you have further questions regarding SQLCipher for Android, please use the search tool, and if you do not find an existing answer, use the "Ask Question" button to ask a fresh question. – CommonsWare Mar 16 '15 at 16:27
Hmm... passing in "" into getWriteableDatabase() works fine. As in, it doesn't fail. Even though the database is unencrypted. – Mar 16 '15 at 16:27
@CommonsWare oh. I guess that's what you meant. Empty string will ALWAYS work for an unencrypted database? – Mar 16 '15 at 16:32
@user2676468: "Empty string will ALWAYS work for an unencrypted database?" -- it is supposed to. – CommonsWare Mar 16 '15 at 16:38
Thanks @CommonsWare I wasn't aware of that. I thought you meant to try empty string because it will fail. – Mar 16 '15 at 16:39

score 0 · Answer 2 · answered May 03 '16 at 13:34

Thanks, it works great. I added this code to encrypt, decrypt and change the password.

@Override
public synchronized SQLiteDatabase getWritableDatabase(String password) {
    SQLiteDatabase sqLiteDatabase;
    try{
        sqLiteDatabase = super.getWritableDatabase(password);
    }catch(Exception e){
        try {
            encrypt(context,DATABASE_NAME,PWOLD,PWNEW);
        } catch (IOException e1) {
            e1.printStackTrace();
        }
        sqLiteDatabase = super.getWritableDatabase(password);
    }
    return sqLiteDatabase;
}

@Override
public synchronized SQLiteDatabase getReadableDatabase(String password) {
    SQLiteDatabase sqLiteDatabase;
    try{
        sqLiteDatabase = super.getReadableDatabase(password);
    }catch(Exception e){
        try {
            encrypt(context, DATABASE_NAME, PWOLD, PWNEW);
        } catch (IOException e1) {
            e1.printStackTrace();
        }
        sqLiteDatabase = super.getReadableDatabase(password);
    }
    return sqLiteDatabase;
}

public static void encrypt(Context context, String dbName,
                           String oldPass, String newPass) throws IOException {
    if(!newPass.isEmpty()) Log.d(TAG,"Encrypt DB...");
    else Log.d(TAG,"Decrypt DB...");

    File originalFile=context.getDatabasePath(dbName);

    if (originalFile.exists()) {
        File newFile = File.createTempFile("sqlcipherutils", "tmp",
                        context.getCacheDir());
        SQLiteDatabase db = SQLiteDatabase.openDatabase(originalFile.getAbsolutePath(),
                oldPass, null,
                SQLiteDatabase.OPEN_READWRITE);

        db.rawExecSQL(String.format("ATTACH DATABASE '%s' AS encrypted KEY '%s';",
                newFile.getAbsolutePath(), newPass));
        db.rawExecSQL("SELECT sqlcipher_export('encrypted')");
        db.rawExecSQL("DETACH DATABASE encrypted;");

        int version=db.getVersion();

        db.close();

        db = SQLiteDatabase.openDatabase(newFile.getAbsolutePath(),
                newPass, null,
                SQLiteDatabase.OPEN_READWRITE);
        db.setVersion(version);
        db.close();

        originalFile.delete();
        newFile.renameTo(originalFile);
    }
}

Converting sqlite to encrypted database:

2 Answers2

Linked