def ReadNotes(db, notes, source, user, is_ios):
'''Read Notestore.sqlite'''
if IsHighSierraOrAboveDb(db):
ReadNotesHighSierraAndAbove(db, notes, source, user, is_ios)
return
if CommonFunctions.ColumnExists(db, 'ZICCLOUDSYNCINGOBJECT',
'ZISPASSWORDPROTECTED'):
enc_possible = True
else:
enc_possible = False
query1 = " SELECT n.Z_12FOLDERS as folder_id , n.Z_9NOTES as note_id, d.ZDATA as data, " + ("c1.ZISPASSWORDPROTECTED as encrypted, c1.ZPASSWORDHINT, " if enc_possible else "") + \
" c2.ZTITLE2 as folder, c2.ZDATEFORLASTTITLEMODIFICATION as folder_title_modified, " \
" c1.ZCREATIONDATE as created, c1.ZMODIFICATIONDATE1 as modified, c1.ZSNIPPET as snippet, c1.ZTITLE1 as title, c1.ZACCOUNT2 as acc_id, " \
" c5.ZACCOUNTTYPE as acc_type, c5.ZIDENTIFIER as acc_identifier, c5.ZNAME as acc_name, " \
" c3.ZMEDIA as media_id, c3.ZFILESIZE as att_filesize, c3.ZMODIFICATIONDATE as att_modified, c3.ZPREVIEWUPDATEDATE as att_previewed, c3.ZTITLE as att_title, c3.ZTYPEUTI, c3.ZIDENTIFIER as att_uuid, " \
" c4.ZFILENAME, c4.ZIDENTIFIER as media_uuid " \
" FROM Z_12NOTES as n " \
" LEFT JOIN ZICNOTEDATA as d ON d.ZNOTE = n.Z_9NOTES " \
" LEFT JOIN ZICCLOUDSYNCINGOBJECT as c1 ON c1.Z_PK = n.Z_9NOTES " \
" LEFT JOIN ZICCLOUDSYNCINGOBJECT as c2 ON c2.Z_PK = n.Z_12FOLDERS " \
" LEFT JOIN ZICCLOUDSYNCINGOBJECT as c3 ON c3.ZNOTE = n.Z_9NOTES " \
" LEFT JOIN ZICCLOUDSYNCINGOBJECT as c4 ON c3.ZMEDIA = c4.Z_PK " \
" LEFT JOIN ZICCLOUDSYNCINGOBJECT as c5 ON c5.Z_PK = c1.ZACCOUNT2 " \
" ORDER BY note_id "
query2 = " SELECT n.Z_11FOLDERS as folder_id , n.Z_8NOTES as note_id, d.ZDATA as data, " + ("c1.ZISPASSWORDPROTECTED as encrypted, c1.ZPASSWORDHINT, " if enc_possible else "") + \
" c2.ZTITLE2 as folder, c2.ZDATEFORLASTTITLEMODIFICATION as folder_title_modified, " \
" c1.ZCREATIONDATE as created, c1.ZMODIFICATIONDATE1 as modified, c1.ZSNIPPET as snippet, c1.ZTITLE1 as title, c1.ZACCOUNT2 as acc_id, " \
" c5.ZACCOUNTTYPE as acc_type, c5.ZIDENTIFIER as acc_identifier, c5.ZNAME as acc_name, " \
" c3.ZMEDIA as media_id, c3.ZFILESIZE as att_filesize, c3.ZMODIFICATIONDATE as att_modified, c3.ZPREVIEWUPDATEDATE as att_previewed, c3.ZTITLE as att_title, c3.ZTYPEUTI, c3.ZIDENTIFIER as att_uuid, " \
" c4.ZFILENAME, c4.ZIDENTIFIER as media_uuid " \
" FROM Z_11NOTES as n " \
" LEFT JOIN ZICNOTEDATA as d ON d.ZNOTE = n.Z_8NOTES " \
" LEFT JOIN ZICCLOUDSYNCINGOBJECT as c1 ON c1.Z_PK = n.Z_8NOTES " \
" LEFT JOIN ZICCLOUDSYNCINGOBJECT as c2 ON c2.Z_PK = n.Z_11FOLDERS " \
" LEFT JOIN ZICCLOUDSYNCINGOBJECT as c3 ON c3.ZNOTE = n.Z_8NOTES " \
" LEFT JOIN ZICCLOUDSYNCINGOBJECT as c4 ON c3.ZMEDIA = c4.Z_PK " \
" LEFT JOIN ZICCLOUDSYNCINGOBJECT as c5 ON c5.Z_PK = c1.ZACCOUNT2 " \
" ORDER BY note_id "
cursor, error1 = ExecuteQuery(db, query1)
if cursor:
ReadQueryResults(cursor, notes, enc_possible, user, source)
else: # Try query2
cursor, error2 = ExecuteQuery(db, query2)
if cursor:
ReadQueryResults(cursor, notes, enc_possible, user, source)
else:
log.error(
'Query execution failed.\n Query 1 error: {}\n Query 2 error: {}'
.format(error1, error2))
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)
