search

ScottKjr3347 / iOS_Local_PL_Photos.sqlite_Queries

iOS Photos.sqlite queries that may help with decoding data stored in Photos.sqlite. These queries are based on testing, research and some community published research. These queries were written to work for the Photos.sqlite database stored at: iOS: /private/var/mobile/media/PhotoData/Photos.Sqlite Mac OS: /Users//Pictures/PhotosLibrary.photoslibrary/database/Photos.sqlite
58 stars 5 forks source link

Trash State Conflict #4

Closed rotchellsgt closed 2 weeks ago

rotchellsgt commented 2 weeks ago

When reviewing an asset within the photos.sql database the zAsset - Trashed State/LocallyAssetRecently had the value of "1 - Asset in Trash/Recently Deleted". The column zIntResou - Trash State had the value of "0 - zIntResou-Not In Trash/Recently Deleted".

I wondered if you knew what would cause this or just an explanation of the difference in columns so i know if the asset is within the recently deleted folder.

This was done on a Mac running macOS Big Sur so used the iOS14_LPL_Phsql_Basic.txt. I also managed to get the Photos.sqlite from the iPhone which was running iOS15 so used the iOS15_LPL_Phsql_Basic.txt and got the same results. This was a cloud synced asset from the iPhone to the Mac

ScottKjr3347 commented 2 weeks ago

Hello,

Based on my testing and research this is normal. I have attempted but have never been successful of getting the ZINTERNALRESOURCE Table ZTRASHEDSTATE to have a value anything other than "0" even when ZASSET table ZTRASHEDSTATE had a value of "1"

I would like to mention that you are using older queries and if possible use the parsers and queries built into iLEAPP. https://github.com/abrignoni/iLEAPP

When reviewing data from the ZINTERNALRESOURCE table I would suggest using Ph50AssetIntResouData.py This parser and embedded query will provide you the necessary data from both the zAsset and zInternalResource table to see how there are other files referenced in the internal resource table that are not referenced in the zAsset table.

If this is something you would like immediate assistance please let me know and we can schedule a time to discuss.

I have attached a screenshot illustrating that this is normal behavior.

Scott Koenig Forensic Examiner Las Vegas, NV @.*** https://theforensicscooter.com/

On Thu, Jun 20, 2024 at 6:57 AM rotchellsgt @.***> wrote:

When reviewing an asset within the photos.sql database the zAsset - Trashed State/LocallyAssetRecently had the value of "1 - Asset in Trash/Recently Deleted". The column zIntResou - Trash State had the value of "0 - zIntResou-Not In Trash/Recently Deleted".

I wondered if you knew what would cause this or just an explanation of the difference in columns so i know if the asset is within the recently deleted folder.

This was done on a Mac running macOS Big Sur so used the iOS14_LPL_Phsql_Basic.txt. I also managed to get the Photos.sqlite from the iPhone which was running iOS15 so used the iOS15_LPL_Phsql_Basic.txt and got the same results. This was a cloud synced asset from the iPhone to the Mac

— Reply to this email directly, view it on GitHub https://github.com/ScottKjr3347/iOS_Local_PL_Photos.sqlite_Queries/issues/4, or unsubscribe https://github.com/notifications/unsubscribe-auth/AIHNN2WKW7QFXCBEYAUWOHTZILNTPAVCNFSM6AAAAABJUA2T5SVHI2DSMVQWIX3LMV43ASLTON2WKOZSGM3DINJSGU2TOMQ . You are receiving this because you are subscribed to this thread.Message ID: @.***>

rotchellsgt commented 2 weeks ago

@ScottKjr3347 I know I also replied on discord, annoying I dont have access to that account at home so can't see your response. I do appreciate your quick answers on both platforms.

The only thing I was trying to solve was if the zASSET table ZTRASHEDSTATE had a value of "1" then that would mean the image was "deleted/in the recently deleted folder".

The one thing I found interesting was that the date/time within the photos.sqlite was earlier than the reported created date of the photo in the "Photos Library" when viewing this within the forensic software (X-Ways). I am new to digital forensics so this is the first time I have ever looked into this database and I don't know if this is also something you have also come across. If this is easier to speak in person I would be happy to arrange a time to speak as im interested to learn more.

ScottKjr3347 commented 2 weeks ago

“The only thing I was trying to solve was if the zASSET table ZTRASHEDSTATE had a value of "1" then that would mean the image was "deleted/in the recently deleted folder".”Sk answer: yes thats correct zAssst-zTrashedState = asset marked/tagged as recently deleted“The one thing I found interesting was that the date/time within the photos.sqlite was earlier than the reported created date of the photo in the "Photos Library" when viewing this within the forensic software (X-Ways). “Sk answer: use caution when using df tools that do not report all date from photos.sqlite. I recommend using a combination of df tools and manual analysis and verification. The displayed date in DF tool can be pulled from any number of dates listed in photos.sqlite, the file system, encoded Exif and or other metadata. What you have observed is normally and there are a number of reasons for this occurrence. Most typical is the file was imported to the analyzed device from an outside source. There are artifacts to analyze which for the most part are discussed in my published research blog.To summarize check the imported bundle ids, exif string, created date and added date. Email me directly to schedule a call. Scott @.://theforensicscooter.comPlease excuse brevity, grammar and punctuation as this email was sent from a mobile device.On Jun 20, 2024, at 1:50 PM, rotchellsgt @.> wrote:The only thing I was trying to solve was if the zASSET table ZTRASHEDSTATE had a value of "1" then that would mean the image was "deleted/in the recently deleted folder".