IT Forensics · macOS

Manually validate SQLite, WAL and SHM artefacts

Many macOS applications – Messages, Safari, Photos, Notes – store their data in SQLite databases. Alongside the main file, there are often a „-wal“ and a „-shm“ file. The WAL file may contain recent changes and older versions of database pages that are not yet, or are no longer, present in the main file.

Enquire without obligation

This point is particularly important for Apple computers, as their hardware and security architecture have changed significantly in recent years. An older Intel-based iMac, an Intel-based Mac with a T2 Security Chip and a current Mac Studio with Apple Silicon cannot be treated according to the same technical approach. APFS, Secure Enclave, FileVault and the respective system state determine which data is accessible and which backup method causes the least disruption.

Automatic parsers may handle WAL contents in different ways. For this reason, relevant data records are checked directly against database pages, the transaction context and supporting files where necessary.

Technical background

In write-ahead logging, changes are first written to the WAL and later transferred to the main file via a checkpoint. Anyone who opens the database using standard tools can trigger a checkpoint and modify the WAL. Different parsers handle WAL frames differently; as a result, records may be missing, appear twice or be assigned to the wrong point in time.

Relevant evidence and data sources

  • Main database, -wal and -shm backed up as a single unit
  • WAL frames with salt values and commit markers
  • Freelist pages and unallocated space (deleted records)
  • Table schema and timestamp formats (e.g. Mac Absolute Time)

Why LanCologne?

LanCologne does not examine Apple computers according to a standardised, one-size-fits-all approach, but rather on the basis of their specific hardware and security architecture. Our staff have decades of experience in information technology and many years of practical experience in IT forensics.

Here’s how we do it

  • Back up the files as a group; perform the analysis on copies only.
  • Compare the parsing result with the page and frame levels.
  • Documentation specifying the source (main file, WAL frame, free space) from which a data record originates.

Borders

  • Once the checkpoint has been processed and the data overwritten, older versions are no longer available.
  • It is not always possible to assign a WAL frame to a precise point in time.

Possible results

  • Validated data sets with source attribution
  • Indications of deleted or amended entries

Typical areas of application

•Judicial and non-judicial expert reports
•Preservation of evidence from iMac, Mac mini, Mac Studio, MacBook and Mac Pro
•Analysis of Apple Silicon, T2 and older Intel systems
•Incident Response and Malware Investigations
•Analysis of APFS, user, application and system artefacts
•Reconstruction of file, user and network activity
•Cross-checking the results of automatic parsers
•Analysis of deleted, historical or only indirectly visible traces
•Documentation for courts, solicitors, businesses and public authorities

Why is this area of investigation relevant to forensics?

Many macOS applications store information in SQLite. A thorough investigation takes into account not only the main database but also, depending on its state, the WAL and SHM files, and distinguishes between active records and reconstructed fragments.

The forensic value lies not solely in the volume of data found, but in its origin and reliability. A file name, a timestamp or a database entry can be misleading without context. That is why we document how an artefact may have come into being, what alternative explanations exist and what further evidence supports the findings.

Automatic parsers may handle WAL contents in different ways. For this reason, relevant data records are checked directly against database pages, the transaction context and supporting files where necessary.

On modern Macs in particular, attempts at a „traditional“ disk analysis may also fail due to technical limitations. Hardware-based encryption, Secure Enclave, FileVault and APFS clearly illustrate why it is so important to carry out a proper backup before the actual analysis begins. Errors made at this stage cannot always be reversed later on.

Frequently Asked Questions

What forensic considerations should be borne in mind when „manually validating SQLite, WAL and SHM artefacts“?
Our investigation does not begin with the launch of an analysis programme, but with the collection of evidence. The device, model, serial number or inventory reference, connection status, visible system status and existing peripherals are documented. A decision is then made as to whether the system must remain switched off, whether a live capture is advisable, or whether a recovery, target disk or share disk procedure is technically justifiable. The next step is to define the backup strategy. In doing so, we distinguish between block-level imaging, logical backups and targeted triage. The choice does not depend on convenience, but on the hardware generation, FileVault, APFS structure, whether the device is unlocked, and the specific evidential question at hand. Where technically possible, work is carried out in read-only mode. Any unavoidable changes to the system’s state during live or recovery procedures are explicitly logged. The backup created is documented using hash values. The actual analysis is not carried out on the original, but on a working copy or a verified forensic image. This allows parsers, search operations or our own utility programmes to be used without continuously altering the original evidence. In complex cases, we deliberately avoid relying on a single piece of software. RECON LAB, RECON ITR, Belkasoft X and X-Ways provide different perspectives on the same body of evidence. If a relevant artefact is not fully covered by standard parsers, we supplement the investigation with custom-developed utilities and scripts. The crucial factor is always the link back to the raw data: a parser developed in-house is only forensically valid if its results are reproducible and technically verifiable. For readers unfamiliar with the subject, one point is particularly important: a forensic programme does not automatically „find“ the truth. It reads data structures and interprets them according to known rules. If Apple changes a database or a metadata format with a new version of macOS, a parser may respond incompletely or incorrectly. That is why, when dealing with crucial findings, we check which file, database or file system structure the result originates from, and whether a second technical approach confirms the same findings. This is also why we use multiple tools. RECON LAB can analyse Apple-specific structures natively on macOS; Belkasoft X offers a different view of artefacts and correlations; X-Ways enables highly detailed file system and raw data analysis. If independent analyses agree, this increases the robustness of the findings. If they do not agree, the cause is investigated rather than simply selecting the most convenient result.
How does such a forensic investigation work in practice?
1. Collection of evidence and documentation of the device’s condition The Apple device is first uniquely identified and documented either photographically or in writing. We note whether it is switched on or off, logged in, locked or connected to external storage devices. This information may prove crucial later on. 2 Technical assessment The chip generation, macOS version, APFS structure, FileVault and relevant security mechanisms are determined. Only then can the appropriate acquisition method be determined. 3 Forensically sound backup The backup method is selected to ensure that as little as possible is altered on the original device. Depending on the case, RECON ITR can be used for imaging or triage of modern Macs. Other situations require Apple Recovery, Target Disk Mode for Intel-based systems, or Share Disk for Apple Silicon-based systems. Every step is logged. 4 Integrity check: Images or logical backups created are uniquely named, assigned hash values and verified. Analysis and searches are then carried out on working copies. 5 Multi-stage analysis RECON LAB, Belkasoft X and X-Ways are combined depending on the specific requirements. Where possible, a key finding is not based on a single parser view alone. 6 Manual Validation If an artefact is particularly relevant to the evidence, unusual, or only partially supported by standard software, we manually examine raw data, database structures, metadata or file system information. Where necessary, we use our own tools. 7 Report and evidence reference Ultimately, we do not merely list what a tool has displayed. We explain which technical source underpins the finding, what conclusion is permissible and where the limitations lie.
What is the forensic significance of the findings in this area?
Many macOS applications store information in SQLite. A robust investigation takes into account not only the main database but also, depending on its condition, WAL and SHM files, and distinguishes between active records and reconstructed fragments. The forensic value lies not solely in the volume of data found, but in its origin and reliability. A filename, a timestamp or a database entry can be misleading without context. That is why we document how an artefact may have come about, what alternative explanations exist and what further evidence supports the findings. Automatic parsers may handle WAL contents differently. Therefore, where necessary, relevant data records are checked directly against database pages, the transaction context and associated files. On modern Macs in particular, attempts at „traditional“ disk analysis may also fail due to technical limitations. Hardware-based encryption, Secure Enclave, FileVault and APFS highlight why a professional backup is so important prior to the actual analysis. Errors made at this stage cannot always be reversed later on.
Are assumptions presented as confirmed findings in such an investigation?
No. The results of technical investigations are presented only to the extent that they are supported by the data actually available. Assumptions that cannot be substantiated are not presented as confirmed findings.
Why can’t you just open the database?
Because opening it may trigger a checkpoint and alter the WAL file.
Can you find deleted messages in the WAL?
Sometimes, when older versions of the page have not yet been overwritten.
Does that only apply to macOS?
No, iOS, Android and Windows applications also use SQLite with WAL.

Related topics

LanCologne – macOS Forensics in Cologne

Do you require a professional investigation into „manually validating SQLite, WAL and SHM artefacts“? LanCologne supports you in carrying out evidence-secure backups, multi-stage analysis and traceable documentation. The key here is not to generate as many automated hits as possible, but to secure technically sound and verifiable evidence.

Get in touch now