Superfluous "ok" output for dot-commands

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?

cc @developernotes

@revolter

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 key pragma 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 .dump or .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 key after invoking .output filename then ok would 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!

1 Like