SQLite
SQLite: 서버 없이도 동작하는 로컬 데이터베이스 관리 시스템으로, 오프라인 상황에서 저장되는 데부분의 데이터는 SQLite에 저장된다. 본 글은 SQLite의 구조를 디지털 포렌식의 관점(삭제, 복구, 복구 가능 여부)에서 바이너리 단위로 분석한 글이다.
SQLite Structure
SQLite는 페이지의 집합으로, 페이지는 SQLite가 데이터를 저장하는 물리적인 단위이다. SQLite에서 Page는 크게 Header Page와 일반 Page로 나뉜다. Header Page는 1번 페이지로 SQLite DataBase 정보와 Schema Table 정보를 포함하고, 일반 Page는 1번 Page를 제외한 모든 페이지(Page 2 ~ N)로 Table Cell 정보를 포함한다.
Page Structure
페이지는 Page Header와 여러 개의 Cell로 이루어져 있다. 이때 Page Header는 해당 페이지 정보와, 해당 페이지 셀 주소 정보를 포함하고, 셀은 해당 페이지의 테이블 레코드 정보를 포한한다. 페이지는 셀 데이터를 저장할 때 페이지의 끝 영역부터 즉, FIFO(First In First Out)의 형태로 데이터를 저장한다. 또한 SQLite는 하나의 테이블을 관리할 때 기본적으로 하나의 페이지가 할당되지만 테이블의 셀 데이터가 많아 하나의 페이지에 전부 담지 못하는 경우, OverFlow Page를 추가적으로 할당하여 테이블의 셀 데이터를 추가 관리한다.
추가적으로 SQLite는 B-Tree 구조로 데이터를 관리하며, B-Tree의 Index 하나가 Page 하나로 대체된다. 때문에 페이지가 특정 정렬을 기준으로 서로 연결되어 있다. 이때 하위 Page가 존재하는 Page를 Internal Page라고 하며, 하위 Page가 존재하지 않는 Page를 Leaf Page라고 한다. 해당 문단의 내용은 SQLite를 포렌식의 관점에서 분석하는 데에는 적합하지 않아 자세히 다루지 않겠다.
Page Structure
구조 분석에 사용할 SQLite를 생성하였다. 아래는 SQLite 내부 테이블의 모습으로, 테이블은 하나만 존재한다.
1. Header Page Structure
Header Page는 SQLite의 1번 페이지로 SQLite DataBase 정보와 Schema Table 정보를 포함한다.
아래는 SQLite Structure 분석에 사용한 실제 SQLite의 Header Page 모습과 Header Page Field를 정리한 내용이다. 추가적으로 SQLite 분석에 중요한 정보는 Field의 배경을 회색으로 표시하고, 해당 Field의 내용을 표로 추가 정리하였다.
주요 확인 가능 정보
Page Size: 0x1000 (빅엔디안)
→
페이지의 크기가 4,096 Byte 즉, 8 Sector이다.
DataBase Size: 0x02 → 해당 SQLite는 총 2개의 Page를 갖는다. (SQLite는 총 16 Sector)
Text Encoding: UTF-8
(Page Header 구조는 아래에 정리해 두었다.)
| Size (Byte) | Info |
| 2 | Page Size (페이지 크기 정보 (빅엔디안 해석)) |
| 4 | File Change Counter (데이터 변경 횟수 정보) |
| 4 | DataBase Size (해당 SQLite의 총 페이지 갯수 정보) |
| 4 | Free Page Offset (가용 상태의 페이지 주소 정보) |
| 4 | Free Page Number (가용 상태의 페이지 갯수 정보) |
| 4 | Text Encoding (0x01: UTF-8, 0x02: UTF-16 LE, 0x03: UTF-16 BE) |
| 4 | Auto Vacuum Mode (데이터 삭제 시 ZeroFill 여부, 0x00: 적용 X, 0x01: 적용 O) |
| Dynamic | Page Header |
Page Header
Page Header는 항상 페이지의 처음에 위치하며, 해당 페이지 정보와 해당 페이지 셀 주소 정보를 포함한다. 단, Header Page의 경우 SQLite의 헤더 시그니처가 먼저 나타나고, 이후에 Page Header가 나타나기 때문에 Page Header는 0x64부터 나타난다.
추가적인 특징으로는 Header Page의 경우 Cell Pointer가 Schema Table의 시작 주소를, 일반 Page의 경우 Cell Pointer가 Cell의 시작 주소 정보를 포함한다.
이전에 확인하지 못한 Header Page의 Page Header를 분석하면 다음과 같다
주요 확인 가능 정보
Page Flag: 0x0D → Leaf Page이다.
Number of Record: 0x01 → 해당 페이지는 1개의 Record를 갖는다. 즉, 1개의 Cell을 갖고, 1개의 Cell Pointer를 갖는다.
Cell Pointer: 0x0F9F → 해당 페이지의 Cell 시작 주소가 해당 페이지의 시작 주소 '0x0000' + '0x0F9F'인 '0x0F9F'임을 의미한다. 현재 분석 중인 Page는 Header Page 이므로, 해당 Cell의 내용은 Schema Table 정보가 된다.
| Size (Byte) | Info |
| 1 | Page Flag (0x05: Internal Page, 0x0D: Leaf Page) |
| 2 | Number of Record (레코드 갯수 정보) |
| 2 | Offset of First Bytes of Record (첫 레코드 시작 주소 (빅엔디안 해석)) (B-Tree 정렬에 의한 첫 레코드) |
| 4 | Page Number of Right Most Child-Page (Internal Page의 경우 존재) (Internal Page의 경우: 하위 페이지의 가장 오른쪽 페이지 번호) |
| 2 | Cell Pointer (빅엔디안 해석) |
Table Schema
Table Schema는 해당 SQLite의 모든 테이블의 항목과 각 항목의 데이터 타입 정보를 포함한다. HxD에서 'Decoded Text' 항목을 확인하면 어느 정도 Table Schema 정보 해석이 가능하다.
추가로, Table Schema의 시작 주소가 해당 페이지의 Page Header의 Cell Pointer 값(0x0F9F)과 동일한 모습을 확인할 수 있다.
2. Page
일반 Page는 1번 Page (Header Page)를 제외한 모든 페이지(Page 2 ~ N)로 Table Cell 정보를 포함한다. 일반 Page의 경우 SQLite의 헤더시그니처가 없는 영역이기 때문에, Page Header가 Page의 가장 처음에 존재한다.
아래는 일반 페이지인 두 번째 페이지 즉, 분석을 위해 생성한 테이블의 셀을 저장하는 Page를 분석한 내용이다.
확인 가능 정보
Page Flag: 0x0D → Leaf Page이다.
Number of Record: 0x01 → 해당 페이지는 1개의 Record를 갖는다. 즉, 1개의 Cell을 갖고, 1개의 Cell Pointer를 갖는다.
Cell Pointer: 0x0F9F → 해당 페이지의 Cell 시작 주소가 해당 페이지의 시작 주소 '0x0000' + '0x0F9F'인 '0x0F9F'임을 의미한다. 현재 분석 중인 Page는 Header Page 이므로, 해당 Cell의 내용은 Schema Table 정보가 된다.
Page Flag: 0x0D → Leaf Page이다.
Number of Record: 0x03 → 해당 페이지는 3개의 Record를 갖는다. 즉, 3개의 Cell을 갖고, 3개의 Cell Pointer를 갖는다.
Offset of First Bytes of Record: 0x0FB7 → B-Tree로 정렬된 테이블의 첫 Record의 시작 주소가 '0x1FB7'이다.
Cell Pointer: 0x0FE8, 0x0FCF, 0x0FB7
→ B-Tree로 정렬된 테이블의 첫 번째 Record의 시작 주소: 0x1FE8 ('0x1000' + '0x0FE8')
→ B-Tree로 정렬된 테이블의 두 번째 Record의 시작 주소: 0x1FCF ('0x1000' + '0x0FCF')
→ B-Tree로 정렬된 테이블의 세 번째 Record의 시작 주소: 0x1FB7, SQLite가 레코드 검색 시 처음으로 접근하는 레코드이다. 때문에 First Bytes of Record와 동일한 주소 값을 갖는다.
Page는 FIFO 방식으로 Cell을 저장하기 때문에, Cell 저장 순서는 B-Tree로 정렬된 순서가 아니라 각 Row ID 순서대로 저장된다.
아래는 3개의 Cell을 각각 다른 색으로 구분한 모습이다. 추가적으로 각 Cell의 시작 주소가 Page Header의 Cell Pointer 값과 일치함을 확인할 수 있다.
(Cell 구조는 아래에 정리해 두었다.)
Cell Structure
Cell은 하나의 Record를 포함하고 추가로, 해당 Record의 Row ID, Record 크기 정보를 포함하는 데이터 구조이다.
이전에 확인하지 못한 Cell 중 첫 번째 Cell을 분석하면 다음과 같다.
주요 확인 가능 정보
Length of Record: 0x16 → Record의 크기가 0x16이다.
→ Cell의 크기는 0x18이다. (0x16(Record 크기) + 0x01(Length of Record 크기) + 0x01(Row ID 크기))
→ (Length of Record의 해석법은 아래에 정리해 두었다.)
Row ID: 0x03 → 해당 Record는 Table의 세 번째 Record이다.
→ (Row ID의 해석법은 Length of Record의 해석법과 같다.)
Length of Data Header: 0x04 → Data Header의 크기가 0x04이다.
Size of Cell 1: 0x13 → Cell 1의 크기는 3((0x13 - 0x0C) / 2)이다.
Size of Cell 2: 0x1B → Cell 2의 크기는 7((0x1B - 0x0C) / 2)이다.
Size of Cell 3: 0x1D → Cell 3의 크기는 8((0x1D - 0x0C) / 2)이다.
Data of Cell 1: 0x4C 65 65 → Cell 1의 데이터는 'Lee'이다.
Data of Cell 2: 0x32 30 32 33 30 30 33 → Cell 2의 데이터는 '2023003'이다.
Data of Cell 3: 0x53 65 63 75 72 69 79 → Cell 3의 데이터는 'Security'이다.
(Cell Size 해석법은 아래에 정리해 두었다.)
| Size (Byte) | Info |
| Dynamic | Length of Record (Record 크기) |
| Dynamic | Row ID (Table의 Record 번호) |
| Dynamic | Length of Data Header (Data Header 크기) |
| 1 | Size of Cell (Cell 크기) |
| Dynamic | Data of Cell (Cell 크기) |
Length of Record & Row ID Calculation Method
Cell에서 Length of Record와 Row ID의 크기를 해석하는 방법은 크기가 1Byte인 경우와 1Byte가 넘는 경우로 나뉜다.
1. 크기가 1Byte인 경우: 2진수 변환 시 하위 7 Bit만으로 해석한다. 이때 가장 상위 비트(8번 째 비트)는 '0'으로 고정된다.
2. 크기가 1Byte가 넘는 경우: 가장 하위 Byte를 제외한 Byte를 2진수 변환 시 해당 Byte의 하위 7 Bit만을 사용한다. 이때 해당 Byte의 가장 상위 Bit(8번째 비트)는 '1'로 고정되고, 가장 하위 Byte를 2진수 변환시 해당 Byte의 하위 7 Bit만으로 사용한다. 이때 해당 Byte의 가장 상위 Bit(8번째 비트)는 '0'로 고정된다. 이렇게 추출된 각 Byte의 7 Bit씩을 Bit 단위로 서로연결하여 연결된 Bit 값을 해석한다.
아래는 0xA69266을 예시로 해석한 과정이다.
1. Byte 분류: 0xA6, 0x92, 0x66으로 나뉜다. 이때 상위 Byte는 0xA6, 0x92이 되고, 가장 하위 Byte는 0x66이된다.
2. 이진수로 변환:
→ 0xA6 = 1010 0110 (2) → 가장 상위 Bit가 '1'임을 확인할 수 있다. 추가로 추출된 7 Bit는 '0100110'이다.
→ 0x92 = 1001 0010 (2) → 가장 상위 Bit가 '1'임을 확인할 수 있다. 추가로 추출된 7 Bit는 '0010010'이다.
→ 0x66 = 0110 0110 (2) → 가장 상위 Bit가 '0'임을 확인할 수 있다. 추가로 추출된 7 Bit는 '1100110'이다.
3. 추출된 7 Bit 연결: '0100110', '0010010', '1100110'를 연결하면, '1001 1000 1001 0110 0110'이 된다. 이는 16진수로 '0x98966'이다.
Size of Cell Calculation Method
Size of Cell에서 Cell 크기를 해석하는 방법은 다음 표와 같다.
| Value | Cell Type | Cell Size |
| 0 | NULL | 0 |
| N (1 ~ 4) | Signed Integer | N |
| 5 | 6 | |
| 6 | 7 | |
| 7 | IEEE Float | 8 |
| 8 ~ 11 | Reserved | |
| N > 12 (Even) |
BLOB | (N - 12) / 2 |
| N > 13 (Odd) |
TEXT | (N - 13) / 2 |
다음 글
[Digital Forensic] SQLite Journal & WAL Structure Analysis
[Digital Forensic] SQLite Journal & WAL Structure Analysis
SQLite Journaling Journaling: SQLite의 트랜잭션이 발생하면 SQLite의 일관성을 보장하기위해 백업하는 기법을 의미한다. 트랜잭션 과정에서 예상치 못한 문제로인해 트랜잭션이 비정상적으로 종료되면,
digitalforensicmaster.tistory.com
Reference
https://www.dbpia.co.kr/journal/articleDetail?nodeId=NODE01646088
SQLite 데이터베이스의 비 할당 영역에 잔존하는 삭제된 레코드 복구 기법 | DBpia
전상준, 변근덕, 방제완, 이근기, 이상진 | 정보보호학회논문지 | 2011.06
www.dbpia.co.kr