6 ms·
Funny, I've actually spent a good amount of time tearing apart the iMessage database back in 2016-2017. The queries you end up with are hilarious. Here's one fr
by megraf 6y ago
Funny, I've actually spent a good amount of time tearing apart the iMessage database back in 2016-2017. The queries you end up with are hilarious. Here's one from my website's sample code
```
INNER JOIN chat_message_join ON message.ROWID = chat_message_join.message_id
INNER JOIN chat ON chat.ROWID = chat_message_join.chat_id
INNER JOIN chat_handle_join ON chat.ROWID = chat_handle_join.chat_id
INNER JOIN handle ON handle.ROWID = chat_handle_join.handle_id
LEFT JOIN message_attachment_join ON message.ROWID = message_attachment_join.message_id
LEFT JOIN attachment ON attachment.ROWID = message_attachment_join.attachment_id
...
```
But it really wasn't that hard. I was able to programmatically send and receive iMessages. The problem (as the author pointed out) was that this changed, and didn't really work for the long. Although, I'm sure it could still be reveng'd – it's a pain in the ass, and (for me) not really worth it... =/
- kccqzy 6y agoI did something very similar, extracting the iMessage database. It works by you making an iTunes backup of your phone, and then accessing a specific file in the backup. I ended up with a query that looks like this (only tested with iOS 8, as I sadly lost interest afterwards): WITH groupchat_membership AS ( SELECT cache_roomnames, group_concat(id, ', ') AS members FROM ( SELECT DISTINCT message.cache_roomnames, handle.id FROM message JOIN handle ON message.handle_id=handle.ROWID WHERE message.cache_roomnames IS NOT NULL AND message.is_from_me=0 ) GROUP BY cache_roomnames ), unsorted_messages AS ( SELECT message.ROWID AS rowid, CASE WHEN message.cache_roomnames IS NOT NULL THEN groupchat_membership.members ELSE handle.id END AS recipient, CASE WHEN message.is_from_me THEN 'Me' ELSE handle.id END AS sender, message.date, strftime('%d/%m/%Y %H:%M:%S', datetime(message.date + 978307200, 'unixepoch', 'localtime')) AS human_date, CASE WHEN message.cache_has_attachments THEN '<Attachment>' || message.text ELSE message.text END AS text FROM message LEFT JOIN handle ON message.handle_id=handle.ROWID NATURAL LEFT JOIN groupchat_membership ) SELECT unsorted_messages.recipient, sender, human_date, text FROM unsorted_messages NATURAL LEFT JOIN ( SELECT recipient, max(date) AS latest_date FROM unsorted_messages GROUP BY recipient ) ORDER BY latest_date DESC, date ASC;
- mwlp 6y agoI've been working on a new version of [my iMessage bot](https://github.com/inculi/Sue https://github.com/inculi/Sue) recently. Previously it was using Applescript handlers on Sierra, now sqlite calls to iMessage's chat.db (I have to give credit to another bot, [Jared](https://github.com/ZekeSnider/Jared https://github.com/ZekeSnider/Jared) for help in this area). I get latest messages with: ``` SELECT handle.id, handle.person_centric_id, message.cache_has_attachments, message.text, message.ROWID, message.cache_roomnames, message.is_from_me, message.date/1000000000 + strftime("%s", "2001-01-01") AS utc_date FROM message INNER JOIN handle ON message.handle_id = handle.ROWID WHERE message.ROWID > #{rowid}; ``` and their attachments with: ``` SELECT attachment.ROWID AS a_id, message_attachment_join.message_id AS m_id, attachment.filename, attachment.mime_type, attachment.total_bytes FROM attachment INNER JOIN message_attachment_join ON attachment.ROWID == message_attachment_join.attachment_id WHERE message_attachment_join.message_id >= #{rowid}; ``` where rowid is the max rowid from the previous search, and attachments with null mime_types get ignored (YouTube previews, etc). Sending through Applescript still.
- megraf 6y agoNice job. I'll take a look at your project and see if I can contrib