Many export errors

[jbaker6953]

A lot of errors.

1. 17 SMS messages were not exported because local variable 'phone' referenced before assignment
2. 56 MMS/Signal messages were not exported because No such file or directory: 'bits/Attachment_8161_1670890804128.bin' or similar filenames. The files actually do not exist.

There were no errors listed in creating the files in the bits directory.

Exporting backup into 'bits//'
Writing HeaderFrame...
Writing DatabaseVersionFrame...
Writing Attachments...
Writing Avatars...
Writing SharedPrefFrame(s)...
Writing KeyValueFrame(s)...
Writing StickerFrames...
Writing EndFrame...
Writing database...
Done!

2022-12-30 19:16:30 - INFO - Finished signalbackup-tools
2022-12-30 19:16:30 - INFO - Parsing the sqlite database bits/database.sqlite
2022-12-30 19:16:30 - INFO - Unable to find a contact on your phone with ID: 461
2022-12-30 19:16:30 - INFO - Unable to find a contact on your phone with ID: 572
2022-12-30 19:16:30 - INFO - Total num SMS messages: 22310
2022-12-30 19:16:30 - INFO - Total number Signal messages: 88869
2022-12-30 19:16:30 - INFO - Total num MMS messages: 19671
2022-12-30 19:16:30 - INFO - Total number Signal media messages: 5233
2022-12-30 19:16:30 - INFO - Starting SMS and Signal text message export

The only thing that catches my eye is the double slash

[jbaker6953]

I ran the process again and verified that those 56 attachments are NOT created during export.

[alexlance]

Thank you for the useful report. Signal's database format was updated, and this script hadn't been updated.

If you run this:

cd signal-message-exporter
git checkout -t -b signal-6-5-6 origin/signal-6-5-6

It'll checkout some changes that I've just pushed up.

Then run the thing again, maybe you'll get some different / more exciting errors.

[jbaker6953]

I don't have git on my system. Is there an easier way than setting up that whole environment in windows?

[alexlance]

You could download the updated file directly:

https://raw.githubusercontent.com/alexlance/signal-message-exporter/signal-6-5-6/signal-message-exporter.py

[jbaker6953]

Reading backup file...
Read entire backup file...

done!

Exporting backup into 'bits//'
Writing HeaderFrame...
Writing DatabaseVersionFrame...
Writing Attachments...
Writing Avatars...
Writing SharedPrefFrame(s)...
Writing KeyValueFrame(s)...
Writing StickerFrames...
Writing EndFrame...
Writing database...
Done!

2022-12-30 23:46:15 - INFO - Finished signalbackup-tools
2022-12-30 23:46:15 - INFO - Parsing the sqlite database bits/database.sqlite
2022-12-30 23:46:15 - INFO - Unable to find a contact on your phone with ID: 461
2022-12-30 23:46:15 - INFO - Unable to find a contact on your phone with ID: 572
2022-12-30 23:46:15 - INFO - Total num SMS messages: 22310
2022-12-30 23:46:15 - INFO - Total number Signal messages: 88869
Traceback (most recent call last):
  File "/root/signal-message-exporter.py", line 254, in <module>
    print_num_mms()
  File "/root/signal-message-exporter.py", line 37, in print_num_mms
    cursor.execute(q)
sqlite3.OperationalError: no such column: type
make: *** [Makefile:6: run] Error 1

[alexlance]

Could you please dive into Signal settings and see what version of Signal you're running.

It'd be useful if I could detect the Signal version from the Signal backup file, but I don't see that information in there - so we need to retrieve it manually.

[jbaker6953]

It's already uninstalled, but it was updated just this morning because Signal refused to let me delete my account until I updated. Prior to that I was still on 5.59 or something thereabouts.

[jbaker6953]

The version was 6.6.8.

[alexlance]

So would this backup file have been generated from the older version of Signal or from the newest version?

[jbaker6953]

The older version.

[jbaker6953]

Is the sqllite structure pretty simple? Can I look in there easily to see if those attachments are really missing?

[alexlance]

Ah ok - then the newer version I just patched isn't going to be much good for you. Revert back to the original one here:
https://raw.githubusercontent.com/alexlance/signal-message-exporter/master/signal-message-exporter.py

Feel free to post exact copy-paste of the error that's occurring.

The sqlite schema is a little hairy, but there is a table named mms and a related table named part that contains the mms files.

And some info in this thread about examining the sqlite database:
alexlance/signal-sms-mms-importer#7

[jbaker6953]

All 56 of the errors are substantially the same, just different message IDs and attachments.

2022-12-30 19:38:06 - ERROR - File not found for media message: bits/Attachment_752_1508789814077.bin for part: {'_id': 752, 'mid': 1883, 'seq': 0, 'ct': 'image/jpeg', 'name': '', 'chset': None, 'cd': 'DOlLha6Ip4UglfXidOc4wjZZsj4VsDoIXioyC8Zu4+mp9oiZJhMcJwDse8sNy1EzjDvf4uJFZPUnZeZILujTbA==', 'fn': None, 'cid': None, 'cl': '7080573386167961144', 'ctt_s': None, 'ctt_t': None, 'encrypted': None, 'pending_push': 1, '_data': None, 'data_size': 4292872, 'file_name': None, 'thumbnail': None, 'aspect_ratio': None, 'unique_id': 1508789814077, 'digest': b'\xfd\xd2\x81\xc6c\xd3\xea\x89\x9d\x9b9r\xc1kq(\xb9\t\xc9\xb4H1\x9c\x01n\x8e\x13\xde\xa1\xcf^\xa1', 'fast_preflight_id': None, 'voice_note': 0, 'data_random': None, 'thumbnail_random': None, 'width': 0, 'height': 0, 'quote': 0, 'caption': None, 'sticker_pack_id': None, 'sticker_pack_key': None, 'sticker_id': -1, 'data_hash': None, 'blur_hash': None, 'transform_properties': None, 'transfer_file': None, 'display_order': 0, 'upload_timestamp': 0, 'cdn_number': 0, 'borderless': 0, 'sticker_emoji': None, 'video_gif': 0}
2022-12-30 19:38:06 - ERROR - Failed to export this media message: {'_id': 1883, 'thread_id': 1, 'date': 1508789805684, 'date_received': 1508789814000, 'date_server': -1, 'msg_box': 10485780, 'read': 1, 'body': "It was Dean's day to bring home Bitey Eater the spider from school.", 'part_count': 1, 'ct_l': '', 'address': 31, 'address_device_id': None, 'exp': None, 'm_type': 132, 'm_size': None, 'st': 1, 'tr_id': None, 'delivery_receipt_count': 0, 'mismatched_identities': None, 'network_failures': None, 'subscription_id': -1, 'expires_in': 0, 'expire_started': 0, 'notified': 0, 'read_receipt_count': 0, 'quote_id': 0, 'quote_author': None, 'quote_body': None, 'quote_attachment': -1, 'quote_missing': 0, 'quote_mentions': None, 'shared_contacts': None, 'unidentified': 0, 'previews': None, 'reveal_duration': 0, 'reactions': None, 'reactions_unread': 0, 'reactions_last_seen': -1, 'remote_deleted': 0, 'mentions_self': 0, 'notified_timestamp': 0, 'viewed_receipt_count': 0, 'server_guid': None, 'receipt_timestamp': -1, 'ranges': None, 'is_story': 0, 'parent_story_id': 0, 'quote_type': 0, 'export_state': None, 'exported': 0} because [Errno 2] No such file or directory: 'bits/Attachment_752_1508789814077.bin'

[alexlance]

I think maybe the parent application signalbackup-tools didn't extract those attachments into files for some reason.

[alexlance]

signalbackup-tools pulls out all the file attachments (MMSes) and dumps them as files into the bits folder. It sounds like it skipped over those 56 files for some reason...?

[jbaker6953]

Right. I'm trying to figure out a way to manually see if those attachments are there, if they're a specific file type, corrupted, or something. I notice the "name" attribute is null for all of them. Not sure if that's true for the ones that are successfully extracted.

[jbaker6953]

I also noticed that it took about 3 hours to import the XML file and it appears that MMS groups are not maintained. The messages are stored under each individual sender.

[alexlance]

Unfortunately I'm not sure how groups are modelled in MMSes - it's possible that someone could perform a standard sms/mms export via SMS Backup and Restore, then find some group messages in that XML file, and then document that format somewhere, and then I might be able to adjust this script to create that same format.

(the lack of groups thing wasn't much of a problem for me, as I didn't have any Signal groups)

[jbaker6953]

I figured out how to look into it and those 56 messages have a part_count=1 in the mms table, implying they should have a corresponding entry in the parts table, but there is no corresponding row in the parts table. It looks to be a Signal issue.

[jbaker6953]

Looks like group MMS look like this in the XML:

  <mms date="1518477381000" spam_report="0" ct_t="application/vnd.wap.multipart.related" msg_box="2" address="+12025551234~+12025551235~+12025551236" sub_cs="null" re_type="0" retr_st="null" re_original_body="null" d_tm="null" exp="null" locked="0" msg_id="0" app_id="0" from_address="null" m_id="null" retr_txt="null" date_sent="1518477381" read="1" rpt_a="null" ct_cls="null" bin_info="0" pri="129" sub_id="-1" re_content_type="null" object_id="null" resp_txt="null" re_content_uri="null" ct_l="null" re_original_key="null" d_rpt="null" reserved="0" using_mode="0" _id="1433" rr_st="0" m_type="128" favorite="0" rr="null" sub="null" hidden="0" deletable="0" read_status="null" d_rpt_st="0" callback_set="0" seen="1" re_recipient_address="null" device_name="null" cmc_prop="null" resp_st="128" text_only="1" sim_slot="0" st="null" retr_txt_cs="null" creator="org.thoughtcrime.securesms" m_size="null" sim_imsi="null" correlation_tag="null" re_body="null" safe_message="0" tr_id="signal:TMessageId(id=3270, mms=true)" m_cls="personal" v="19" secret_mode="0" re_file_name="null" re_count_info="null" readable_date="Feb 12, 2018 3:16:21 PM" contact_name="Bob Smith, Joe Smith">
    <parts>
      <part seq="0" ct="text/plain" name="null" chset="null" cd="null" fn="null" cid="&lt;signal:text&gt;" cl="null" ctt_s="null" ctt_t="null" text="First message text" sef_type="0" />
    </parts>
    <addrs>
      <addr address="+12025551234" type="151" charset="106" />
      <addr address="+12025551235" type="151" charset="106" />
      <addr address="+12025551236" type="137" charset="106" />
    </addrs>
  </mms>
  <mms date="1518477436000" spam_report="0" ct_t="application/vnd.wap.multipart.related" msg_box="1" address="+12025551234~+12025551235~+12025551236" sub_cs="null" re_type="0" retr_st="null" re_original_body="null" d_tm="null" exp="null" locked="0" msg_id="0" app_id="0" from_address="null" m_id="null" retr_txt="null" date_sent="1518477433" read="1" rpt_a="null" ct_cls="null" bin_info="0" pri="129" sub_id="-1" re_content_type="null" object_id="null" resp_txt="null" re_content_uri="null" ct_l="null" re_original_key="null" d_rpt="null" reserved="0" using_mode="0" _id="1434" rr_st="0" m_type="128" favorite="0" rr="null" sub="null" hidden="0" deletable="0" read_status="null" d_rpt_st="0" callback_set="0" seen="1" re_recipient_address="null" device_name="null" cmc_prop="null" resp_st="128" text_only="1" sim_slot="0" st="null" retr_txt_cs="null" creator="org.thoughtcrime.securesms" m_size="null" sim_imsi="null" correlation_tag="null" re_body="null" safe_message="0" tr_id="signal:TMessageId(id=3272, mms=true)" m_cls="personal" v="19" secret_mode="0" re_file_name="null" re_count_info="null" readable_date="Feb 12, 2018 3:17:16 PM" contact_name="Bob Smith, Joe Smith">
    <parts>
      <part seq="0" ct="text/plain" name="null" chset="null" cd="null" fn="null" cid="&lt;signal:text&gt;" cl="null" ctt_s="null" ctt_t="null" text="Second message text" sef_type="0" />
    </parts>
    <addrs>
      <addr address="+12025551234" type="137" charset="106" />
      <addr address="+12025551235" type="151" charset="106" />
      <addr address="+12025551236" type="151" charset="106" />
    </addrs>
  </mms>

Hope that helps. The 12025551236 number is my phone. I think the "type="137" indicates the originator of the message.

[alexlance]

Perfect thank you! I've pushed a change which should make the address field a tilda-separated list of phone numbers and the contact_name field a comma-separated list of names.

No guarantee it'll import the groups correctly - but unless I've missed something the format now looks very similar to what you've posted. You'll need to fetch the latest copy of the script again.

If it still doesn't create the groups correctly, then I guess there's something else going on.

[alexlance]

This was the change I added:

signal-message-exporter/signal-message-exporter.py

Line 142 in 90d5ca5

if receiver in GROUPS and len(GROUPS[receiver]):

[jbaker6953]

It's doing some weird stuff on group messages. Here are some I found in the output XML:

<mms date="1672277748272" ct_t="application/vnd.wap.multipart.related" msg_box="2" rr="null" sub="null" read_status="1"
 address="+12025551212~+12025551213~+12025551214~+12025551215" contact_name ="Bob Smith, Joe Smith, Roger
 Smith" m_id="null" read="1" m_size="None" m_type="128" sim_slot="0">
  <addr address="+12025551212" type="137" charset="UTF-8"/>
  <addr address="+12025551213" type="137" charset="UTF-8"/>
  <addr address="+12025551214" type="137" charset="UTF-8"/>
  <addr address="+12025551215" type="137" charset="UTF-8"/>
</mms>
<mms date="1672112568807" ct_t="application/vnd.wap.multipart.related" msg_box="2" rr="null" sub="null" read_status="1" 
address="+12025551212~+12025551213~+12025551214~+12025551215" contact_name ="Bob Smith, Joe Smith, Roger 
Smith" m_id="null" read="1" m_size="None" m_type="128" sim_slot="0">
    <addr address="+12025551212" type="137" charset="UTF-8"/>
	<addr address="+12025551213" type="137" charset="UTF-8"/>
	<addr address="+12025551214" type="137" charset="UTF-8"/>
	<addr address="+12025551215" type="137" charset="UTF-8"/>
</mms>
<mms date="1672112817790" ct_t="application/vnd.wap.multipart.related" msg_box="2" rr="null" sub="null" read_status="1" 
address="+12025551212~+12025551213~+12025551214~+12025551215" contact_name ="Bob Smith, Joe Smith, Roger 
Smith" m_id="null" read="1" m_size="None" m_type="128" sim_slot="0">
  <addr address="+12025551212" type="137" charset="UTF-8"/>
  <addr address="+12025551213" type="137" charset="UTF-8"/>
  <addr address="+12025551214" type="137" charset="UTF-8"/>
  <addr address="+12025551215" type="137" charset="UTF-8"/>
</mms>

Two things I see:

1. The messages are missing the <parts> and <part> structure that carries the actual message content.
2. All addresses are set to type 137.

[alexlance]

Re 1. I've just pushed some code which removes a bit of error suppression - if you download the latest version and run it again - an error will occur and you can let me know what it is (the longer the copy-paste the more helpful).

2. I've never been 100% on what to set these, but it looks like I've left myself a comment suggesting 137 or 151, I've added a branch in the code to try and set it.

[jbaker6953]

It seems to me that the addr structure should be that the sender's address has type=137 and the other members of the group addresses are type=151.

Let me run the latest

[jbaker6953]

I didn't experience any new errors. Just the same errors with attachments as before.

[alexlance]

There's something a bit weird with determining who the sender is when it comes to group messages.

There's a field on each MMS and it states who receives the message. The field is named address and it's on the mms table.

This address field is actually the primary key from the recipient table. The recipient table can contain a single person, or a link off to a group of people.

There's no field I can see that specifies this person was the sender, and that person was the receiver though...

I wonder if it's just the first of the recipients. I.e. if there are 4 recipients to an MMS, just assume that the first number was the sender, and everyone else is the receiver. I can add a modification to the code if you think that's a goer.

[alexlance]

Just the same errors with attachments as before.

This is definitely the same "older" backup file that we were working with earlier?

No file attachments at all? Quite sure? Or just particular messages don't have parts?

[jbaker6953]

1. In my XML exported by the app, the order of the <addr> varies with each message. Sometimes the sender is first, sometimes last, sometimes in the middle. Faced with this situation my first inclination is to label the group member that is not in the list of recipients. I have to investigate the tables some to say definitively.
2. I get the same 56 messages failed to export because the parts don't exist in '/bits'. This appears to be a Signal issue since these parts aren't in the part table even though the MMS table has part_count=1.

[jbaker6953]

I'm still trying to figure out why there is no

<parts>
      <part />
</parts>

structure in the output.

[jbaker6953]

OK. I have fixed the node issue and made it write more readable XML. Here's the raw file.
signal-message-exporter.txt

Working on getting the message text into a <part> and not duplicating parts.

[jbaker6953]

I verified that the type=137 on the sender's <address> is how authorship is determined. Still working.

[jbaker6953]

OK. Now I have it properly creating a <part> for the mms.body text. Stand by for fixing the sender.

[jbaker6953]

Fixed the python bug with newlines. Still working on sender info.

[jbaker6953]

Got the sender issue fixed. Only issue left I can see is that messages with an attachment AND text aren't working properly. Still working.

[jbaker6953]

OK. Everything's working. The only issue I'm having is that a couple of the messages are blank (no text and no attachment). I am researching the issue. Uploading here to make a snapshot of what I've done so far.
signal-message-exporter.py.txt

[jbaker6953]

The blank messages are when somebody sent me a shared contact. They do not appear in the message body nor in the part table. I am trying to see where those are stored.

[jbaker6953]

The data is in the database under mms.shared_contacts. The problem is that it looks like SMS Backup & Restore doesn't know how to deal with those. I can tell because when I use it to export one of those messages it's also blank. Not sure how to approach this one yet.

[jbaker6953]

This one's a far more complicated nut to crack. It looks like "SMS Backup & Restore" can handle vCard info just fine. The problem is translating what's stored in the Signal database into vCard format. The database stores it as flat text instead of the usual base64 encoded vCard format that appears to be standard, and it the text is not in vCard format.

[jbaker6953]

Here's my work in a diff format so it's easier to see what I've done.
signal-message-exporter.diff.txt

[jbaker6953]

Cleaned it up a little more by "monkey patching" the minidom bug and redefining the writexml() call to produce formatted XML without having to use prettyXML.
signal-message-exporter.diff.txt

[alexlance]

This is all epic. Well done.

The monkey patch thing didn't work for me - it produced an error - but the rest is great. I'm just running an end-to-end test and if all good I'll merge your work into master asap.

[jbaker6953]

I patched the monkey patch ... lol. The way it is in there right now works. I'm working on unrolling the vCard data into a legitimate vCard attachment. I'm hoping that I can just create some Python dictionaries with the existing data and unroll them in the code to produce a vCard and then base64 encode it.

[alexlance]

Yeah I'm definitely getting an error with the monkey patch - what version of python are you running?

When I run the code, I do so via make run (see the Makefile) which invokes the application inside of a Docker container. The reason I'm doing this is so that the environment that the code is running in is predictable - i.e. it should work for any user regardless of the menagerie of local dependencies or packages they have installed on their system.

I'm a bit surprised about the vcard stuff - what problem are you trying to solve there? (I'm tempted to suggest to not worry about it - but I don't understand what you're trying to fix)

[alexlance]

The monkey patch error:

  File "/root/signal-message-exporter.py", line 393, in <module>
    root.writexml(f, indent="\t", addindent="\t", newl="\n", encoding="utf-8", standalone="yes")
  File "/usr/lib/python3.10/xml/dom/minidom.py", line 1828, in writexml
    node.writexml(writer, indent, addindent, newl)
  File "/root/signal-message-exporter.py", line 40, in writexml
    node.writexml(writer, indent + addindent, addindent, newl)
  File "/root/signal-message-exporter.py", line 35, in writexml
    _write_data(writer, attrs[a_name].value, isAttrib=True)
  File "/root/signal-message-exporter.py", line 17, in _write_data
    data = data.replace("\r", "&#xD;").replace("\n", "&#xA;")
AttributeError: 'NoneType' object has no attribute 'replace'
make: *** [Makefile:6: run] Error 1

[jbaker6953]

Yes. I just ran into that. Testing a fix now. I am just not doing the .replace if data is None.

[jbaker6953]

I'm a bit surprised about the vcard stuff - what problem are you trying to solve there? (I'm tempted to suggest to not worry about it - but I don't understand what you're trying to fix)

All the messages where someone has texted me a contact information (vCard) show up as blank messages. I am trying to make those messages appear with the vCard attachment like they do in Signal and stock Android Messenger.

[jbaker6953]

Yes. I just ran into that. Testing a fix now. I am just not doing the .replace if data is None.

Nope. Still blowing up. I'll keep trying to figure it out.

[alexlance]

Have updated master, you may want to fetch again - to simplify your diff

[alexlance]

It looks like you've fixed the mms authorship btw!

One catch on that is in a group chat that has both mms and sms, the problem I described last night is still occurring, the sender of the sms is not being set.

[jbaker6953]

I think I got it. The money patch is quite old, so I just copy/pasted from the current xml.dom.minidom and modified it with the relevant stuff from the monkey patch. Running on my 10 GB Signal database now.

[jbaker6953]

It looks like you've fixed the mms authorship btw!

That was the hardest part.

One catch on that is in a group chat that has both mms and sms, the problem I described last night is still occurring, the sender of the sms is not being set.

I didn't know there was a such thing as group SMS. I was under the impression that all group messages were MMS.

[alexlance]

I might be mis-stating what I'm seeing. It's like there's this Signal group chat, and there's a bunch of messages in there that are media messages (stored in the mms table), and also text only messages (that are stored in the sms table). But Signal presents them all in the same group chat.

[alexlance]

Btw I haven't had a lot of luck with the various sms applications for verifying the results of an import. The only one that seems to be rendering the entire message history ok is qksms installed via f-droid. Not sure if that's helpful.

(my phone's a bit de-googled so I don't have google messages)

[jbaker6953]

It's fixed on my end. It doesn't blow up any more. The issue was just that the patch was stale in relation to the current xml.dom.minidom code base. I updated the patch and everything ran OK over here. Let me know.

As to the message in the SMS table showing up in your Signal under a group chat, are those messages all from you by any chance? I've heard of people having a problem where they reply to a group message but their message is sent out as individual SMS to each group member rather than as an MMS to the group.

signal-message-exporter.diff.txt

[alexlance]

Thanks @jbaker6953 it ran successfully for me now too! You've really nailed your goals here. Super impressed😍 I've just re-formatted the patch a bit so it passes my linter pylint and merged into master.

With the sms in group chat problem, unfortunately they are messages not just sent by me, but by others in the group too.

It's a Signal group chat that has messages in it that are in both the sms and mms tables (quite a lot of sms entries too - so it's not just a one-off or something). I'll take another look at it tomorrow.

[jbaker6953]

I don't think I have anything like that. Perhaps because all of my group chats involve at least one person who's not using Signal, which makes it default to MMS. I bet your groups where this is an issue involve all Signal users. I'd be curious to see how those are represented in the database. As long as we can build some kind of JOIN query to link them all up it shouldn't be too difficult to fix.

Happy to help people be free of vendor lock-in of their own personal data. I'd hate for Signal to pull the plug on us and have no way to to make our data portable, and if I'm going to do it for myself I might as well let everyone benefit from the work.

[jbaker6953]

There's a major bug in my code. I would suggest you revert it until I fix it. It probably explains why you were seeing the issue you described. I think I can fix it tonight or tomorrow.

[jbaker6953]

I believe this is fixed. I'll be curious to see if it addresses your issue too.

I also discovered a bug where we were writing "None" in XML attributes when we should have been writing "null." Fixed that, too.

Also fixed bug when there were naked 'bad' characters in the text message (e.g., "<", ">", "/", etc.), which weren't being escaped the way I had the monkey patch before.

Tried to improve resource use on very large databases by only selecting columns used in XML generation.

signal-message-exporter.diff.txt

[jbaker6953]

I got it to handle vCards stored before Signal fixed the vCard bug, but I still can't figure out where the vCard data is stored after they fixed that bug. I'm going to have to dig into the Signal source to investigate.

signal-message-exporter.diff.txt