In continuation of https://github.com/sqlcipher/sqlcipher/issues/353 and https://github.com/sqlcipher/sqlcipher/issues/355:
From what I understood, SQLite prints “ok” when using PRAGMA key? Wasn’t the key pragma added by SQLCipher? Why would SQLite want a pragma used for encrypted databases, if it doesn’t support encryption?
Also, that means that “ok” is actually printed when using PRAGMA key, not when using .dump or .output, as I initially thought, right?
From what I understood, SQLite prints “ok” when using
PRAGMA key?
SQLite 3.30.1, when compiled to use an encryption extension or codec (by defining SQLITE_HAS_CODEC) will return a result of “ok” for a call to PRAGMA key. Note that a result is different from printing, though practically equivalent when dealing with the shell. However, any standard sqlite3 will not be compiled with SQLITE_HAS_CODEC support. In that case, PRAGMA key is just an unknown pragma, equivalent to calling PRAGMA does_not_exist, and thus will return no result.
Wasn’t the
keypragma added by SQLCipher? Why would SQLite want a pragma used for encrypted databases, if it doesn’t support encryption?
No, the handler for PRAGMA key has, for the duration of SQLCipher development, been included in SQLite upstream. SQLite does support encryption in a way: the SQLite team has their own SQLite-based encrypted database products called the SQLite Encryption Extension (SEE) and Compressed and Encrypted Read Only Database extension (CEROD) respectively. In other words, the pragma itself was not added by SQLCipher, instead we provide a third-party implementation of the encryption layer invoked by PRAGMA key.
Also, that means that “ok” is actually printed when using
PRAGMA key, not when using.dumpor.output, as I initially thought, right?
As noted above, the “ok” is actually a SQL result set returned by PRAGMA key. When you invoke the shell the default behavior is to print the result. .output writes results to a file. If you were to invoke PRAGMA key after invoking .output filename then ok would be included in the file. If you invoked PRAGMA key prior to .output you could avoid that.
The “ok” would not be included in the output of .dump, because that dot command is actually exporting database data.
I’m using this command:
user_version=$(sqlcipher -cmd "PRAGMA key = '<password>'; PRAGMA cipher_compatibility = 3; PRAGMA user_version" database.sqlite .output)
If you were to invoke
PRAGMA keyafter invoking.output filenamethenokwould be included in the file.
It looks to me like I am already invoking PRAGMA key before .output. So, the problem is caused by the fact that I am not using a file for it?
@revolter - why not just pipe the output into tail like this?
user_version=$(./sqlcipher -cmd "PRAGMA key = 'testkey'; PRAGMA user_version;" test.db .output | tail -n 1)
This would always capture only the last line from the output, corresponding to the PRAGMA user_version. In fact, this would be much safer for general use, since it is possible for any user to place arbitrary statements in their ~./.sqliterc which will be interpreted automatically by the shell and could inject unexpected result sets into the shell output.
It wouldn’t work in my case as I’m also using the .dump method. And removing the first line (instead of leaving the last line) wouldn’t work either, as another developer might have an older version of SQLCipher installed, so tail would remove the first line from the dump instead, because it wouldn’t print out ok.
@revolter Why don’t we take a step back. The example you provided was capturing the user_version into a variable. Piping to tail clearly works in that case and it is safer than your original since it guards against unexpected output being introduced by ~/.sqliterc.
If there is a different scenario where it won’t work, why don’t you explain in detail (ideally with code), exactly what you are trying to accomplish and then we can recommend from there.
Ok, sorry.
So, I am also using this command:
sqlcipher -cmd "PRAGMA key = '<password>'; PRAGMA cipher_compatibility = 3" database.sqlite .dump > database.sql
which creates the file like:
ok
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "user" (
...
if I am using SQLCipher 3.4.0. But if I am using an older version of SQLCipher, the mentioned command creates the file like:
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "user" (
...
Hello @revolter - Thanks for the clarification, that is very helpful. To recap, in this case you are trying to dump the database to a file named database.sql, so I’d recommend doing this:
sqlcipher -cmd "PRAGMA key = '<password>'; PRAGMA cipher_compatibility = 3" -cmd ".output database.sql" database.sqlite .dump
Basically, instead of using .dump to standard output and redirecting, use a second -cmd argument to specify the file using .output after the key is set. This will give you the results you desire, and also guards against unexpected output being introduced by the contents of ~./.sqliterc.
Let me know if that works for you.
It worked!
And do you also know how to do fix this when trying to capture the output in a variable?
user_version=$(sqlcipher -cmd "PRAGMA key = '<password>'; PRAGMA cipher_compatibility = 3; PRAGMA user_version" database.sqlite .output)
echo "PRAGMA user_version=$user_version;" >> database.sql
Hi @revolter
It appears you are trying to dump both the database schema and content along with the user_version within the same database.sql file. You can utilize the printf function along with the PRAGMA as a function behavior to splice your data together. Something like this should accomplish what you are looking for:
sqlcipher \
-cmd "PRAGMA key = 'foo';" \
-cmd ".output database.sql" foo.db .dump \
-cmd "select printf(\"PRAGMA user_version=%s;\", (select user_version from pragma_user_version));"
Would you give that a try?
It adds the user version at the top of the file now, but I think it’s better this way, as it’s close to the PRAGMA foreign_keys=OFF; line so it’s consistent.
Thank you very much!
Hi @revolter
Great, we are happy to hear that worked for you. Take care!
The solution mentioned here is just working for an instance that runs on the same host where the dump should be taken, right?
I am running SQLCipher within a Docker container (I’m using yspreen/sqlcipher in case that makes any difference) though, this makes the situation more complicated, right?
A volume mount would be as short lived as the container run — therefore just for the time of the database dump.
Is there any recommendation how to handle that properly? Do I have to dump and echo the dump afterwards within the container or is there any other solution for making a simple dump to STDOUT?
Hello @barney - in in the case you mentioned when you can ensure you are using a specific version of SQLCipher (i.e. greater that 4.3.0) then I think you could use the suggestion and just pipe the SDTOUT through tail and mentioned as an option earlier in the thread, i.e. | tail -n 1. Let us know if that works.