sqlite_dissect [-h] [-v] [-d OUTPUT_DIRECTORY] [-p FILE_PREFIX]
[-e EXPORT_TYPE] [-n | -w WAL | -j ROLLBACK_JOURNAL] [-r | EXEMPTED_TABLES]
[-s | -t] [-g] [-c] [-f] [-k] [-l LOG_LEVEL] [-i LOG_FILE] [--warnings]
SQLITE_PATH
SQLite Dissect is a SQLite parser with recovery abilities over SQLite databases and their accompanying journal files. If no options are set other than the file name, the default behaviour will be to check for any journal files and print to the console the output of the SQLite files. The directory of the SQLite file specified will be searched through to find the associated journal files. If they are not in the same directory as the specified file, they will not be found and their location will need to be specified in the command. SQLite carving will not be done by default. Please see the options below to enable carving.
Argument | Description | Example Usage |
---|---|---|
SQLITE_PATH | The path and filename of the SQLite file or directory to be carved. If a directory is provided, it will recursively search for files with the extensions: .db , .sqlite , .sqlite3 . |
sqlite_dissect SQLITE_PATH |
Argument | Flag | Description |
---|---|---|
--help | -h | show this help message and exit |
--version | -v | display the version of SQLite Dissect |
--directory DIRECTORY | -d DIRECTORY | directory to write output to (must be specified for outputs other than console text) |
--file-prefix PREFIX | -p PREFIX | the file prefix to use on output files; default is the name of the SQLite file (the directory for output must be specified) |
--export FORMATS | -e FORMATS | the format(s) to export to {text, csv, sqlite, xlsx, case}. text written to console if -d is not specified. Multiple space-delimited format values are permitted ex. -e sqlite csv xlsx . |
--no-journal | -n | turn off automatic detection of journal files |
--wal WAL | -w WAL | the WAL file to use instead of searching the SQLite file directory by default |
--rollback-journal JOURNAL | -j JOURNAL | the rollback journal file to use instead of searching the SQLite file directory by default (under development, currently only outputs to csv, output directory needs to be specified) |
--exempted-tables TABLES | -r TABLES | comma-delimited string of tables [table1,table2,table3] to exempt (currently only implemented and allowed for rollback journal parsing) ex. -r table1,table2,table3 |
--schema | -s | output the the initial schema found in the main database file to console |
--schema-history | -t | output the schema history to console, prints the --schema information and write-head log changes |
--signatures | -g | output the signatures generated to console |
--carve | -c | carves and recovers table data |
--carve-freelists | -f | carves freelist pages (carving must be enabled, under development) |
--tables TABLES | -b TABLES | specified comma-delimited string of tables [table1,table2,table3] to carve ex. -b table1,table2,table3 |
--disable-strict-format-checking | -k | disable strict format checks for SQLite databases (this may result in improperly parsed SQLite files) |
--log-level LEVEL | -l LEVEL | level to log messages at {critical, error, warning, info, debug, off} |
--log-file FILE | -i FILE | log file to write to; appends to file if file already exists. default is to write to console. ignored if log-level set to off |
--warnings | enable runtime warnings | |
--header | enable header info printing | |
--config FILE | file containing configuration values for the execution of SQLite Dissect |
sqlite_dissect --version
sqlite_dissect [SQLITE_PATH]
sqlite_dissect [SQLITE_PATH] --schema-history -d [OUTPUT_DIRECTORY] -e sqlite
sqlite_dissect [SQLITE_PATH] --signatures -d [OUTPUT_DIRECTORY] -e sqlite --carve
sqlite_dissect [SQLITE_PATH] -d [OUTPUT_DIRECTORY] -e sqlite --carve --carve-freelists -b [TABLES]
sqlite_dissect [SQLITE_PATH] -d [OUTPUT_DIRECTORY] -e xlsx --schema-history --carve --signatures --log-level debug -i [LOG_FILE]
sqlite_dissect [SQLITE_PATH] -d [OUTPUT_DIRECTORY] -e csv --carve -j [ROLLBACK_JOURNAL]
SQLite Dissect can optionally be configured with configuration files that are provided using the CLI argument --config
The format for the configuration file is as follows:
# this is a comment
; this is also a comment (.ini style)
--- # lines that start with --- are ignored (yaml style)
-------------------
[section] # .ini-style section names are treated as comments
# how to specify a key-value pair (all of these are equivalent):
name value # key is case sensitive: "Name" isn't "name"
name = value # (.ini style) (white space is ignored, so name = value same as name=value)
name: value # (yaml style)
--name value # (argparse style)
# how to set a flag arg (eg. arg which has action="store_true")
--name
name
name = True # "True" and "true" are the same
# how to specify a list arg (eg. arg which has action="append")
fruit = [apple, orange, lemon]
indexes = [1, 12, 35 , 40]
For example:
[export]
directory=/path/to/output
export=[text, sqlite, case]
SQLite Dissect can also be configured using environment variables with the prefixed version of the argument flag (SQLD_).
For example:
export SQLD_DIRECTORY=/path/to/output
export SQLD_EXPORT_TYPE="[text, sqlite, case]"
This application focuses on carving by analyzing the allocated content within each of the SQLite
database tables and creating signatures. Where there is no content in the table, the signature
is based off of analyzing the create table statement in the master schema table. The signature
contains the series of possible serial types that can be stored within the file for that table.
This signature is then applied to the unallocated content and freeblocks of the table b-tree in
the file. This includes both interior and leaf table b-tree pages for that table. The signatures
are only applied to the pages belonging to the particular b-tree page it was generated from due
to initial research showing that the pages when created or pulled from the freelist set are
overwritten with zeros for the unallocated portions. Fragments within the pages can be reported
on but, due to the size (<4 bytes), are not carved. Due to the fact that entries are added into
tables in SQLite from the end of the page and moving toward the beginning, the carving works
in the same manner in order to detect previously partially overwritten entries better. This
carving can also be applied to the set of freelist pages within the SQLite file if specified
but the freelist pages are currently treated as sets of unallocated data with the exception
of the freelist page metadata.
The carving process does not currently account for index b-trees as the more pertinent information is included in the table b-trees. Additionally, there are some table b-trees that are not currently supported. This includes tables that are "without row_id", virtual, or internal schema objects. These are unique cases which are slightly more rare use cases or don't offer as much as the main tables do. By default all tables will be carved if they do not fall into one of these cases. You can send in a specific list of tables to be carved.
This application is written in the hopes that many of these use cases can be addressed in the future and is scalable to those use cases. Although one specific type of signature is preferred by default in the application, SQLite Dissect generates multiple versions of a signature and can eventually support carving by specifying other signatures or providing your own. Since SQLite Dissect generates the signature based off of existing data within the SQLite files automatically, there is no need to supply SQLite Dissect a signature for a particular schema or application. This could be implemented though to allow possibly more specific/targeted carving of SQLite files through this application.
Journal carving is supported primarily for WAL files. If a WAL file is found, this application will parse through each of the commit records in sequence and assign a version to them. This is the same as timelining that some applications use to explain this. Rollback journals are currentlytreated as a full unallocated block and only support export to csv files.
SQLite Dissect can support output to various forms: text, csv, xlsx, and sqlite. Due to certain constraints on what can be written to some file types, certain modifications need to be made. For instance, when writing SQLite columns such as rowid that are already going to pre-exist in the table for export to a SQLite file we need to preface the columns with "sd" so they will not conflict with the actual rowid column. This also applies to internal schema objects. If certain SQLite tables are requested to be written to a SQLite file, than these will be prefaced with "iso" so they will not conflict with similar internal schema objects that may already exist in the SQLite file bring written to. In xlsx or csv, due to a "=" symbol indicating a type of equation, these are prefaced with a " " character to avoid this issue. More details can be found in the code documentation of the export classes themselves.
SQLite Dissect opens the file as read only and acts as a read only interpreter when parsing and carving the SQLite file. This is to ensure no changes are made to the files being analyzed. The only use of the sqlite3 libraries in Python are to write the output to a SQLite file if that option is specified for output.
Note: Even though virtual tables are skipped, virtual tables may create other non-virtual tables which are not skipped. Currently nothing ties these tables back to the virtual table that created them.
Note: This applies to previous WAL records that were previously written to the SQLite database.
High level scripts that are used to access the rest of the library and provide the base application for executing SQLite Dissect when built.
This script shows an example of the api usage for a specific test file.
TODO:
This script shows examples of how this library can be used.
TODO:
This script is used to setup the sqlite_dissect package for use in python environments.
Note: To compile a distribution for the project run "python setup.py sdist" in the directory this file is located in.
Note: openpyxl is needed for the xlsx export and will install jdcal, et-xmlfile ["openpyxl>=2.4.0b1"]
Note: PyInstaller is used for generation of executables but not included in this setup.py script and will install altgraph, dis3, macholib, pefile, pypiwin32, pywin32 as dependencies. [pyinstaller==3.6 needs to be used for Python 2.7 since the newer versions of PyInstaller of 4.0+ require Python 3.6] Information on how to run PyInstaller is included in the spec files under the pyinstaller directory. Four files are here, two for windows and two for linux, both for x64 platforms. The two different files for each allow you to build it as one single file or a directory of decompressed files. Since the one file extracts to a temp directory in order to run, on some systems this may be blocked and therefore the directory of files is preferred.
This script acts as the command line script to run this library as a stand-alone application.
TODO: