forensic analysis of mysql db systems - sans.org · (ibm systems journal vol. 12, iss. 1) 6 / 58....
TRANSCRIPT
![Page 1: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/1.jpg)
Forensic Analysis of MySQL DB SystemsMarcel Niefindt | SANS DFIR Prague 2014Prague, 05.10.2014
![Page 2: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/2.jpg)
whoami
Abstract
Marcel Niefindt
28 years old
M.Sc. in Security Management
Profession
Information Security Officer
Security Consultant
Lecturer
IT-Security Speaker
Security Focus
Network & Web-App Security
Database Forensic
Threat Modeling
ISMS
2 / 58
![Page 3: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/3.jpg)
What you will get
3 / 58
![Page 4: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/4.jpg)
What you will miss
4 / 58
![Page 5: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/5.jpg)
Road map
5 / 58
MySQL Basics
Defined Post-Mortem process (with hints & tips)
Useful artefacts
References to other cool MySQL-Forensics projects
Your chance to get involved into a nice project
![Page 6: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/6.jpg)
MySQL Basics
Relational Database Systemby Codd in 1960th / 70th
Likely structured as 5-Layer Model
IBM Prototype „System R“ by Härder (1987)
based on idea of Senko (1973)(IBM Systems Journal Vol. 12, Iss. 1)
6 / 58
![Page 7: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/7.jpg)
Basic System
7 / 58
Data
Table
Database
Database System
Operating System
![Page 8: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/8.jpg)
5-Layer Model
DB-Application- Website with SQL-Stmts- Connector to MySQL
Connection-Manager- Session Management
Query Processing- Query Cache- Parser- Security Manager- Optimizer- Execution Engine
Storage Engines- MyISAM / InnoDB- Transaction Management- Recovery Management
File System / Main Storage
(„MySQL 5.6: Das umfassende Handbuch“, Pröll et al., 2013, S. 154)8
![Page 9: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/9.jpg)
Forensical Methods
Post-Mortem
AnalyseLive-Analyse
Hybride-Analyse
Taking candy from a baby vs. Heart surgery
9 / 58
![Page 10: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/10.jpg)
Post-Mortem Process
Many defined Post-Mortem processes
„SQL Server Forensic Analysis“, by Kevvie Fowler
„Computer Forensik“, by Alexander Geschonneck
…
I compared them and defined my own
Preparation Verification Analysis Evaluation Rework
10 / 58
![Page 11: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/11.jpg)
Preparation Preparation Verification Analysis Evaluation Rework
11 / 58
![Page 12: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/12.jpg)
Verification Preparation Verification Analysis Evaluation Rework
Without verification it could cost you a lot of money
Time is money, you may safe a lot of time
The results in this phase give you an approach for the rest process
12 / 58
![Page 13: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/13.jpg)
Verification Preparation Verification Analysis Evaluation Rework
13 / 58
![Page 14: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/14.jpg)
Verification Preparation Verification Analysis Evaluation Rework
Results
Plausibility
Is it urgent
Do we need an Incident Response Process
How many systems are involved
What will be our further process
14 / 58
![Page 15: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/15.jpg)
Analysis Preparation Verification Analysis Evaluation Rework
15 / 58
![Page 16: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/16.jpg)
Analysis Preparation Verification Analysis Evaluation Rework
16 / 58
![Page 17: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/17.jpg)
Analysis Preparation Verification Analysis Evaluation Rework
17 / 58
![Page 18: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/18.jpg)
Analysis Preparation Verification Analysis Evaluation Rework
System time
18 / 58
![Page 19: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/19.jpg)
Analysis
System time
Preparation Verification Analysis Evaluation Rework
19 / 58
(http://www.hgst.com, Accessed 02.10.2014)
![Page 20: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/20.jpg)
Analysis
System time – Example Firefox
FF safes properties, visited websites etc in the profile directory.C:\Users\Johnny Cash\AppData\Roaming\Mozilla\Firefox\Profiles\eyv1b2pj.default
FF Add-On SQLite-Manager helps to read the records via SQL-Statementscookies.sqlplaces.sql
SELECT host, datetime(lastAccessed/1000000, 'unixepoch') FROM moz_cookies order by lastAccessed
Preparation Verification Analysis Evaluation Rework
20 / 58
![Page 21: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/21.jpg)
Analysis
MAC Times
Preparation Verification Analysis Evaluation Rework
21 / 58
![Page 22: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/22.jpg)
Analysis
MAC Times
Preparation Verification Analysis Evaluation Rework
22 / 58
![Page 23: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/23.jpg)
Analysis
MAC Times
find /home –ctime 1 –atime 1 –mtime 1 –printf “ %p;%Tx;%TT;%Ax;%AT;%Cx;%CT;\n” >> mac_time.txt
/var/lib/mysql
/var/log/apache2
/var/log
/home
/root
/
Preparation Verification Analysis Evaluation Rework
23 / 58
![Page 24: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/24.jpg)
Analysis
MAC Times
find /home –ctime 1 –atime 1 –mtime 1 –printf “ %p;%Tx;%TT;%Ax;%AT;%Cx;%CT;\n” >> mac_time.txt
Preparation Verification Analysis Evaluation Rework
24 / 58
![Page 25: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/25.jpg)
Analysis
Log Files
Apache Log-Files
MySQL Log-Files
Auth.log
Dmesg
Kern.log
Udev
syslog
Preparation Verification Analysis Evaluation Rework
25 / 58
![Page 26: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/26.jpg)
Analysis Preparation Verification Analysis Evaluation Rework
26 / 58
![Page 27: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/27.jpg)
Analysis
Query Cache
Optimization of return time
Saves SQL-Statements as Hash Valuesnot so useful
Statistical values could be usefulQcache_hits, Qcache_not_cached …
If the attacker adds „SQL_NO_CACHE“ to the Statement´,the statement will not be logged
Preparation Verification Analysis Evaluation Rework
(„MySQL 5.6: Das umfassende Handbuch“, Pröll et al., 2013, S. 154)
27 / 58
![Page 28: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/28.jpg)
Analysis Preparation Verification Analysis Evaluation Rework
Query Cache
Optimization of return time
Saves SQL-Statements as Hash Valuesnot so useful
Statistical values could be usefulQcache_hits, Qcache_not_cached …
If the attacker adds „SQL_NO_CACHE“ to the Statement´,the statement will not be logged
(„MySQL 5.6: Das umfassende Handbuch“, Pröll et al., 2013, S. 154)
28 / 58
![Page 29: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/29.jpg)
Analysis
RAM
Preparation Verification Analysis Evaluation Rework
29 / 58
![Page 30: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/30.jpg)
Analysis
RAM
Preparation Verification Analysis Evaluation Rework
30 / 58
m.y.s.q.l
-.+.
![Page 31: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/31.jpg)
Analysis
DB structure
/var/lib/<database>/
Database tables ends with .frm
If the option innodb_file_per_table is active (default in MySQL 5.6)
InnoDB tables have a second file .ibd
MyISAM with .MYD & .MYI
MEMORY only has .from (stores data in RAM)
Preparation Verification Analysis Evaluation Rework
31 / 58
![Page 32: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/32.jpg)
Analysis
DB structure
What if innodb_file_per_table is not active?
Preparation Verification Analysis Evaluation Rework
32 / 58
![Page 33: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/33.jpg)
Analysis
DB structure reconstruction example (.frm File)
Offset 0x03 describes the Storage Engine0x09 == MyISAM0x0c == InnoDB0x06 == MEMORY
More values in the Source Code/sql/handler.h – Lines 374 – 397 (Revision 5585)Enum „legacy_db_type“
Preparation Verification Analysis Evaluation Rework
(„InnoDB Database Forensics“, Frühwirt et al., 2010, S. 2)
33 / 58
![Page 34: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/34.jpg)
Analysis
DB structure reconstruction example (.frm File)
Information about the references (keys) start at 0x10000x1001 == column in table0x1002 == number of keys0x1018 == 7 Byte with type of key (PK / FK)
Preparation Verification Analysis Evaluation Rework
(„InnoDB Database Forensics“, Frühwirt et al., 2010, S. 3)
34 / 58
![Page 35: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/35.jpg)
Analysis
DB structure reconstruction example (.frm File)
Information of columns are defined between 0x2100 and EOF
0x2102 has 2 bytes with the number of fields (columns) in the table
Definition of columns types do not have a specific starting point
So let’s do some math…
Preparation Verification Analysis Evaluation Rework
35 / 58
![Page 36: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/36.jpg)
Analysis
DB structure reconstruction example (.frm File)
Each column is defined within 17 byte
At EOF you find the column names
Column names are separated by the value FF
𝑠𝑡𝑎𝑟𝑡 = 𝐸𝑂𝐹 − (𝑟𝑒𝑎𝑑 0𝑥2102 ∗ 𝑓𝑖𝑛𝑑 ff + 1) − 17 𝑏𝑦𝑡𝑒 ∗ 𝑟𝑒𝑎𝑑(0𝑥2102)
Offset 0x0D within a 17 byte field defines the column type
/include/mysql_com.h has all valuesenum “enum_field_type” in rows 369 - 392
Preparation Verification Analysis Evaluation Rework
36 / 58
(„InnoDB Database Forensics“, Frühwirt et al., 2010, S. 4ff)
![Page 37: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/37.jpg)
Analysis
DB structure reconstruction example (.frm File)
𝑠𝑡𝑎𝑟𝑡 = 𝐸𝑂𝐹 − (𝑟𝑒𝑎𝑑 0𝑥2102 ∗ 𝑓𝑖𝑛𝑑 ff + 1) − 17 𝑏𝑦𝑡𝑒 ∗ 𝑟𝑒𝑎𝑑(0𝑥2102)
Preparation Verification Analysis Evaluation Rework
37 / 58
![Page 38: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/38.jpg)
Analysis
Reconstruction of SQL Manipulation Statements
Manipulation statements are Insert, Update, Delete
Just look in /home/<someUser>/.mysql_history
How easy is that, right?
Preparation Verification Analysis Evaluation Rework
38 / 58
![Page 39: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/39.jpg)
Analysis
Reconstruction of SQL Manipulation Statements
Preparation Verification Analysis Evaluation Rework
39 / 58
![Page 40: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/40.jpg)
Analysis
Reconstruction of SQL Manipulation Statements
Normal user have access rights!
Preparation Verification Analysis Evaluation Rework
40 / 58
![Page 41: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/41.jpg)
Analysis
Reconstruction of SQL Manipulation Statements
Use these log-files/var/lib/mysql/ib_logfile0/var/lib/mysql/ib_logfile1/var/lib/mysql/ibdata1
„InnoDB Database Forensics: Reconstructing Data Manipulation Queries from Redo Logs“ by Frühwirt et al., 2012
Preparation Verification Analysis Evaluation Rework
41 / 58
![Page 42: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/42.jpg)
Analysis
Reconstruction of SQL Manipulation Statements
The ib_logfileX
Preparation Verification Analysis Evaluation Rework
(„InnoDB Database Forensics: Reconstructing Data Manipulation Queries from Redo Logs“, Frühwirt et al., 2012, S. 2)
42 / 58
![Page 43: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/43.jpg)
Analysis
Reconstruction of SQL Manipulation Statements
Preparation Verification Analysis Evaluation Rework
(„InnoDB Database Forensics: Reconstructing Data Manipulation Queries from Redo Logs“, Frühwirt et al., 2012, S. 2 ff)
43 / 58
![Page 44: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/44.jpg)
Analysis
Reconstruction of SQL Manipulation Statements
Beware!The log-block is not consistent like the log-block-header or the .frm-files
It depends on the Storage Engine AND the manipulation statements
Update / Delete == mlog_undo_insert entries (Starts with Offset 0x14)Insert == mlog_comp_rec_insert entries (starts with offset 0x26)
Preparation Verification Analysis Evaluation Rework
44 / 58
![Page 45: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/45.jpg)
Analysis
Reconstruction of SQL Manipulation Statements
Beware²!If there is only 1 entry / page we have an OR conjunction with the flag
mlog_single_rec_flag (0x80)
So the entry would start with 0x94 not 0x14
All log entry types are defined in /storage/innobase/include/mtr0mtr.h (lines 65 –189)
Preparation Verification Analysis Evaluation Rework
45 / 58
![Page 46: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/46.jpg)
Analysis
Reconstruction of SQL Manipulation Statements
Preparation Verification Analysis Evaluation Rework
46 / 58
![Page 47: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/47.jpg)
Analysis Preparation Verification Analysis Evaluation Rework
Reconstruction of SQL Manipulation Statements
47 / 58
![Page 48: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/48.jpg)
Analysis
Python will do it for you
Frm_parser.pyReconstructing the database structure by parsing the .frm files
Iblogfile_parser.pyReconstructing the sql manipulation statements by using ib_logfile(0|1) &
ibdata1 files
Scripts are available at https://github.com/KasperFridolin/mysql_forensics Unfortunately not ready for productive use, now Let’s say it is a prototype with a lot of “challenges”
Preparation Verification Analysis Evaluation Rework
48 / 58
![Page 49: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/49.jpg)
frm_parser.py
49 / 58
/var/lib/<database>/
0x1 0xf
0x4 0xb
0x10xd
0xa 0x3
Table1.frm Table2.frm Table3.frm Table4.frm
Frm_parser.py
Preparation Verification Analysis Evaluation Rework
![Page 50: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/50.jpg)
50 / 58
/var/lib/mysql/
0x1 0xf
0x4 0xb
0xa 0x3
ib_logfile0 ib_logfile1 ibdata1
iblogfile_parser.py
iblogfile_parser Preparation Verification Analysis Evaluation Rework
![Page 51: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/51.jpg)
Evaluation
From single information to meta-level
Preparation Verification Analysis Evaluation Rework
51 / 58
![Page 52: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/52.jpg)
09:30 15:00
Posteingang
Postausgang
09:57
Von [email protected]: Urlaub zuende?!
10:00
Re: Urlaub zuende?!An [email protected]
10:02
Von [email protected]: Urlaub zuende?!
10:48
Von [email protected] bearbeiten
10:55
Re: Bitte bearbeitenAn [email protected]
11:03
11:04
Re: MittagAn [email protected]
11:58
Von [email protected]: Bitte bearbeiten
13:34
Von [email protected]: Bitte bearbeiten
13:37
Re: Bitte bearbeitenAn [email protected]
13:39
Von [email protected]: Bitte bearbeiten
13:45
Fw: Bitte bearbeitenAn [email protected]
14:40
Re: Urlaub zuende?!An [email protected]
14:41
Re: Urlaub zuende?!An [email protected]
10:29
Re: Urlaub zuende?!An [email protected]
16.12.2010
Evaluation
From single information to meta-level
Preparation Verification Analysis Evaluation Rework
52
![Page 53: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/53.jpg)
Evaluation
Do your report!
Preparation Verification Analysis Evaluation Rework
53 / 58
![Page 54: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/54.jpg)
Rework
After the game is before the game
Preparation Verification Analyses Evaluation Rework
54 / 58
(Sepp Herberger)
![Page 55: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/55.jpg)
Rework
Motivation!
Preparation Verification Analysis Evaluation Rework
55 / 58
![Page 56: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/56.jpg)
Rework
Motivation!
Preparation Verification Analysis Evaluation Rework
56 / 58
![Page 57: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/57.jpg)
What‘s up next?
Get involved!
Reverse Engineering
Code review
Implementation of new features (or bugs)
Shift bugs into features
Chatting about other cool forensic stuff
And so on
And so on
57 / 58
![Page 58: Forensic Analysis of MySQL DB Systems - sans.org · (IBM Systems Journal Vol. 12, Iss. 1) 6 / 58. Basic System 7 / 58 Data Table Database Database System Operating System. 5-Layer](https://reader030.vdocuments.us/reader030/viewer/2022040312/5e030240d9e2ea2f204154aa/html5/thumbnails/58.jpg)
Thank you for your kind attention!