How can I read the "attributedBody" column in macOS' iMessage database?

Question

Apple changed the Messages database schema in the latest macOS Ventura update, and sent messages seem to no longer store their body/content in the text column. The attributedBody column has the content, but it's stored as an encoded blob.

Has anyone had any luck getting plaintext out of this?

Answer 1

The attributedBody column is a serialized NSMutableAttributedString — packed using NSArchiver. It can be unpacked and read using NSUnarchiver but must first be extracted from the Messages sqlite database without losing any of its non-printable characters.

To preserve the column's content when performing a query, you can use sqlite3's HEX() function. The resulting bytes can then be read back into their original state by iterating over them and building a new NSString.

---

In the example below, NSData is extended with two helper methods to handle reading a file with hex-encoded data. Using dataWithContentsOfHexEncodedFile, a message record's attributedBody can be passed to NSUnarchiver, which will handle decoding the serialized NSAttributedString. This can then be converted to a normal NSString by accessing the string property.

#import <Foundation/Foundation.h>

@implementation NSData (NSDataExtended)
+ (NSData *)dataWithContentsOfHexEncodedString:(NSString *) string {
    const char * chars = [string UTF8String];
    int i = 0;
    
    NSMutableData *data = [NSMutableData dataWithCapacity: string.length / 2];
    char byteChars[3] = {'\0', '\0', '\0'};
    unsigned long wholeByte;
    
    while (i < string.length) {
        byteChars[0] = chars[i++];
        byteChars[1] = chars[i++];
        wholeByte = strtoul(byteChars, NULL, 16);
        [data appendBytes:&wholeByte length:1];
    }
    
    return data;
}

+ (NSData *)dataWithContentsOfHexEncodedFile:(NSString *) filePath {
    return [self dataWithContentsOfHexEncodedString:[NSString
                                    stringWithContentsOfFile:filePath
                                    encoding:NSUTF8StringEncoding
                                    error:nil]];
}
@end

int main(int argc, const char * argv[]) {
    system([[[NSString alloc] initWithFormat:@"%s %s > %s",
             "/usr/bin/sqlite3 ~/Library/Messages/chat.db",
             "'SELECT HEX(attributedBody) FROM message ORDER BY ROWID DESC LIMIT 1'",
             "/private/tmp/msgbody"] UTF8String]);
    
#pragma clang diagnostic ignored "-Wdeprecated-declarations"
    NSMutableAttributedString *msg = [[[NSUnarchiver alloc]
                                       initForReadingWithData:[NSData dataWithContentsOfHexEncodedFile:@"/private/tmp/msgbody"]
                                      ] decodeTopLevelObjectAndReturnError:nil];
    
    NSLog(@"%@", [msg string]);
    
    return 0;
}