Parsing public.url attachments in note BLOB

[renesugar]

Currently, mac_apt does not extract public.url attachments in the note BLOB of Apple Notes.

When creating a note from a web page, a note containing a public.url attachment is created.

Since the Notes app allows the user to choose which note to use, a note may contain multiple public.url attachments.

Each public.url attachment in the note BLOB contains an UUID that can be used to look up the URL.

This will only extract one public.url attachment from the note BLOB:

e.g. High Sierra

SELECT n.Z_PK, n.ZNOTE as note_id, n.ZDATA as data, 
c3.ZFILESIZE, 
c4.ZFILENAME, c4.ZIDENTIFIER as att_uuid, c3.ZURLSTRING as att_url, 
c1.ZTITLE1 as title, c1.ZSNIPPET as snippet, c1.ZIDENTIFIER as noteID, 
c1.ZCREATIONDATE1 as created, c1.ZLASTVIEWEDMODIFICATIONDATE, c1.ZMODIFICATIONDATE1 as modified, 
c2.ZACCOUNT3, c2.ZTITLE2 as folderName, c2.ZIDENTIFIER as folderID, 
c5.ZNAME as acc_name, c5.ZIDENTIFIER as acc_identifier, c5.ZACCOUNTTYPE 
FROM ZICNOTEDATA as n 
LEFT JOIN ZICCLOUDSYNCINGOBJECT as c1 ON c1.ZNOTEDATA = n.Z_PK 
LEFT JOIN ZICCLOUDSYNCINGOBJECT as c2 ON c2.Z_PK = c1.ZFOLDER 
LEFT JOIN ZICCLOUDSYNCINGOBJECT as c3 ON c3.ZNOTE= n.ZNOTE 
LEFT JOIN ZICCLOUDSYNCINGOBJECT as c4 ON c4.ZATTACHMENT1= c3.Z_PK 
LEFT JOIN ZICCLOUDSYNCINGOBJECT as c5 ON c5.Z_PK = c1.ZACCOUNT2 
ORDER BY note_id

[ydkhatri]

Can you share a sample of a notes database having this? You can email me if you don't want it shared publicly.

[renesugar]

It is a large database.

If you view a web page in Safari on iPhone, you can create a note from the web page which will include a public.url attachment. If you choose the same note, more public.url attachments will be added to the same note.

I did find this which describes the Apple note BLOB format in detail (including public.url attachments) and includes a parser written in Ruby (MIT License):

Revisiting Apple Notes (2): Easy Embedded Objects

https://ciofecaforensics.com/2020/01/13/apple-notes-revisited-easy-embedded-objects/

Revisiting Apple Notes (1): Improved Note Parsing

https://ciofecaforensics.com/2020/01/10/apple-notes-revisited/

More embedded objects:

https://ciofecaforensics.com/2020/01/20/apple-notes-revisited-galleries/

https://ciofecaforensics.com/2020/01/14/apple-notes-revisited-embedded-tables/

MIT License, written in Ruby:

https://github.com/threeplanetssoftware/apple_cloud_notes_parser

ISC License, written in Python:

https://github.com/mildsunrise/protobuf-inspector

[ydkhatri]

Well, I don't have an iphone. So, if you could generate some data and share it, I could look into it.

[renesugar]

Here are CSV files with a subset of the data. The BLOB columns are encoded using the SQLite hex() function:

https://www.dropbox.com/s/qmmqqs0r2irkxmk/ZICCLOUDSYNCINGOBJECT.csv?dl=0

https://www.dropbox.com/s/8syzrl7bqxtt0t9/ZICNOTEDATA.csv?dl=0

[threeplanetssoftware]

I accidentally ended up at this repository while looking for other information about MacOS and was going to offer a suggestion on this issue, but then I saw @renesugar already found everything I had to offer :). I eyeballed plugins/notes.py and can confirm that issue issue is that the protobuf itself isn't being read, you are just taking one attachment. That issue shouldn't be limited to URLs since modern Notes functionality allows embedding many attachments and attachments themselves can have children (such as thumbnails). If you don't read the protobuf and then also deal with the mergable objects from ZICCLOUDSYNCINGOBJECT, you won't get all the modern attachments.

You can recreate URLs on a Mac as well, if you don't have an iOS device:

1. Open Safari
2. Click the Action button in the upper right of the window, next to the tabs icon
3. Click on Notes, to embed this in a new note

If needed, I can provide some specific URL BLOBs, but this link Rene already provided specifically deals with URLs about half way through.

I hope this helps!

[ydkhatri]

Will take a look as time permits, it would really help if someone could just share sample databases with this info.

[renesugar]

I ended up using a library that parses the various Protobuf objects (including URLs, tables, etc.) embedded in Apple Notes here:

https://github.com/renesugar/readnotes/blob/master/notes2html.py

The library was sufficient for extracting data to migrate notes; for forensics, there is more data to extract like the thumbnails mentioned.

The CSV files that I provided contain examples of Protobuf BLOBs containing URLs.

[ydkhatri]

Revisited this today. The base query is fine, it's just missing a few columns.
In 6bebfff I've now fixed the query to read the embed types, urls, summary and titles. This gets table-data and URLs. I haven't looked into other types.

This does not attempt to read the protobuf and reconstruct the original note view yet. It just gives you the text content of everything in the note, which mostly works to see what's in it, except for tables, where you don't know which piece of the data was in which row/col.

[ydkhatri]

Closing this as public.url attachments are now displayed.