net.zetetic.database.sqlcipher.SQLiteException: unknown error (code 0): Queries can be performed using SQLiteDatabase query or rawQuery methods only.

[Chozzle]

I am trying to handle migration for SQLCipher 3 to 4 using a SQLiteDatabaseHook. This code fails with the error:
net.zetetic.database.sqlcipher.SQLiteException: unknown error (code 0): Queries can be performed using SQLiteDatabase query or rawQuery methods only.

@Override
public void postKey(SQLiteConnection connection) {
    connection.execute("PRAGMA cipher_migrate", null, null);
}

However there is a workaround to use one of the other methods which returns a value. It seems like the default execute method isn't compatible with the latest Android restrictions. This code works:

@Override
public void postKey(SQLiteConnection connection) {
    connection.executeForString("PRAGMA cipher_migrate", null, null);
}

[developernotes]

Hi @Chozzle

The API is slightly different between the legacy and new version of SQLCipher for Android. The execute(...) ¹ method does not expect a result to be returned, whereas executeForString(...) ² expects a single result. The PRAGMA cipher_migrate command returns a result code that reflects the success or failure of the migration.

Footnotes

1. https://github.com/sqlcipher/sqlcipher-android/blob/master/sqlcipher/src/main/java/net/zetetic/database/sqlcipher/SQLiteConnection.java#L562-L601 ↩

2. https://github.com/sqlcipher/sqlcipher-android/blob/master/sqlcipher/src/main/java/net/zetetic/database/sqlcipher/SQLiteConnection.java#L646-L687 ↩

[xuhongchang]

i have the same problem

android.database.sqlite.SQLiteException: SQL logic error (code 1): , while compiling: SELECT COUNT(*) FROM sqlite_schema;
at net.zetetic.database.sqlcipher.SQLiteConnection.nativePrepareStatement(Native Method)
at net.zetetic.database.sqlcipher.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:973)
at net.zetetic.database.sqlcipher.SQLiteConnection.executeForLong(SQLiteConnection.java:628)
at net.zetetic.database.sqlcipher.SQLiteConnection.open(SQLiteConnection.java:240)
at net.zetetic.database.sqlcipher.SQLiteConnection.open(SQLiteConnection.java:202)
at net.zetetic.database.sqlcipher.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:475)
at net.zetetic.database.sqlcipher.SQLiteConnectionPool.open(SQLiteConnectionPool.java:189)
at net.zetetic.database.sqlcipher.SQLiteConnectionPool.open(SQLiteConnectionPool.java:181)
at net.zetetic.database.sqlcipher.SQLiteDatabase.openInner(SQLiteDatabase.java:1028)
at net.zetetic.database.sqlcipher.SQLiteDatabase.open(SQLiteDatabase.java:1013)
at net.zetetic.database.sqlcipher.SQLiteDatabase.openDatabase(SQLiteDatabase.java:840)
at net.zetetic.database.sqlcipher.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:359)
at net.zetetic.database.sqlcipher.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:278)

here is my code :
public void postKey(SQLiteConnection connection) {

          String s=  connection.executeForString("PRAGMA cipher_migrate", null, null);

        }

[developernotes]

Hi @xuhongchang,

Try calling executeForLong instead as PRAGMA cipher_migrate will return a numeric result code. We have an example within the test suite here.

[xuhongchang]

What is the difference between executeForString and executeForLong besides the different return value types?

[xuhongchang]

There's still a problem. from android-database-sqlcipher 3.5.9 to sqlcipher-android4.5.6

(1) SQL logic error in "SELECT COUNT() FROM sqlite_schema;"
2024-03-25 10:38:21.994 10557-10557 SQLiteDatabase com.test.test E Failed to open database '/data/user/0/com.test.test/databases/test.db'.
android.database.sqlite.SQLiteException: SQL logic error (code 1): , while compiling: SELECT COUNT() FROM sqlite_schema;
at net.zetetic.database.sqlcipher.SQLiteConnection.nativePrepareStatement(Native Method)
at net.zetetic.database.sqlcipher.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:973)
at net.zetetic.database.sqlcipher.SQLiteConnection.executeForLong(SQLiteConnection.java:628)
at net.zetetic.database.sqlcipher.SQLiteConnection.open(SQLiteConnection.java:240)
at net.zetetic.database.sqlcipher.SQLiteConnection.open(SQLiteConnection.java:202)
at net.zetetic.database.sqlcipher.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:475)
at net.zetetic.database.sqlcipher.SQLiteConnectionPool.open(SQLiteConnectionPool.java:189)
at net.zetetic.database.sqlcipher.SQLiteConnectionPool.open(SQLiteConnectionPool.java:181)
at net.zetetic.database.sqlcipher.SQLiteDatabase.openInner(SQLiteDatabase.java:1028)
at net.zetetic.database.sqlcipher.SQLiteDatabase.open(SQLiteDatabase.java:1013)
at net.zetetic.database.sqlcipher.SQLiteDatabase.openDatabase(SQLiteDatabase.java:840)
at net.zetetic.database.sqlcipher.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:359)
at net.zetetic.database.sqlcipher.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:278)

[developernotes]

Hi @xuhongchang,

It is difficult to say what the cause of your error is currently. As you are migrating from version 3.x to 4.x, I would recommend trying to perform the migration locally on your host machine first to make sure the migration is working properly outside of the device with the correct key material. You can use the SQLCipher command line shell to do this.

It is interesting that the error message you are receiving a SQL logic error and the output contains the following:

SELECT COUNT() FROM sqlite_schema;

which is missing the * from the query which would run after opening the connections, and running the preKey/postKey operations.

[xuhongchang]

I made a mistake up there.The query does not have a lesser * number,and execute in post key method
long result = connection.executeForLong("PRAGMA cipher_migrate;", null, null);
the result is 1.

environments：from android-database-sqlcipher 3.5.9 to sqlcipher-android4.5.6

error log：

SQLiteConnection        com.test.test                    I  Database keying operation returned:0
2024-03-26 09:50:28.292 30747-30747 X-LOG                   com.test.test                    I  SQLCipherHelperImpl$1.postKey(L:30): SQLCipherHelperImpl  postKey
2024-03-26 09:50:28.819 30747-30747 SQLiteLog               com.test.test                    E  (26) statement aborts at 2: [PRAGMA user_version;] file is not a database
2024-03-26 09:50:28.932 30747-30747 SQLiteLog               com.test.test                    E  (26) statement aborts at 2: [PRAGMA user_version;] file is not a database
2024-03-26 09:50:28.941 30747-30747 SQLiteLog               com.test.test                    E  (26) statement aborts at 2: [PRAGMA user_version;] file is not a database
2024-03-26 09:50:28.957 30747-30747 SQLiteLog               com.test.test                    E  (26) statement aborts at 2: [PRAGMA user_version;] file is not a database
2024-03-26 09:50:28.959 30747-30747 			            com.test.test                    W  JNI critical lock held for 654.839ms on Thread[1,tid=30747,Runnable,Thread*=0x7df1d33800,peer=0x72769888,"main"]
2024-03-26 09:50:28.962 30747-30747 X-LOG                   com.test.test                    I  SQLCipherHelperImpl$1.postKey(L:35): post key:1
2024-03-26 09:50:28.963 30747-30747 SQLiteLog               com.test.test                    E  (1) SQL logic error in "SELECT COUNT(*) FROM sqlite_schema;"
2024-03-26 09:50:28.968 30747-30747 SQLiteDatabase          com.test.test                    E  Failed to open database '/data/user/0/com.test.test/databases/Test.db'.
                                                                                                   android.database.sqlite.SQLiteException: SQL logic error (code 1): , while compiling: SELECT COUNT(*) FROM sqlite_schema;
                                                                                                   	at net.zetetic.database.sqlcipher.SQLiteConnection.nativePrepareStatement(Native Method)
                                                                                                   	at net.zetetic.database.sqlcipher.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:973)
                                                                                                   	at net.zetetic.database.sqlcipher.SQLiteConnection.executeForLong(SQLiteConnection.java:628)
                                                                                                   	at net.zetetic.database.sqlcipher.SQLiteConnection.open(SQLiteConnection.java:240)
                                                                                                   	at net.zetetic.database.sqlcipher.SQLiteConnection.open(SQLiteConnection.java:202)
                                                                                                   	at net.zetetic.database.sqlcipher.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:475)
                                                                                                   	at net.zetetic.database.sqlcipher.SQLiteConnectionPool.open(SQLiteConnectionPool.java:189)
                                                                                                   	at net.zetetic.database.sqlcipher.SQLiteConnectionPool.open(SQLiteConnectionPool.java:181)
                                                                                                   	at net.zetetic.database.sqlcipher.SQLiteDatabase.openInner(SQLiteDatabase.java:1028)
                                                                                                   	at net.zetetic.database.sqlcipher.SQLiteDatabase.open(SQLiteDatabase.java:1013)
                                                                                                   	at net.zetetic.database.sqlcipher.SQLiteDatabase.openDatabase(SQLiteDatabase.java:840)
                                                                                                   	at net.zetetic.database.sqlcipher.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:359)
                                                                                                   	at net.zetetic.database.sqlcipher.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:278)

how to use shell command to migrate?

[developernotes]

Hi @xuhongchang,

When you have the SQLCipher command line shell, you can provide the key material and perform the PRAGMA cipher_migrate; command ¹.

Footnotes

1. https://www.zetetic.net/sqlcipher/sqlcipher-api/#cipher_migrate ↩