How to Connect DBeaver Universal DBMS to a SQLite encrypted DB (Aka SQL Cipher)?
Introduction
SQLCipher is a standalone fork of SQLite that adds security features. It's frequently used in industries where offline data security matters.
If you're looking for the best possible way to manage it easily, then you're reading the right and exact article needed to get the job done!
Available Solutions
Before heading to the topic I'd like to mention all the available solutions to manage the SQLCipher database:
Use the CLI
Using DB Browser for SQLite
And Finally, Use DBeaver which I'll explain how to in this article.
Why DBeaver?
Well, alongside running queries against the database, inspect its data or check other DB objects like the Views, tables or Stored Procedures. you can also generate an ERD for the existing database which is too helpful when it comes to exploring the database from a high-level perspective!
Challenges
DBeaver has no GUI to support the configuration process for SQLCipher databases.
The Good NEWS we can make it work out-of-the-box!
Instructions
1. Download and Install DBeaver
head to dbeaver.io
download and install the one that suits your OS (MAC or Windows)
2. Getting the right driver
Open the following Link sqlite-jdbc-crypt and download the latest
sqlite-jdbc-<version>.jar.Copy that file to a safe place on your pc that you DON'T ACCIDENTALLY REMOVE!
Personally, I prefer to place them in the
%UserProfile%/Documentsdirectory as no one care about it!
3. Create a custom driver for DBeaver
From Database Menu --> Open DriverManager
Hit New, and now let‘s configure it
3.1. Settings Tab
Driver Name = SqlCipherV3 (or any name you’d like)
Driver Type, Select Generic
Class Name =
org.sqlite.JDBCURL Template =
jdbc:sqlite:{file}
The rest are not mandatory, feel free to set them up on your own!
3.2. Libraries Tab
Remember the .jar file that we’ve downloaded recently?
No, please have a look at Getting the right driver. Yes, Let‘s continue ...
Click
Add Fileand Select that.jarfileClick
Find Classand selectorg.sqlite.JDBC
3.3. Driver Prosperities
Here comes the most important part, make sure that you’ve configured that one properly!
We are targeting SQLCipherV3 since it's more common between developers but it's the same idea once you grasp it!
To Add a property, Right-Click and press Add
| Key | Value |
| cipher | sqlcipher |
| fast_kdf_iter | 2 |
| hmac_algorithm | 0 |
| hmac_pgno | 1 |
| hmac_salt_mask | 0x3a |
| hmac_user | 1 |
| kdf_iter | 64000 |
| legacy | 3 |
| legacy_page_size | 1024 |
There is one more PRAGMA which is called key and it is used to pass a passphrase that’s used to decrypt your Database, but we won’t set it here as it differs from one database to another!
For different version parameters of SqlCipher visit: utelle.github.io/SQLite3MultipleCiphers/doc..
| Parameter | v4 | v3 | v2 | v1 |
kdf_iter | 256000 | 64000 | 4000 | 4000 |
fast_kdf_iter | 2 | 2 | 2 | 2 |
hmac_use | 1 | 1 | 1 | 0 |
hmac_pgno | 1 | 1 | 1 | n/a |
hmac_salt_mask | 0x3a | 0x3a | 0x3a | n/a |
legacy | 4 | 3 | 2 | 1 |
legacy_page_size | 4096 | 1024 | 1024 | 1024 |
kdf_algorithm | 2 | 0 | 0 | 0 |
hmac_algorithm | 2 | 0 | 0 | 0 |
plaintext_header_size | 0 | n/a | n/a | n/a |
Note
for
n/avalues don’t enter anything!
v<x>indicates the SQLCipher Version!
4. Add Your Database to DBeaver
From Database Menu —> Select New Database Connection
Search for SQLCipherV3 (the one we created recently) and press Next
Click Browse and Select your SQLCipher Database file.
In the Driver, properties tab make sure that your defaults have been set correctly.
- In
KeyProperty double click the Value and Enter your Passphrase
- In
Test Connection, Voila!
Press Finish
Need Help?
If you encountered any problems, please feel free to comment below and I’ll gladly help!
Support
