A Blog by Eric Arnoux –  Mobile Forensic Specialist at MSAB

In this article, I’ll demonstrate the importance of performing a Logical iOS FFS extraction as soon as possible after the phone’s seizure to limit data loss. To do this, using XAMN Pro alongside a Python script, I’ll explain how to search for/restore deleted artifacts from an SQLite database. 

Why it is so important to consider the extraction method 

At present, no commercial forensic tool can perform a true physical extraction on recent iOS devices. The most comprehensive option available is a logical Full File System (FFS) extraction carried out through Local Privilege Escalation (LPE).

Because this approach requires the device to be powered on and the operating system to be running, it differs fundamentally from a physical extraction, which can be performed without booting iOS and thus preserves the device’s original state. With logical FFS extraction, however, the OS is active during acquisition, making it difficult to determine what data may have already been automatically deleted by the system. If records in Biomes files are permanently lost after a period of 28 days, data deleted from SQLite databases can still be recovered for some time and conditions. 

Cache.sqlite database is one of the perfect candidates to illustrate this problem. 

Locations from Cache.sqlite database 

Apple uses many SQLite databases to store records used by system processes and apps. In high priority cases such as murders, it is very important to provide a timeline of where the device has been. 

 Well known to specialists in digital investigations, the file Cache.sqlite located in /private/var/mobile/Library/Caches/com.apple.routined/, stores location history generated by Apple’s routined service which collects and caches location data from GPS, Wi-Fi, and cell towers. 

What’s inside Cache.sqlite 

The Cache.sqlite database is used by iOS and Apps to determine significant locations, predict routes, and optimize system services like Siri suggestions and Maps. The ‘routined’ process helps iOS provide features like: 

• Frequent Locations / Significant Locations (where your phone learns about places you visit often, like home or work). 
• Proactive suggestions (e.g., “It will take 20 minutes to drive to work” notifications). 
• Optimizations for apps that request location data, so they don’t always need to use GPS hardware (which saves battery!). 

Forensic value 

The Cache.sqlite database is one of the most evidentiary iOS artifacts in criminal investigations, especially when combined with corroborating evidence. This file is a gold mine for digital forensics if the records are preserved properly. it contains cached location data collected by the device: 

• Precise GPS coordinates: Latitude, longitude, altitude. 
• Timestamps: Entry and exit times for visits. 
• Accuracy values: Horizontal accuracy radius in meters. 
• Speed & course: Sometimes recorded, useful for movement reconstruction. 
• Visit clustering: Groups of visits that reveal “home,” “work,” or other frequently visited places. 

This data can: 

• Place a suspect at a specific location and time. 
• Corroborate or contradict alibis. 
• Reconstruct travel patterns over days or weeks

Figure 1.0 demonstrates XAMN Pro displaying something that looks like trips when using the map view. 

Several locations on the right side of the map between Strasbourg, Mulhouse, Lyon, and Grenoble are particularly noteworthy.

Using a time filter to June 26, 2025, we are able to obtain 7,797 artifacts clearly indicating a trip between Strasbourg and Grenoble.

By using source mode as shown in Figure 2.0, I can confirm that these locations come from the Cache.sqlite database. In my case, it would have been interesting to know how and when the user arrived in Strasbourg. Looking at previous days, we can observe that this trip took place on 24/06/2025, which is consistent with the train tickets found on the device!

As shown in Figure 4.0, most of these locations come from the Local.sqlite database.

Looking at the Cache.sqlite database, we can see that the oldest records held are dated June 25th. Thus, all rows from June 24 and older have been deleted.

That’s the problem! The data in this database isn’t permanent: iOS purges it periodically.

That retention varies, often 7–30 days but can be longer depending on usage and the iOS version. In my example it was purged after 8 days!

Do we have deleted data in SQLite?

SQLite database update

If WAL (Write-Ahead-Log) journaling is enabled, recent transactions (including deletions) are first written to the WAL file. This means deleted rows may still exist in the WAL until it is merged back into the main database.

SQLite doesn’t automatically shrink or reorganize the file. Only a VACUUM command rewrites the database and permanently erase deleted content.

That’s why forensic investigators can often restore deleted rows: the data lingers invisibly until the database reuses or cleans up the space. With an SQLite viewer like DB Browser or sqlite3 python module it is not possible to restore deleted rows from the database.

You can do this by hand with a hex editor if you have the time or use XAMN Pro and XRY’s Python API.

Restore deleted rows with the XRY’s Python API

For all digital Cases, it is possible to find even more artifacts than those proposed by the decoding carried out by the tool itself.

XRY’s Python API is one way to achieve this. Keep in mind that you don’t need to be an expert in Python programming, just a little knowledge is enough, especially since for beginners, AI can be of great help.

To put it simply, it’s always the same process, the script must:

• Search and find a file (XRY Python API)
• Open it (Python or XRY Python API)
• Select and decode relevant data (Python)
• Records new artifacts in the XRY container (XRY Python API)

On the MSAB customer portal you will find the MSAB Python documentation needed to find files and integrate new artifacts into the .XRY file. This documentation can be found under XRY – Extract > Documents > Python documentation.

The first benefit is that once the script is made you can reuse and share it. The second is that it allows you to personalize or even enrich the decoding and analyze the results with XAMN Pro.

The XRY Python API includes a class for listing deleted rows from an SQLite database. This class is described in the xry.sqlite page where you will find the method to return deleted rows:

Restore deleted rows from SQLite database with a python script

This paragraph describes how to use XRY’s Python API to extract additional artifacts from the Cache.sqlite database. Note: With XAMN Pro, you need to go to the Hex viewer to create and run a Python script.

Don’t forget to start your script by importing the xry module:

Kindly review the XAMN Help documentation and consult the introductory section of the Python documentation as needed.

I only detail the main parts of the code. It is advisable to enrich it to suit your needs.

Step 1: Search and find the Cache.sqlite database

Using XRY’s Python API it is possible to search for a dedicated file in the XRY container. The MSAB Python documentation in the xry.image class, describes the module get_children():

‘parent’ should content the xry.nodeids.views category that you are looking for.

So, to search for a file with XRY’s Python API, you need to know which category to find it in.

As Cache.sqlite is a database, this file can be found with XAMN Pro in the ‘Files & Media / Database’ (databases_view).

Below is the code:

dbObject = image.get_children(xry.nodeids.views.databases_view)

The code above returns the first database object found in the database view.

To find the Cache.sqlite database, we need to create a loop to select one by one all objects found in this category. For each database object found, we will read the name property to compare it with the name of the database to search for.

According to the MSAB Python documentation, the module get_properties_of_type() retrieve properties from a node:

‘owner’ is the dbObject found with get_children() and type is the property of the node. The code below will select the file name object from the database object:

FileNameObject = image.get_properties_of_type(dbObject, xry.nodeids.views.databases_view.properties.file_name)

It’s recommended to search the file with the name and the path!

Figure 7.0 below is an example of how to use the XRY Python API to find a database:

When we have a match with the name and the path the loop is stopped. The Cache.sqlite database is recorded in the dbObject variable.

The database has been found, we can continue with the code below:

Step 2: Open the database

According to MSAB Python documentation we use the xry.sqlite class to open the database:

In the code below, XRY API opens the database from the dbObject variable:

The code above creates a cursor that allows you to iterate through rows one by one.

Step 3: Read and select data

It is at this step that we can retrieve deleted rows.The execute() method has an option to retrieve deleted rows:

According to this documentation, the code below executes a SQL query and returns the deleted rows with the default parameter:

As we are working with deleted data it is possible to remove duplicate rows using the unique values in the Z_PK column. To do this, simply save this value in a list that will be used to discard each row with the same ID. If the Z_PK value is not in the list, the column values are saved in a dictionary to be used to create a new artifact.

The advantage of using a dictionary is that we can quickly retrieve values using a unique identifier, which in my example is the column name.

Figure 8.0 below is an example of how it is possible to fill the dictionary:

Note: Most of the time, it is necessary to convert values into a human-readable format, like a timestamp!

You can create a function or use XRY’s Python API below:

Step 4: Create a new artifact

To create a new artifact in the XRY container, I strongly recommend that you systematically use a Python function.

The MSAB Python documentation describes the method create_item from the xry.image class:

‘parent’ will contain one of the categories listed in the xry.nodeids.views module. For this step, it is important to select the appropriate category to create the corresponding properties of the new artifact.

In my example, I use location_history_view:

newArtifact = image.create_item(xry.nodeids.views.location_history_view)

In this new node named newArtifact it is possible to add properties:

‘owner’ is the node: newArtifact and ‘type’ will take one of the properties listed below:

xry.nodeids.views.location_history_view.properties.address xry.nodeids.views.location_history_view.properties.address_description xry.nodeids.views.location_history_view.properties.address_extended xry.nodeids.views.location_history_view.properties.altitude xry.nodeids.views.location_history_view.properties.arrival_time xry.nodeids.views.location_history_view.properties.badoo_id xry.nodeids.views.location_history_view.properties.base_time xry.nodeids.views.location_history_view.properties.calculated_time xry.nodeids.views.location_history_view.properties.cdma_network_identifier xry.nodeids.views.location_history_view.properties.cdma_system_identifier xry.nodeids.views.location_history_view.properties.cell_tower_name xry.nodeids.views.location_history_view.properties.cid_bid xry.nodeids.views.location_history_view.properties.city xry.nodeids.views.location_history_view.properties.connection_type xry.nodeids.views.location_history_view.properties.country xry.nodeids.views.location_history_view.properties.course xry.nodeids.views.location_history_view.properties.creator_package_name xry.nodeids.views.location_history_view.properties.decoy xry.nodeids.views.location_history_view.properties.deleted xry.nodeids.views.location_history_view.properties.deleted_time xry.nodeids.views.location_history_view.properties.departure_time xry.nodeids.views.location_history_view.properties.distance_estimated xry.nodeids.views.location_history_view.properties.district xry.nodeids.views.location_history_view.properties.editor_package_name xry.nodeids.views.location_history_view.properties.elevation xry.nodeids.views.location_history_view.properties.end xry.nodeids.views.location_history_view.properties.flight_action xry.nodeids.views.location_history_view.properties.gps_available xry.nodeids.views.location_history_view.properties.gps_health xry.nodeids.views.location_history_view.properties.gps_time xry.nodeids.views.location_history_view.properties.horizontal_accuracy xry.nodeids.views.location_history_view.properties.house_nr xry.nodeids.views.location_history_view.properties.index xry.nodeids.views.location_history_view.properties.lac_tac xry.nodeids.views.location_history_view.properties.last_visit xry.nodeids.views.location_history_view.properties.latitude xry.nodeids.views.location_history_view.properties.latitude_span xry.nodeids.views.location_history_view.properties.location_map xry.nodeids.views.location_history_view.properties.location_name xry.nodeids.views.location_history_view.properties.location_type xry.nodeids.views.location_history_view.properties.longitude xry.nodeids.views.location_history_view.properties.longitude_span xry.nodeids.views.location_history_view.properties.mcc_mnc xry.nodeids.views.location_history_view.properties.momentary_current xry.nodeids.views.location_history_view.properties.navigated_by xry.nodeids.views.location_history_view.properties.number_of_satellites xry.nodeids.views.location_history_view.properties.original_latitude xry.nodeids.views.location_history_view.properties.original_longitude xry.nodeids.views.location_history_view.properties.po_box xry.nodeids.views.location_history_view.properties.postal_code xry.nodeids.views.location_history_view.properties.region xry.nodeids.views.location_history_view.properties.related_application xry.nodeids.views.location_history_view.properties.related_device xry.nodeids.views.location_history_view.properties.related_device_user xry.nodeids.views.location_history_view.properties.related_location xry.nodeids.views.location_history_view.properties.remote_control_connected xry.nodeids.views.location_history_view.properties.source_application xry.nodeids.views.location_history_view.properties.speed xry.nodeids.views.location_history_view.properties.speed_average xry.nodeids.views.location_history_view.properties.speed_top xry.nodeids.views.location_history_view.properties.start xry.nodeids.views.location_history_view.properties.state xry.nodeids.views.location_history_view.properties.step_count xry.nodeids.views.location_history_view.properties.street xry.nodeids.views.location_history_view.properties.textual_time xry.nodeids.views.location_history_view.properties.third_party_source_file xry.nodeids.views.location_history_view.properties.time xry.nodeids.views.location_history_view.properties.uid xry.nodeids.views.location_history_view.properties.vertical_accuracy xry.nodeids.views.location_history_view.properties.visit_count

 

It is up to you to select from the list above which property you want to use for the new record. To finish the function, we use set_value to set the value of the property from the dictionary.

The function used to record a new artifact in the XRY file, can look like this:

As you can see at the end of this code above, it is possible to link this new artifact with the database Cache.sqlite with relate_nodes method describe below:

For each valid row of the cursor, a new artifact will be created using the function _newArtifact():

Now finish the script with the following lines:

Details of a created artifact

Each new artifact created with the python script described above will be presented in the Details view:

It is possible to improve this script by adding additional tables from the database and optimizing the decoding with deleted rows only for example.

Restore locations removed from June 24

In my example, records prior to June 25 were deleted from the Cache.sqlite database.

As you can see in the log below, my script retrieved 4,266 unique artifacts from deleted rows from the Cache.sqlite database.

It is now possible to view these new artifacts with XAMN Pro and find 4 069 locations dated June 24:

With this new data, I have all the details of the trip made on June 24 between Grenoble and Strasbourg.

Databases are an integral part of operating systems and mobile applications, but they are rarely accessible without advanced extraction.

This example highlights the importance of prioritizing physical extraction over logical LPE methods. When physical extraction is not possible, logical extraction must be performed as quickly as possible to minimize data loss, which can sometimes be critical.