Share My Creation SQLite DB Browser (for encrypted files)

for a project I am working on right now, I needed an SQLite reader for encrypted DB files but could not find one so I build my own SQLite browser.
I was using DB Browser (SQLite) (which I find really awesome) but it is not working with encrypted SQLite DB files. I tried also DB Browser (SQLCipher) but still no luck so I build my own.
it is for simple tasks to browse quickly inside the SQLite DB and perform some queries.

 

[Mashiane]

Hi ilan, about the browser you did, I assume you are able to open password protected files?

We are looking for same that can perform CRUD, can you help, can we source this from you if you have?

 

[tchart]

@ilan if you are not using SQLCipher do you mind sharing what library/method you are using to encrypt the database?

 

[ilan]

Hi ilan, about the browser you did, I assume you are able to open password protected files?

We are looking for same that can perform CRUD, can you help, can we source this from you if you have?

it does all CRUD operations. but after some experiments i now am able to use SQLite Browser(Chipper) and open encrypt Password protected files.

@ilan if you are not using SQLCipher do you mind sharing what library/method you are using to encrypt the database?

now i am using SQLite Chiper with the following settings (see next post!)

first download SQLite Browser from here: https://sqlitebrowser.org/dl/

after installing open the DB Browser (SQLCipher)

Press Open Database:

select SQLChiper 4 defaults and enter your password:

and you are ready to go:

 

[ilan]

and now for the b4x settings:

initialize the SQL db with this settings:

Sub initializeSQL
    dataDb.Initialize("org.sqlite.JDBC",$"jdbc:sqlite:file:data.db?cipher=sqlcipher&legacy=4&kdf_iter=256000&key=${getKey}"$)
End Sub

Sub getKey As String
    Return "xxxxxx" '<--- enter here your key (the same key is the password for the sqlite chiper app)
End Sub

file:data.db is the name of the database that is stored in File.DirApp (Object folder) change it to the database of yours

have fun coding!

 

[Mashiane]

Thanks @ilan, just a final question please. Do I have to have the DBBrowser running if I want to use this with b4J or it is optional. Im sorry I just want to understand clearly.

 

[ilan]

Thanks @ilan, just a final question please. Do I have to have the DBBrowser running if I want to use this with b4J or it is optional. Im sorry I just want to understand clearly.

DBBrowser is just a GUI app to enter your data like PHPMyAdmin. it is not a SERVER and SQLite does not need a server running it is a local file. Everything is stored locally and you can enter the data using b4j or for example SQLite Chipper. you don't need both to be running. it is just a powerful tool that will help you with the CRUD operations in a graphical way. Of course, you can use inside SQL commands too (Execute SQL)!

btw SQLite is very powerful just make sure to set the correct data types to your columns.

Datatypes In SQLite

SQLite supports of course JOINS!!

 

[Mashiane]

Thanks for the clary, I thought it was some form of requirement for this to work. Great, let me check this out. Awesome, thanks a lot.

 

[ilan]

Thanks for the clary, I thought it was some form of requirement for this to work. Great, let me check this out. Awesome, thanks a lot.

you are welcome

i can tell you that i am working now on a big project and i am still undecided if to use SQLite or MySql.
i have already build both options for tests. so i am running a LAMP server on my RP4 (Apache, MySQL, PHP) and a local SQLite DB. i have also watch tons of comparison videos and threads like this: https://www.hostinger.com/tutorials/sqlite-vs-mysql-whats-the-difference/ and many more.

because i believe the DB will have lots of connections i will go for the MySQL option but everyone needs to know that SQLite is very powerful and can be a lot of times a good choice for small traffic mobile applications.

 

[Jmu5667]

for a project I am working on right now, I needed an SQLite reader for encrypted DB files but could not find one so I build my own SQLite browser.
I was using DB Browser (SQLite) (which I find really awesome) but it is not working with encrypted SQLite DB files. I tried also DB Browser (SQLCipher) but still no luck so I build my own.
it is for simple tasks to browse quickly inside the SQLite DB and perform some queries.

Nice Job ilan

 

[tchart]

@ilan if you are undecided about the database , have a look at H2. It can be run as embedded or server and also has a compatability mode for Postgres - meaning you can easily move to Postgres at a later date.

Thanks for you reply BTW

 

[ilan]

@ilan if you are undecided about the database , have a look at H2. It can be run as embedded or server and also has a compatability mode for Postgres - meaning you can easily move to Postgres at a later date.

Thanks for you reply BTW

you mean this: https://www.h2database.com/html/main.html

do you have a working example for b4x?

thanx

 

[tchart]

you mean this: https://www.h2database.com/html/main.html

do you have a working example for b4x?

thanx

Hi @ilan Ive just posted a quick tutorial here;

H2 Database

This is a very quick tutorial on the H2 database - https://www.h2database.com/html/main.html The main features of H2 are: Very fast, open source, JDBC API Embedded and server modes; in-memory databases Browser based Console application Small footprint: around 2 MB jar file size Other...

www.b4x.com