Incorporating SQL Artifacts Into Your Forensic Investigation

Slide 1

Slide 1 text

www.encase.com/ceic Incorporating SQL Artifacts into Your Forensic Investigations

Slide 2

Slide 2 text

Agenda Incorporating SQL Artifacts into Investigations Page 2

Slide 3

Slide 3 text

• 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

Slide 4

Slide 4 text

About Me Incorporating SQL Artifacts into Investigations Page 4

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

You -> Me -> Incorporating SQL Artifacts into Investigations Page 6

Slide 7

Slide 7 text

You -> Me -> Incorporating SQL Artifacts into Investigations Page 7

Slide 8

Slide 8 text

Why Do SQL Artifacts Matter? Incorporating SQL Artifacts into Investigations Page 8

Slide 9

Slide 9 text

Why Do SQL Artifacts Matter? Page 9 Incorporating SQL Artifacts into Investigations

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

Why Do SQL Artifacts Matter? (cont.) Page 13 Incorporating SQL Artifacts into Investigations

Slide 14

Slide 14 text

Why Do SQL Artifacts Matter? (cont.) Page 14 Incorporating SQL Artifacts into Investigations *As of 03/30/2015, from National Conference of State Legislators

Slide 15

Slide 15 text

• 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

Slide 16

Slide 16 text

• 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

Slide 17

Slide 17 text

• 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

Slide 18

Slide 18 text

Collecting SQL Artifacts Incorporating SQL Artifacts into Investigations Page 18

Slide 19

Slide 19 text

• 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

Slide 20

Slide 20 text

• 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

Slide 21

Slide 21 text

• 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

Slide 22

Slide 22 text

• 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

Slide 23

Slide 23 text

• 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

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

Traditional SQL Databases and Their Artifacts Incorporating SQL Artifacts into Investigations Page 25 Microsoft SQL Server PostgreSQL MySQL

Slide 26

Slide 26 text

Traditional SQL Databases and Their Artifacts Microsoft SQL Server Incorporating SQL Artifacts into Investigations Page 26

Slide 27

Slide 27 text

• Database Information • Data Cache • Plan Cache • VLFs Microsoft SQL Server – Volatile Artifacts Incorporating SQL Artifacts into Investigations Page 27

Slide 28

Slide 28 text

• 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

Slide 29

Slide 29 text

• 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

Slide 30

Slide 30 text

• 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

Slide 31

Slide 31 text

• 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

Slide 32

Slide 32 text

• 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

Slide 33

Slide 33 text

• 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

Slide 34

Slide 34 text

• 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 .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

Slide 35

Slide 35 text

• 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 .dbo.sysusers Microsoft SQL Server – Collecting Database Information Incorporating SQL Artifacts into Investigations Page 35

Slide 36

Slide 36 text

Microsoft SQL Server – Collecting Database Information Incorporating SQL Artifacts into Investigations Page 36 • User Accounts (cont.)

Slide 37

Slide 37 text

• 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

Slide 38

Slide 38 text

Microsoft SQL Server – Collecting Database Information Incorporating SQL Artifacts into Investigations Page 38 • User Logins (cont.)

Slide 39

Slide 39 text

• 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

Slide 40

Slide 40 text

• 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

Slide 41

Slide 41 text

• 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

Slide 42

Slide 42 text

Microsoft SQL Server – Volatile Artifacts; Data Cache Incorporating SQL Artifacts into Investigations Page 42

Slide 43

Slide 43 text

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 (',‘',-1) • Step 2: Turn on Tracing so we receive output • DBCC TRACEON(3604) • Step 3: Use DBCC • DBCC PAGE(‘’,1,,3) with tableresults Demo it already!

Slide 44

Slide 44 text

Microsoft SQL Server – Volatile Artifacts; Data Cache Incorporating SQL Artifacts into Investigations Page 44

Slide 45

Slide 45 text

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!

Slide 46

Slide 46 text

• 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

Slide 47

Slide 47 text

• 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

Slide 48

Slide 48 text

• 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

Slide 49

Slide 49 text

Microsoft SQL Server – Volatile Artifacts; Data Cache Incorporating SQL Artifacts into Investigations Page 49

Slide 50

Slide 50 text

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!

Slide 51

Slide 51 text

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

Slide 52

Slide 52 text

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

Slide 53

Slide 53 text

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__ROW(S) • LOP_COMMIT_XACT Demo it already!

Slide 54

Slide 54 text

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/

Slide 55

Slide 55 text

• 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

Slide 56

Slide 56 text

• 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

Slide 57

Slide 57 text

• 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(,default) Demo it already! Microsoft SQL Server – Log Types; Internal Logs Incorporating SQL Artifacts into Investigations Page 57

Slide 58

Slide 58 text

• 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

Slide 59

Slide 59 text

• 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

Slide 60

Slide 60 text

• 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

Slide 61

Slide 61 text

Traditional SQL Databases and Their Artifacts PostgreSQL Incorporating SQL Artifacts into Investigations Page 61

Slide 62

Slide 62 text

PostgreSQL – Who’s Using It? Incorporating SQL Artifacts into Investigations Page 62

Slide 63

Slide 63 text

• 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

Slide 64

Slide 64 text

• 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

Slide 65

Slide 65 text

• 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

Slide 66

Slide 66 text

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 ; 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 ; 1d log_rotation_size Log file size life ; 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 ; postgres event_source Windows EventLog Source ; PostgreSQL

Slide 67

Slide 67 text

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

Slide 68

Slide 68 text

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)

Slide 69

Slide 69 text

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 ; -1, 0, +1 log_timezone Sets log timestamps timezone GMT; typically overwritten within local TZ

Slide 70

Slide 70 text

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\\data\pg_log (Windows) • Logs are actually quite informative; include LOG, FATAL, ERROR, DETAIL, STATEMENT events • Easy to parse to incorporate with other artifacts

Slide 71

Slide 71 text

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

Slide 72

Slide 72 text

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?

Slide 73

Slide 73 text

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

Slide 74

Slide 74 text

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

Slide 75

Slide 75 text

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

Slide 76

Slide 76 text

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.

Slide 77

Slide 77 text

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/.txt • Select * from information_schema.tables; • Select * from information_schema.columns; • Review the output file, script

Slide 78

Slide 78 text

Traditional SQL Databases and Their Artifacts MySQL Incorporating SQL Artifacts into Investigations Page 78

Slide 79

Slide 79 text

• 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

Slide 80

Slide 80 text

• 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

Slide 81

Slide 81 text

• 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

Slide 82

Slide 82 text

• 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 -p --all-databases > MySQL – Forensic Capture of Databases (cont.) Incorporating SQL Artifacts into Investigations Page 82

Slide 83

Slide 83 text

• Database list: • SHOW DATABASES; • For each database, list tables: • SHOW TABLES; • For each table, describe the table: • DESCRIBE ; MySQL – Collecting Database Information Incorporating SQL Artifacts into Investigations Page 83

Slide 84

Slide 84 text

• 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

Slide 85

Slide 85 text

• Logging… • By default, logging is not enabled • Except error logs on Windows MySQL - Logging Incorporating SQL Artifacts into Investigations Page 85

Slide 86

Slide 86 text

• 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

Slide 87

Slide 87 text

• 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

Slide 88

Slide 88 text

SQLite Incorporating SQL Artifacts into Investigations Page 88

Slide 89

Slide 89 text

SQLite Applications and Usage Incorporating SQL Artifacts into Investigations Page 89

Slide 90

Slide 90 text

• 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

Slide 91

Slide 91 text

SQLite Parse and Read SQLite Artifacts Incorporating SQL Artifacts into Investigations Page 91

Slide 92

Slide 92 text

• 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

Slide 93

Slide 93 text

Google Chrome Artifacts • Windows: C:\Users\\AppData\Local\Google\Chrome\User Data\Default • Linux: /home//.config/google-chrome/Default • Mac: /Users//Library/Application Support/Google/Chrome/Default/Preferences ~/.config/google-chrome/Default$ file * | grep SQLite SQLite – Parsing Artifacts Incorporating SQL Artifacts into Investigations Page 93

Slide 94

Slide 94 text

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

Slide 95

Slide 95 text

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

Slide 96

Slide 96 text

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)