Importing CSV file to encrypted database

Moving sqlite to sqlcipher, we have a script that loads a table into the database with the command

sqlite3 database_file “.import csv_file tablename;”

Now withe sqlcipher, I have to give it the key, but you can’t do

sqlcipher database file “pragma key=mykey; .import csv_file tablename;”

as it croaks saying the . is invalid sql syntax. I don’t want to put the pragma into an init file as it plain text.

Any way to do this?

(I’m actually building up the command line in python so I can distribute just a compiled .pyc file so the key is not exposed)

Hello @mrsinger

There are a few requirements that make the dot commands special compared to standard SQL commands. Specifically:

  • A dot-command must begin with the “.” at the left margin with no preceding whitespace.
  • The dot-command must be entirely contained on a single input line.
  • A dot-command cannot occur in the middle of an ordinary SQL statement. In other words, a dot-command cannot occur at a continuation prompt.
  • Dot-commands do not recognize comments.

You might try to format your commands and pipe it into SQLCipher, could you give that a try?

Yes, that is what I ended up doing. Write a temporary file with the commands and pipe it in or use it as file file for -init

Works but was trying to avoid putting the key in anything readable. But the temp file is only there for a very short time, so should be acceptable.

Thanks for the suggestion