Use SQLite DB with sqlcipher

[Alpha549]

Hello everyone,

There are components enabling us to use SQLite databases.

However, using SQLiteStudio, which allow us to cypher the SQLite DB with authentication credentials, I would like to know if it's possible to use this cyphered SQLite DB ?

If it's not possible natively in Talend, is it possible coding it all by ourselves in Java / Python (tJava(Row) or importing jars...) ?

Thank you 🙂

[Anonymous]

I'm afraid I do not use SQLite so don't know about this. However, if you could point me to a location that describes exactly what you are wishing to achieve (I've looked, but haven't found a location that I am certain matches your requirement), I can look into this for you.

[Alpha549]

Hello,

here is the page of SQLiteStudio : https://sqlitestudio.pl/

In the galery we can see screenshots of this application : https://sqlitestudio.pl/gallery/

Here are my own screen

 

I can create a new db file, here mydb.db.

No authentication is required, so anyone who has the mydb.db file, can open it with a SQLite client.

Here is now a cyphered db file :

 

Here the security is higher.

When we use Talend SQLite components, we are not asked about any password. So I think that Talend can't natively use cyphered SQLite files.

Here my question, is there a way to use Cyphered SQLite files ?

🙂

[Anonymous · Accepted solution]

Thanks for this question, I like a challenge occasionally 😉

I found a way of doing this, but it is not straight forward and requires an update that you should make sure that you record so that you can revert it if it causes problems.

First of all, I downloaded the Jar from here.....

https://github.com/Willena/sqlite-jdbc-crypt/releases/tag/3.37.2

Pay attention to the GitHub site, because there is lots of info there.

Once I had downloaded the Jar, I then modified the "Modules" tab in Talend Studio. Here are some screenshots to show what I did....

First, copy the existing Maven URI for the SQLITE Context. Keep this in case you want to revert to the older Jar.

Next, click on the button in the red box.

The following screen will appear....

Click on the button in the red box.

Select the Jar file you have just downloaded and click "Open".

The above screen looks a little different to what you will see as I have already added this file and Maven URI. You should click "OK" here.

You will see that the Maven URI should have changed for all of the SQLite components.

Now to create a Job. Below is the Job I created....

Set up your tDBConnection as normal. This will be used as a "dummy" component.

After that, add a tJava component with the following code (replace my file path and password with yours)....

java.sql.Connection connection = java.sql.DriverManager.getConnection("jdbc:sqlite:/Users/richardhall/Documents/TestDB.db?cipher=sqlcipher&key=password&legacy=4");

globalMap.put("conn_tDBConnection_1", connection);

What is happening above is the "dummy" tDBConnection is being replaced in code by the connection code above. When the Job starts, the "dummy" connection is attempted, then the tJava code is run. This supersedes the tDBConnection.

After that, the rest of the Job will run as you would expect.

[Alpha549]

Hello @Richard Hall​ 

Thanks a lot for your answer ! I'm glad my question challenged you 🙂

Let me tell you what I understood, and please, correct me if necessary.

You updated a module shipped with Talend.

You first looked if there was a context for SQLite. I understand that the context name must in uppercase like here : SQLITE

In the Maven URL, you read sqlitejdbc, and you searched it on Google, and came across the Github repo you sent : https://github.com/Willena/sqlite-jdbc-crypt/releases/tag/3.37.2

You replaced the Maven URL to replace the module (saving the original URL first).

Question : why didn't you choose this repo. instead : https://github.com/xerial/sqlite-jdbc ?

What put you on the right track ?

In the code, tDBConnection_1 set on SQLite, you checked the code, searching for the tDBConnection_1Process method, and saw the connection code, and globalMap "conn_tDBConnection_1" entry, which you replace in your tJava code.

I am right ? 🙂

So, the same way, I could update other Talend modules, if their version lacks features ?

[Anonymous]

First of all I just looked to find the Jar used for SQLite. Context doesn't really matter, I just needed to find the Jar.

I then replaced it with one I found using Google. I simply looked for a Jar with examples that said it would work with ciphered SQLite DBs. I didn't know it would work, it was my first attempt and I was lucky. I didn't need to try another Jar, so the xerial one I didn't attempt. That may work as well.

The code I used came from a solution where I once needed to switch dynamically between different DB connections. The DB connection to be used had to be decided when the job was running.

You could attempt this way for other modules, but the Java is not guaranteed to be the same.

[Alpha549]

Thanks a lot @Richard Hall​ , topic solved 🙂