Unable to open encrypted database file with sqlcipher CLI

[klingtnet]

I am unable to open an encrypted database file that was created using this library with sqlcipher's CLI.

This is my example Go program that creates a database and inserts a record in a table:

package main

import (
    "context"
    "database/sql"
    "fmt"
    "log"
    "os"
    "time"

    _ "github.com/mutecomm/go-sqlcipher/v4"
)

func wrapErr(err, deferErr error) error {
    if err == nil {
        return deferErr
    }
    if deferErr == nil {
        return err
    }
    return fmt.Errorf("%s: %w", err.Error(), deferErr)
}

func run(ctx context.Context) (err error) {
    if len(os.Args) != 2 {
        err = fmt.Errorf("Usage: %s <password>", os.Args[0])
        return
    }
    password := os.Args[1]
    // https://www.zetetic.net/sqlcipher/sqlcipher-api/#PRAGMA_key
    urn := fmt.Sprintf("file:%s?_pragma_key='%s'&_pragma_cipher_page_size=4096", "example.sqlite3", password)
    db, err := sql.Open("sqlite3", urn)
    if err != nil {
        return
    }
    defer func() {
        err = wrapErr(err, db.Close())
    }()
    _, err = db.ExecContext(ctx, `CREATE TABLE IF NOT EXISTS example_table (id INTEGER PRIMARY KEY, foo TEXT NOT NULL)`)
    if err != nil {
        return
    }
    res, err := db.ExecContext(ctx, `INSERT INTO example_table (foo) VALUES (?)`, fmt.Sprintf("some record inserted at %s", time.Now()))
    if err != nil {
        return
    }
    id, err := res.LastInsertId()
    if err != nil {
        return
    }
    log.Println("inserted record with ID:", id)
    return
}

func main() {
    err := run(context.TODO())
    if err != nil {
        log.Fatal(err)
    }
}

And here's how to reproduce the issue:

$ go version
go version go1.15 linux/amd64
$ go run main.go thisismypassword
2020/08/21 17:19:35 inserted record with ID: 1
$ sqlcipher -version
3.31.0 2020-01-22 18:38:59 f6affdd41608946fcfcea914ece149038a8b25a62bbe719ed2561c649b86alt1 (SQLCipher 4.4.0 community)
$ sqlcipher 
> PRAGMA key='thisismypassword';
> PRAGMA cipher_page_size=4096;
> .open 'example.sqlite3';
> SELECT * FROM example_table;
Error: file is not a database

[klingtnet]

@frankbraun Are databases created with go-sqlcipher compatible with sqlcipher's cli? If yes, do you've any suggestion where to start debugging (different encryption defaults, etc.)?

[frankbraun]

@klingtnet I never tested it, but I also asked myself why this isn't working.

The parameters seem to be the same. I'm wondering if you have to switch the order of key and cipher_page_size in the sqlcipher call. Also the documentation https://www.zetetic.net/sqlcipher/sqlcipher-api/#cipher_default_page_size seems to indicate that you have to use cipher_default_page_size in the command line call. But it shouldn't make any difference anyway since 4096 is the default value in SQLCipher 4.

My other idea is that this is an incompatibility between libtomcrypt and OpenSSL. go-sqlcipher uses libtomcrypt as the crypto provider and the default crypto provider of sqlcipher is OpenSSL. It should be possible to compile sqlcipher with libtomcrypt.

I would start debugging by compiling two versions of sqlcipher, one with OpenSSL and one with libtomcrypt. And then see if encrypted database files are interoperable between the two.

Please keep us posted here!

[klingtnet]

Thank you for the reply!

I'm wondering if you have to switch the order of key and cipher_page_size in the sqlcipher call.

I just tried this but the error remains.

My other idea is that this is an incompatibility between libtomcrypt and OpenSSL. go-sqlcipher uses libtomcrypt as the crypto provider and the default crypto provider of sqlcipher is OpenSSL. It should be possible to compile sqlcipher with libtomcrypt.

That sounds reasonable and I'm curious about the result.

[klingtnet]

Surprisingly I was able to build sqlcipher against libtomcrypt since it was easier than expected. The following instructions are a note to myself or the interested reader:

$ git clone https://github.com/libtom/libtomcrypt
$ make -C libtomcrypt
$ git clone https://github.com/sqlcipher/sqlcipher.git
$ cd sqlcipher
$ sqlcipher/configure --enable-tempstore=yes CFLAGS="-DSQLITE_HAS_CODEC" LDFLAGS=../libtomcrypt/libtomcrypt.a
$ make

Sadly, I still get the error when trying to select from the database.

[frankbraun]

Thanks for the update. I'm going to try to reproduce this error on my machine now and see if I have another idea.

[frankbraun]

It's a quoting problem. Let's say we have the password secret. In your code you use '%s' which leads to a password 'secret'. But PRAGMA key='secret'; leads to the password secret (without the single quotes). Therefore the passwords differ and sqlcipher cannot decrypt the database.

If you use plain text passwords you have to escape them with net.QueryEscape(), as described in c35c778656b2d7c6af521ebe29252c7e18022be3. Otherwise things like & in your passwords lead to nasty bugs.

If you run the tools added in c9a3a061e2fd8798c4d6a1c8f75fc1a4fbb250d2 with go run you can test that it works. Works for me together with sqlcipher in all directions.

I'm glad that it actually works in combination with the sqlcipher binary, that would have been nasty otherwise. I was just about to use that combination myself.

BTW, this package might be interesting for you: https://godoc.org/github.com/mutecomm/mute/encdb It allows you to easily rekey the database.

[klingtnet]

Thank you very much, I totally overlooked the quotes :facepalm:

If you run the tools added in c9a3a06 with go run you can test that it works. Works for me together with sqlcipher in all directions.

The tools work for me too, create and select but I did not have any luck opening the database file either with plain sqlcipher from the arch linux packages or with my custom build against libtomcrypt. It still says file is not a database.

For reproducability:

$ go run util/create.go example.db thisismypassword
# github.com/mutecomm/go-sqlcipher/v4
sqlite3.c: In function ‘sqlite3SelectNew’:
sqlite3.c:132475:10: warning: function may return address of local variable [-Wreturn-local-addr]
132475 |   return pNew;
       |          ^~~~
sqlite3.c:132435:10: note: declared here
132435 |   Select standin;
       |          ^~~~~~~
$ go run util/select.go example.db thisismypassword
# github.com/mutecomm/go-sqlcipher/v4
sqlite3.c: In function ‘sqlite3SelectNew’:
sqlite3.c:132475:10: warning: function may return address of local variable [-Wreturn-local-addr]
132475 |   return pNew;
       |          ^~~~
sqlite3.c:132435:10: note: declared here
132435 |   Select standin;
       |          ^~~~~~~
one for the money, two for the show
$ cat select.sql
PRAGMA key='thisismypassword';
PRAGMA cipher_page_size=4096;
.open 'example.db';
SELECT * FROM t1;
$ sqlcipher < select.sql
ok
Error: near line 4: file is not a database

[frankbraun]

The commands in your select.sql have the wrong order. This works on my machine:

$ cat select.sql
.open 'example.db';
PRAGMA key='thisismypassword';
PRAGMA cipher_page_size=4096;
SELECT * FROM t1;

[klingtnet]

Can confirm, strange since I thought that I needed to specify the key first. Nonetheless, thanks again!