Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Incorporating SQL Artifacts Into Your Forensic ...

Incorporating SQL Artifacts Into Your Forensic Investigation

In this session, we will examine artifacts and data from technologies that leverage SQL databases. First, we will analyze various artifacts created by popular versions of SQL, including Microsoft, PostgreSQL, and MySQL. Attendees will learn where these artifacts are located, how to examine them, and how they may be integral to understanding the actions of an attacker. Sometimes the story of the data at rest is more telling than the story of the data in motion.

Second, we will examine the embeddable SQLite and its applications within forensic examinations. Attendees will examine technologies utilizing SQLite databases, including mobile devices and Apple’s OS X. Similar to the first part, attendees will learn how to examine these artifacts and incorporate them into their investigations.

bromiley

May 21, 2015
Tweet

More Decks by bromiley

Other Decks in Technology

Transcript

  1. • About Me • Why Do SQL Artifacts Matter? •

    Collecting SQL Artifacts • Traditional SQL Databases and their Artifacts • MS SQL • PostgreSQL • MySQL • SQLite • Applications and Usage • How to Parse and Read SQLite Artifacts Agenda Incorporating SQL Artifacts into Investigations Page 3
  2. Matt Bromiley Currently a Senior Associate within KPMG’s Forensic Technology

    practice 4+ years consulting experience with a focus on data breaches, incident response, and digital forensics. Work with clients from Fortune Global 50 to small regional shops I like to develop open source forensic tools, focusing on enterprise-level forensics, log analysis, and visualization [@]505forensics[.com] About Me Incorporating SQL Artifacts into Investigations Page 5
  3. Why Do SQL Artifacts Matter? (cont.) Example: Page 10 Le

    simplified org.. Incorporating SQL Artifacts into Investigations Yes!
  4. Le simplified org.. Why Do SQL Artifacts Matter? (cont.) Example

    1 (cont.): Page 11 Freak Out! Freak Out! Who is this? Can we find him? Incorporating SQL Artifacts into Investigations
  5. Le simplified org.. Why Do SQL Artifacts Matter? (cont.) Example

    1 (cont.): Page 12 What I NEED to care about Incorporating SQL Artifacts into Investigations
  6. Why Do SQL Artifacts Matter? (cont.) Page 14 Incorporating SQL

    Artifacts into Investigations *As of 03/30/2015, from National Conference of State Legislators
  7. • We’re entering that sweet spot of only having Microsoft

    extended support available • 2008R2 – 08Jul2014; 09Jul2019 • Organizations may not upgrade as fast as they should; outdated SQL is still used heavily • As software/implementations become outdated/unpatched, attackers success rates goes up Why Do SQL Artifacts Matter? (cont.) Page 15 Incorporating SQL Artifacts into Investigations Success is mine
  8. • Where the data sits • Attacker < -- Infrastructure

    --> Database • Infrastructure is important, but external parties want information about data • What is in the DB? What was taken? • May change notification requirements • What is the maximum loss/damage? • Allows us to understand how the data was interacted with • DB account information (creation/password change/etc.) Why Do SQL Artifacts Matter? (cont.) Page 16 Incorporating SQL Artifacts into Investigations
  9. • Helps the investigator (us) paint a better picture of

    what happened during a breach • SQL artifacts can be included in timeline analysis, and help depict attacker activity • Can also be used to recreate attacker actions, possibly profiling data accessed • Logs help us profile attacker TTPs; knowledge to protect the house! Why Do SQL Artifacts Matter? (cont.) Page 17 Incorporating SQL Artifacts into Investigations
  10. • Live? Dead? Bon Jovi? • Depends on the DB,

    and your artifacts • Depends on the attack! • IMPORTANT: Depends on notification • Volatile information: Live • DB Records: Live/Bon Jovi (WARNING) • Logs/config files: Dead Collecting SQL Artifacts Page 19 Incorporating SQL Artifacts into Investigations
  11. • Critical systems; can they be taken offline? • How

    massive is the backend? • How big is your appetite for pain? • Ambitious associate vs. experienced pro Collecting SQL Artifacts (cont.) Page 20 Incorporating SQL Artifacts into Investigations
  12. • Live Collections: • Remote Connections (default ports) • MS

    SQL: 1433 • PostgreSQL: 5432 • MySQL: 3306 • Local • Interactive command line sessions • Same tools as remote connections Collecting SQL Artifacts (cont.) Page 21 Incorporating SQL Artifacts into Investigations
  13. • Dead Collections: • Copy disk images, analyze artifacts at

    a later time • Can be very dicey; a lot of databases run heavily in memory these days • Best way for dead acquisition? • Pull power plug (dirty) • Image • Analyze later Collecting SQL Artifacts (cont.) Page 22 Incorporating SQL Artifacts into Investigations
  14. • Hybrid Collections: • Collect volatile information from server; preserve

    information about current state as best possible • THEN unplug, and collect what you need via best practices • Is it necessary?? • Up to you and your workflow • Is it possible?? • Up to the client! Collecting SQL Artifacts (cont.) Page 23 Incorporating SQL Artifacts into Investigations
  15. Whichever method you choose, always make sure to preserve integrity

    of evidence collected. Especially live systems!!  Save all of your scripts  Test, test, test (on your own data!)  Output to external files  Hash files, store hash so that data can be validated at a later point in time  Back that thing up!  If you’re collecting volatile data, then the state may change immediately. Very difficult to replicate Collecting SQL Artifacts (cont.) Page 24 Incorporating SQL Artifacts into Investigations
  16. Traditional SQL Databases and Their Artifacts Incorporating SQL Artifacts into

    Investigations Page 25 Microsoft SQL Server PostgreSQL MySQL
  17. • Database Information • Data Cache • Plan Cache •

    VLFs Microsoft SQL Server – Volatile Artifacts Incorporating SQL Artifacts into Investigations Page 27
  18. • Collecting: • Server Information • Stats on DBs and

    tables • User permissions • Active Sessions Microsoft SQL Server – Collecting Database Information Incorporating SQL Artifacts into Investigations Page 28
  19. • Server Information • Script to pull back consolidated server

    information at once • Utilize SERVERNAME expression to pull back information about server • Results must be converted from sql_variant to varchar for printing Microsoft SQL Server – Collecting Database Information Incorporating SQL Artifacts into Investigations Page 29
  20. • Server Information (cont.) - (Print out) PRINT 'Server Name:

    ' + CONVERT(varchar(100), SERVERPROPERTY('servername') PRINT 'Server Version: ' + CONVERT(varchar(10), SERVERPROPERTY('productversion') PRINT 'Server Edition: ' + CONVERT(varchar(50), SERVERPROPERTY('edition') Microsoft SQL Server – Collecting Database Information Incorporating SQL Artifacts into Investigations Page 30
  21. • Server Information (cont.) - (Create a table) SELECT SERVERPROPERTY('servername')

    'ServerName', SERVERPROPERTY('productversion') 'Product Version', SERVERPROPERTY('edition') 'Product Edition‘ Easier to export to CSV once done! Microsoft SQL Server – Collecting Database Information Incorporating SQL Artifacts into Investigations Page 31
  22. • Stats on DBs and Tables • We want information

    from the sys.sysdatabases [or master..sysdatabases] table • sys. and master.. will return same values • However, some values will depend on what you need • sys.databases vs. sys.sysdatabases • 56 columns vs 12 columns • For your forensic purposes, pull what you need • Then use query magic to make it pretty Microsoft SQL Server – Collecting Database Information Incorporating SQL Artifacts into Investigations Page 32
  23. • Stats on DBs and Tables (cont.) SELECT a.dbid 'DB

    ID', a.name as 'DB Name', b.name 'Name', str(b.size * 0.0078125,10,2) 'Size (MB)‘ FROM sys.sysdatabases a, sys.sysaltfiles b WHERE a.dbid = b.dbid Microsoft SQL Server – Collecting Database Information Incorporating SQL Artifacts into Investigations Page 33
  24. • Stats on DBs and Tables (cont.) • Column information:

    SELECT table_catalog 'DB Name', table_name 'Table Name', column_name 'Column Name', data_type 'Data Type' FROM <db_name>.information_schema.columns • Specify which table you want to search, or script iteration through each table within the DB Microsoft SQL Server – Collecting Database Information Incorporating SQL Artifacts into Investigations Page 34
  25. • User Logins • Use ‘master’ or specific database in

    FROM statement to be specific SELECT uid 'UserID', name 'User Name', sid 'SID', createdate 'Account Creation', updatedate 'Account Updated', password 'Account Password' FROM <dbname or master>.dbo.sysusers Microsoft SQL Server – Collecting Database Information Incorporating SQL Artifacts into Investigations Page 35
  26. Microsoft SQL Server – Collecting Database Information Incorporating SQL Artifacts

    into Investigations Page 36 • User Accounts (cont.)
  27. • User Logins • Timestamps can be used to identify

    attacker activity, login sessions, password changes SELECT name 'Username', sid 'User SID', password 'Password', createdate 'Login Creation', updatedate 'Login Update' FROM sys.syslogins Microsoft SQL Server – Collecting Database Information Incorporating SQL Artifacts into Investigations Page 37
  28. • Session History (VERY VALUABLE!!) SELECT session_id 'Session ID', login_time

    'Login Time', host_name 'Host_Name', host_process_id 'Process ID', login_name 'Login Name', nt_domain 'NT Domain‘ FROM sys.dm_exec_sessions ORDER BY host_name DESC Microsoft SQL Server – Collecting Database Information Incorporating SQL Artifacts into Investigations Page 39
  29. • Ever notice how the first query is slow, but

    the second and third are faster? Thank you data cache! • Data cache, aka buffer pool, is made up of pages read into memory • SQL Server is made up of 8KB pages • Data requests decide what goes in the cache • Requests upon the same data use the data cache; optimizations, saves time • HIGHLY volatile • Memory runs low – see ya! • Additional queries override space (be careful collecting!) Microsoft SQL Server – Volatile Artifacts; Data Cache Incorporating SQL Artifacts into Investigations Page 40
  30. • How to pull data cache SELECT * FROM sys.dm_os_buffer_descriptors

    Microsoft SQL Server – Volatile Artifacts; Data Cache Incorporating SQL Artifacts into Investigations Page 41
  31. Microsoft SQL Server – Volatile Artifacts; Data Cache Incorporating SQL

    Artifacts into Investigations Page 43 • I want to know what’s inside!! • Let’s clean that up (DBCC = DataBase Console Commands) • Step 1: Find data cache related to our database/table • DBCC IND (<DB_NAME>',‘<TABLE_NAME>',-1) • Step 2: Turn on Tracing so we receive output • DBCC TRACEON(3604) • Step 3: Use DBCC • DBCC PAGE(‘<DB_NAME>’,1,<PAGE_NUMBER>,3) with tableresults Demo it already!
  32. Microsoft SQL Server – Volatile Artifacts; Data Cache Incorporating SQL

    Artifacts into Investigations Page 45 • Data cache can be pulled and examined offline • Need to pull back memory page information, page mapping • Lengthy process (similar to previous) to iterate through and map data caches to tables, data • Script it!
  33. • When a statement is received, the query processor identifies

    the most efficient way to execute; the “execution plan” • Hence, the “plan cache” • Stores previously executed SQL “plans” • Can be used to find queries from stored procedures and functions • Not all commands touch table records, or force a data page • Great for finding user account activity/creation commands • Some details are stored in plain text!! Microsoft SQL Server – Volatile Artifacts; Plan Cache Incorporating SQL Artifacts into Investigations Page 46
  34. • Highly volatile • Subject to SQL flushing policies •

    Stored procedure updates • Stopping the SQL Server service (clears associated memory) • May be cleared by an attacker!!! Microsoft SQL Server – Volatile Artifacts; Plan Cache (cont.) Incorporating SQL Artifacts into Investigations Page 47
  35. • How to pull plan cache SELECT * FROM sys.dm_exec_query_stats

    Microsoft SQL Server – Volatile Artifacts; Plan Cache (cont.) Incorporating SQL Artifacts into Investigations Page 48
  36. Microsoft SQL Server – Volatile Artifacts; Plan Cache Incorporating SQL

    Artifacts into Investigations Page 50 • Clean that up already • Step 1: Find data cache related to our database/table • SELECT * FROM sys.dm_exec_query_stats • Step 2: Use the plan_handle to isolate a specific query • SELECT * FROM sys.dm_exec_sql_text(plan_handle) Demo it already!
  37. Microsoft SQL Server – Volatile Artifacts; VLFs Incorporating SQL Artifacts

    into Investigations Page 51 • Virtual Log Files • Store previously executed DML and DLL operations • READ: Transaction logs • Includes INSERT, UPDATE, and DELETE statements • Highly volatile!! • May be purged by SQL Server at intervals • Will not survive service/machine reboot • Transactions on server • Available memory
  38. Microsoft SQL Server – Volatile Artifacts; VLFs (cont.) Incorporating SQL

    Artifacts into Investigations Page 52 • Can be accessed from live SQL Server, examined for historical entries • Follow order of volatility best practices; may be best to analyze offline • Follow same steps as data cache analysis • Will need to pull multiple sources of information to correlate offline, but not impossible
  39. Microsoft SQL Server – Volatile Artifacts; VLFs (cont.) Incorporating SQL

    Artifacts into Investigations Page 53 • If you are able to view on a live system • SELECT * FROM ::fn_dblog(null, null) • Database scoped!! • Expected sequence of events: • LOP_BEGIN_XACT • LOP_<INSERT|MODIFY|DELETE>_ROW(S) • LOP_COMMIT_XACT Demo it already!
  40. Microsoft SQL Server – Volatile Artifacts; VLFs (cont.) Incorporating SQL

    Artifacts into Investigations Page 54 • SQL Data Record Structure Source: http://aboutsqlserver.com/2013/10/15/sql-server-storage-engine-data-pages-and-data-rows/
  41. • Internal Logs • Trace Logs • Transaction Logs (VLFs)

    • Error Logs • External Logs • Windows Event Logs • Web Logs • Firewall Logs • Etc. Microsoft SQL Server – Log Types Incorporating SQL Artifacts into Investigations Page 55
  42. • Trace Logs • Store information about logins, DML/DDL operations

    • SQL Server 2005+ runs default trace • Stored automatically • Low volatility; files are persistent • New trace file is created each time MS SQL is started Microsoft SQL Server – Log Types; Internal Logs Incorporating SQL Artifacts into Investigations Page 56
  43. • Trace Logs (cont.) • Can be removed from a

    system and preserved forensically • Examined later on a separate, analysis system • Use ‘SQL Server Profiler’ to load a trace file • Load directly into SQL for analysis • SELECT * FROM sys.fn_trace_gettable(<file_path>,default) Demo it already! Microsoft SQL Server – Log Types; Internal Logs Incorporating SQL Artifacts into Investigations Page 57
  44. • Error Logs • Same location as trace files •

    Plain text (FINALLY!), can be opened by ${insert_your_fav_text_editor} • Tracks startup, shutdown, login, logout, authentication success/failures • Oddly enough, not just errors.. • Can be removed from a system and preserved forensically • Examined later on a separate, analysis system Microsoft SQL Server – Log Types; Internal Logs Incorporating SQL Artifacts into Investigations Page 58
  45. • Error Logs (cont.) • Important to track account activity!

    • Authentication Failure States -> • Can be very important to detail attacker activity Microsoft SQL Server – Log Types; Internal Logs Incorporating SQL Artifacts into Investigations Page 59 State Code Meaning 2 Invalid ID 5 Invalid ID 6 Invalid Windows login attempt 7 Incorrect password/disabled 8 Incorrect password 9 Invalid password 11 Server access failure with valid creds 12 Server access failure with valid creds 18 Password must be changed
  46. • Event Logs • Application.evtx • SQL Startup Events, Database

    creations, Enabling Trace, Database Option Settings • Security.evtx • Authentication Events, Group Policies, SQL Server Logon Events Microsoft SQL Server – Log Types; External Logs Incorporating SQL Artifacts into Investigations Page 60
  47. • All security is controlled via config file (postgresql.conf) or

    via server command line • Security settings help us understand how attackers may have had access to the database • Log settings let us know what’s available, what we can expect to see in logs • Open source: flat text file for config options • Open source: defaults usually suck PostgreSQL – Config File Incorporating SQL Artifacts into Investigations Page 63
  48. • Security settings can be found in a block of

    the config file • Gives default port • Encryption used? • SSL Information • Is DB tied to Kerberos? • May be useful for tracking user activity outside of DB Server PostgreSQL – Security Incorporating SQL Artifacts into Investigations Page 64
  49. • All logging is controlled via config file (postgresql.conf) or

    via server command line • Default logging output is to stderr • Default logging level is WARNING, ERROR for error statements • Logging capabilities can be broken out into where, when, and what Official Docs: http://www.postgresql.org/docs/9.0/static/runtime-config- logging.html PostgreSQL – Logging Incorporating SQL Artifacts into Investigations Page 65
  50. PostgreSQL – Logging (cont.) | WHERE Incorporating SQL Artifacts into

    Investigations Page 66 Setting Purpose Options(default in bold) log_destination Log type and destination Csv, stderr, syslog, eventlog logging_collector Don’t lose messages On/Off log_directory Save location <Path>; pg_log log_filename Strftime filename pattern postgresql-%Y-%m-$d_%H%M%S.log log_file_mode Mode for file to be created (*nix) 0600; Windows ignores log_rotation_age Log file shelf life <Time>; 1d log_rotation_size Log file size life <Size>; 10MB log_truncate_on_rotation Overwrite vs. append On/Off syslog_facility Syslog level to be used LOCAL0-LOCAL7; LOCAL0 syslog_ident Identifier in syslogs <Text>; postgres event_source Windows EventLog Source <Text>; PostgreSQL
  51. PostgreSQL – Logging (cont.) | WHEN Incorporating SQL Artifacts into

    Investigations Page 67 Setting Purpose Options(default in bold) client_min_messages Messages sent to the client PANIC, FATAL, ERROR, WARNING, NOTICE, LOG DEBUG1-5 log_min_messages Messages written to server log PANIC, FATAL, LOG, ERROR, WARNING, NOTICE, INFO, DEBUG1-5 log_min_error_statement Messages from errors related to SQL statements PANIC, FATAL, LOG ERROR, WARNING, NOTICE, INFO, DEBUG1-5 log_min_duration_statement Log if statement runs duration -1, 0, Milliseconds > 0
  52. PostgreSQL – Logging (cont.) | WHAT Incorporating SQL Artifacts into

    Investigations Page 68 Setting Purpose Options(default in bold) application_name Application connected to server Set by application debug_print_[parse|rewritten|plan] Various debugging output controls On/Off debug_pretty_print Creates more readable logs On/Off log_checkpoints Log checkpoints and restartpoints On/Off log_connections Log each connection On/Off log_disconnections Log each disconnection On/Off log_duration Log each statement duration On/Off log_error_verbosity How detailed is each entry Terse, Default, Verbose log_hostname Include hostname with IP On/Off log_line_prefix Printf string at beginning of each line %t (timestamp without milliseconds)
  53. PostgreSQL – Logging (cont.) | WHAT (cont.) Incorporating SQL Artifacts

    into Investigations Page 69 Setting Purpose Options(default in bold) log_lock_waits Log sessions that exceed lock wait On/Off log_statement Which statements to log Off, DDL, MOD, All log_temp_files Log details about temp files <Size in KB>; -1, 0, +1 log_timezone Sets log timestamps timezone GMT; typically overwritten within local TZ
  54. PostgreSQL – Logging (cont.) Incorporating SQL Artifacts into Investigations Page

    70 • PostgreSQL logs are in a convenient location: • /var/log/postgresql (*nix) • C:\Program Files\PostgreSQL\<version>\data\pg_log (Windows) • Logs are actually quite informative; include LOG, FATAL, ERROR, DETAIL, STATEMENT events • Easy to parse to incorporate with other artifacts
  55. PostgreSQL – Logging (cont.) Incorporating SQL Artifacts into Investigations Page

    71 • PostgreSQL has another specified file of just psql statement history • /var/lib/postgresql/.psq l_history • Keeps a detailed history of psql statements run against the server; • Useful for identifying previously-issued commands
  56. PostgreSQL – Forensic Capture Incorporating SQL Artifacts into Investigations Page

    72 • Due to structure, PostgreSQL will allows us to copy the entire database out • Pros: • Great for analysis later • Able to replicate steps taken, if data is still available • Cons: • Should stop service • Need creds for server/file permissions • DB might be massive! • Do you really need the whole thing?
  57. PostgreSQL – Forensic Capture (cont.) Incorporating SQL Artifacts into Investigations

    Page 73 • Within config file, there will be a data path: • Using forensic tools, such as dd or dcfldd (ooh so fancy!), this entire directory can be captured/hashed/backed up (after stopping service) • pg_dumpall can backup • To restore database for analysis purposes, copy to analysis machine, and point test PostgreSQL instance to the data directory • Remember, will require old credentials!! • May need to assign ownership privileges on new system so that files can be accessed
  58. PostgreSQL – Capturing Information from the DB Incorporating SQL Artifacts

    into Investigations Page 74 • Whether Bon Jovi on server, information can be captured from DB in running state • PostgreSQL offers easy ‘cheat’ commands from the command line; makes it easy to not remember long statements! • Users: \du • Tables: \dt • External Users: \deu+ • Command History: \s
  59. PostgreSQL – Capturing Information from the DB (cont.) Incorporating SQL

    Artifacts into Investigations Page 75 • Other commands require a bit more language • Select * from information_schema.tables; • Provides table information about current DB • Can be scripted, run, and output externally
  60. PostgreSQL – Capturing Information from the DB (cont.) Incorporating SQL

    Artifacts into Investigations Page 76 • Other commands require a bit more language • Select * from information_schema.columns; • Just…ick.
  61. PostgreSQL – Capturing Information from the DB (cont.) Incorporating SQL

    Artifacts into Investigations Page 77 • Let’s get that data outside the DB so we can make it pretty • \o /tmp/<output_file>.txt • Select * from information_schema.tables; • Select * from information_schema.columns; • Review the output file, script
  62. • Largest market share by instances • MySQL can store

    a LOT of data • Up to 256TB (allegedly) • Also open source! • Structures are consistent across all operating systems • Config files and log files are all flat text • Easy to read, parse, script against MySQL Incorporating SQL Artifacts into Investigations Page 79
  63. • FRM, MYD, MYI • All related to databases/table structure

    • iblogfile(0|1), ibdata1 • InnoDB system • Contain changes not yet committed to DB • Will sync if MySQL is shutdown/restarted • Few parsers exist; not fully built out • Strings can return gibberish and some table names -> MySQL - File Layouts Incorporating SQL Artifacts into Investigations Page 80
  64. • Structure is the same across operating systems • FRM

    – Table structure • MYD – Contains data • MYI – Indexes of table • Using forensic tool, preserve files of interest • Want to recreate? Simply copy/paste into clean test instance, and start MySQL! • Allows for analysis offline • May need to adjust permissions of files MySQL – Forensic Capture of Databases Incorporating SQL Artifacts into Investigations Page 81
  65. • Sometimes forensic capture can turn into forensic restore •

    Try to collect as many artifacts as possible for MySQL instance • Size a problem? May need to recover on-site, if corrupted • Include ib_logfiles as they may have changes not yet committed • If worse comes to worse (no other options working), use sqldump to output MySQL • sqldump.exe –u<username> -p<password> --all-databases > <output_file.txt> MySQL – Forensic Capture of Databases (cont.) Incorporating SQL Artifacts into Investigations Page 82
  66. • Database list: • SHOW DATABASES; • For each database,

    list tables: • SHOW TABLES; • For each table, describe the table: • DESCRIBE <table_name>; MySQL – Collecting Database Information Incorporating SQL Artifacts into Investigations Page 83
  67. • Users list: • SELECT * FROM MYSQL.user; • Version

    Information: • SELECT VERSION(); • Similar to PostgreSQL, we can dump INFORMATION_SCHEMA information • SELECT * FROM INFORMATION_SCHEMA.TABLES; • SELECT * FROM INFORMATION_SCHEMA.COLUMNS; • SELECT * FROM INFORMATION_SCHEMA.USER_PRIVILEGES; MySQL – Collecting Database Information (cont.) Incorporating SQL Artifacts into Investigations Page 84
  68. • Logging… • By default, logging is not enabled •

    Except error logs on Windows MySQL - Logging Incorporating SQL Artifacts into Investigations Page 85
  69. • Log Types • Error Log • Starting/Stopping/Errors with MySQL

    Service • General Query Log • Connections and statements; has to be specifically enabled with MySQL Service • Binary Log • Statements to change data MySQL – Logging (cont.) Incorporating SQL Artifacts into Investigations Page 86
  70. • Log Types (cont.) • Relay Log • Data changes

    from a replication server (only used on nodes) • Slow Query Log • Queries that took longer than a certain time (performance metrics) MySQL – Logging (cont.) Incorporating SQL Artifacts into Investigations Page 87
  71. • Mobile applications and tools • Self-contained applications (browsers, financial

    tools, etc.) • Quick, light, nimble • Expandable • Max DB size of 140TB • Unlimited number of concurrent readers • One writer at a time SQLite – Applications and Usage Incorporating SQL Artifacts into Investigations Page 90
  72. • Command line • Linux • Mac OS X •

    GUI Tools • SQLite Browser via FireFox • Forensic Tools • EnCase 7 • IEF • BlackLight SQLite – How to Parse and Read SQLite Artifacts Incorporating SQL Artifacts into Investigations Page 92
  73. Google Chrome Artifacts • Windows: C:\Users\<user>\AppData\Local\Google\Chrome\User Data\Default • Linux: /home/<user>/.config/google-chrome/Default

    • Mac: /Users/<user>/Library/Application Support/Google/Chrome/Default/Preferences ~/.config/google-chrome/Default$ file * | grep SQLite SQLite – Parsing Artifacts Incorporating SQL Artifacts into Investigations Page 93
  74. Google Chrome History (parsing with Linux command line): • Load

    DB via CLI • sqlite3 History • > select * from downloads; • Entries are pipe delimited, organized by timestamp SQLite – Parsing Artifacts Incorporating SQL Artifacts into Investigations Page 94
  75. Google Chrome History (parsing with FireFox SQLite Browser): • Connect

    to DB • May need to take offline! • Demo it already? SQLite – Parsing Artifacts Incorporating SQL Artifacts into Investigations Page 95
  76. References Incorporating SQL Artifacts into Investigations Page 96 • SQL

    Server Forensic Analysis by Kevvie Fowler (ISBN: 978-0321544360) • Forensic Analysis of MySQL DB Systems (https://digital-forensics.sans.org/summit- archives/dfirprague14/Forensic_Analysis_of_MySql_DB_Systems_Marcel_Niefindt.pdf) • Don’t Drop That Table: A Case Study in MySQL Forensics (https://digital-forensics.sans.org/summit- archives/dfir14/Don't_Drop_That_Table_A_Case_Study_in_MySQL_Forensics_Jeff_Hamm.pdf) • InnoDB Database Forensics: Reconstructing Data Manipulation Queries from Redo Logs (https://www.sba-research.org/wp-content/uploads/publications/WSDF2012_InnoDB.pdf)