Hi, I have just discovered SQLCipher. It is great, I was looking for such a tool for years! I’ve been using sqlite for keeping some not-so-secret data with quite a bit of scripting magic to have the contents encrypted on disk all the time - but SQLCipher is so much easier!
Compiled it, tested - works fine. No command history? Ok, just need libreadline-dev installed… Now history works… but wait! My ‘pragma key’ commands get saved into plaintext .sqlite_history as well! Hmmm… I can’t believe nobody noticed that. Is everybody using SQLCipher in their code only and never from the prompt?
I made a simple patch for myself to be able to avoid that security problem, by not putting lines starting with space into history - but I need to be careful to type: [SPACE]pragma key etc.
I am looking for opinions on what would be an elegant, user-friendly and idiot-proof solution. Here are some ideas:
Check for ‘pragma key’ in input line and do not add it to history. But it will be either very dirty or not completely idiot-proof. Simple strncmp in one_input_line() would not catch extra spaces etc. Some loop skipping over whitespace? regexp? seems complicated. Even it would not catch the pragma If the command input is split into multiple lines, e.g.
...> = 'secret';
The shell_add_history() shall be moved somewhere into process_input() after a whole command is completed and checked for this pragma. But it will change the behavior of having partial commands in history.
Add a command-line option (e.g. --askpass) that will interactively ask for the key, remember it, call sqlite3_key() with it after opening the database and dispose of it securely. This will make typing ‘pragma key’ unnecessary, but still someone could do it and ruin his security. So a patch to input history handling would be necessary as well - or removal of ‘pragma key’ so -askpass would be the only way. But it will break attaching multiple databases with different keys… Hmmm.
Remove ‘pragma key’ but add dot command ‘.key’ with no argument which will ask for key interactively and call sqlite3_key(). It will not need changes to history handling.
Add dot command .history on|off. Not idiot-proof, someone can forget to switch history off before doing