supply encryption key from command line

[JackMordaunt]

First off, thanks for this application, it's very useful!

Now I interact with encrypted sqlite databases and it's getting annoying copying in an encryption key every time I open it. I'm trying to build a script that will select the database file and launch sqlitebrowser from the command line.

The trouble is that I'm still stuck pasting in the key every time. Frequently the app will crash (unclear why) and supplying the key become real tiresome, real quick.

I've had a look around and haven't found any solution.

I would like to be able to do something like this: sqlitebrowser --encryption-key $key data.db.
I have tried to use the -s option to supply some SQL with PRAGMAs to set the key however that doesn't seem to work.

For example

--- key.sql
PRAGMA key = "x'${key}'";
PRAGMA cipher_page_size = 4096;
PRAGMA kdf_iter = 256000;
PRAGMA cipher_hmac_algorithm = SHA512;
PRAGMA cipher_kdf_algorithm = SHA512;
PRAGMA cipher_default_plaintext_header_size = 0;

Thanks! :)

[scottfurry]

Have you looked it sqlite init files?
Ubuntu Man Page - sqlcipher describes how to direct sqlcipher to an initialization file via the command line switch -init. You would have a text file containing PRAGMA statements to startup the sqlcipher session. PRAGMA key=... could be kept here. HOWEVER, it is not secure to store a security key in this manner. YMMV Caveat Emptor

I would have to look in our code to double check if we handle the -init switch.
(Others feel free to chime in here)

[JackMordaunt]

The command line help doesn't show -init, though I can verify that sqlcipher -init does work for this.

~ 
❯ sqlitebrowser -h
Usage: sqlitebrowser [options] [<database>|<project>]

Possible command line arguments:
  -h, --help          Show command line options
  -q, --quit          Exit application after running scripts
  -s, --sql <file>    Execute this SQL file after opening the DB
  -t, --table <table> Browse this table after opening the DB
  -R, --read-only     Open database in read-only mode
  -o, --option <group>/<setting>=<value>
                      Run application with this setting temporarily set to value
  -O, --save-option <group>/<setting>=<value>
                      Run application saving this value for this setting
  -v, --version       Display the current version
  <database>          Open this SQLite database
  <project>           Open this project file (*.sqbpro)
~ 
❯ sqlitebrowser -v
DB Browser for SQLite Version 3.12.2

Built for x86_64-little_endian-lp64, running on x86_64
Qt Version 5.15.6
SQLCipher Version 4.5.4 community (based on SQLite 3.39.2).

[scottfurry]

Yeah. That's my fault for not being exact or clear. I was referring to sqlcipher on the command line. I didn't think DB4S had a mechanism to pass on init type files. Alternative is to put all the PRAGMA statements into a file and use the --sql tag. I'm not sure if it would work but in theory it might.

Adding this item to the #3419.

[JackMordaunt]

Alternative is to put all the PRAGMA statements into a file and use the --sql tag. I'm not sure if it would work but in theory it might.

This doesn't work. I assume that "Execute this SQL file after opening the DB" implies the key has already been supplied. If we can't supply the key, the DB is never considered "open".

[scottfurry]

I wasn't sure it would work but I had to offer something to help. Thanks for trying. Now I know for next time.

Sorry I can't give you a "do this" solution.

[scottfurry]

For the sake of completeness - posting link to SQLCipher API page showing various PRAGMA usage.

[justinclift]

Frequently the app will crash ...

Hmmm. We've had reports of the latest official release (3.12.2) crashing on Linux for some users.

That's normally fixed by them using the .AppImage automatically created using our latest source code:

https://github.com/sqlitebrowser/sqlitebrowser/releases/tag/continuous

That might fix the crashing problem for you too. If so, that might help make things easier. 😄

[mgrojo]

What sqlitebrowser is using is .env files. Take a look to this wiki section and pull request #1404, it might be what you're after.

[JackMordaunt]

Thanks for the link. I'm struggling to get this to work. Placed the .env next to the db and sqlitebrowser still opens the password dialog.

No error message or anything to indicate a specific failure.

<name> = <key>
<name>_keyFormat = 1
<name>_pageSize = 4096
<name>_kdfIter = 256000
<name>_hmacAlgorithm = SHA512
<name>_kdfAlgorithm = SHA512
<name>_plaintextHeaderSize = 0

I tried the key as a literal string and a quoted string... 🤷‍♂️

[JackMordaunt]

Would it matter if the DB is in WAL mode or anything 😕

[scottfurry]

Did you try a change to key format, i.e. <name>_keyFormat = 0?

[JackMordaunt]

Yep. It's for sure a "raw key", but yes I did try that as well.

[JackMordaunt]

Is the name supposed to the absolute path or anything like that?