def ReadNotesV2_V4_V6(db, notes, version, source, user):
'''Reads NotesVx.storedata, where x= 2,4,6,7'''
try:
query = "SELECT n.Z_PK as note_id, n.ZDATECREATED as created, n.ZDATEEDITED as edited, n.ZTITLE as title, "\
" (SELECT ZNAME from ZFOLDER where n.ZFOLDER=ZFOLDER.Z_PK) as folder, "\
" (SELECT zf2.ZACCOUNT from ZFOLDER as zf1 LEFT JOIN ZFOLDER as zf2 on (zf1.ZPARENT=zf2.Z_PK) where n.ZFOLDER=zf1.Z_PK) as folder_parent_id, "\
" ac.ZEMAILADDRESS as email, ac.ZACCOUNTDESCRIPTION as acc_desc, ac.ZUSERNAME as username, b.ZHTMLSTRING as data, "\
" att.ZCONTENTID as att_id, att.ZFILEURL as file_url "\
" FROM ZNOTE as n "\
" LEFT JOIN ZNOTEBODY as b ON b.ZNOTE = n.Z_PK "\
" LEFT JOIN ZATTACHMENT as att ON att.ZNOTE = n.Z_PK "\
" LEFT JOIN ZACCOUNT as ac ON ac.Z_PK = folder_parent_id"
db.row_factory = sqlite3.Row
cursor = db.execute(query)
for row in cursor:
try:
att_path = ''
if row['file_url'] != None:
att_path = ReadAttPathFromPlist(row['file_url'])
note = Note(
row['note_id'], row['folder'], row['title'], '',
row['data'], row['att_id'], att_path, row['acc_desc'],
row['email'], row['username'],
CommonFunctions.ReadMacAbsoluteTime(row['created']),
CommonFunctions.ReadMacAbsoluteTime(row['edited']),
version, 0, '', user, source)
notes.append(note)
except (sqlite3.Error, KeyError):
log.exception('Error fetching row data')
except sqlite3.Error:
log.exception('Query execution failed. Query was: ' + query)
def parse_hsts_plist(plist, cookies, user_name, plist_path):
'''Parse plist and add items to cookies list'''
hsts_store = plist.get('com.apple.CFNetwork.defaultStorageSession', None)
if hsts_store:
for site, items in hsts_store.items():
if isinstance(items, dict): # Newer hsts (version 3 or higher seen)
c_time = CommonFunctions.ReadMacAbsoluteTime(items.get('Create Time', None))
e_time = items.get('Expiry', None)
if e_time in (float('inf'), float('-inf')):
e_time = None
else:
e_time = CommonFunctions.ReadMacAbsoluteTime(e_time)
elif isinstance(items, float): # In older hsts file (version 1):
c_time = None
e_time = items
if e_time in (float('inf'), float('-inf')):
e_time = None
else:
e_time = CommonFunctions.ReadMacAbsoluteTime(e_time)
#else:
# log.error(f"Unknown type for hsts items : {str(type(items))}") # This is HSTS_Content_Version and schema
cookies.append(Cookie(site, c_time, e_time, '', '','', user_name, plist_path))
else:
log.error(f'Did not find com.apple.CFNetwork.defaultStorageSession in {plist_path}')
def ReadLastSessionPlist(plist, safari_items, source_path, user):
try:
version = plist['SessionVersion']
if version != '1.0':
log.warning('SessionVersion is {}, this may not parse properly!'.format(version))
except KeyError:
log.error('SessionVersion not found')
try:
session_windows = plist['SessionWindows']
for windows in session_windows:
selectedIndex = windows.get('SelectedTabIndex', None)
index = 0
for tab in windows.get('TabStates', []):
info = 'SELECTED WINDOW' if index == selectedIndex else ''
date_closed = tab.get('DateClosed', '')
log.debug(date_closed)
if date_closed:
if info:
info += ', TAB_CLOSED_DATE=' + str(date_closed)
else:
info = 'TAB_CLOSED_DATE=' + str(date_closed)
si = SafariItem(SafariItemType.LASTSESSION, tab.get('TabURL', ''), tab.get('TabTitle', ''),
CommonFunctions.ReadMacAbsoluteTime(tab.get('LastVisitTime', '')),
info, user, source_path) # Skipping SessionState(its encrypted) & TabIdentifier
safari_items.append(si)
index += 1
except KeyError as ex:
log.error('SessionWindows not found or unable to parse. Error was {}'.format(str(ex)))
def ReadHistoryPlist(plist, safari_items, source_path, user):
try:
version = plist['WebHistoryFileVersion']
if version != 1:
log.warning('WebHistoryFileVersion is {}, this may not parse properly!'.format(version))
except KeyError:
log.error('WebHistoryFileVersion not found')
try:
history_dates = plist['WebHistoryDates']
for item in history_dates:
try:
redirect_urls = ",".join(item.get('redirectURLs', ''))
si = SafariItem(SafariItemType.HISTORY, item.get('',''), item.get('title', ''), \
CommonFunctions.ReadMacAbsoluteTime(item.get('lastVisitedDate', '')), \
'' if (redirect_urls == '') else ('REDIRECT_URLS:' + redirect_urls) , user, source_path) # Skipped visitCount
safari_items.append(si)
except ValueError as ex:
log.error(str(ex))
except KeyError:
log.error('WebHistoryDates not found')
try:
history_domains = plist['WebHistoryDomains.v2']
for item in history_domains:
si = SafariItem(SafariItemType.HISTORYDOMAINS, '', item.get('', ''), None,
'ITEMCOUNT:' + str(item.get('itemCount', 0)) , user, source_path)
safari_items.append(si)
except KeyError:
log.error('WebHistoryDomains.v2 not found')
def parse_user_acct_plist(plist, user_accounts, plist_path):
'''Parse plist and add items to app list'''
for user, items in plist.items():
uid = items.get('uid', '')
for k, v in items.items():
if k == 'uid':
continue
elif isinstance(v, list): # tty or console
session_name = k
for session in v:
ua = UserAcct(
session_name,
CommonFunctions.ReadMacAbsoluteTime(
session.get('inTime', None)),
CommonFunctions.ReadMacAbsoluteTime(
session.get('outTime', None)), uid, user,
plist_path)
user_accounts.append(ua)
def parse_cookie_file(cookie_file, cookies, user_name, file_path):
'''Parse .binarycookies or .cookies file'''
data = cookie_file.read()
if data[0:4] == b'cook':
num_pages = struct.unpack('>I', data[4:8])[0]
if num_pages == 0:
return
page_sizes = []
pos = 8
for x in range(num_pages):
page_sizes.append(struct.unpack('>I', data[pos : pos + 4])[0])
pos +=4
page_start = pos
for page_size in page_sizes:
# read page
pos = page_start
pos += 4
num_cookies = struct.unpack('<I', data[pos : pos + 4])[0]
pos += 4
offsets = []
for y in range(num_cookies):
offsets.append(struct.unpack('<I', data[pos : pos + 4])[0])
pos += 4
for offset in offsets:
cookie_data = data[page_start + offset : page_start + page_size]
length, unk1, flags, unk2, url_offset, \
name_offset, path_offset, value_offset, \
unk3, expiry_time, create_time = struct.unpack('<IIIIIIIIQdd', cookie_data[0:56])
url = read_cstring(cookie_data[url_offset:])
name = read_cstring(cookie_data[name_offset:])
path = read_cstring(cookie_data[path_offset:])
value = read_cstring(cookie_data[value_offset:])
if url and url[0] == '.':
url = url[1:]
expiry_time = CommonFunctions.ReadMacAbsoluteTime(expiry_time)
create_time = CommonFunctions.ReadMacAbsoluteTime(create_time)
cookies.append(Cookie(url, create_time, expiry_time, name, path, value, user_name, file_path))
page_start = page_start + page_size
else:
log.error('Not the expected header for cookie file. Got {} instead of "cook"'.format(str(data[0:4])))
def ReadQueryResults(cursor, notes, enc_possible, user, source):
for row in cursor:
try:
att_path = ''
if row['media_id'] != None:
att_path = row['ZFILENAME']
if enc_possible and row['encrypted'] == 1:
text_content = ''
pw_hint = row['ZPASSWORDHINT']
else:
pw_hint = ''
data = GetUncompressedData(row['data'])
text_content = ProcessNoteBodyBlob(data)
note = Note(row['note_id'], row['folder'], row['title'],
row['snippet'], text_content, row['att_uuid'],
att_path, row['acc_name'], row['acc_identifier'], '',
CommonFunctions.ReadMacAbsoluteTime(row['created']),
CommonFunctions.ReadMacAbsoluteTime(row['modified']),
'NoteStore', row['encrypted'] if enc_possible else 0,
pw_hint, user, source)
notes.append(note)
except sqlite3.Error:
log.exception('Error fetching row data')
def parse_app_usage_plist(plist, app_usage_list, plist_path):
'''Parse plist and add items to app list'''
for app_path, items in plist.items():
app_name = items.get('Name', '')
run_data = items.get('runData', None)
if run_data:
for run in run_data:
was_quit = run.get('wasQuit', 0)
frontmost = run.get('Frontmost', 0)
start_time = CommonFunctions.ReadMacAbsoluteTime(
run.get('Launched', ''))
run_length = run.get('runLength', '')
user = run.get('userName', '')
au = AppUsage(app_name, app_path, was_quit, frontmost,
start_time, run_length, user, plist_path)
AppUsageInsertUnique(app_usage_list, au)
def ReadNetUsageDb(db, netusage_items, source):
'''Reads netusage.sqlite db'''
try:
query = "SELECT pk.z_name as item_type, na.zidentifier as item_name, "\
"na.zfirsttimestamp as first_seen_date, "\
"na.ztimestamp as last_seen_date, "\
"rp.ztimestamp as rp_date, rp.zbytesin, rp.zbytesout "\
"FROM znetworkattachment as na "\
"LEFT JOIN z_primarykey pk ON na.z_ent = pk.z_ent "\
"LEFT JOIN zliverouteperf rp ON rp.zhasnetworkattachment = na.z_pk "\
"ORDER BY pk.z_name, zidentifier, rp_date desc"
db.row_factory = sqlite3.Row
cursor = db.execute(query)
for row in cursor:
try:
nu_data = NetUsage(
row['item_type'], row['item_name'],
CommonFunctions.ReadMacAbsoluteTime(
row['first_seen_date']),
CommonFunctions.ReadMacAbsoluteTime(row['last_seen_date']),
CommonFunctions.ReadMacAbsoluteTime(row['rp_date']), '',
'', '', '', '', '', row['zbytesin'], row['zbytesout'],
"Bytes in/out are based off ArtifactDate", source)
netusage_items.append(nu_data)
except (sqlite3.Error, ValueError):
log.exception('Error fetching row data')
# Get process info now
query = "SELECT pk.z_name as item_type ,p.zprocname as process_name, "\
"p.zfirsttimestamp as first_seen_date, "\
"p.ztimestamp as last_seen_date, "\
" lu.ztimestamp as usage_since, "\
"lu.zwifiin, lu.zwifiout,lu.zwiredin,lu.zwiredout,lu.zwwanin,lu.zwwanout "\
"FROM zliveusage lu LEFT JOIN zprocess p ON p.z_pk = lu.zhasprocess "\
"LEFT JOIN z_primarykey pk ON p.z_ent = pk.z_ent "\
"ORDER BY process_name"
db.row_factory = sqlite3.Row
cursor = db.execute(query)
for row in cursor:
try:
nu_data = NetUsage(
row['item_type'], row['process_name'],
CommonFunctions.ReadMacAbsoluteTime(
row['first_seen_date']),
CommonFunctions.ReadMacAbsoluteTime(row['last_seen_date']),
CommonFunctions.ReadMacAbsoluteTime(row['usage_since']),
row['zwifiin'], row['zwifiout'], row['zwiredin'],
row['zwiredout'], row['zwwanin'], row['zwwanout'], '', '',
"Data usage is counted from ArtifactDate onwards", source)
netusage_items.append(nu_data)
except (sqlite3.Error, ValueError):
log.exception('Error fetching row data')
except (sqlite3.Error, ValueError):
log.exception('Query execution failed. Query was: ' + query)
def ReadHistoryDb(conn, safari_items, source_path, user):
try:
conn.row_factory = sqlite3.Row
cursor = conn.execute("select title, url, load_successful, visit_time as time_utc from "
"history_visits left join history_items on history_visits.history_item = history_items.id")
try:
for row in cursor:
try:
si = SafariItem(SafariItemType.HISTORY, row['url'], row['title'],
CommonFunctions.ReadMacAbsoluteTime(row['time_utc']),'', user, source_path)
safari_items.append(si)
except sqlite3.Error as ex:
log.exception ("Error while fetching row data")
except sqlite3.Error as ex:
log.exception ("Db cursor error while reading file " + source_path)
conn.close()
except sqlite3.Error as ex:
log.exception ("Sqlite error")
def ReadQuarantineDb(db, quarantined, source, user):
'''Reads com.apple.LaunchServices.QuarantineEventsV2 sqlite db'''
try:
query = "SELECT LSQuarantineEventIdentifier as id, LSQuarantineTimeStamp as ts, LSQuarantineAgentBundleIdentifier as bundle, "\
" LSQuarantineAgentName as agent_name, LSQuarantineDataURLString as data_url, "\
" LSQuarantineSenderName as sender_name, LSQuarantineSenderAddress as sender_add, LSQuarantineTypeNumber as type_num, "\
" LSQuarantineOriginTitle as o_title, LSQuarantineOriginURLString as o_url, LSQuarantineOriginAlias as o_alias "\
" FROM LSQuarantineEvent "\
" ORDER BY ts"
db.row_factory = sqlite3.Row
cursor = db.execute(query)
for row in cursor:
try:
q_event = Quarantine(
row['id'], CommonFunctions.ReadMacAbsoluteTime(row['ts']),
row['bundle'], row['agent_name'], row['data_url'],
row['sender_name'], row['sender_add'], row['type_num'],
row['o_title'], row['o_url'], row['o_alias'], user, source)
quarantined.append(q_event)
except (sqlite3.Error, KeyError):
log.exception('Error fetching row data')
except sqlite3.Error:
log.exception('Query execution failed. Query was: ' + query)
def ReadAccountsDb(db, accounts, source_path, user):
'''Parses Accounts3.sqlite & Accounts4.sqlite files'''
try:
query = " SELECT Z_PK as acc_id, "\
" (SELECT ZACCOUNTTYPEDESCRIPTION from ZACCOUNTTYPE where ZACCOUNTTYPE.Z_PK=a.ZACCOUNTTYPE) as acc_type, "\
" a.ZACCOUNTDESCRIPTION as acc_name, a.ZUSERNAME as acc_user, a.ZDATE as acc_date, "\
" a.ZPARENTACCOUNT as acc_parent_id, a.ZIDENTIFIER as acc_uuid, a.ZOWNINGBUNDLEID as acc_bundle "\
" FROM ZACCOUNT as a "\
" WHERE a.Z_ENT = (SELECT Z_ENT FROM Z_PRIMARYKEY WHERE Z_NAME LIKE 'Account')"
db.row_factory = sqlite3.Row
cursor = db.execute(query)
for row in cursor:
try: # id, type, name, username, date, parent_id, uuid, bundle, user, source)
account = Account(
row['acc_id'], row['acc_type'], row['acc_name'],
row['acc_user'], '',
CommonFunctions.ReadMacAbsoluteTime(row['acc_date']),
row['acc_parent_id'], row['acc_uuid'], row['acc_bundle'],
user, source_path)
accounts.append(account)
except sqlite3.Error:
log.exception('Error fetching row data')
except sqlite3.Error:
log.exception('Query execution failed. Query was: ' + query)
def ReadSafariPlist(plist, safari_items, source, user):
'''Read com.apple.safari.plist'''
try:
searches = plist['RecentSearchStrings'] # Mavericks
try:
for search in searches:
si = SafariItem(SafariItemType.GENERAL, '', search, None, 'RECENT_SEARCH', user, source)
safari_items.append(si)
except ValueError as ex:
log.exception('Error reading RecentSearchStrings from plist')
except KeyError: # Not found
pass
try:
searches = plist['RecentWebSearches'] # Yosemite
try:
for search in searches:
si = SafariItem(SafariItemType.GENERAL, '', search.get('SearchString',''),
search.get('Date', None), 'RECENT_SEARCH', user, source)
safari_items.append(si)
except ValueError as ex:
log.exception('Error reading RecentWebSearches from plist')
except KeyError: # Not found
pass
try:
freq_sites = plist['FrequentlyVisitedSitesCache'] # seen in El Capitan
try:
for site in freq_sites:
si = SafariItem(SafariItemType.FREQUENTLY_VISITED, site.get('URL', ''), site.get('Title',''),
None, 'FrequentlyVisitedSitesCache', user, source)
safari_items.append(si)
except ValueError as ex:
log.exception('Error reading FrequentlyVisitedSitesCache from plist')
except KeyError: # Not found
pass
try:
download_path = plist['DownloadsPath']
si = SafariItem(SafariItemType.GENERAL, '', download_path, None, 'DOWNLOADS_PATH', user, source)
safari_items.append(si)
except KeyError: # Not found
pass
try:
home = plist['HomePage']
si = SafariItem(SafariItemType.GENERAL, home, '', None, 'HOME_PAGE', user, source)
safari_items.append(si)
except KeyError: # Not found
pass
try:
last_ext_pref_selected = plist['LastExtensionSelectedInPreferences']
si = SafariItem(SafariItemType.EXTENSION, '', last_ext_pref_selected, None, 'LastExtensionSelectedInPreferences', user, source)
safari_items.append(si)
except KeyError: # Not found
pass
try:
last_root_dir = plist['NSNavLastRootDirectory']
si = SafariItem(SafariItemType.GENERAL, last_root_dir, '', None, 'NSNavLastRootDirectory', user, source)
safari_items.append(si)
except KeyError: # Not found
pass
try:
time = CommonFunctions.ReadMacAbsoluteTime(plist['SuccessfulLaunchTimestamp'])
si = SafariItem(SafariItemType.GENERAL, '', '', time, 'SuccessfulLaunchTimestamp', user, source)
safari_items.append(si)
except KeyError: # Not found
pass
def ReadNotesHighSierraAndAbove(db, notes, source, user, is_ios):
'''Read Notestore.sqlite'''
try:
query_1 = \
"""
SELECT n.Z_PK, n.ZNOTE as note_id, n.ZDATA as data, c1.ZISPASSWORDPROTECTED as encrypted, c1.ZPASSWORDHINT,
c3.ZFILESIZE, c4.ZFILENAME, c4.ZIDENTIFIER as att_uuid,
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,
c3.ZSUMMARY, c3.ZTITLE, c3.ZURLSTRING, c3.ZTYPEUTI 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
"""
query_2 = \
"""
SELECT n.Z_PK, n.ZNOTE as note_id, n.ZDATA as data, c1.ZISPASSWORDPROTECTED as encrypted, c1.ZPASSWORDHINT,
c3.ZFILESIZE, c4.ZFILENAME, c4.ZIDENTIFIER as att_uuid,
c1.ZTITLE1 as title, c1.ZSNIPPET as snippet, c1.ZIDENTIFIER as noteID,
c1.ZCREATIONDATE1 as created, c1.ZLASTVIEWEDMODIFICATIONDATE, c1.ZMODIFICATIONDATE1 as modified,
c2.ZACCOUNT4, c2.ZTITLE2 as folderName, c2.ZIDENTIFIER as folderID,
c5.ZNAME as acc_name, c5.ZIDENTIFIER as acc_identifier, c5.ZACCOUNTTYPE,
c3.ZSUMMARY, c3.ZTITLE, c3.ZURLSTRING, c3.ZTYPEUTI 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.ZACCOUNT3
ORDER BY note_id
"""
if CommonFunctions.ColumnExists(db, 'ZICCLOUDSYNCINGOBJECT',
'ZACCOUNT4'):
query = query_2
else:
query = query_1
# ZACCOUNTTYPE - 1=iCloud, 3=local
db.row_factory = sqlite3.Row
cursor = db.execute(query)
for row in cursor:
try:
att_path = ''
if row['encrypted']:
text_content = ''
pw_hint = row['ZPASSWORDHINT']
else:
pw_hint = ''
data = GetUncompressedData(row['data'])
text_content = ProcessNoteBodyBlob(data)
if row['att_uuid'] != None:
try:
if is_ios:
base_path, _ = os.path.split(source)
att_path = base_path + '/Accounts/' + row[
'acc_identifier'] + '/Media/' + row[
'att_uuid'] + '/' + (
row['ZFILENAME'] if row['ZFILENAME']
is not None else row['att_uuid'])
elif user:
att_path = '/Users/' + user + '/Library/Group Containers/group.com.apple.notes/Media/' + row[
'att_uuid'] + '/' + (
row['ZFILENAME'] if row['ZFILENAME']
is not None else row['att_uuid'])
else:
att_path = 'Media/' + row['att_uuid'] + '/' + (
row['ZFILENAME'] if row['ZFILENAME']
is not None else row['att_uuid'])
except TypeError as ex:
log.error('Error computing att path for row ' +
str(row['note_id']) + ' Error was ' +
str(ex))
note = Note(
row['note_id'], row['folderName'], row['title'],
row['snippet'], text_content, row['att_uuid'], att_path,
row['acc_name'], row['acc_identifier'], '',
CommonFunctions.ReadMacAbsoluteTime(row['created']),
CommonFunctions.ReadMacAbsoluteTime(row['modified']),
'NoteStore', row['encrypted'], pw_hint, user, source,
row['ZTYPEUTI'], row['ZSUMMARY'], row['ZURLSTRING'],
row['ZTITLE'])
notes.append(note)
except sqlite3.Error:
log.exception('Error fetching row data')
except sqlite3.Error:
log.exception('Query execution failed. Query was: ' + query)
