Difference between pages "Mac OS X" and "SQLite database format"

From ForensicsWiki
(Difference between pages)
Jump to: navigation, search
m (EFI boot)
 
(Use Cases)
 
Line 1: Line 1:
{{Expand}}
+
{{expand}}
  
Apple Inc.'s Macintosh OS X (pronounced "'''OS Ten'''") is the operating system distributed with Apple computers. It includes heavily used several programs by default, including [[Apple Mail]], a web browser called [[Apple Safari | Safari]], and an [[Apple Address Book]], and [[iCal]].  
+
SQLite databases are used by many programs including several forensics tools, e.g. [[Autopsy]] 3.
 +
SQLite 3 is current and older SQLite packages cannot use sqlite3 databases so use sqlite3 tools.
  
== EFI boot ==
+
== SQLite3 ==
The firmware is responsible for initializing the hardware and performing a POST (Power-On Self Test).
+
  
The default boot volume is stored in NVRAM and can be configured through the "Startup Disk" preference pane or the nvram command line utility [https://developer.apple.com/library/mac/documentation/Darwin/Reference/Manpages/man8/nvram.8.html]. E.g. to print all of the firmware variables.
+
SQLite version 3 uses a page-based storage where the pages are used for various types of data e.g. there are:
<pre>
+
* lock-byte pages
nvram -p
+
* freelist pages
</pre>
+
** freelist trunk pages
 +
** freelist leaf pages
 +
* B-tree pages
 +
** table B-tree interior pages
 +
** table B-tree leaf pages
 +
** index B-tree interior pages
 +
** index B-tree leaf pages
 +
* payload overflow pages
 +
* pointer map pages
  
Additional boot arguments can be provided via the "boot-args" value [http://www.cnet.com/news/boot-argument-options-in-os-x/].
+
=== Write-Ahead Log (WAL) ===
 +
The default method by which SQLite implements atomic commit and rollback is a rollback journal. In version 3.7.0 a "Write-Ahead Log" option was added.
  
Mac OS X extends EFI with a read-only HFS+ driver. According to [http://refit.sourceforge.net/info/boot_process.html] HFS+ volume header fields are used to point to a "blessed file" to be loaded as an EFI application. Though it is not clear which header field the source is referring to but likely related to the "bless" utility [https://developer.apple.com/library/mac/documentation/Darwin/Reference/Manpages/man8/bless.8.html].
+
== Temporary sqlite files ==
 
+
Seen in e.g.
The firmware starts the Mac OS X boot loader (boot.efi). The bootloader displays a dark grey Apple logo on the screen and loads the Darwin kernel from disk, as well as the essential driver extensions.
+
 
+
The bootloader can be eithe a MZ-PE/COFF or EFI fat binary type [[Executable|executables]] and is commonly stored in:
+
 
<pre>
 
<pre>
/com.apple.recovery.boot/boot.efi
+
/Users/%USERNAME%/AppData/Local/Temp/etilqs_%RANDOM%
/System/Library/CoreServices/boot.efi
+
/usr/standalone/i386/boot.efi
+
 
</pre>
 
</pre>
  
The behavior of the bootloader can be configured in the com.apple.Boot.plist [https://developer.apple.com/library/mac/documentation/Darwin/Reference/ManPages/man5/com.apple.Boot.plist.5.html] which can be found in:
+
Where "etilqs" is "sqlite" in reverse
<pre>
+
/Library/Preferences/SystemConfiguration/
+
</pre>
+
  
== Disk image types ==
+
== Use Cases ==
 +
=== Web Browser Data ===
 +
[[Mozilla Firefox]] and [[Google Chrome]] both use SQLite version 3 databases for user data such as history, downloaded files.
  
Mac OS X has support for various disk image types build-in, some of which are:
+
=== Mobile OS ===
* read-write disk image (.dmg) some of which use the [[Raw Image Format]]
+
[[Google Android]] and [[Apple iOS]] use SQLite3 databases for many system applications. Phone data including calls, messages, and credentials are all stored in SQLite3.
* [[Sparse Image format|Sparse disk image (.spareimage)]]
+
* [[Sparse Bundle Image format|Sparse bundle disk image (.sparsebundle)]]
+
 
+
== Burn Folder ==
+
 
+
Mac OS X Burn Folder:
+
<pre>
+
$NAME.fpbf
+
</pre>
+
 
+
This folder normally contains [[Mac OS X Alias Files|alias files]] (similar to LNK files under Windows). Which should have the following signature.
+
<pre>
+
00000000  62 6f 6f 6b 00 00 00 00  6d 61 72 6b 00 00 00 00  |book....mark....|
+
</pre>
+
 
+
These [[Mac OS X Alias Files|alias files]] contain additional date and time values.
+
 
+
Also check the following files for references to deleted .fpbf paths:
+
<pre>
+
/Users/$USERNAME/Library/Preferences/com.apple.finder.plist
+
/Users/$USERNAME/Library/Preferences/com.apple.sidebarlists.plist
+
</pre>
+
 
+
Actual burning of optical media is logged in:
+
<pre>
+
/var/log/system.log
+
/Users/$USERNAME/Library/Logs/DiscRecording.log
+
/private/var/.logs_exporter/cache/Users/$USERNAME/Library/Logs/DiscRecording.log
+
</pre>
+
 
+
== HFS/HFS+ date and time values ==
+
 
+
In HFS+ date and time values are stored in an unsigned 32-bit integer containing the number of seconds since January 1, 1904 at 00:00:00 (midnight) UTC (GMT). This is slightly different from HFS where the date and time value are stored using the local time. The maximum representable date is February 6, 2040 at 06:28:15 UTC (GMT). The date values do not account for leap seconds. They do include a leap day in every year that is evenly divisible by four. This is sufficient given that the range of representable dates does not contain 1900 or 2100, neither of which have leap days. Also see: [http://web.archive.org/web/20090214212148/http://developer.apple.com/technotes/tn/tn1150.html Technical Note TN1150 - HFS Plus Volume Format]
+
 
+
Converting HFS/HFS+ date and time values with Python:
+
<pre>
+
import datetime
+
 
+
print datetime.datetime( 1904, 1, 1 ) + datetime.timedelta( seconds=0xCBDAF25B )
+
</pre>
+
 
+
== Launch Agents ==
+
System-wide:
+
<pre>
+
/Library/LaunchAgents
+
/System/Library/LaunchAgents
+
</pre>
+
 
+
Per user:
+
<pre>
+
/Users/$USERNAME/Library/LaunchAgents
+
</pre>
+
 
+
These directories contain  [[Property list (plist)]] files.
+
 
+
== Launch Daemons ==
+
System-wide:
+
<pre>
+
/Library/LaunchDaemons
+
/System/Library/LaunchDaemons
+
</pre>
+
 
+
These directories contain [[Property list (plist)]] files.
+
 
+
== Startup Items ==
+
<pre>
+
/Library/StartupItems/
+
/System/Library/StartupItems/
+
</pre>
+
 
+
== Crash Reporter ==
+
<pre>
+
/Library/Application Support/CrashReporter
+
</pre>
+
 
+
Contains text files named .crash, .diag, .spin
+
 
+
== Diagnostic Reports ==
+
<pre>
+
/Library/Logs/DiagnosticReports
+
</pre>
+
 
+
== Internet Plug-Ins ==
+
System-wide:
+
<pre>
+
/Library/Internet Plug-Ins
+
</pre>
+
 
+
Per user:
+
<pre>
+
/Users/$USERNAME/Library/Internet Plug-Ins
+
</pre>
+
 
+
== Quarantine event database ==
+
See [http://menial.co.uk/blog/2011/06/16/mac-quarantine-event-database/]
+
 
+
Snow Leopard and earlier
+
<pre>
+
/Users/$USER/Library/Preferences/com.apple.LaunchServices.QuarantineEvents
+
</pre>
+
 
+
<pre>
+
SELECT datetime(LSQuarantineTimeStamp + 978307200, "unixepoch") as LSQuarantineTimeStamp, LSQuarantineAgentName, LSQuarantineOriginURLString, LSQuarantineDataURLString from LSQuarantineEvent;
+
</pre>
+
 
+
Lion and later
+
<pre>
+
/Users/$USER/Library/Preferences/com.apple.LaunchServices.QuarantineEventsV2
+
</pre>
+
 
+
== sleepimage ==
+
This file is similar to the hibernation file on Windows.
+
<pre>
+
/private/var/vm/sleepimage
+
</pre>
+
 
+
Also see: [http://osxdaily.com/2010/10/11/sleepimage-mac/]
+
 
+
== Last shutdown logs ==
+
<pre>
+
/private/var/log/com.apple.launchd/launchd-shutdown.system.log
+
/private/var/log/com.apple.launchd/launchd-shutdown.system.log.1
+
</pre>
+
 
+
== Package Files (.PKG) ==
+
Package Files (.PKG) are XAR archives [http://en.wikipedia.org/wiki/Xar_(archiver)] that contain a cpio archive and metadata [http://s.sudre.free.fr/Stuff/Ivanhoe/FLAT.html].
+
 
+
== Also see ==
+
* [[MacOS Process Monitoring]]
+
* [[Acquiring a MacOS System with Target Disk Mode]]
+
* [[Converting Binary Plists]]
+
* [[FileVault Disk Encryption]]
+
* [[File Vault]]
+
 
+
=== Formats ===
+
* [[Basic Security Module (BSM) file format]]
+
* [[Property list (plist)]]
+
  
 
== External Links ==
 
== External Links ==
* [http://www.apple.com/macosx/ Official website]
+
* [http://sqlite.org/fileformat2.html The SQLite Database File Format], by the [[SQLite|SQLite project]]
* [http://en.wikipedia.org/wiki/OS_X Wikipedia entry on OS X]
+
* [http://sqlite.org/wal.html Write-Ahead Logging], by the [[SQLite|SQLite project]]
* [http://menial.co.uk/blog/2011/06/16/mac-quarantine-event-database/ Quarantine event database]
+
* [http://forensicsfromthesausagefactory.blogspot.com/2011/04/carving-sqlite-databases-from.html Carving SQLite databases from unallocated clusters], by Richard Drinkwater, April 27, 2011
* [http://www2.tech.purdue.edu/cit/Courses/cit556/readings/MacForensicsCraiger.pdf Mac Forensics: Mac OS X and the HFS+ File System] by P. Craiger
+
* [http://linuxsleuthing.blogspot.ch/2013/09/recovering-data-from-deleted-sqlite.html Recovering Data from Deleted SQLite Records: Redux], by [[John Lehr]], September 13, 2013
* [http://web.me.com/driley/iWeb/Previous_files/Directory_Services_Overview.pdf Mac OS X Directory Services Integration including Active Directory]
+
* [http://digitalinvestigation.wordpress.com/2012/04/04/geek-post-nskeyedarchiver-files-what-are-they-and-how-can-i-use-them/ NSKeyedArchiver files – what are they, and how can I use them?]
+
* [http://krypted.com/mac-os-x/command-line-alf-on-mac-os-x/ Command Line ALF on Mac OS X]
+
* [http://newosxbook.com/DMG.html Demystifying the DMG File Format]
+
* [https://code.google.com/p/mac-security-tips/wiki/ALL_THE_TIPS mac-security-tips]
+
 
+
=== Apple Examiner ===
+
* [http://www.appleexaminer.com/ The Apple Examiner]
+
* [http://www.appleexaminer.com/MacsAndOS/Analysis/USBOSX/USBOSX.html USB Entries on OS X]
+
* [http://www.appleexaminer.com/Downloads/MacForensics.pdf Macintosh Forensics - A Guide for the Forensically Sound Examination of a Macintosh Computer] by Ryan R. Kubasiak
+
 
+
=== EFI ===
+
* [http://refit.sourceforge.net/info/boot_process.html The Intel Mac boot process], by the [[rEFIt|rEFIt project]]
+
* [http://ho.ax/posts/2012/02/carving-up-efi-fat-binaries/ Carving up EFI fat binaries], by snare, February 24, 2012
+
  
=== iCloud ===
+
== Tools ==
* [http://support.apple.com/kb/HT4865?viewlocale=en_US&locale=en_US iCloud: iCloud security and privacy overview]
+
* [[SQLite]]
 +
* [[SQLite Forensic Reporter]]
  
[[Category:Mac OS X]]
+
[[Category:File Formats]]
[[Category:Operating systems]]
+

Revision as of 11:15, 26 June 2014

Information icon.png

Please help to improve this article by expanding it.
Further information might be found on the discussion page.

SQLite databases are used by many programs including several forensics tools, e.g. Autopsy 3. SQLite 3 is current and older SQLite packages cannot use sqlite3 databases so use sqlite3 tools.

SQLite3

SQLite version 3 uses a page-based storage where the pages are used for various types of data e.g. there are:

  • lock-byte pages
  • freelist pages
    • freelist trunk pages
    • freelist leaf pages
  • B-tree pages
    • table B-tree interior pages
    • table B-tree leaf pages
    • index B-tree interior pages
    • index B-tree leaf pages
  • payload overflow pages
  • pointer map pages

Write-Ahead Log (WAL)

The default method by which SQLite implements atomic commit and rollback is a rollback journal. In version 3.7.0 a "Write-Ahead Log" option was added.

Temporary sqlite files

Seen in e.g.

/Users/%USERNAME%/AppData/Local/Temp/etilqs_%RANDOM%

Where "etilqs" is "sqlite" in reverse

Use Cases

Web Browser Data

Mozilla Firefox and Google Chrome both use SQLite version 3 databases for user data such as history, downloaded files.

Mobile OS

Google Android and Apple iOS use SQLite3 databases for many system applications. Phone data including calls, messages, and credentials are all stored in SQLite3.

External Links

Tools