JoeCode

TIL: explore iMessage database with SQLite

Apr 12, 2023

Give Terminal “Full Disk Access” permissions:

 Apple menu –> “Privacy & Security” –> “Full Disk Access”

Connect to iMessage database using SQLite:

 $ sqlite3 /Users/username/Library/Messages/chat.db

Turn on headers:

sqlite> .headers yes

Determine group chat ids:

sqlite> 
SELECT 
  ROWID, 
  display_name 
FROM chat 
WHERE display_name != '' 
GROUP BY 
  display_name, 
  group_id;

Get messages using chat_identifier: (cache_roomnames == chat_identifier)

SELECT 
  ROWID, 
  guid, 
  date, 
  text, 
  cache_roomnames,
  HEX(attributedBody) AS message_blob
FROM message 
WHERE cache_roomnames = 'chat123456789012345678' 
ORDER BY ROWID DESC 
LIMIT 20;

Build full group chat history:

sqlite> 
SELECT 
  message.ROWID, 
  chat_id, 
  handle_id,
  handle.id AS handle,
  display_name, 
  cache_has_attachments,
  message.is_from_me,
  datetime (message.date/1000000000 + strftime("%s", "2001-01-01"), "unixepoch", "localtime") AS message_date,
  text,
  HEX(attributedBody) AS message_blob
FROM message 
JOIN chat_message_join ON message.ROWID = chat_message_join.message_id
JOIN chat ON chat_id = chat.ROWID
JOIN handle ON handle_id = handle.ROWID
WHERE chat_id=55
ORDER BY message.ROWID DESC 
LIMIT 10;

HEX(attributedBody)?

For some reason, Apple has started using a blob to store message text occasionally. You will need to decode the blob in order to extract the text data.

Python code to decode attributedBody

GitHub - imessage_tools.py

attributed_body = attributed_body.decode('utf-8', errors='replace')

if "NSNumber" in str(attributed_body):
    attributed_body = str(attributed_body).split("NSNumber")[0]
    if "NSString" in attributed_body:
        attributed_body = str(attributed_body).split("NSString")[1]
        if "NSDictionary" in attributed_body:
            attributed_body = str(attributed_body).split("NSDictionary")[0]
            attributed_body = attributed_body[6:-12]
            body = attributed_body

Ruby code to decode attributedBody

GitHub - NSMutableAttributedString.rb

# Grab HEX(attributedBody) from chat.db and unfark it
def unfark_imessage_attributed_body(hex_string)
  wtf_string = [hex_string].pack("H*").encode('UTF-8', 'binary', invalid: :replace, undef: :replace, replace: ' ')
  wtf_string.gsub!(/\s+/, ' ')
  if wtf_string.include? 'NSNumber'
    wtf_string = wtf_string.split('NSNumber')[0]
    if wtf_string.include? 'NSString'
      wtf_string = wtf_string.split('NSString')[1]
      if wtf_string.include? 'NSDictionary'
        wtf_string = wtf_string.split('NSDictionary')[0]
        # highly suspect and brittle code to clean up the last bit of blob flotsam
        if wtf_string.include? "\u0001+ \u0000"
          wtf_string = wtf_string.split("\u0001+ \u0000")[1]
        end
        if wtf_string.include? "\u0001 \u0001+"
          wtf_string = wtf_string.split("\u0001 \u0001")[1][2..]
        end
        if wtf_string.include? "\u0002iI\u0001"
          wtf_string = wtf_string.split("\u0002iI\u0001")[0]
        end
      end
    end
  end
  return wtf_string
end

Resources