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.

Transcript

1. www.encase.com/ceic Incorporating SQL Artifacts into Your Forensic Investigations
2. Agenda Incorporating SQL Artifacts into Investigations Page 2
3. • 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
4. About Me Incorporating SQL Artifacts into Investigations Page 4
5. 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
6. You -> Me -> Incorporating SQL Artifacts into Investigations Page 6
7. You -> Me -> Incorporating SQL Artifacts into Investigations Page 7
8. Why Do SQL Artifacts Matter? Incorporating SQL Artifacts into Investigations Page 8
9. Why Do SQL Artifacts Matter? Page 9 Incorporating SQL Artifacts into Investigations
10. Why Do SQL Artifacts Matter? (cont.) Example: Page 10 Le simplified org.. Incorporating SQL Artifacts into Investigations Yes!
11. 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
12. 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
13. Why Do SQL Artifacts Matter? (cont.) Page 13 Incorporating SQL Artifacts into Investigations
14. Why Do SQL Artifacts Matter? (cont.) Page 14 Incorporating SQL Artifacts into Investigations *As of 03/30/2015, from National Conference of State Legislators
15. • 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
16. • 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
17. • 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
18. Collecting SQL Artifacts Incorporating SQL Artifacts into Investigations Page 18
19. • 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
20. • 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
21. • 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
22. • 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
23. • 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
24. 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
25. Traditional SQL Databases and Their Artifacts Incorporating SQL Artifacts into Investigations Page 25 Microsoft SQL Server PostgreSQL MySQL
26. Traditional SQL Databases and Their Artifacts Microsoft SQL Server Incorporating SQL Artifacts into Investigations Page 26
27. • Database Information • Data Cache • Plan Cache • VLFs Microsoft SQL Server – Volatile Artifacts Incorporating SQL Artifacts into Investigations Page 27
28. • 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
29. • 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
30. • 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
31. • 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
32. • 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
33. • 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
34. • 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
35. • 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
36. Microsoft SQL Server – Collecting Database Information Incorporating SQL Artifacts into Investigations Page 36 • User Accounts (cont.)
37. • 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
38. Microsoft SQL Server – Collecting Database Information Incorporating SQL Artifacts into Investigations Page 38 • User Logins (cont.)
39. • 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
40. • 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
41. • 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
42. Microsoft SQL Server – Volatile Artifacts; Data Cache Incorporating SQL Artifacts into Investigations Page 42
43. 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!
44. Microsoft SQL Server – Volatile Artifacts; Data Cache Incorporating SQL Artifacts into Investigations Page 44
45. 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!
46. • 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
47. • 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
48. • 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
49. Microsoft SQL Server – Volatile Artifacts; Data Cache Incorporating SQL Artifacts into Investigations Page 49
50. 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!
51. 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
52. 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
53. 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!
54. 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/
55. • 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
56. • 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
57. • 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
58. • 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
59. • 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
60. • 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
61. Traditional SQL Databases and Their Artifacts PostgreSQL Incorporating SQL Artifacts into Investigations Page 61
62. PostgreSQL – Who’s Using It? Incorporating SQL Artifacts into Investigations Page 62
63. • 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
64. • 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
65. • 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
66. 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
67. 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
68. 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)
69. 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
70. 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
71. 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
72. 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?
73. 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
74. 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
75. 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
76. 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.
77. 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
78. Traditional SQL Databases and Their Artifacts MySQL Incorporating SQL Artifacts into Investigations Page 78
79. • 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
80. • 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
81. • 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
82. • 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
83. • 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
84. • 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
85. • Logging… • By default, logging is not enabled • Except error logs on Windows MySQL - Logging Incorporating SQL Artifacts into Investigations Page 85
86. • 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
87. • 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
88. SQLite Incorporating SQL Artifacts into Investigations Page 88
89. SQLite Applications and Usage Incorporating SQL Artifacts into Investigations Page 89
90. • 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
91. SQLite Parse and Read SQLite Artifacts Incorporating SQL Artifacts into Investigations Page 91
92. • 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
93. 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
94. 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
95. 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
96. 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)
97. Q&A Incorporating SQL Artifacts into Investigations Page 97
98. www.encase.com/ceic Thank You!