The Internal Structures of SQLite Workshop

Mark McKinnon
Davenport University

Workshops

SQLite is a relational database that is embedded in just about every modern OS and device used today. This workshop will take a look at how the structures within a SQLite database work. The following will be discussed:

  1. Page sizes in the database and what they mean.
  2. Identify different types of B-Tree pages.
  3. How tables are created.
  4. How data is inserted, updated and deleted from tables.
  5. Internal structures of pages.
  6. Identifying modified and deleted structures.
  7. Identifying modified and deleted data and what tables they belong to.
  8. Examine different journal types used.

Examples of SQLite databases will be used throughout the workshop to explore the above concepts. At the end of the workshop there will be a Capture the Flag contest for participants to reinforce what was learned.

Requirements:

  • Windows/Linux laptop with your favorite hex editor installed.

Mark McKinnon
Mark McKinnon has over 28 years experience in IT. He started his career writing programs on a mainframe computer, then went on to do systems analysis, database administration, security audits and finally computer forensics. He received his computer forensic training from Key Computer Service through their training partnership with Kennesaw State University in Georgia. Mark is a Certified Computer Examiner (CCE) and an GIAC Certified Incident Handler through SANS. In 2005, Mark started RedWolf Computer Forensics and developed a program called “Drive Prophet” which is a triage program for Windows Systems. He has created many free programs used by forensic examiners around the world including Skype Log Parser, Google Chrome Parser, Windows Prefetch Parser, MFT Parser and the Vista Thumbcache Parser on which Mark holds a US copyright. Mark is currently an Assistant Professor at Davenport University where he teaches Digital Forensics, Cyber Defense and Computer Science. Mark is also a Forensic Examiner at DataExam LLC. Mark has written over 30 python plugins for Autopsy. He also took 1st place and 3rd place and 1st place in the OSDFCon 2015, 2016 and 2017 Autopsy Python plugin module competition. Mark has presented at the OSDFCon Conference, DoD Cybercrime conference, Sans What Works in Incident Response and Computer Forensics, and several regional conferences.