Using DBeaver To Manage SQLCipher Databases

Using DBeaver To Manage SQLCipher Databases

The right tool for your SQLCipher database

Subscribe to my newsletter and never miss my upcoming articles

I’ve tried multiple solutions to manage the SQLCipher database. either using a CLI or a GUI and the only one that seemed to work properly was DB Browser for SQLite with easy GUI to get into the Database. but there were a lot of restrictions or I can say a missing functionalities. For example, I wanted to view the ERD of the database but I didn't find the proper functionality to do so!

I knew DBeaver from a long time ago, and I wondered if I can use it with SQLCipher. but like any other thing in life, it doesn't seem to work without a headache!

The bad news is there is no GUI to support the configuration process for SQLCipher databases.

The good news we can customize it to make it work Out-of-the-box!

1. Download and Install DBeaver


  1. head to dbeaver.io
  2. download the one that suits your OS
  3. Install!

2. Getting the right driver


  1. head to sqlite-jdbc-crypt and download the latest sqlite-jdbc-<version>.jar.
  2. copy that file in a safe place on your pc that you DON'T ACCIDENTALLY REMOVE!

    Personally, I prefer to place them in the %UserProfile%/Documents directory as no one care about it!

3. Linking Everything together


Well, there are multiple versions of SQLCipher and in this article, I'll target only SQLCipherV3 since it's more common between developers but it's the same Idea once you grasp it!

Creating a custom driver for DBeaver

  1. From Database Menu --> Open DriverManager
  2. Hit New, and now let‘s configure it

1. Settings Tab

  • Driver Name = SqlCipherV3 (or any name you’d like)
  • Driver Type, Select Generic
  • Class Name = org.sqlite.JDBC
  • URL Template = jdbc:sqlite:{file}

The rest are not mandatory, feel free to set them up on your own!

2. Libraries Tab

Remember the .jar file that we’ve downloaded recently?

If your answer was no, please have a look Getting the right driver

If your answer was yes, Let‘s continue ...

  • Click Add File and Select that .jar file
  • Click Find Class and select org.sqlite.JDBC

3. Driver Prosperities

Here comes the most important part, make sure that you’ve configured that one properly!

To Add a property, Right-Click and press Add

KeyValue
ciphersqlcipher
fast_kdf_iter2
hmac_algorithm0
hmac_pgno1
hmac_salt_mask0x3a
hmac_user1
kdf_iter64000
legacy3
legacy_page_size1024

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 a database to another!

For different version parameters of SqlCipher visit: utelle.github.io/SQLite3MultipleCiphers/doc.. and actually I’ve acquired the above one from there!

Parameterv4v3v2v1
kdf_iter2560006400040004000
fast_kdf_iter2222
hmac_use1110
hmac_pgno111n/a
hmac_salt_mask0x3a0x3a0x3an/a
legacy4321
legacy_page_size4096102410241024
kdf_algorithm2000
hmac_algorithm2000
plaintext_header_size0n/an/an/a

Note: -

  • for n/a values 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 Key Property double click the Value and Enter your Passphrase
  • Test Connection, Voila!
  • Press Finish

Further Support

If you encountered any problems, please feel free to comment below and I’ll gladly help!

 
Share this