Photos.sqlite: How to Detect an Adjusted Photo Date or Location on iOS

Research and testing by Michael Edwards, Digital Forensics Examiner, Richland County Sheriff's Department
Test device: iPhone 16 Pro, iOS 18.3

Photos.sqlite is where most examiners go for a photo's capture time and location on iOS. The problem: any user can change both in the Photos app with a few taps. If you read the wrong column in Photos.sqlite, you report the user's edit as fact.

We tested what those edits change, what they leave alone and how you can spot them. The short version: the original values survive, and the edit leaves a mismatch you can query for.

The feature being tested

In the Photos app, swipe up on a photo or tap the info button. You get an "Adjust" link next to the date and another next to the map. One changes the capture date and time. The other changes or removes the location.

Both menus include a "Revert" button. Photos can only revert if it still has the original values. That was the starting clue: the originals had to be stored somewhere on the device.

Test setup

Four photos were taken with the native Camera app at the same spot, then edited in Photos:

These screenshots show what the user sees after the edits. Nothing on screen tells you a value was changed.

Where Photos.sqlite stores time and location

The same data lives in three tables. That duplication is what makes detection possible.

ZADDEDDATE is when the photo joined the camera roll. The other three time columns relate to when the photo was created. For a photo taken on the device itself, all four agree.

Results

IMG_0139: the control

Every source agrees. This is what an untouched photo looks like.

IMG_0140: location removed

ZASSET lost the coordinates and the accuracy value flipped to -1.0. ZEXTENDEDATTRIBUTES and the EXIF data kept the original position.

The original horizontal accuracy (122.73 m) now exists only in the EXIF data. Photos.sqlite overwrote it. If you need the accuracy radius for a photo with a removed location, you have to pull it from the file.

IMG_0141: location changed

ZASSET now points to 5623 Two Notch Rd, about 3 km from where the photo was taken. The accuracy value changed to 1.0. ZEXTENDEDATTRIBUTES and EXIF still hold the real location.

ZA.ZADDEDDATE for this photo read 12:49:25, one second after the create time. That's normal rounding and not related to the edit.

One more detail. The ZASSET coordinates, 34.053193, -80.9806143, are the exact values Apple Maps stored for the same address in our MapsSync_0.0.1 test. That suggests Photos writes Apple's geocoded point for the address. A real GPS fix carries long, noisy decimals and a varying accuracy value. Short, clean coordinates with an accuracy of exactly 1.0 look like an address lookup, not a sensor reading. That's our inference from one photo, so test it before you lean on it.

IMG_0142: time changed

Only one column moved. ZASSET.ZDATECREATED took the user's new time. Every other time source kept the original.

Location data for this photo was untouched and matched across all sources (34.058689, -80.948647, accuracy 35.98 m).

Note the adjusted value has zero seconds. The time picker in Photos sets hours and minutes only. A ZASSET.ZDATECREATED that ends in :00 while the other columns don't is one more sign of a manual edit.

Summary: what each edit changes

How to detect an adjustment in your case

1. Run the query below. It puts the ZASSET values beside the ZEXTENDEDATTRIBUTES and ZADDITIONALASSETATTRIBUTES values for every asset.
2. Compare ZA.ZLATITUDE and ZA.ZLONGITUDE against the ZEXTA pair. A mismatch means the location was adjusted. -180.0 in ZASSET with real coordinates in ZEXTA means the user removed it.
3. Compare ZA.ZDATECREATED against ZEXTA.ZDATECREATED and ZAAA.ZEXIFTIMESTAMPSTRING. A mismatch means the time was adjusted.
4. Check ZAAA.ZGPSHORIZONTALACCURACY for -1.0 or 1.0.
5. Export the file and run exiftool. Confirm the original time, coordinates and horizontal positioning error from the EXIF data.
6. Report both values: what the user set and what the device recorded. The edit itself is evidence.

The Photos app shows the ZASSET values, and that's the table the user can change. Check which columns your forensic tool parses before you trust its capture time or location. When photo time or place matters to the case, go to the database.

The query

SELECT
          ZASSET.ZFILENAME AS 'ZA.ZFILENAME',
          ZASSET.ZDIRECTORY AS 'ZA.ZDIRECTORY',
          datetime('2001-01-01', ZASSET.ZADDEDDATE || ' seconds', 'localtime') AS 'ZA.ZADDEDDATE',
          datetime('2001-01-01', ZASSET.ZDATECREATED || ' seconds', 'localtime') AS 'ZA.ZDATECREATED',
          ZADDITIONALASSETATTRIBUTES.ZEXIFTIMESTAMPSTRING AS 'ZAAA.ZEXIFTIMESTAMPSTRING',
          datetime('2001-01-01', ZEXTENDEDATTRIBUTES.ZDATECREATED || ' seconds', 'localtime')
              AS 'ZEXTA.ZDATECREATED',
          ZEXTENDEDATTRIBUTES.ZTIMEZONENAME AS 'ZEXTA.ZTIMEZONENAME',
          ZADDITIONALASSETATTRIBUTES.ZTIMEZONENAME AS 'ZAAA.ZTIMEZONENAME',
          ZASSET.ZLATITUDE AS 'ZA.ZLATITUDE',
          ZASSET.ZLONGITUDE AS 'ZA.ZLONGITUDE',
          ZEXTENDEDATTRIBUTES.ZLATITUDE AS 'ZEXTA.ZLATITUDE',
          ZEXTENDEDATTRIBUTES.ZLONGITUDE AS 'ZEXTA.ZLONGITUDE',
          ZADDITIONALASSETATTRIBUTES.ZGPSHORIZONTALACCURACY AS 'ZAAA.ZGPSHORIZONTALACCURACY'
      FROM ZASSET
      LEFT JOIN ZEXTENDEDATTRIBUTES
          ON ZASSET.ZEXTENDEDATTRIBUTES = ZEXTENDEDATTRIBUTES.Z_PK
      LEFT JOIN ZADDITIONALASSETATTRIBUTES
          ON ZASSET.ZADDITIONALATTRIBUTES = ZADDITIONALASSETATTRIBUTES.Z_PK
      ORDER BY ZASSET.ZADDEDDATE ASC

Bottom line

A user can change what the Photos app shows for a photo's time and place. In this test they could not change what the device recorded. The original capture time, coordinates and horizontal accuracy survived every edit, in ZEXTENDEDATTRIBUTES, ZADDITIONALASSETATTRIBUTES and the file's EXIF data. Compare those against ZASSET in Photos.sqlite and the edit shows itself.

Testing and original report by Michael Edwards, Digital Forensics Examiner, Richland County Sheriff's Department. DMS to decimal conversions used the FCC conversion tool.