net.sqlcipher.database.SQLiteException: error code 100: another row available

Question

I am using SQLiteCipher to encrypt my database. Previously I was using db.execsql() statement which was working fine. Now I changed the query to SQLStatment.

Here is my code

private static void encrypt(Context ctxt) {
        File originalFile = ctxt.getDatabasePath(DBNAME);

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

                SQLiteStatement preparedStatement = db.compileStatement("ATTACH DATABASE ? AS encrypted KEY ?");
                preparedStatement.bindString(1, newFile.getAbsolutePath());
                preparedStatement.bindString(2, DataControllers.getDbKey());
                preparedStatement.execute();

                SQLiteStatement preparedStatement1= db.compileStatement("SELECT sqlcipher_export('encrypted')");
                preparedStatement1.execute();

                SQLiteStatement preparedStatement2= db.compileStatement("DETACH DATABASE encrypted");
                preparedStatement2.execute();

                int version = db.getVersion();
                db.close();
                db = SQLiteDatabase.openDatabase(newFile.getAbsolutePath(), DataControllers.getDbKey(), null, SQLiteDatabase.OPEN_READWRITE);
                db.setVersion(version);
                db.close();
                originalFile.delete();
                newFile.renameTo(originalFile);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

First execute statements runs but 2nd execute statement throws exception.

Here is stack trace

java.lang.RuntimeException: Unable to start activity ComponentInfo{com.package/com.package.ui.Dashboard}: net.sqlcipher.database.SQLiteException: error code 100: another row available
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2327)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2392)
        at android.app.ActivityThread.access$800(ActivityThread.java:153)
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1305)
        at android.os.Handler.dispatchMessage(Handler.java:102)
        at android.os.Looper.loop(Looper.java:135)
        at android.app.ActivityThread.main(ActivityThread.java:5293)
        at java.lang.reflect.Method.invoke(Native Method)
        at java.lang.reflect.Method.invoke(Method.java:372)
        at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:903)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:698)
     Caused by: net.sqlcipher.database.SQLiteException: error code 100: another row available
        at net.sqlcipher.database.SQLiteStatement.native_execute(Native Method)
        at net.sqlcipher.database.SQLiteStatement.execute(SQLiteStatement.java:58)
        at com.package.dbconnections.DatabaseOpenHelper.encrypt(DatabaseOpenHelper.java:172)
        at com.package.dbconnections.DatabaseOpenHelper.isDbEncrypted(DatabaseOpenHelper.java:151)
        at com.package.dbconnections.DatabaseOpenHelper.getInstance(DatabaseOpenHelper.java:136)
        at com.package.dbconnections.DatabaseOpenHelper.getUrls(DatabaseOpenHelper.java:605)

Answer 1

I believe that the only resolution is to use the specially created rawExecSQL, method.

can`t use. raw or rawExecSQL due to security issue

There is no security issue with using this as there is no chance for SQL Injection as there is no user input. perhaps see SQL injection.

I believe the issue is that in general exec/execute allow limited results, rawQuery/query will return a Cursor. I believe that the conversion is probably generating SQL, modifying it by encrypting the data and then executing that resultant SQL as a stream of statements (hence the error code 100 when trying to use execute). A special method (hence rawExecSQL) is required as most of the in-built methods only allow a single statement to be run.

Working Example

Here's a working example, with other attempts commented out with the result (including the error code 100 if trying to use SQLiteStatement).

The example creates the normal database, loads some data, extracts and dumps the data (for comparison/proof) closes it using the stock android SQLiteDatabase methods.

The encrypted database is then created using the SQlCipher openorcreate method and then immediately closed (thus creating the file).

The normal database is then opened with the SQLCipher methods, the newly created empty encrypted database is then attached, the conversion then done and the encrypted database is detached. The normal is then closed.

Finally the new encrypted database is open, the data extracted and dumped (for comparison/proof).

The code is :-

public class MainActivity extends AppCompatActivity {

    String normaldbname = "mydb";
    String encrypteddbname = "myencrypteddb";
    String password = "thepassword";
    String tablename = "mytable";
    String idcolumn = BaseColumns._ID;
    String namecolumn = "name";
    String[] namelist = new String[]{
            "Fred","Anne","Jane","John",
    };

    SQLiteDatabase normaldb;
    net.sqlcipher.database.SQLiteDatabase normal_for_encryption;
    net.sqlcipher.database.SQLiteDatabase encrypteddb;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        net.sqlcipher.database.SQLiteDatabase.loadLibs(this);
        normaldb = SQLiteDatabase.openOrCreateDatabase(this.getDatabasePath(normaldbname).getPath(),null);
        normaldb.execSQL("CREATE TABLE IF NOT EXISTS " + tablename + " (" +
                idcolumn +
                " INTEGER PRIMARY KEY, " +
                namecolumn +
                " TEXT)");
        ContentValues cv = new ContentValues();
        normaldb.beginTransaction();
        //for (int i=0; i < 1000; i++) { for larger test
            for (String name : namelist) {
                cv.clear();
                cv.put(namecolumn, name);
                normaldb.insert(tablename, null, cv);
            }
        //}
        normaldb.setTransactionSuccessful();
        normaldb.endTransaction();
        DatabaseUtils.dumpCursor(
                normaldb.query(tablename,null,null,null,null,null,null)
        );
        normaldb.close();

        net.sqlcipher.database.SQLiteDatabase.openOrCreateDatabase(this.getDatabasePath(encrypteddbname).getPath(),password,null).close();


        normal_for_encryption = net.sqlcipher.database.SQLiteDatabase.openDatabase(
                this.getDatabasePath(normaldbname).getPath(),
                "",null,
                net.sqlcipher.database.SQLiteDatabase.OPEN_READWRITE
        );
        net.sqlcipher.database.SQLiteStatement stmnt = normal_for_encryption.compileStatement("ATTACH DATABASE ? AS encrypted KEY ?");
        stmnt.bindString(1,this.getDatabasePath(encrypteddbname).getPath());
        stmnt.bindString(2,password);
        stmnt.execute();

        /* Ouch net.sqlcipher.database.SQLiteException: error code 100: another row available
        net.sqlcipher.database.SQLiteStatement stmnt2 = normal_for_encryption.compileStatement("SELECT sqlcipher_export('encrypted')");
        stmnt2.execute();
        */
        //normal_for_encryption.rawQuery("SELECT sqlcipher_export('encrypted')",null); //<<<<<<<<< Ouch no such table: mytable: , while compiling: SELECT * FROM mytable
        //normal_for_encryption.execSQL("SELECT sqlcipher_export('encrypted')"); //<<<<<<<<< Ouch net.sqlcipher.database.SQLiteException: unknown error: Queries cannot be performed using execSQL(), use query() instead.
        normal_for_encryption.rawExecSQL("SELECT sqlcipher_export('encrypted')"); //<<<<<<<<< WORKS >>>>>>>>>>
        normal_for_encryption.execSQL("DETACH DATABASE encrypted");
        normal_for_encryption.close();
        encrypteddb = net.sqlcipher.database.SQLiteDatabase.openDatabase(
                this.getDatabasePath(encrypteddbname).getPath(),
                password,null,
                net.sqlcipher.database.SQLiteDatabase.OPEN_READWRITE
        );
        net.sqlcipher.DatabaseUtils.dumpCursor(
                encrypteddb.query(tablename,null,null,null,null,null,null)
        );
        encrypteddb.close();
    }
}

• Again note the commented out lines, that do not work.
• The only security flaw in the above is the password which was not protected for convenience.

Result :-

Part 1 - prior to the conversion/encryption :-

2019-05-14 21:10:54.032 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@c237ffc
2019-05-14 21:10:54.032 I/System.out: 0 {
2019-05-14 21:10:54.032 I/System.out:    _id=1
2019-05-14 21:10:54.032 I/System.out:    name=Fred
2019-05-14 21:10:54.032 I/System.out: }
2019-05-14 21:10:54.032 I/System.out: 1 {
2019-05-14 21:10:54.032 I/System.out:    _id=2
2019-05-14 21:10:54.033 I/System.out:    name=Anne
2019-05-14 21:10:54.033 I/System.out: }
2019-05-14 21:10:54.033 I/System.out: 2 {
2019-05-14 21:10:54.033 I/System.out:    _id=3
2019-05-14 21:10:54.033 I/System.out:    name=Jane
2019-05-14 21:10:54.033 I/System.out: }
2019-05-14 21:10:54.033 I/System.out: 3 {
2019-05-14 21:10:54.034 I/System.out:    _id=4
2019-05-14 21:10:54.034 I/System.out:    name=John
2019-05-14 21:10:54.034 I/System.out: }
2019-05-14 21:10:54.034 I/System.out: <<<<<

Part 2 after encryption from the encrypted dataabse.

2019-05-14 21:10:54.871 I/System.out: >>>>> Dumping cursor net.sqlcipher.CrossProcessCursorWrapper@1bff13d
2019-05-14 21:10:54.872 I/System.out: 0 {
2019-05-14 21:10:54.872 I/System.out:    _id=1
2019-05-14 21:10:54.872 I/System.out:    name=Fred
2019-05-14 21:10:54.872 I/System.out: }
2019-05-14 21:10:54.872 I/System.out: 1 {
2019-05-14 21:10:54.872 I/System.out:    _id=2
2019-05-14 21:10:54.872 I/System.out:    name=Anne
2019-05-14 21:10:54.872 I/System.out: }
2019-05-14 21:10:54.872 I/System.out: 2 {
2019-05-14 21:10:54.872 I/System.out:    _id=3
2019-05-14 21:10:54.872 I/System.out:    name=Jane
2019-05-14 21:10:54.872 I/System.out: }
2019-05-14 21:10:54.873 I/System.out: 3 {
2019-05-14 21:10:54.873 I/System.out:    _id=4
2019-05-14 21:10:54.873 I/System.out:    name=John
2019-05-14 21:10:54.873 I/System.out: }
2019-05-14 21:10:54.873 I/System.out: <<<<<