CoderAnswer - 程序员编程问答

net.sqlcipher.database.SQLiteException:错误代码100:另一行可用

by @kartoos khan

android sqlite android-sqlite prepared-statement sqlite-cipher

我正在使用SQLiteCipher来加密我的数据库。以前我使用的db.execsql()语句工作正常。现在我将查询更改为SQLStatment

这是我的代码

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

第一个执行语句运行,但第二个执行语句抛出异常。

这是堆栈跟踪

java.lang.RuntimeException: Unable to start activity ComponentInfo{com.package/com.package.ui.Dashboard}: net.sqlcipher.database.SQLiteException:错误代码100:另一行可用
        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:错误代码100:另一行可用
        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)

1个答案

0 by @MikeT

我相信唯一的解决方案是使用专门创建的 rawExecSQL 方法。

使用它没有安全问题,因为没有用户输入,因此没有机会进行SQL注入。也许请参阅SQL injection

我认为问题在于一般exec / execute允许有限的结果,rawQuery / query将返回一个Cursor。我认为转换可能是生成SQL,通过加密数据然后将结果SQL作为语句流执行来修改它(因此在尝试使用execute时错误代码为100)。需要一个特殊的方法(因此 rawExecSQL ),因为大多数内置方法只允许运行单个语句。

Working Example

这是一个工作示例,其他尝试已注释掉结果(如果尝试使用 SQLiteStatement ,则包括错误代码100)。

该示例创建 normal 数据库,加载一些数据,提取和转储数据(用于比较/证明)使用stock android SQLiteDatabase方法关闭它。

然后使用SQlCipher openorcreate方法创建加密的数据库,然后立即关闭(从而创建文件)。

然后使用SQLCipher方法打开 normal 数据库,然后附加新创建的空加密数据库,然后完成转换并分离加密的数据库。然后关闭 normal

最后新的加密数据库打开,数据被提取和转储(用于比较/证明)。

代码是: -

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 (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); // <<<<<<<<<哎哟没有这样的表:mytable :,同时编译:SELECT * FROM mytable
        //normal_for_encryption.execSQL("SELECT sqlcipher_export('encrypted')"); // <<<<<<<<< Ouch net.sqlcipher.database.SQLiteException:未知错误:无法使用execSQL()执行查询,而是使用query()。
        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();
    }
}

Result :-

Part 1 - prior to the conversion/encryption :-

2019-05-14 21:10:54.032 I/System.out: >>>>> Dumping cursor [email protected]
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 [email protected]
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: <<<<<  

相关问题