sqlite3.OperationalError: malformed database schema (messages) - near "AS": syntax error

[berrytsakala]

~/downloads/signal-export  $ ./sigexport.py --manual exports/
ok

Traceback (most recent call last):
  File "./sigexport.py", line 621, in <module>
    main()
  File "/home/dy/.local/lib/python3.7/site-packages/click/core.py", line 1137, in __call__
    return self.main(*args, **kwargs)
  File "/home/dy/.local/lib/python3.7/site-packages/click/core.py", line 1062, in main
    rv = self.invoke(ctx)
  File "/home/dy/.local/lib/python3.7/site-packages/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/dy/.local/lib/python3.7/site-packages/click/core.py", line 763, in invoke
    return __callback(*args, **kwargs)
  File "./sigexport.py", line 585, in main
    convos, contacts = fetch_data(db_file, key, manual=manual, chats=chats)
  File "./sigexport.py", line 217, in fetch_data
    c.execute(query)
sqlite3.OperationalError: malformed database schema (messages) - near "AS": syntax error

what have i done to deserve this:

• downloaded signal-export just now, 2021-08-07,
• followed the install instructions from the readme,
• compiled without problems
• had to add the --manual argument ;
• received the above error.

my setup:

• signal 5.11.0
• mx linux
• uname -a: 5.10.0-5mx-amd64 #1 SMP Debian 5.10.26-1~mx19+1 (2021-04-01) x86_64

thanks.

[carderne]

Hi @berrytsakala, are you comfortable to try opening the decrypted database in another SQLite browser and see if you're able to query that way?

[coletonodonnell]

Also am having this problem:

./sigexport.py output
Traceback (most recent call last):
  File "./sigexport.py", line 621, in <module>
    main()
  File "/home/coleton/.anaconda3/lib/python3.8/site-packages/click/core.py", line 829, in __call__
    return self.main(*args, **kwargs)
  File "/home/coleton/.anaconda3/lib/python3.8/site-packages/click/core.py", line 782, in main
    rv = self.invoke(ctx)
  File "/home/coleton/.anaconda3/lib/python3.8/site-packages/click/core.py", line 1066, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/coleton/.anaconda3/lib/python3.8/site-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
  File "./sigexport.py", line 585, in main
    convos, contacts = fetch_data(db_file, key, manual=manual, chats=chats)
  File "./sigexport.py", line 217, in fetch_data
    c.execute(query)
pysqlcipher3.dbapi2.OperationalError: malformed database schema (messages) - near "AS": syntax error

System Info:

Signal Version: 5.14.0 System Version: 5.10.52-gentoo

[coletonodonnell]

Running this script also shows a similar error:

sigBase="${HOME}/.config/Signal/";
key=$( /usr/bin/jq -r '."key"' ${sigBase}config.json );
db="${HOME}/.config/Signal/sql/db.sqlite";
clearTextMsgs="${sigBase}clearTextMsgs.csv";

/usr/bin/sqlcipher -list -noheader "$db" "PRAGMA key = \"x'"$key"'\";select json from messages;" > "$clearTextMsgs";

This outputs:

Error: malformed database schema (messages) - near "AS": syntax error

Along with this, I tried opening up the file in both sqlitebrowser and sqlite3 and both said that the "file is not a database." Could the data base be corrupted?

[carderne]

@coletonodonnell did you try opening the decrypted database produced by sigexport.py? It'll be called db-decrypt.sqlite in the same dir as the original. Note that https://github.com/carderne/signal-export/blob/8e119b1dbaa5e59312fd830f2252763092e61de9/sigexport.py#L257-L258 deletes it (should get around to sticking this in a finally), so you want want to comment that out.

[coletonodonnell]

Ah I see that now, only issue is that it seems to be blank. Opening it up in sqlitebrowser yields nothing and opening it in sqlite3 and running .tables shows no tables. I am not very experienced with sql so.

[coletonodonnell]

Running this command with --manual yields:

Error: near line 1: malformed database schema (messages) - near "AS": syntax error
Traceback (most recent call last):
  File "./sigexport.py", line 621, in <module>
    main()
  File "/home/coleton/.anaconda3/lib/python3.8/site-packages/click/core.py", line 829, in __call__
    return self.main(*args, **kwargs)
  File "/home/coleton/.anaconda3/lib/python3.8/site-packages/click/core.py", line 782, in main
    rv = self.invoke(ctx)
  File "/home/coleton/.anaconda3/lib/python3.8/site-packages/click/core.py", line 1066, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/coleton/.anaconda3/lib/python3.8/site-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
  File "./sigexport.py", line 585, in main
    convos, contacts = fetch_data(db_file, key, manual=manual, chats=chats)
  File "./sigexport.py", line 217, in fetch_data
    c.execute(query)
sqlite3.OperationalError: no such table: conversations

Not sure if this has anything to do with it.

[carderne]

If you're keen to keep digging, here's what I'd do. First get the decryption key: https://github.com/carderne/signal-export/blob/8e119b1dbaa5e59312fd830f2252763092e61de9/sigexport.py#L569-L570 https://github.com/carderne/signal-export/blob/8e119b1dbaa5e59312fd830f2252763092e61de9/sigexport.py#L576-L578

Then try running the "manual decryption" directly in bash and see if you have any luck, get any interesting messages: https://github.com/carderne/signal-export/blob/8e119b1dbaa5e59312fd830f2252763092e61de9/sigexport.py#L188-L196

i.e.

echo "PRAGMA key = "x'your-key-here'"; ATTACH DATABASE 'db-output.sqlite' AS plaintext KEY ''; SELECT sqlcipher_export('plaintext'); DETACH DATABASE plaintext;" | sqlcipher db.sqlite

And if it's still giving an empty output maybe we can figure it out... All these incantations are just copied from somewhere, I'm no expert either...

[coletonodonnell]

Alright I ran the command, got my key using:

jq -r '."key"' ~/.config/Signal/config.json

With that, I ran the above command and it ran successfully, only issue is that again, db-output.sqlite is blank.

[carderne]

Weird that it doesn't say anything... Can you try this. Also that SO page prompts a question: are you on Android?

[coletonodonnell]

Alright this is confusing me on so many levels. So I first tried with the backup of the data base I made on my home, and it actually outputted just fine, though the 'plaintext.db' database was sadly blank. I then attempted to try it on the actual original non-copied database in ~/.config/Signal/sql and did the whole thing again, and when running .backup plaintext.db it throws the error:

sqlite> .backup plaintext.db
Error: file is not a database

Yes, I am on Android. The main reason I am attempting this is because my phone is bootlooping and I am trying to backup my signal messages before factory resetting my phone.

[coletonodonnell]

If you mean is this database on Android, no this is the Signal Desktop app on Gentoo. I am trying to export the messages on my Desktop.

[carderne]

This response to that same SO question suggests that Android SQLCipher (presumably what your Android Signal is using) is built with ICU… A bit of searching found this issue, which is not super promising…

I have no idea if this is the issue. Would be great if @berrytsakala could confirm whether they’re also on Android.

[coletonodonnell]

Yeah that is not super promising...

Are you running Apple? I find it hard to believe that everyone who has used this thus far has been an Apple user and hasn't ran into this issue yet, though, it isn't impossible.

[coletonodonnell]

Actually, I understand now why. Apple can't export Signal Messages, so Desktop would be the only way. That makes sense, I and most likely @berrytsakala are probably abnormalities.

[carderne]

Hmm good point, Android probably not relevant since the db is presumably generated on your desktop. I use Ubuntu (and an iOS phone).

[carderne]

Ohhh that is interesting… so maybe Android is relevant and the rest of us are all using iOS. Will add something to the README to see if we can get any data on that…

[coletonodonnell]

Not really sure then, I would assume that there isn't any difference between how Android and Apple send messages to the Desktop client, but perhaps there is?

[coletonodonnell]

Sounds good, I am still going to factory reset my device though, I have a backup from about a month ago anyway.

[franklin-be]

I'm on android as well.

[coletonodonnell]

@franklin-be did it work or did you get a similar error?

[franklin-be]

export generally works. Only have issues with received media lately as stated here: https://github.com/carderne/signal-export/issues/28

[haarp]

@coletonodonnell Did you ever manage to solve this? Also on Gentoo here, getting the exact same errors when trying to dump Desktop client's db. I wonder if the Gentoo ebuild compiles sqlcipher differently...

[haarp]

Solved it.

Problem 1: Error: file is not a database: sqlcipher can't decrypt the database. Usually because the key is entered with wrong syntax. The proper syntax is: pragma key = "x'abc123abc123'";.

Problem 2: malformed database schema (messages) - near "AS": syntax error: sqlcipher is too old. Version 4.0.1 spits out this error, version 4.5.0 worked.

[coletonodonnell]

Sweet, great work. Are you running Apple or Android? I haven't looked at the old issue too hard but iirc we thought it could've been a problem with Apple vs. Android.

[haarp]

Sweet, great work. Are you running Apple or Android? I haven't looked at the old issue too hard but iirc we thought it could've been a problem with Apple vs. Android.

Desktop Signal on Linux. I actually don't think it's a matter of platform. I briefly insttalled sqlcipher on Android (via Termux) and it managed to open the Desktop database without effort.

[carderne]

@haarp I haven't quite followed the discussion here but is there a PR you could submit?

[haarp]

@haarp I haven't quite followed the discussion here but is there a PR you could submit?

Good idea. #53

[carderne]

Thanks! And the pragma key = ... syntax? I assume that was just entering it incorrectly when trying to decrypt in the command line?

[haarp]

Yes, that's probably the problem. I discovered this while messing around on the commandline, and presumably @coletonodonnell did too. Syntax looks to be correct inthe script: https://github.com/carderne/signal-export/blob/8e119b1dbaa5e59312fd830f2252763092e61de9/sigexport.py#L190

I must admit I haven't used this script, just found this issue on Google trying to use sqlcipher on Signal :)

[carderne]

:P well thanks for your contribution!