Android Tutorial Android database encryption with SQLCipher library

walterf25 · Jan 28, 2015

Erel said:
You cannot load an unencrypted database with SQLCipher. You need to load it with SQL.

Erel thanks for the reply, i think i might have explained the issue wrong, i did set encrypt my database with SQlite Expert Pro 3, but i get the error posted in the post above.

What could be going on?

Thanks,
walter

Erel · Jan 29, 2015

There are all kinds of encryption methods. Does this program use SQLCipher? Which version of it?

tdocs2 · Feb 14, 2015

Greetings, all.

Thank you in advance for your replies.

I finally got around to testing the SQLCipher data base encryption. I used DBUtils with the required modifications.

QUESTION

Is the best place to hide the password Process Globals?

Code: Copy

Sub Process_Globals
    Dim SQL As SQLCipher
    Dim DBPASSWORD As String : DBPASSWORD="Password123"
End Sub

Best regards.

Sandy

tdocs2 · Feb 14, 2015

Greetings, all.

I ran a parallel test to determine the impact on the size of the data bases and apk's using DBUtils with SQL and with SQLCipher. I changed the parameters to make the Student Table 1000 records and 100 tests per student.

SQL:
DB Size (per ES Explorer): 2.53MB
SQLCipher:
DB Size (per ES Explorer): 2.69MB - only a 6% increase - good news.

The apk is a different matter (based on the device App Manager after clearing the data):
Android 4.4
SQL: 416KB
SQLCipher: 6.88MB
Android 4.2
SQL: 408KB
SQLCipher: 5.16MB

These apk stats seem to be contrary to those reported in this thread. The SQLCipher adds around 5MB to the apk (look at the size of the jars and the asset required)

The other good news - it works. I tried to open the SQLCipher db with other apps, and I could not at all. Like Erel said in another post, it encrypts everything!

I did not test performance. If anyone has done this, please add it to this thread. If anyone knows of any good tools to measure SQLite performance, please let me know.

Any replies are welcome.

Sandy

PS: I have a question on the use of the Apache License which I will post on a new thread (see here).

Derek Jee · Apr 28, 2015

Hi there

I have just purchased the latest SQLLiteManager (vers 4.3.5) to help with my database design. I have created a database and added it to the files folder. When I run the project it errors trying to initialise the database with the following error. It is an SQLCipher encrypted database.. I also have updated my SQLCipher lib to 1.31.. can you advise?

Thanks,

Derek.

net.sqlcipher.database.SQLiteException: unable to open database file
at net.sqlcipher.database.SQLiteDatabase.dbopen(Native Method)
at net.sqlcipher.database.SQLiteDatabase.<init>(SQLiteDatabase.java:1951)
at net.sqlcipher.database.SQLiteDatabase.openDatabase(SQLiteDatabase.java:901)
at net.sqlcipher.database.SQLiteDatabase.openDatabase(SQLiteDatabase.java:948)
at anyhwheresoftware.b4a.objects.sqlcipher.SQLCipher.Initialize(SQLCipher.java:51)
at b4a.example.main._activity_create(main.java:445)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:187)
at b4a.example.main.afterFirstLayout(main.java:100)
at b4a.example.main.access$100(main.java:17)
at b4a.example.main$WaitForLayout.run(main.java:78)
at android.os.Handler.handleCallback(Handler.java:733)
at android.os.Handler.dispatchMessage(Handler.java:95)
at android.os.Looper.loop(Looper.java:146)
at android.app.ActivityThread.main(ActivityThread.java:5653)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1291)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1107)
at dalvik.system.NativeStart.main(Native Method)

Erel · Apr 29, 2015

Try to create the database on the Android and then copy it to the desktop.

Derek Jee · Apr 29, 2015

Hi Erel

Thank you, that worked.. But when I amend the database on the desktop and copy it back into the app then I get the same error running the app opening the database.. So it didn't work.. Sorry.

Derek..

Derek Jee · Apr 29, 2015

Hi Again Erel

I am now not actually convinced that it is anything to do with my new purchase.. After taking your advice I copied the database file to my desktop and without opening it I placed it into my files folder and reloaded the app onto the device.. it sill errors with the same message when I do this so it must not like being copied to my desktop and back.. Strange..

Derek Jee · Apr 29, 2015

After all that.. It was because I was trying to access the db from the DirAssets folder and I have just learned that you cannot do that. I copy the db into DirInternal and use it from there.. Scare over

Sorry to bother you. But thanks..

Derek.

koaunglay · May 28, 2015

Erel said:
The native SQLite engine included in the OS doesn't support encryption.
SQLCipher is an open source project that extends SQLite and adds full database encryption.

B4A SQLCipher object is a special subtype of SQL object. There is almost no need to change any code in order to switch from regular SQL to SQLCipher.

SQLCipher depends on a resource named icudt46l.zip. You should add this resource to the Files tab.

Preferably you should use a real device when developing as the emulator is too slow to handle the native resources.

The only difference between SQL API and SQLCipher API is the Initialize method.
SQLCipher.Initialize expects two additional values: Password and NativeLibsFolder.

Password is the database password. You can pass an empty string if there is no password. Note that it is not possible to change the password (or set a new password) to an existing database.

Code changes required to convert from SQL to SQLCipher
- Declare the SQL object as SQLCipher.
- Change the initialize code to:

Code: Copy

ProgressDialogShow("Initializing database...") SQL1.Initialize(File.DirRootExternal, "1.db", True, DB_PASSWORD, "") ProgressDialogHide

Note that there is no need to change anything in DBUtils module.

The library can be downloaded here:
http://www.b4x.com/android/files/SQLCipher.zip

Another compiled library is available which is based on SQLCipher v3. Note that this library is not compatible with databases created with previous versions. You can download it here:
http://www.b4x.com/android/files/SQLCipher310.zip

Thank you @hypergreatthing for sharing this.

Installation instructions:
- Unzip the file.
- Copy all files from the InternalLibrariesFolder to the internal libraries folder.
- Add icudt46l.zip from the NativeResources folder to your project as described above.
- You should reference both SQL and SQLCipher in your project.
- Starting from B4A v3.50 you need to disable the virtual assets folder (a new debugging feature). This is done by adding the following line to the main activity:

Code: Copy

#DebuggerForceStandardAssets: true

v1.20 is now available. See this link: http://www.b4x.com/android/forum/th...th-sqlcipher-library.14965/page-4#post-207005

I'm working sqlcipher lesson.

-------------------------

Code: Copy

#Region  Project Attributes
    #ApplicationLabel: B4A Example
    #VersionCode: 1
    #VersionName:
    'SupportedOrientations possible values: unspecified, landscape or portrait.
    #SupportedOrientations: unspecified
    #CanInstallToExternalStorage: False
#End Region
'#DebuggerForceStandardAssets: true

#Region  Activity Attributes
    #FullScreen: False
    #IncludeTitle: True
#End Region

Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.
    Dim sqc As SQLCipher
    Dim sq As SQL
    Dim cu As Cursor
    '    ---------------------
    Dim myf As String : myf = File.DirDefaultExternal& "/AunglayDb/.myfolder"
    '    -------------
    Dim col1, col2, col3 As String
    Dim colInt As Int
        Dim newCol1, newCol2, newCol3 As String
End Sub

Sub Globals
    'These global variables will be redeclared each time the activity is created.
    'These variables can only be accessed from this module.
    Dim ins As Button
End Sub

Sub Activity_Create(FirstTime As Boolean)
    'Do not forget to load the layout file created with the visual designer. For example:
    'Activity.LoadLayout("Layout1")
    If FirstTime Then
        If File.Exists(myf, "") = False Then
        File.MakeDir(File.DirDefaultExternal, "/AunglayDb/.myfolder")
        End If       
    End If
        If File.Exists(myf, "1.db") = False Then
            File.Copy(File.DirAssets, "1.db", myf, "1.db")
        End If
        If sq.IsInitialized = False Then
            sq.Initialize(myf, "1.db", True)
        End If
        '    -----------------
        ins.Initialize("ins")
        ins.Text = "Insert To New"
        Activity.AddView(ins, 30%x, (100%y/2) +10%y, 40%x, 10%y)
       
End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub
Sub LoadOldDb
    cu = sq.ExecQuery("SELECT * FROM data")
    For i = 0 To cu.RowCount -1
        cu.Position = i
        col1 = cu.GetString("na")
        col2 = cu.GetString("ad")
        col3 = cu.GetString("ph")
        colInt = cu.Position
    Next
End Sub
Sub InsertNewDb
    LoadOldDb
    If sqc.IsInitialized = False Then
       
        sqc.Initialize(myf, "new1.db", True, "yourfatheraung", File.DirAssets)
        sqc.BeginTransaction
        Try
            sqc.ExecNonQuery("CREATE TABLE data (na TEXT, ad TEXT, ph TEXT)")
            For i = 0 To colInt -1
                sqc.ExecNonQuery2("INSERT INTO data VALUES (?, ?, ?)", Array As Object(col1, col2, col3))
               
            Next
        sqc.TransactionSuccessful
        Msgbox("Success!!!", "")
        Catch
            Msgbox(LastException.Message, "Error!")
        End Try
        sqc.EndTransaction
    End If
   
End Sub
Sub ins_Click
    InsertNewDb
    ToastMessageShow("OK!!!!!!!", False)
End Sub

But I get this error!
--------------

Code: Copy

** Activity (main) Create, isFirst = true **


** Activity (main) Resume **


java.lang.RuntimeException: java.io.FileNotFoundException: sqlcipher_native.zip


    at anyhwheresoftware.b4a.objects.sqlcipher.SQLCipher$1.run(SQLCipher.java:93)
    at java.lang.Thread.run(Thread.java:841)
Caused by: java.io.FileNotFoundException: sqlcipher_native.zip
    at android.content.res.AssetManager.openAsset(Native Method)
    at android.content.res.AssetManager.open(AssetManager.java:316)
    at android.content.res.AssetManager.open(AssetManager.java:290)
    at anyhwheresoftware.b4a.objects.sqlcipher.SQLCipher$1.run(SQLCipher.java:68)
    ... 1 more
java.lang.UnsatisfiedLinkError: dlopen failed: library "/data/data/b4a.example/files/libstlport_shared.so" not found


    at java.lang.Runtime.load(Runtime.java:330)
    at java.lang.System.load(System.java:511)
    at anyhwheresoftware.b4a.objects.sqlcipher.SQLCipher.Initialize(SQLCipher.java:43)
    at b4a.example.main._insertnewdb(main.java:402)
    at b4a.example.main._ins_click(main.java:388)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:525)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:187)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:175)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:171)
    at anywheresoftware.b4a.objects.ViewWrapper$1.onClick(ViewWrapper.java:77)
    at android.view.View.performClick(View.java:4240)
    at android.view.View$PerformClick.run(View.java:17752)
    at android.os.Handler.handleCallback(Handler.java:730)
    at android.os.Handler.dispatchMessage(Handler.java:92)


    at android.os.Looper.loop(Looper.java:137)
    at android.app.ActivityThread.main(ActivityThread.java:5457)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:525)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:875)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:691)
    at dalvik.system.NativeStart.main(Native Method)

Please help!

koaunglay · May 28, 2015

Now my error is like it!
-------------

Code: Copy

** Activity (main) Create, isFirst = true **


** Activity (main) Resume **


java.lang.UnsatisfiedLinkError: Couldn't load stlport_shared from loader dalvik.system.PathClassLoader[DexPathList[[zip file "/data/app/b4a.example-2.apk"],nativeLibraryDirectories=[/data/app-lib/b4a.example-2, /vendor/lib, /system/lib]]]: findLibrary returned null


    at java.lang.Runtime.loadLibrary(Runtime.java:355)
    at java.lang.System.loadLibrary(System.java:525)
    at net.sqlcipher.database.SQLiteDatabase.loadLibs(SQLiteDatabase.java:112)
    at net.sqlcipher.database.SQLiteDatabase.loadLibs(SQLiteDatabase.java:107)
    at anyhwheresoftware.b4a.objects.sqlcipher.SQLCipher.Initialize(SQLCipher.java:50)
    at b4a.example.main._insertnewdb(main.java:404)
    at b4a.example.main._ins_click(main.java:388)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:525)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:187)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:175)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:171)


    at anywheresoftware.b4a.objects.ViewWrapper$1.onClick(ViewWrapper.java:77)
    at android.view.View.performClick(View.java:4240)
    at android.view.View$PerformClick.run(View.java:17752)
    at android.os.Handler.handleCallback(Handler.java:730)
    at android.os.Handler.dispatchMessage(Handler.java:92)
    at android.os.Looper.loop(Looper.java:137)
    at android.app.ActivityThread.main(ActivityThread.java:5457)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:525)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:875)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:691)
    at dalvik.system.NativeStart.main(Native Method)

Erel · May 28, 2015

1. Are you testing it on a real device?
2. Have you followed the instructions at the end of the first post?

koaunglay · May 29, 2015

Erel said:
1. Are you testing it on a real device?
2. Have you followed the instructions at the end of the first post?

Yes I testing on real device.

Erel · May 29, 2015

Erel said:
2. Have you followed the instructions at the end of the first post?

?

koaunglay · May 29, 2015

Erel said:
?

Yes Erel I wrote with your instructions. But I'm not clever in English. So May be wrong somewhere. But I have already followed your instructions. When connection is good I'll upload my test project.

BarryW · Jul 22, 2015

Is The

Erel said:
The native SQLite engine included in the OS doesn't support encryption.
SQLCipher is an open source project that extends SQLite and adds full database encryption.

B4A SQLCipher object is a special subtype of SQL object. There is almost no need to change any code in order to switch from regular SQL to SQLCipher.

SQLCipher depends on a resource named icudt46l.zip. You should add this resource to the Files tab.

Preferably you should use a real device when developing as the emulator is too slow to handle the native resources.

The only difference between SQL API and SQLCipher API is the Initialize method.
SQLCipher.Initialize expects two additional values: Password and NativeLibsFolder.

Password is the database password. You can pass an empty string if there is no password. Note that it is not possible to change the password (or set a new password) to an existing database.

Code changes required to convert from SQL to SQLCipher
- Declare the SQL object as SQLCipher.
- Change the initialize code to:

Code: Copy

ProgressDialogShow("Initializing database...") SQL1.Initialize(File.DirRootExternal, "1.db", True, DB_PASSWORD, "") ProgressDialogHide

Note that there is no need to change anything in DBUtils module.

The library can be downloaded here:
http://www.b4x.com/android/files/SQLCipher.zip

Another compiled library is available which is based on SQLCipher v3. Note that this library is not compatible with databases created with previous versions. You can download it here:
http://www.b4x.com/android/files/SQLCipher310.zip

Thank you @hypergreatthing for sharing this.

Installation instructions:
- Unzip the file.
- Copy all files from the InternalLibrariesFolder to the internal libraries folder.
- Add icudt46l.zip from the NativeResources folder to your project as described above.
- You should reference both SQL and SQLCipher in your project.
- Starting from B4A v3.50 you need to disable the virtual assets folder (a new debugging feature). This is done by adding the following line to the main activity:

Code: Copy

#DebuggerForceStandardAssets: true

v1.20 is now available. See this link: http://www.b4x.com/android/forum/th...th-sqlcipher-library.14965/page-4#post-207005

Is there any tool to use to create sqlcipher like sqlite studio? like importing data from csv to sqlcipher?
Tnx

Derek Jee · Jul 22, 2015

SQLiteManager @ http://www.sqlabs.com/ is what I use..

Rusty · Aug 11, 2015

I've been using SQLCipher for several years with no problem.
I am now receiving the following error on a REAL device that has been working for a long time as well.

** Activity (main) Create, isFirst = true **
java.lang.RuntimeException: java.io.FileNotFoundException: sqlcipher_native.zip
at anyhwheresoftware.b4a.objects.sqlcipher.SQLCipher$1.run(SQLCipher.java:93)
at java.lang.Thread.run(Thread.java:841)
Caused by: java.io.FileNotFoundException: sqlcipher_native.zip
at android.content.res.AssetManager.openAsset(Native Method)
at android.content.res.AssetManager.open(AssetManager.java:316)
at android.content.res.AssetManager.open(AssetManager.java:290)
at anyhwheresoftware.b4a.objects.sqlcipher.SQLCipher$1.run(SQLCipher.java:68)
... 1 more

Any advice?
Thanks,
Rusty

Andy S. · Aug 12, 2015

I have SQLCipher 1.31 working but I am getting random crashes on Android 4.4.2. Any advice?

Thanks,
Andy

Andy S. · Aug 12, 2015

Here is more information on my crashing issue...

** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
** Activity (main) Pause, UserClosed = false **
** Activity (planedit) Create, isFirst = true **
** Activity (planedit) Resume **
java.util.concurrent.TimeoutException: net.sqlcipher.database.SQLiteCursor.finalize() timed out after 10 seconds
at java.lang.Object.wait(Native Method)
at java.lang.Thread.parkFor(Thread.java:1205)
at sun.misc.Unsafe.park(Unsafe.java:325)
at java.util.concurrent.locks.LockSupport.park(LockSupport.java:157)
at java.util.concurrent.locks.AbstractQueuedSynchronizer.parkAndCheckInterrupt(AbstractQueuedSynchronizer.java:813)
at java.util.concurrent.locks.AbstractQueuedSynchronizer.acquireQueued(AbstractQueuedSynchronizer.java:846)
at java.util.concurrent.locks.AbstractQueuedSynchronizer.acquire(AbstractQueuedSynchronizer.java:1175)
at java.util.concurrent.locks.ReentrantLock$FairSync.lock(ReentrantLock.java:195)
at java.util.concurrent.locks.ReentrantLock.lock(ReentrantLock.java:256)
at net.sqlcipher.database.SQLiteDatabase.lock(SQLiteDatabase.java:429)
at net.sqlcipher.database.SQLiteProgram.close(SQLiteProgram.java:294)
at net.sqlcipher.database.SQLiteQuery.close(SQLiteQuery.java:136)
at net.sqlcipher.database.SQLiteCursor.close(SQLiteCursor.java:510)
at net.sqlcipher.database.SQLiteCursor.finalize(SQLiteCursor.java:595)
at java.lang.Daemons$FinalizerDaemon.doFinalize(Daemons.java:187)
at java.lang.Daemons$FinalizerDaemon.run(Daemons.java:170)
at java.lang.Thread.run(Thread.java:841)
** Activity (planedit) Pause, UserClosed = true **
** Activity (main) Resume **

Any help appreciated!

Andy

Android Tutorial Android database encryption with SQLCipher library

Attachments

Expert

B4X founder

Well-Known Member

Well-Known Member

Active Member

B4X founder

Active Member

Active Member

Active Member

Member

Member

B4X founder

Member

B4X founder

Member

Active Member

Active Member

Well-Known Member

Member

Member

Similar Threads