Difference between pages "Bulk extractor" and "SQLite database format"

From ForensicsWiki
(Difference between pages)
Jump to: navigation, search
m
 
(Use Cases)
 
Line 1: Line 1:
== Overview ==
+
{{expand}}
'''bulk_extractor''' is a C++ program that scans a disk image, a file, or a directory of files and extracts useful information without parsing the file system or file system structures. The results are stored in [[feature files]] that can be easily inspected, parsed, or processed with automated tools. '''bulk_extractor''' also created a histograms of features that it finds, as features that are more common tend to be more important.
+
  
bulk_extractor is distinguished from other forensic tools by its speed and thoroughness. Because it ignores file system structure, bulk_extractor can process different parts of the disk in parallel. In practice, the program splits the disk up into 16MiByte pages and processes one page on each available core. This means that 24-core machines process a disk roughly 24 times faster than a 1-core machine. bulk_extractor is also thorough. That’s because bulk_extractor automatically detects, decompresses, and recursively re-processes compressed data that is compressed with a variety of algorithms. Our testing has shown that there is a significant amount of compressed data in the unallocated regions of file systems that is missed by most forensic tools that are commonly in use today.
+
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.
  
Another advantage of ignoring file systems is that bulk_extractor can be used to process any digital media. We have used the program to process hard drives, SSDs, optical media, camera cards, cell phones, network packet dumps, and other kinds of digital information.
+
== SQLite3 ==
  
==Output Feature Files==
+
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
  
bulk_extractor now creates an output directory that has the following layout:
+
=== Write-Ahead Log (WAL) ===
;alerts.txt
+
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.
:Processing errors.
+
;ccn.txt
+
:Credit card numbers
+
;ccn_track2.txt
+
:Credit card “track 2″ informaiton, which has previously been found in some bank card fraud cases.
+
;domain.txt
+
:Internet domains found on the drive, including dotted-quad addresses found in text.
+
;email.txt
+
:Email addresses.
+
;ether.txt
+
;Ethernet MAC addresses found through IP packet carving of swap files and compressed system hibernation files and file fragments.
+
;exif.txt
+
:EXIFs from JPEGs and video segments. This feature file contains all of the EXIF fields, expanded as XML records.
+
;find.txt
+
:The results of specific regular expression search requests.
+
;ip.txt
+
:IP addresses found through IP packet carving.
+
;rfc822.txt
+
:Email message headers including Date:, Subject: and Message-ID: fields.
+
;tcp.txt
+
:TCP flow information found through IP packet carving.
+
;telephone.txt
+
:US and international telephone numbers.
+
;url.txt
+
:URLs, typically found in browser caches, email messages, and pre-compiled into executables.
+
;url_searches.txt
+
:A histogram of terms used in Internet searches from services such as Google, Bing, Yahoo, and others.
+
;url_services.txt
+
:A histogram of the domain name portion of all the URLs found on the media.
+
;wordlist.txt
+
:A list of all “words” extracted from the disk, useful for password cracking.
+
;wordlist_*.txt
+
:The wordlist with duplicates removed, formatted in a form that can be easily imported into a popular password-cracking program.
+
;zip.txt
+
:A file containing information regarding every ZIP file component found on the media. This is exceptionally useful as ZIP files contain internal structure and ZIP is increasingly the compound file format of choice for a variety of products such as Microsoft Office
+
  
For each of the above, two additional files may be created:
+
== Temporary sqlite files ==
;*_stopped.txt
+
Seen in e.g.
:bulk_extractor supports a stop list, or a list of items that do not need to be brought to the user’s attention. However rather than simply suppressing this information, which might cause something critical to be hidden, stopped entries are stored in the stopped files.
+
<pre>
;*_histogram.txt
+
/Users/%USERNAME%/AppData/Local/Temp/etilqs_%RANDOM%
:bulk_extractor can also create histograms of features. This is important, as experience has shown that email addresses, domain names, URLs, and other informaiton that appear more frequently on a hard drive or in a cell phone’s memory can be used to rapidly create a pattern of life report.
+
</pre>
  
Bulk extractor also creates a file that captures the provenance of the run:
+
Where "etilqs" is "sqlite" in reverse
;report.xml
+
:A Digital Forensics XML report that includes information about the source media, how the bulk_extractor program was compiled and run, the time to process the digital evidence, and a meta report of the information that was found.
+
  
==Post-Processing==
+
== Use Cases ==
 +
=== Web Browser Data ===
 +
[[Mozilla Firefox]] and [[Google Chrome]] both use SQLite version 3 databases for user data such as history, downloaded files.
  
We have developed four programs for post-processing the bulk_extractor output:
+
=== Mobile OS ===
;bulk_diff.py
+
[[Google Android]] and [[Apple iOS]] use SQLite3 databases for many system applications. Phone data including calls, messages, and credentials are all stored in SQLite3.
:This program reports the differences between two bulk_extractor runs. The intent is to image a computer, run bulk_extractor on a disk image, let the computer run for a period of time, re-image the computer, run bulk_extractor on the second image, and then report the differences. This can be used to infer the user’s activities within a time period.
+
;cda_tool.py
+
:This tool, currently under development, reads multiple bulk_extractor reports from multiple runs against multiple drives and performs a multi-drive correlation using Garfinkel’s Cross Drive Analysis technique. This can be used to automatically identify new social networks or to identify new members of existing networks.
+
;identify_filenames.py
+
:In the bulk_extractor feature file, each feature is annotated with the byte offset from the beginning of the image in which it was found. The program takes as input a bulk_extractor feature file and a DFXML file containing the locations of each file on the drive (produced with Garfinkel’s fiwalk program) and produces an annotated feature file that contains the offset, feature, and the file in which the feature was found.
+
;make_context_stop_list.py
+
:Although forensic analysts frequently make “stop lists”—for example, a lsit of email addresses that appear in the operating system and should therefore be ignored—such lists have a significant problem. Because it is relatively easy to get an email address into the binary of an open source application, ignoring all of these email addresses may make it possible to cloak email addresses from forensic analysis. Our solution is to create context-sensitive stop lists, in which the feature to be stopped is presented with the context in which it occures. The make_context_stop_list.py program takes the results of multiple bulk_extractor runs and creates a single context-sensitive stop list that can then be used to suppress features when found in a specific context. One such stop list constructed from Windows and Linux operating systems is available on the bulk extractor website.
+
  
== Download ==
+
== External Links ==
The current version of '''bulk_extractor''' is 1.0. It can be downloaded from https://github.com/simsong/bulk_extractor
+
* [http://sqlite.org/fileformat2.html The SQLite Database File Format], by the [[SQLite|SQLite project]]
 +
* [http://sqlite.org/wal.html Write-Ahead Logging], by the [[SQLite|SQLite project]]
 +
* [http://forensicsfromthesausagefactory.blogspot.com/2011/04/carving-sqlite-databases-from.html Carving SQLite databases from unallocated clusters], by Richard Drinkwater, April 27, 2011
 +
* [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
  
==Sample Output==
+
== Tools ==
Running on 2.4Ghz iMac with MacOS 10.5.8 on the nps-2009-realistic.aff disk image, bulk extractor version 0.0.10 took 21816 seconds (6 hours, 3 minutes) and produced an [[Media:Nps-2009-realistic.extract.txt|output with 14,160 lines]].
+
* [[SQLite]]
 +
* [[SQLite Forensic Reporter]]
  
Here are the first 200 lines:
+
[[Category:File Formats]]
<pre>
+
Input file: /corp/images/nps/nps-2009-domexusers/nps-2009-realistic.aff
+
Starting page number: 0
+
Last processed page number: 2559
+
Time: Tue Aug 11 04:39:03 2009
+
 
+
Top 10 email addresses:
+
=======================
+
domexuser1@gmail.com: 572
+
domexuser2@gmail.com: 412
+
domexuser3@gmail.com: 319
+
ips@mail.ips.es: 268
+
premium-server@thawte.com: 252
+
CPS-requests@verisign.com: 243
+
someone@example.com: 232
+
domexuser2@live.com: 192
+
inet@microsoft.com: 145
+
domexuser2@hotmail.com: 138
+
 
+
Top 10 email domains:
+
=====================
+
gmail.com: 1693
+
hotmail.com: 630
+
netscape.com: 543
+
example.com: 470
+
microsoft.com: 390
+
thawte.com: 376
+
live.com: 329
+
msn.com: 298
+
mail.ips.es: 268
+
passport.com: 267
+
 
+
Top 10 URLs:
+
=====================
+
http://www.microsoft.com/contentredirect.asp.: 6257
+
http://ocsp.verisign.com0: 3030
+
http://www.mozilla.org/keymaster/gatekeeper/there.is.only.xul: 2241
+
http://: 1666
+
http://crl.verisign.com/tss-ca.crl0: 1515
+
http://crl.verisign.com/ThawteTimestampingCA.crl0: 1513
+
http://www.microsoft.com/pki/certs/CodeSignPCA2.crt0: 1311
+
http://crl.microsoft.com/pki/crl/products/CodeSignPCA2.crl0O: 1310
+
http://www.mozilla.org/MPL/: 1000
+
http://support.microsoft.com: 974
+
 
+
All email addresses:
+
====================
+
domexuser1@gmail.com: 572
+
domexuser2@gmail.com: 412
+
domexuser3@gmail.com: 319
+
ips@mail.ips.es: 268
+
premium-server@thawte.com: 252
+
CPS-requests@verisign.com: 243
+
someone@example.com: 232
+
domexuser2@live.com: 192
+
inet@microsoft.com: 145
+
domexuser2@hotmail.com: 138
+
domexuser1@hotmail.com: 135
+
domexuser1@live.com: 133
+
myname@msn.com: 115
+
example@passport.com: 111
+
ca@digsigtrust.com: 110
+
info@valicert.com: 94
+
piracy@microsoft.com: 91
+
certificate@trustcenter.de: 80
+
hewitt@netscape.com: 69
+
name_123@hotmail.com: 67
+
talkback@mozilla.org: 67
+
lord@netscape.com: 64
+
someone@microsoft.com: 53
+
mcgreer@netscape.com: 51
+
domexuser1%40gmail.com@imap.gmail.com: 48
+
neil@parkwaycc.co.uk: 47
+
9name_123@hotmail.com: 43
+
mazrob@panix.com: 43
+
Outldomexuser2@gmail.com: 41
+
server-certs@thawte.com: 37
+
sspitzer@netscape.com: 36
+
49091023.6070302@gmail.com: 35
+
73A94919-FF6B-4E3F-938E-FB39BBC7497C@gmail.com: 34
+
cps@netlock.net: 33
+
ellenorzes@netlock.net: 33
+
thayes@netscape.com: 33
+
DOMEXUSER2@GMAIL.COM: 32
+
personal-basic@thawte.com: 32
+
nome_123@hotmail.com: 31
+
alecf@netscape.com: 30
+
ManageLinks.aspx%3Fmkt%3Den-us%26noteid%3DNote.Linked%26notelevel%3D1%26notesec%3D0%26username%3Ddomexuser1@hotmail.com: 29
+
domesxuser2@gmail.com: 28
+
javi@netscape.com: 28
+
mscott@mozilla.org: 28
+
personal-premium@thawte.com: 28
+
admin@digsigtrust.com: 27
+
personal-freemail@thawte.com: 27
+
49091664.70508@gmail.com: 26
+
admin@startcom.org: 25
+
cmanske@netscape.com: 24
+
feste@feste.org: 24
+
fritz@google.com: 22
+
silver-certs@saunalahti.fi: 21
+
DOMEXUSER1@GMAIL.COM: 20
+
exemplo@passport.com: 20
+
gold-certs@saunalahti.fi: 20
+
jemand@example.com: 20
+
joku@example.com: 20
+
meunome@msn.com: 20
+
osoba@example.com: 20
+
prova@example.com: 20
+
toolkit@mozilla.org: 20
+
CPh@99841.PA: 19
+
alguem@exemplo.pt: 19
+
birisi@example.com: 19
+
ddrinan@netscape.com: 19
+
noen@example.com: 19
+
valaki@example.com: 19
+
eksempel@passport.com: 18
+
navn_123@hotmail.com: 18
+
law@netscape.com: 17
+
mano@mozilla.com: 17
+
microsof@t.com: 17
+
mscott@netscape.com: 17
+
iemand@microsoft.com: 16
+
myk@mozilla.org: 16
+
ndarnamn@example.com: 16
+
nekdo@example.com: 16
+
nekdo@priklad.com: 16
+
niekto@example.com: 16
+
adamw@gnome.org: 15
+
en@li.org: 15
+
info@netlock.hu: 15
+
nogen@eksempel.dk: 15
+
priklad@passport.com: 15
+
Outldomexuser2@hotmail.com: 14
+
ben@netscape.com: 14
+
ca@firmaprofesional.com: 14
+
ca@ptt-post.nl: 14
+
correo_cert@correo.com.uy: 14
+
ben@mozilla.org: 13
+
doronr@us.ibm.com: 13
+
ehsan.akhgari@gmail.com: 13
+
info@e-trust.be: 13
+
314d3a220810291941w4b52597fh206faba1e5063365@mail.gmail.com: 12
+
DOMEXUSER3@GMAIL.COM: 12
+
MSNPrivacy@msn.com: 12
+
alguien@example.com: 12
+
bsmedberg@covad.net: 12
+
glazman@netscape.com: 12
+
someone@msn.com: 12
+
xyx@example.com: 12
+
Beispiel@passport.com: 11
+
MeinName@msn.com: 11
+
Name_123@hotmail.com: 11
+
St@atus.eU: 11
+
bienvenu@nventure.com: 11
+
disttsc@bart.nl: 11
+
esempio@passport.com: 11
+
exemple@passport.com: 11
+
grafta@bl.com: 11
+
hwaara@chello.se: 11
+
mijnnaam@msn.com: 11
+
mionome@msn.com: 11
+
mojanazwa@msn.com: 11
+
monnom@msn.com: 11
+
ms@n.com: 11
+
naam_123@hotmail.com: 11
+
nazwa_123@hotmail.com: 11
+
przyklad@passport.com: 11
+
voorbeeld@passport.com: 11
+
zeniko@gmail.com: 11
+
christopher@aillon.com: 10
+
community@linuxhall.org: 10
+
dolske@mozilla.com: 10
+
i18n@mova.org: 10
+
id@Us.tc: 10
+
info@netlock.net: 10
+
locales@geez.org: 10
+
rangansen@netscape.com: 10
+
rcassin@supernova.org: 10
+
WindowsXP@gn.microsoft.com: 9
+
ad@msn.com: 9
+
blaker@netscape.com: 9
+
corehc@aol.net: 9
+
exempel@passport.com: 9
+
gnom@prevod.org: 9
+
icw5@gn.microsoft.com: 9
+
jmeno_123@hotmail.com: 9
+
jwalden+code@mit.edu: 9
+
mitnavn@msn.com: 9
+
mittnamn@msn.com: 9
+
name@domain.com: 9
+
namn_123@hotmail.com: 9
+
nevem@msn.com: 9
+
ntsbvt@microsoft.com: 9
+
ornek@passport.com: 9
+
pelda@passport.com: 9
+
rbs@maths.uq.edu.au: 9
+
robert@accettura.com: 9
+
tatarish.l10n@gmail.com: 9
+
alexeyc@bigfoot.com: 8
+
beng@google.com: 8
+
blakeross@telocity.com: 8
+
</pre>
+

Revision as of 10: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