Android Tutorial Android database encryption with SQLCipher library

Status
Not open for further replies.
SQLCipher is an open source project that extends SQLite and adds full database encryption.
License: https://www.zetetic.net/sqlcipher/open-source/

B4A SQLCipher 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.

The only difference between SQL API and SQLCipher API is the Initialize method.
SQLCipher.Initialize expects two additional values: Password and a second parameter that is not used (it was used in the past).

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:
B4X: 
SQL1.Initialize(File.DirRootExternal, "1.db", True, DB_PASSWORD, "")

V1.70
V1.60
V1.50
  • Based on SQLCipher v3.59
  • Supports targetSdkVersion 26.
  • The icu.zip file is no longer required. You can delete it from the Files folder.
  • It is no longer required to disable the debugger virtual assets feature.
    Remove this line: #DebuggerForceStandardAssets: True
  • Old version: www.b4x.com/android/files/SQLCipher150.zip

Depends on: https://repo1.maven.org/maven2/net/...er/4.5.4/android-database-sqlcipher-4.5.4.aar
You should download and copy it to the additional libraries folder.
 

Attachments

  • SQLCipher.zip
    37.2 KB · Views: 384
Last edited:
Click to expand...
walterf25

walterf25

Expert
Licensed User
Longtime User
Erel said:
You cannot load an unencrypted database with SQLCipher. You need to load it with SQL.
Click to expand...
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
 
tdocs2

tdocs2

Well-Known Member
Licensed User
Longtime User
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?

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

Best regards.

Sandy
 
Last edited:
tdocs2

tdocs2

Well-Known Member
Licensed User
Longtime User
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).
 
Last edited:
D

Derek Jee

Active Member
Licensed User
Longtime User
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)
 
Last edited:
D

Derek Jee

Active Member
Licensed User
Longtime User
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..
 
Last edited:
D

Derek Jee

Active Member
Licensed User
Longtime User
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..
 
D

Derek Jee

Active Member
Licensed User
Longtime User
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

koaunglay

Member
Licensed User
Longtime User
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:
B4X: 
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:
B4X: 
#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
Click to expand...
I'm working sqlcipher lesson.

-------------------------
B4X: 
#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!
--------------
B4X: 
** 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

koaunglay

Member
Licensed User
Longtime User
Now my error is like it!
-------------
B4X: 
** 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)
 
koaunglay

koaunglay

Member
Licensed User
Longtime User
Erel said:
?
Click to expand...

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

BarryW

Active Member
Licensed User
Longtime User
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:
B4X: 
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:
B4X: 
#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
Click to expand...

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

Rusty

Well-Known Member
Licensed User
Longtime User
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
Click to expand...
Any advice?
Thanks,
Rusty
 
A

Andy S.

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

Thanks,
Andy
 
A

Andy S.

Member
Licensed User
Longtime User
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
 
Status
Not open for further replies.

Similar Threads

Guenter Becker
  • Question
Android Question SQLITE/SQLCipher
Replies
10
Views
562
aeric
aeric
tummosoft
  • Article
B4A Library xSQLCipher for Android Sqlcipher 4.5.4
Replies
17
Views
2K
RB Smissaert
R
Guenter Becker
  • Article
B4A Library TDDBUtils Functions to handle SQLite and SQLCipher
Replies
4
Views
2K
Guenter Becker
Guenter Becker
R
  • Question
Android Question Use both sqlcipher and spatialite?
Replies
5
Views
3K
RB Smissaert
R
G
  • Question
Android Question SqlCipher - recreate current database structure to SQLCipher Db
Replies
13
Views
3K
GeoffT660
G
Top