8 ms·
Analyzing iMessage with SQL
- dancocos 5y agoThis post needs some caveats/updates: 1) You have to enable full disk access to be able to read the db, on Monterey at least. 2) The schema has changed a bit so the queries presented don't work.
- mikestew 5y agoA query that works on my Monterey box: https://news.ycombinator.com/item?id=31103404 https://news.ycombinator.com/item?id=31103404
- simpleintheory 5y agoTBH I'm surprised iMessage hasn't been reverse engineered more--it's a very popular app yet it seems like the only thing backing Apple's claim that it's secure and end-to-end encrypted is "trust me"
- c0balt 5y agoThere has been some research around the APN protocol and iMessage in the past by NowSecure[0] and otherse (see links in the article). I would guess the usefullness of a reverse engineered variant is just too low given the requirement for a valid serial number for each account. [0]: https://www.nowsecure.com/blog/2021/01/27/reverse-engineering-imessage-leveraging-the-hardware-to-protect-the-software/ https://www.nowsecure.com/blog/2021/01/27/reverse-engineerin...
- aaaaaaaaata 5y agoOr, people who do this sort of work don't want to encourage more people to stay in the walled garden of Apple.
- FinalBriefing 5y agoI don't think that's the case. There are plenty of open source projects that open up Airdrop to devices other than Apple's.
- c0balt 5y agoIsn't it just a simple question of the ROI for sich a project? If you need an iPhone anyway why should you use an alternative app on another phone? The only reasonable usecase I can identify are people writing iMessage bots/ spammers but those groups most likely wouldn't want to open-source their stuff.
- axutio 5y agoFor some pretty thorough reverse engineering, there's a Matrix bridge for iMessage [1] that's being used in production by Beeper [2]. [1] https://github.com/tulir/mautrix-imessage https://github.com/tulir/mautrix-imessage [2] https://beeper.com/ https://beeper.com/
- can16358p 5y agoHow on Earth does the blogger have only 1GB of iMessage data since 2009? Mine gets bloated with images and videos to multi gigabytes in days, and it's unlikely that it's used only for text for 13 years. (I mean, it's either never used at all, or used for all sorts of media not just text, and since it has been used, it should be the latter) Even if the actual media isn't stored in the database, all the metadata for all those messages should exceed 1GB IMO.
- philshem 5y agoIn the iOS settings under Messages you can choose how long you want to meep your messages and content. https://www.macrumors.com/how-to/auto-delete-old-messages-iphone-ipad/ https://www.macrumors.com/how-to/auto-delete-old-messages-ip...
- can16358p 5y agoYeah but I think the author has all the messages retained dating back to the beginning.
- zimpenfish 5y ago> How on Earth does the blogger have only 1GB of iMessage data since 2009? Anecdatum: My `chat.db` on this laptop is 44MB for 30863 messages going back just under 4 years (basically just a handful of family.) I can see that being 1GB even for triple the length of time and many more interactions, sure. The `Attachments` folder next to it, on the other hand, is 6.4GB.
- can16358p 5y agoYeah for some reason I assumed the size is for both (db + attachments) while writing the comment, which is wrong.
- aaaaaaaaata 5y ago> it's either never used at all, or used for all sorts of media not just text This seems to be where you went wrong.
- neoncontrails 5y agoI'm not sure that figuring out who among them you chat most frequently with constitutes "surprising facts about the people you text along the way," I was expecting some lurid metadata leakage not exposed through the UI and left feeling disappointed and a bit clickbaited.
- np_tedious 5y agoI think that's just a parsing issue/ ambiguity (surprising facts about the people) you text Vs surprising facts about (the people you text) While i don't use iMessage and therefore have no intention of doing this myself, I find exploring on-device sqlite pretty interesting and enjoyed this read. Doesn't seem at all like click bait
- Cthulhu_ 5y agoMust be just me or an American thing, but imessage isn't used nearly as much here; probably because Android takes up 80% market share, so people stick to cross-OS applications like Whatsapp.
- deleted 5y ago[deleted]
- kalleboo 5y agoI think iMessage is uniquely popular in the US since they all got hooked on SMS since their plans got "unlimited free texting" pretty early on, and iMessage is a mostly-transparent layer onto of SMS. Whereas in the rest of the world it was more common to be charged by the message so people adopted apps for "free texting". Also outside of the US it's probably more common to send international texts, which cost even more.
- jernejzen 5y agoNot really, in Europe you have unlimited sms’ from like forever. Think people went to different platforms because communication was less clumsy and it was easier to do it also with Android friends. So all in one
- muxator 5y agoSMS used to be an important revenue source for telcos. Basically they were charging for a best effort service on a signalling side channel. Free money.
- jorams 5y agoIt's a very country-specific thing. In the Netherlands approximately everyone switched to WhatsApp long before unlimited SMS became the norm. WhatsApp was ubiquitous for years before Facebook bought them in 2014, but even 5 or so years ago my cheap monthly plan did not include unlimited SMS.
- FinalBriefing 5y ago
- jes 5y agoiMessage is next to useless for me due to SMS spam from political fundraising groups that send texts from so many different numbers that manually blocking each phone number is practically impossible. I want an app that will prowl my pool of SMS texts on my phone every couple of minutes and delete every SMS text that matches a regexp I give it, without sending all of my texts off-device.
- aaaaaaaaata 5y agoTime to switch to Android, we have all sorts of productive stuff like that.
- Spooky23 5y agoStop donating to political entities and giving them your number. I made the mistake of giving my real phone number to an organization giving a relative an award 8 years ago. (Had to buy tickets) I still get SMS spam as a result.
- forbiddenlake 5y agoThat isn't sufficient. I have never done that, yet I still receive the texts (oddly, they usually address my sister, and sometimes my mother). It's my understanding that voter registration lists in the US are public.
- _moof 5y agoIf you select a party affiliation when you register, There Will Be Spam.
- Spooky23 5y agoYour family probably fell for some sort of scam where their contacts were skimmed. Voter registration lists are public, your phone number is not. I get texts from Trumpsters because I worked at a place with a public sector union and some ass gave the GAL to a anti-union group, which subsequently sold it to some other groups.
- 5y ago
- eatonphil 5y agoThis was interesting and surprisingly fun too, especially the "rate your friendships bit" based on reply ratio.
- giarc 5y agoFor vanity measures, I'd like to see which of my friends thinks I'm the funniest and therefore gives me the most "Haha"s on my messages.
- atribecalledqst 5y agoNice writeup. I've been self-studying SQL lately for kicks -- I can bumble my way through a SELECT, INSERT, CREATE TABLE, etc. but things like joins and subqueries are foreign to me. Always neat to see something I'm studying turn up on the HN front page so I can see how far I've come :) The writeup makes me wonder what other Apple software uses SQLite under the hood. In particular if the iTunes library is backed by a SQLite file, I could actually use that for some future projects. Cursory inspection shows that it's hdf-compressed so I'll need to get past that first... anyway, nice task for after-work hours.
- tangue 5y agohttps://selectstarsql.com/ https://selectstarsql.com/ may help you for JOIN I recommend that your do it in its entirety even if you understand the first concepts
- jeffdn 5y ago`join` isn't so bad -- you are connecting two tables based on a common value. If you have `person` and `address`: create table person ( id serial primary key, first_name text not null, last_name text not null, date_of_birth date not null ); create table address ( id serial primary key, person_id integer not null references person (id), street_number text not null, apartment_number text, city text not null, state text, postal_code text not null ); Then you'd join using `person.id` to `address.person_id`. An inner join hides rows from the joining table if there is no corresponding row in the joined-to table, while an outer join includes rows from the joining table if there are no corresponding rows in the joined-to table. If we've got: insert into person (first_name, last_name, date_of_birth) values ('Brian', 'Kernighan', '1950-01-01'), -- gets id 1 ('Dennis', 'Ritchie', '1948-01-01'); -- gets id 2 insert into address (person_id, street_number, city, postal_code) values (1, '123 Main St.', 'Berkeley', '94701'); Then these two queries will return one and two rows, respectively: -- an inner join, only returns `person` rows with a corresponding -- row in the `address` table select person.first_name, person.last_name, address.street_number, address.city, address.postal_code from person inner join address on person.id = address.person_id -- output: -- first_name | last_name | street_number | city | postal_code -- Brian | Kernighan | 123 Main St. | Berkeley | 94701 -- a left outer join, returns all `person` rows, regardless of -- the existence of a corresponding row in the `address` table select person.first_name, person.last_name, address.street_number, address.city, address.postal_code from person left outer join address on person.id = address.person_id -- output: -- first_name | last_name | street_number | city | postal_code -- Brian | Kernighan | 123 Main St. | Berkeley | 94701 -- Dennis | Ritchie | | | One important thing to know is if you are joining to multiple tables, and there are multiple corresponding rows (i.e., a `favorite_food` table with three rows per person, and a `favorite_color` table with three rows per person), you will get _multiplicative_ data -- `n_favorite_foods * n_favorite_colors` rows will be returned per person! This is where things like `group by` and `distinct` come into play. There are other types of joins, but these two are the usual suspects in most queries.
- mostlysimilar 5y agoHeads up: Arctype makes a series of outbound requests when launching that, when blocked at the network level, cripple the app and won't allow it to function.
- EvanAnderson 5y agoA similar thing: I've scraped the 'sms.db' from iPhone backups to create messages in my IMAP mailbox for years. It's nice to have all my textual correspondence in one place.
- kemayo 5y agoIt's worth noting that if you have the "Messages in iCloud" feature turned on, the local sqlite database can't be relied on any more. Or, rather, you become able to access messages in the client that aren't actually stored on-disk, and querying the local database will only turn up messages received since you set up the particular computer you're using.
- radicality 5y agoThat seems not true for me. I have that setting enabled, and I last updated my computer November last year, and before that in 2019. Just checked my imessage db file and it goes back to 2014. What you experienced sounds worrying though, I wouldn't want to lose that db on a potential computer upgrade. Do you have any more details on it or what you think happened?
- kemayo 5y agoActually, I might be thinking of the ~/Library/Messages/Archive folder, which used to contain a readable backup of your messages and which stops updating with the iCloud sync.
- jordache 5y agoHmm so their schema contains both camelCase and snake_case ? ewww
- radicality 5y agoIf you are on Mac, open up your Messages and go to any thread with some historical messages. Now scroll up to load past messages, and do that a few times. How slow is that for you? For me, when I scroll up to load historical data, it takes always multiple _seconds_. This is on the newest, highest specced m1 macbook. What the hell is it doing in that time? Multiple seconds to do read-only queries a <100MB sqlite DB (and sqlite can be incredibly fast), on an SSD with >5GB/s read speeds, with 64GB of ram (where the whole sqlite can/should be loaded into memory), to render a blue bubble with some text inside. What is Messages doing, and why is it so bad and inefficient. I'm certain the local sqlite db has the data I need because the above bothered me so much i wrote some scripts to read my historical chats from the cli. Quality of Apple software is certainly not increasing but going downhill, you see that across many of their apps.
- foxbarrington 5y agoI've always wondered about this, but it's not limited to Messages on macOS. WhatsApp is painful when you want to do the same thing. I don't know about other messaging apps, but I'm not sure why there isn't UI/UX where you can easily/quickly scrub through months/years.
- fesc 5y agoBut at least in WhatsApp I can scroll as fast as I want while the App never is stuck. With iMessage it loads for seconds every 20 messages or so. I also really don't get it.
- upbeat_general 5y agoTo be fair it’s been this bad as far as I can remember with iMessage. Incredibly frustrating and slow, but not new.
- vimy 5y agoThe search is also terrible.
- cmckn 5y agoWe're hardly the first people to complain about this, but it drives me nuts. I rarely delete messages (for better or worse), so I have an ever-growing iMessage database that is basically inaccessible to me. I don't know why I pay to back up 20 gigs of messages that I can't even access, but here we are. For instance, I wanted to figure out the exact date I met my partner. The only way to do this on iMessage is to scroll up...all the way up. With a few seconds of god-knows-what in between every page. The endeavor is usually futile, though; because when you get a ways back, the iMessage app will crash.
- shortstuffsushi 5y agoFunny timing on that, I just spent some time last week doing a very basic "sentiment analysis" (basically emoji analysis) on the iMessage db. I was a bit surprised by the database layout, but it's pretty easy to get into and could make for some more interesting analysis and visualization like this.
- deleted 5y ago[deleted]
- mikestew 5y agoMaybe it's just me, but I'm surprised that no one has posted the query that works on latest macOS, as it appears that the schema on my machine differs from the author's. The below works on my Monterey machine; you might want to add back the limit 50: select h.id as sender_name, m.text as message_body from message m join handle h on h.rowid = m.handle_id order by m.date;