Even when using --manual flag: pysqlcipher3.dbapi2.DatabaseError: file is not a database

[aaronkirkman]

I'm running sigexport on Debian under Windows with Python 3.7.3 (installed under Debian) and Signal v1.40.1 (installed under Windows). I installed pysqlcipher3 with pip3 install pysqlcipher3 and ran this command:

python3 sigexport.py --source "/mnt/c/Users/ak/AppData/Roaming/Signal/" --manual --overwrite outputdir

throws this exception:

Error: near line 1: file is encrypted or is not a database
Traceback (most recent call last):
  File "sigexport.py", line 489, in <module>
    main()
  File "/home/ak/.local/lib/python3.7/site-packages/click/core.py", line 829, in __call__
    return self.main(*args, **kwargs)
  File "/home/ak/.local/lib/python3.7/site-packages/click/core.py", line 782, in main
    rv = self.invoke(ctx)
  File "/home/ak/.local/lib/python3.7/site-packages/click/core.py", line 1066, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/ak/.local/lib/python3.7/site-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
  File "sigexport.py", line 477, in main
    convos, contacts = fetch_data(db_file, key, manual=manual, chat=chat)
  File "sigexport.py", line 182, in fetch_data
    c.execute(query)
pysqlcipher3.dbapi2.OperationalError: no such table: conversations

If I take the key from the config.json file (prepending 0x to it), and browse the db.sqlite manually, I can see the table conversations in it. It's the decryption that isn't working because the db-decrypt.sqlite file is empty.

[carderne]

I don't know how this works with WSL/Windows, but please try following the For Linux instructions in the Installation part of the README.

[carderne]

Feel free to re-open this issue!

[anon8675309]

I was able to reproduce this issue on Debian 11 (bullseye). There were a few missing dependencies that preventing compiling sqlicipher (namely make and a C compiler, which caused ./configure to fail). I can't say if this is the cause of the original poster's problem, but I can confirm that it did bite me. I also found the answer, so you can skip straight to the end if you want that.

Reproduction steps

# Install dependencies for sqlcipher
sudo apt install libsqlite3-dev tclsh libssl-dev make build-essential
# Check out, build and install sqlcipher
git clone https://github.com/sqlcipher/sqlcipher.git
cd sqlcipher
./configure --enable-tempstore=yes CFLAGS="-DSQLITE_HAS_CODEC" LDFLAGS="-lcrypto -lsqlite3"
make && sudo make install
cd ..

# Install dependencies for installing signal-export
sudo apt install python3-pip
# Install signal-export
sudo pip install signal-export[sql]

Here's the output when I attempt to use --list-chats:

vagrant@bullseye:~$ sigexport --list-chats
╭─────────────────────────────── Traceback (most recent call last) ────────────────────────────────╮
│ /home/vagrant/.local/lib/python3.9/site-packages/sigexport/main.py:573 in main                   │
│                                                                                                  │
│   570 │   else:                                                                                  │
│   571 │   │   from sigexport.data import fetch_data                                              │
│   572 │   │                                                                                      │
│ ❱ 573 │   │   convos, contacts = fetch_data(                                                     │
│   574 │   │   │   db_file,                                                                       │
│   575 │   │   │   key,                                                                           │
│   576 │   │   │   manual=manual,                                                                 │
│                                                                                                  │
│ ╭─────────────────────────────────────────── locals ───────────────────────────────────────────╮ │
│ │             _ = <module 'pysqlcipher3.dbapi2' from                                           │ │
│ │                 '/home/vagrant/.local/lib/python3.9/site-packages/pysqlcipher3/dbapi2.py'>   │ │
│ │         chats = None                                                                         │ │
│ │          conf = <_io.TextIOWrapper name='/home/vagrant/.config/Signal/config.json' mode='r'  │ │
│ │                 encoding='UTF-8'>                                                            │ │
│ │       db_file = PosixPath('/home/vagrant/.config/Signal/sql/db.sqlite')                      │ │
│ │          dest = None                                                                         │ │
│ │  docker_image = None                                                                         │ │
│ │    fetch_data = <function fetch_data at 0x7f35978aa0d0>                                      │ │
│ │          html = True                                                                         │ │
│ │ include_empty = False                                                                        │ │
│ │           key = 'ee02a46e612c02c203a3d7544ae37a89122db51e921685f80ef1c6a2a6c71e9f'           │ │
│ │    list_chats = True                                                                         │ │
│ │        manual = False                                                                        │ │
│ │           old = None                                                                         │ │
│ │     overwrite = False                                                                        │ │
│ │      paginate = 100                                                                          │ │
│ │    print_data = False                                                                        │ │
│ │         quote = True                                                                         │ │
│ │        source = PosixPath('/home/vagrant/.config/Signal/config.json')                        │ │
│ │           src = PosixPath('/home/vagrant/.config/Signal')                                    │ │
│ │    use_docker = False                                                                        │ │
│ │       verbose = False                                                                        │ │
│ │       version = None                                                                         │ │
│ ╰──────────────────────────────────────────────────────────────────────────────────────────────╯ │
│                                                                                                  │
│ /home/vagrant/.local/lib/python3.9/site-packages/sigexport/data.py:58 in fetch_data              │
│                                                                                                  │
│   55 │   │   c.execute("PRAGMA cipher_kdf_algorithm = PBKDF2_HMAC_SHA512")                       │
│   56 │                                                                                           │
│   57 │   query = "SELECT type, id, e164, name, profileName, members FROM conversations"          │
│ ❱ 58 │   c.execute(query)                                                                        │
│   59 │   for result in c:                                                                        │
│   60 │   │   if log:                                                                             │
│   61 │   │   │   secho(f"\tLoading SQL results for: {result[3]}, aka {result[4]}")               │
│                                                                                                  │
│ ╭────────────────────────────────────────── locals ──────────────────────────────────────────╮   │
│ │                 c = <pysqlcipher3.dbapi2.Cursor object at 0x7f3596b62960>                  │   │
│ │             chats = None                                                                   │   │
│ │          contacts = {}                                                                     │   │
│ │            convos = {}                                                                     │   │
│ │                db = <pysqlcipher3.dbapi2.Connection object at 0x7f3596b55e30>              │   │
│ │           db_file = PosixPath('/home/vagrant/.config/Signal/sql/db.sqlite')                │   │
│ │ db_file_decrypted = PosixPath('/home/vagrant/.config/Signal/sql/db-decrypt.sqlite')        │   │
│ │     include_empty = False                                                                  │   │
│ │               key = 'ee02a46e612c02c203a3d7544ae37a89122db51e921685f80ef1c6a2a6c71e9f'     │   │
│ │               log = False                                                                  │   │
│ │            manual = False                                                                  │   │
│ │             query = 'SELECT type, id, e164, name, profileName, members FROM conversations' │   │
│ ╰────────────────────────────────────────────────────────────────────────────────────────────╯   │
╰──────────────────────────────────────────────────────────────────────────────────────────────────╯
DatabaseError: file is not a database

Troubleshooting

I tried to debug this using sqlcipher from a response from another report of this issue to no avail.

vagrant@bullseye:~$ sqlcipher ~/.config/Signal/sql/db.sqlite   "PRAGMA key = \"x'$(jq -r '.key' ~/.config/Signal/config.json)'\"; select * from messages limit 1;"
Error: in prepare, file is not a database (26)

Solution

That other ticket claims that Signal is now using sqlcipher version 4 instead of 3. I was able to confirm that from a commit message in the Signal-Desktop repo saying they're on 4.5.2 as of now.

cd sqlcipher
git checkout v4.5.2
./configure --enable-tempstore=yes CFLAGS="-DSQLITE_HAS_CODEC" LDFLAGS="-lcrypto -lsqlite3"
make && sudo make install
cd ..

# Now sigexport works
sigexport --list-chats

If you want, I can submit a pull request with the updated instructions. It's only a couple packages and the git checkout command, so it's probably easier to just add them to the README instead of having me make my own fork of the repo.

[carderne]

Hi @anon8675309 a pull request would be great!

[floe]

JFYI, if you don't want to clutter your /usr/local/ folder with spurious library installations (a recipe for disaster sooner or later), then you can build sqlcipher, but skip sudo make install and just run LD_LIBRARY_PATH=/path/to/sqlcipher/.libs sigexport --list-chats.