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.
sqlite> pragma
...> key
...> = '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
pragma key.

Any ideas/opinions?

Hello @Malgond - thanks for using SQLCipher. Yes, in practice the vast majority of SQLCipher use is in applications so it is possible to invoke commands without retention by readline. The shell prompt is typically only used for testing, debugging, etc. That said, we agree that it would be best not to store these commands in history. We’ll have to look into this a bit further. We might go for a combination of two options, e.g. a simple check for pragma key on a single line along with a dot command to prompt for key material.

The shell prompt is typically only used for testing, debugging, etc. That
said, we agree that it would be best not to store these commands in
history. We’ll have to look into this a bit further. We might go for a
combination of two options, e.g. a simple check for pragma key on a single
line along with a dot command to prompt for key material.

Thanks @sjlombardo for the response. What about sensitive information that
may be present in INSERT, UPDATE, WHERE clause, etc.?

brodybits:

What about sensitive information that may be present in INSERT, UPDATE, WHERE clause, etc.?

This is also a valid concern. Definitely, shell prompt is not for the average Joe.

I am going to try using SQLCipher shell prompt for some time, implement small changes, and see how it fits my usage patterns. Then I will come back with some ideas and likely code patches.

Hello @brodybits - It would simply be best to build the command line shell without readline. In fact, I believe OP did so originally, and then added readline after the fact.

To clarify, we don’t really feel that this is a bug, given that there is a compromise between convenience and security. When a shell tool is configured to log everything you write into it for easy replay, there is a tradeoff.

With regard to the earlier suggested adjustments, our thought is just that excluding PRAGMA key, and perhaps providing a prompting mechanism as the OP suggested, would provide a slightly better protection for someone that happens to be using readline for convenience but still wants some minimum amount of security for their key material.

I have implemented a dot command .history on|off with the default state off (modeled after .bail command with a global state variable). This allows me to type ‘pragma key’ at start without having it saved in history, then I can enable/disable history as needed. Works for now. I may also implement .key later. Here’s a patch if you’d like to take it:

diff --git a/src/shell.c b/src/shell.c
index 6212cb2..726282d 100644
--- a/src/shell.c
+++ b/src/shell.c
@@ -364,6 +364,14 @@ static void endTimer(void){
 static int bail_on_error = 0;
+** If the following flag is set, then input lines are saved in
+** command history for later recall both in memory and (at exit) on
+** disk, provided that readline or equivalent library is compiled in.
+** NOTE: this is off by default for security.
+static int add_lines_to_history = 0;
 ** Threat stdin as an interactive input if the following variable
 ** is true.  Otherwise, assume stdin is connected to a file or pipe.
@@ -624,7 +632,7 @@ static char *one_input_line(FILE *in, char *zPrior, int isContinuation){
 #else
     free(zPrior);
     zResult = shell_readline(zPrompt);
-    if( zResult && *zResult ) shell_add_history(zResult);
+    if( zResult && *zResult && add_lines_to_history) shell_add_history(zResult);
 #endif
   return zResult;
@@ -4053,6 +4061,7 @@ static char zHelp[] =
   ".fullschema ?--indent? Show schema and the content of sqlite_stat tables\n"
   ".headers on|off        Turn display of headers on or off\n"
   ".help                  Show this message\n"
+  ".history on|off        Turn command history on or off\n"
   ".import FILE TABLE     Import data from FILE into TABLE\n"
 #ifndef SQLITE_OMIT_TEST_CONTROL
   ".imposter INDEX TABLE  Create imposter table TABLE on index INDEX\n"
@@ -5851,6 +5860,15 @@ static int do_meta_command(char *zLine, ShellState *p){
   }else
+  if( c=='h' && strncmp(azArg[0], "history", n)==0 ){
+    if( nArg==2 ){
+      add_lines_to_history = booleanValue(azArg[1]);
+    }else{
+      raw_printf(stderr, "Usage: .history on|off\n");
+      rc = 1;
+    }
+  }else
   if( c=='h' && strncmp(azArg[0], "help", n)==0 ){
     utf8_printf(p->out, "%s", zHelp);
   }else

Thanks for the patch!

Thinking about possible solutions this appears to be the best for my usage pattern. It would be fairly difficult to spot all the uses of keys in the input, since as you mentioned there are the multi-line issues, plus the key in ATTACH commands, not to mention hexkeys, but I do want history most of the time. Even if the SQLCipher had a callback to notify the shell there was a key in the input there would still be the question of which line out of the last so many contained it.

I’ve managed to upgrade to 4.0.0 on FreeBSD. There is a small bug in your history fix for the command line sqlite3 that causes a crash when you use ctrl-D to exit instead of ‘.quit’. The fix is simple, just check the result for NULL or empty before trying to match it - in file shell.c.in:

/* BEGIN SQLCIPHER */
#ifdef SQLITE_HAS_CODEC
    /* Simplistic filtering of input lines to prevent PRAGKA key and 
       PRAGMA rekey statements from being stored in readline history. 
       Note that this will only prevent single line statements, but that
       will be sufficient for common cases. */
    /*TBR: watch out for empty line on ^D as input. */
    if(zResult && *zResult && sqlite3_strlike("%pragma%key%=%", zResult, 0)==0) return zResult;
#endif
/* END SQLCIPHER */
    if( zResult && *zResult ) shell_add_history(zResult);
#endif

Hi Stephen,

Yes, I should have looked before posting. I’ve downloaded 4.0.1, but it
will be a few days before I can try it since I’m building from scratch
with ‘waf’ and I can’t just drop in the new version :).