Android Question SQLCipher-problems...

[BlueVision]

Searched the forum for hours now, no success...

The problem is hard to describe. I am using SQLCipher V1.2 and SQL V1.2 in my current project on B4A 3.2. My program is able to encrypt and decrypt its own database by copying the contents of the database into a new one and renaming it after that. So far so good.
When giving the apk-file away, the program is crashing always when accessing an encrypted database. "Database disk image is malformed" when accessing the first table. The remote device is API 10.
When I do the same scenario on my developing device (API 15), all works fine.

So is there anything general I forgot to put into the apk-file? The "icudt46l.zip" is included in the files-folder. As it looks to me, there is no encryption support on the remote device, only on my developing device. Whats wrong, whats missing? Thanks for any hint.

Cheers, BV

 

[Erel]

Try to create a small project that creates a new encrypted database and see whether it works on the other device.

 

[BlueVision]

Thanks for replying Erel.
This is becoming more and more strange to me. I connected the API10-Device via B4A-Bridge and gave the compiler another run directly to that device. Same thing. Can't be something I missed to put into the files-folder. In opposite to my API15-device, after running the encryption-routine on the API10-device and restarting the application, there is a "corrupted" database. The database is encrypted, because I can't watch it with the SQLLite-Viewer. Maybe it is really only an issue with the password created by the device itself. Hopefully. I will try to test with a fixed password next time I have access to that device, because the owner needs it.
If the bug is within my coding, then I ask myself why my device is working properly. I just gave the routine some runs on it. 3times encrypted and decrypted. the content of the database got no "modification", no error during the routine's run and the data is visible with the SQLLite-Viewer after that.
Bad thing is, there is no chance to do investigations with the corrupted database itself.
I'll keep you informed. Maybe it is really only a password thing or whatever. The password is generated from device-specific IMEI, gave it a readout on that API10-phone, works. Need some time and another phone for testing...

 

[BlueVision]

A late response now. Still unsolved, but I found the problem.

Sub Databasecheck1
'Check unencrypted/encrypted 0/1
    Try
        SQL1.Initialize(DBDirName, DBFileName1, False)
    Catch
        Flag = 1
        ImageView3.Bitmap = LoadBitmap(File.DirAssets,"Locked.png")
    End Try
    Try
        SQL2.Initialize(DBDirName, DBFileName1, False, CODE,"")
    Catch
        Flag = 0
        ImageView3.Bitmap = LoadBitmap(File.DirAssets,"Unlocked.png")
    End Try   
End Sub

The code above works on my API15, but not on the API10. SQL1 is defined as SQL, SQL2 as SQLCipher
Looks like only initializing a database is not enough to detect encryption. I created a blank unencrypted database on both devices and gave then my encryption-routine a run. Then restarting the program. On API15 (HTC) this small routine works, sets the flag to 1 and the image in the imageview gets a change. On API10 (Samsung) both catch-codings will not be touched.
So the question is now, how to detect in a fast way if a database is encrypted or not.

Any ideas?

 

[Erel]

You can issue this query:
"SELECT name FROM sqlite_master WHERE type = 'table'"

 

[BlueVision]

Hello Erel,
I fixed it so far with the attempt to read the content of a table. This is like standing in the middle of a wood without realizing the trees near me. Your method is for sure not that timeconsuming like mine. I will give it a try, looks promising. Sometimes when working on a problem it is good to get some ideas from outside, resetting the thinking to the basics, restart with an easy solution... Thanks for guiding!

 

[BlueVision]

A very late update:
Because there was no solution for my problem, I focused in finishing my project first. Now the only thing not working is this issue with SQLCipher. It works perfekt on Android 4.01, fails on 4.4 and 5.01 in the same way.

1. Creating an unencryted database
2. Filled database with data
3. Copied the data of the unencrypted database to a new encrypted database
4. Opening this database works on the 4.01-device, fails with Android version 4.4 and above
5. Copying back to an unencrypted database works on the 4.01-device perfectly

For the 4.4-and-above-devices, the copy-routine works when I do it without encryption (using normal SQL-commands)

I changed the SQLCipher-Library via downloading the files from the link Erel gave in the introdution-section of SQLCypher. There is another version available, appearing as Version 1.31 when loaded (not Version 3, this is confusing me). I exchanged all the sqlcipher-files in the library's folder and exchanged also the zip-file within the files-folder. B4A-version is 3.82 now.

No success.
Anybody an idea whats going on here? Looks to me like an incompatibility when running on Kitkat and above, maybe Jellybean too.

 

[Erel]

I've just tested it with Android 5.1 and it worked fine.

The B4A library version is 1.20 and I've downloaded SQLCipher310.zip.

If you are testing it in debug mode then make sure to set this attribute:
#DebuggerForceStandardAssets: true

If it still doesn't work for you then please post the error message.

 

[BlueVision]

Hi Erel, thanks for responding so fast.
a) I added the DebuggerForceStandardAssets-Line months ago, it's there
b) First thing this morning, I changed the SQLCipher-password to a fixed one, hard encoded for testing puposes. Then started encrypting on my 5.01-device, copied the database to the IceCreamSandwich-device and gave it a run. It came up, all fine, detected an encoded database and was able to get the data from the database. Converting to an unencrypted database on that device after that ran also without an error.

The working principle of my application forces an automatic shutdown of the application after encryting/decrypting is done. Simply to prevent problems in touching the database in a wrong way and to adjust running variables once during start.

I defined SQL1 as SQL and SQL2 as SQLcipher. Thats all. During app-start I try to detect if the database is encrypted or not with Try/Catch with this code (I know there could be a faster way by checking for a special table within the database, can change this later, but it works in this way on 4.01).

Try
SQL1.Initialize(DBDirName, DBFileName1, False)
Dim Query2 AsString
Query2 = "SELECT ID, Latitude, Longitude, Customer, Code, Town, Street, Contact, Phone, Comment, Timestamp, Pairing FROM " & DBTableName1 & " ORDER BY Customer ASC"
LstTable5 = DBUtils.ExecuteMemoryTable(SQL1, Query2, Null, 0)
Catch
OdysseyFlag = 1
ImageView3.Bitmap = LoadBitmap(File.DirAssets,"Locked.png")
EndTry
Try
SQL2.Initialize(DBDirName, DBFileName1, False, PHIMEI,"")
Dim Query2 AsString
Query2 = "SELECT ID, Latitude, Longitude, Customer, Code, Town, Street, Contact, Phone, Comment, Timestamp, Pairing FROM " & DBTableName1 & " ORDER BY Customer ASC"
LstTable5 = DBUtils.ExecuteMemoryTable(SQL2, Query2, Null, 0)
Catch
OdysseyFlag = 0
ImageView3.Bitmap = LoadBitmap(File.DirAssets,"Unlocked.png")
EndTry
LstTable5.Clear

My database consists of 4 tables, LOCATIONS, REPORTS, SETTINGS, TEMPDATA.
Using DBUtils for handling the database, I get this error from within DBUtils during running ExecuteMemoryTable, the database was encrypted before that on the same device.

table could not be found... (DBUtils Codeline 166)

Connected to B4A-Bridge (Wifi)
Installing file.
** Activity (main) Pause, UserClosed = false **
PackageAdded: package:Odyssey.Final
** Activity (main) Create, isFirst = true **
ExecuteMemoryTable: Select ID, DE FROM LOCALIZATION
(Main, 6454) Object was already initialized. (warning #1003)
net.sqlcipher.database.SQLiteException: no such table: Locations: , while compiling: SELECT ID, Latitude, Longitude, Customer, Code, Town, Street, Contact, Phone, Comment, Timestamp, Pairing FROM Locations ORDER BY Customer ASC
at net.sqlcipher.database.SQLiteCompiledSql.native_compile(Native Method)
at net.sqlcipher.database.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:91)
at net.sqlcipher.database.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:64)
at net.sqlcipher.database.SQLiteProgram.<init>(SQLiteProgram.java:83)
at net.sqlcipher.database.SQLiteQuery.<init>(SQLiteQuery.java:49)
at net.sqlcipher.database.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:42)
at net.sqlcipher.database.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1441)
at net.sqlcipher.database.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1410)
at anyhwheresoftware.b4a.objects.sqlcipher.SQLCipher.ExecQuery2(SQLCipher.java:198)
at anyhwheresoftware.b4a.objects.sqlcipher.SQLCipher.ExecQuery(SQLCipher.java:185)
at Odyssey.Final.dbutils._executememorytable(dbutils.java:703)
at Odyssey.Final.main._databasecheck1(main.java:10130)
at Odyssey.Final.main._activity_create(main.java:1350)
at java.lang.reflect.Method.invoke(Native Method)
at java.lang.reflect.Method.invoke(Method.java:372)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:175)
at Odyssey.Final.main.afterFirstLayout(main.java:98)
at Odyssey.Final.main.access$100(main.java:16)
at Odyssey.Final.main$WaitForLayout.run(main.java:76)
at android.os.Handler.handleCallback(Handler.java:739)
at android.os.Handler.dispatchMessage(Handler.java:95)
at android.os.Looper.loop(Looper.java:155)
at android.app.ActivityThread.main(ActivityThread.java:5696)
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:1028)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:823)

Hope this helps. As said before, my code runs perfect on Android 4.01 with an HTC One V, the trouble starts with my GalaxyTab3 running 4.42 and is the same on my on my HTC ONE M8 running 5.01.
I bet you can help me.

 

[Erel]

In order to test it, please create a new project with a different package name and create a simple table. Do you get any error?

 

[BlueVision]

Ok Erel,
I will reduce all the coding of my project down to that problem and try to create a small application by using the same routines. This will need some time...

 

[BlueVision]

Please download DBCrypto from here, the sourcecode is not a big puzzle, easy to understand. It shows the problem, fails exactly in the same way on Android 4.4 and above...

https://www.dropbox.com/sh/7kqd5ohqbce13ix/AAD5L0gZSxRkJ3dVIMfuURSsa?dl=0

 

[Erel]

It is better to create a really small project with only the relevant features. Try this code:

Sub Process_Globals
   Private sql1 As SQLCipher
End Sub

Sub Globals

End Sub

Sub Activity_Create(FirstTime As Boolean)
   If FirstTime Then
     File.Delete(File.DirInternal, "1.db")
     sql1.Initialize(File.DirInternal, "1.db", True, "123", "")
     sql1.ExecNonQuery("CREATE TABLE table1 (col1 TEXT)")
     sql1.ExecNonQuery("INSERT INTO table1 VALUES ('aa')")
     Log(sql1.ExecQuerySingleResult("SELECT * FROM table1"))
   End If
End Sub

1. Use a different package name (one that you haven't used before).
2. Make sure to add the icu file to the Files tab.

 

[BlueVision]

Erel, thanks for your patience with me. Feeling really like a pain in the neck for you already.
Sorry, but your answer does not really help. I am using DBUtils, because I am not that familiar with that SQL-terminology. So far DBUtils worked fine for me, I love this interface of macros for handling SQL-databases. My whole program works with it.
In the way I understand your code above, it creates an encoded database and fills it with a single value. This works with DBUtils and SQLcipher in the same way. In my problem-application there is a need for the user to encrypt a complex database for transferring via Email. All is running fine without encryption. Encrypting (copying all the data into a new with SQLcipher created database) works fine too. I proved that by decrypting this database on another device. But loading an encrypted database with DBUtils-functions will always run into an error here on my site, exception is Android 4.0, there the code works as expected. Maybe Android 2.3 will make it too, I have no chance for testing it.

The coding above is not my problem-program, this contains more than 7000 lines of code. I wrote that program above (230 lines of code) to make it easy for you, because it is really hard for me to define the problem exactly in words. It's an extract that creates only an unencrypted database during installation, then by clicking on a button you can add data and it gets displayed in a webview. Another two buttons are there for encrypting and decrypting the database. The zip contains the whole project, design-file and the ICU-file included, because of this the filesize looks huge.

Did you gave the small program a run? Please don't understand me wrong. There is no need for an immediate response. Maybe others can help too. I think we have enough expert knowledge here, you have enough work on your shoulders already.
But if you try, run it in debug mode after you encrypted the database for monitoring. It will fail during the attempt of loading the encrypted database within a DBUtils routine (think it was ExecuteMemorytable, stop at DBUtils-line 166). The question is why. And this is exactly the problem I have with my big program.

BTW: Is it possible that this behave depends somehow with the API-Levels installed within the SDK-Manager? Is there a recommendation which API's to install?

 

[Erel]

Did you gave the small program a run?

Yes. But there were too many things that I didn't understand or didn't think that they were correct in this program.

In order to debug such issues you need to simplify the process as much as possible. So with the code I posted above we see that SQLCipher works properly on all your device. At least in this case.

What is the purpose of encrypting the unencrypted database? Why not work with the encrypted database only?

 

[BlueVision]

Hi Erel,
good question. sure I can change it to encrypted only. But there comes the point where I have to exchange data from the databases of different devices. On each device a different password... For exchanging and mailing the data, there is a need of using a public or userdefined password.

There are some requirements and this is the reason, why DBCrypt may look complicated. It isn't really.

I stripped it down to these minimum requirements:

- installs automatically preferred on external, if not on internal memory a blank database
- filling the database independing if it's encrypted or not and displaying in a webview
- managing button-settings depending if it's encrypted or not
- changing of the encryption state with a click of a button
- displaying encryption state
- all operations run with help of DBUtils

There are some problems in realizing this, because fileoperations are needed. Renaming is not easy and so on.
I tried several methods without success. Last was reading the data into a list, deleting the database-file and creating a new file with help of the list.

The question is, why does it always run on my old HTC, but not on newer Androids when it comes to open the encrypted database? Maybe somebody is able to test with an Android-version below 4.0? Does it fail too?

My programming is not perfect, also everybody is using it's own stile. But the code runs through the compiler and works on my old HTC as expected...

Further investigating, this gives me real headache.

 

[BlueVision]

Can you confirm the following?

In my project I put the icudt46l.zip-file to the files-folder for including into the final apk.
After installation of that apk on an android-version 4.0 I can find the icudt46l.dat within the folder system/usr/icu on that device. Think this is the normal process during installation of the apk, the icudt46l.zip gets unpacked and the dat-file copied to the icu-folder.
On that device everything is fine.

For the failing devices:
The same apk installed on android 4.4.2 shows a file called icudt51l.dat within the icu-folder. Filesize is 18.33 MB, date 03.04.2015
The same apk installed on android 5.0.1 shows a file called icudt53l.dat within the icu-folder. Filesize is 17,85 MB, date 01.08.2008

Think this is wrong. No appearance of icudt46l.dat there.
I tryed to copy the file manually to the ICU-folder for testing. This is not possible with a filemanager. The filesystem is flagged as read only. Maybe this is the reason of the missing file. Starting with Jellybean, something changed...

Think this is the problem. Pls. respond.

Andi

 

[Erel]

Have you tried to follow the exact steps I wrote in post #13?

What were the results?

 

[BlueVision]

I tried your code and it works, Erel. Getting an "aa"-echo within the logs during execution. Unfortunally it solves not the problem, because it is not complex enough to create the problem. Really hard to describe. Invested hours in finding the reason why my coding fails starting with Android version 4.4, maybe earlier. I finally gave up. It makes no sense to me, that the coding works on Android 4.0 and below and fails on later versions. The reason for doing it in this way was to have a single datafile, detecting by a touch, if it is encrypted or not. As said before, this worked previously. I simplified my coding within the testprogram more and more, but used strictly DBUtils. All without success or any steps further.

It runs without failure when I start using a second filename, one for use with SQLcipher and one for normal SQL. The advantage of this is, that you already realize by the filename if it's encrypted or not. Changed in this way my little testprogram, the code ran immediately, independing the android version. Not that elegant than before, but it works now. I will use this principle now within my app ODYSSEY, have to implement it there and giving it an edurable test there.