FORENSICINSIGHT SEMINAR SQLite Recovery zurum herosdfrc@google.co.kr
Contents 1. SQLite! 2. SQLite 구조 3. 레코드의삭제 4. 삭제된영역추적 5. 레코드복원기법 forensicinsight.org Page 2 / 22
SQLite! - What is.. - and why? forensicinsight.org Page 3 / 50
SQLite! What is.. DataBase! (Local DB) Open Source & Library Small & Fast SQLite & MySQL, MSSQL, Oracle.. etc RDBMS 의장점계승 표준 SQL 쿼리사용 SQLite & plist, xml.. etc 어플리케이션단위운용 활용방안유사 운용데이터저장및활용 forensicinsight.org Page 4 / 22
SQLite! 약진! forensicinsight.org Page 5 / 22
SQLite! Digital Forensics & SQLite Analysis 포렌식분석가와 SQLite 분석?! 활용도 스마트폰포렌식 Mac Artifact 분석 SQLite 파일구조의특징 다소복잡하고체계적인방법으로레코드를적립 구조를분석하여레코드복원을해보는것으로파일구조분석및복원에대한학습에효과적 forensicinsight.org Page 6 / 22
Structure of SQLite DataBase - File Structure - Page Structure - Cell Structure forensicinsight.org Page 7 / 50
SQLite DataBase File Structure SQLite DataBase File 전체구조 페이지단위 (Number of header page == 1) Page 1 (Header Page) Page 2,3,4.. (Table B-Tree, Index B-Tree, Free, OverFlow) forensicinsight.org Page 8 / 22
SQLite DataBase File Structure SQLite DataBase File Header Page DataBase Header Page 1 (Header Page) SQLite DataBase Signature : SQLite 데이터베이스식별정보 Page Size : 페이지크기 (0x200 alignment) Text Encoding : 문자열인코딩 ( 0: UTF-8, 1: UTF-16 LE, 2: UTF-16 BE ) Auto Vacuum mode : 삭제시데이터자동정리여부 forensicinsight.org Page 9 / 22
SQLite DataBase File Structure SQLite DataBase File Header Page Schema String Page 1 (Header Page) 생성시의 Query 형태 ( 문자열 ) 로저장 복원시필드명확인에필요 ( 가능할경우획득 ) forensicinsight.org Page 10 / 22
SQLite DataBase File Structure SQLite DataBase File Pages in the SQLite database file Pages Pages for Table B-Tree(Main target) Page 1 Pages for Index B-Tree(Get Row ID) Overflow Page Free Page Page 2 Page 3 Page 4 forensicinsight.org Page 11 / 22
Table B-Tree Overview - Tree 의각노드 - 하나의페이지 - Internal 페이지 - 하위페이지의번호를포함 - Leaf 페이지 - 실제데이터 ( 레코드 ) 를포함 forensicinsight.org Page 12 / 22
Pages Page Structure Page Header Cell offset 0 Cell offset 1 Cell offset 2 Free Space Page Header Table b-tree Offset 0 0x05 - Internal page 0x0D Leaf page Size 12 Byte Internal page 8 Byte - Leaf page Cell Offset 2byte Big endian integer Cell 2 Cell 1 Cell 0 forensicinsight.org Page 13 / 22
Pages Page headers Internal Page header Page Flag(Internal : 0x05, Leaf : 0x0D) offset of first block of free space Number of record Offset of the first bytes of the record Num of fragmented free bytes Page number of right most child-page (Intenal page only) forensicinsight.org Page 14 / 22
Pages Page Header 0x200 + 0x134 = 0x334 0x200 + 0xE9= 0x2E9 Cell Offsets Cells Links forensicinsight.org Page 15 / 22
Cells Internal Cell Internal Cell 트리순회를위해존재 Child page number Var Int(Unknown) forensicinsight.org Page 16 / 22
Cells Internal Cell Simulation(B-Tree Traverse) mmssms.db Header Page Root Page 확인 Internal Page Flag 확인 Root(Internal Page) Cell 위치확인 forensicinsight.org Page 17 / 22
Cells Internal Cell Simulation(B-Tree Traverse) mmssms.db Root(Internal Page) 자식페이지번호확인 Data Page Flag 확인 Leaf Page forensicinsight.org Page 18 / 22
Cells Leaf Cell Cell Header and Variable length integer Cell Header Row ID Field 1 Field 2... Field N Data of Field 1 Data of Field 2... Data of Field N Variable length integer 2 Byte Byte Value range Bit pattern 1 7 bit 0XXXXXXX 2 14 bit 1XXXXXXX 0XXXXXXX 3 21 bit 1XXXXXXX 1XXXXXXX 0XXXXXXX 4 28 bit 1XXXXXXX 1XXXXXXX 1XXXXXXX 0XXXXXXX 5 35 bit 1XXXXXXX 1XXXXXXX 1XXXXXXX 1XXXXXXX 0XXXXXXX 6 42 bit 1XXXXXXX 1XXXXXXX 1XXXXXXX 1XXXXXXX 1XXXXXXX 0XXXXXXX 7 49 bit 1XXXXXXX 1XXXXXXX 1XXXXXXX 1XXXXXXX 1XXXXXXX 1XXXXXXX 0XXXXXXX 8 56 bit 1XXXXXXX 1XXXXXXX 1XXXXXXX 1XXXXXXX 1XXXXXXX 1XXXXXXX 1XXXXXXX 0XXXXXXX 9 64 bit 1XXXXXXX 1XXXXXXX 1XXXXXXX 1XXXXXXX 1XXXXXXX 1XXXXXXX 1XXXXXXX 1XXXXXXX XXXXXXXX forensicinsight.org Page 19 / 22
Cells Leaf Cell and transfer table Cell Header Row ID Field 1 Field 2... Field N Data of Field 1 Data of Field 2... Data of Field N Variable length integer 2 Byte Value Data Type Data Size 0 NULL 0 N (N=1-4) Signed Integer N 5 6 6 Signed Integer 8 7 IEEE float 8 8-11 Reserved N>12 (N:even) N>13 (N:odd) BLOB TEXT (N-12)/2 (N-13)/2 forensicinsight.org Page 20 / 22
Cells Leaf Cell Simulation(Cell parsing) mmssms.db Cell Header Row ID Field 1 Field 2... Field N Data of Field 1 Data of Field 2... Data of Field N Variable length integer 2 Byte (182) 0x8136(var Int) 10000001 001101102 (var Int) 101101102 182 Start of Cell forensicinsight.org Page 21 / 22
Cells Leaf Cell Simulation(Cell parsing) mmssms.db Cell Header Row ID Field 1 Field 2... Field N Data of Field 1 Data of Field 2... Data of Field N Variable length integer 2 Byte Row ID(1807) (182) 0x8E0F(var Int) 10001110 000011112 (var Int) 111000011112 1807 forensicinsight.org Page 22 / 22
Cells Leaf Cell Simulation(Cell parsing) mmssms.db Cell Header Row ID Field 1 Field 2... Field N Data of Field 1 Data of Field 2... Data of Field N Variable length integer 2 Byte Row ID(1807) (182) (0x40-0x2 = 0x3E = 62) forensicinsight.org Page 23 / 22
Cells Leaf Cell Simulation(Cell parsing) mmssms.db Cell Header Row ID Field 1 Field 2... Field N Data of Field 1 Data of Field 2... Data of Field N 2 Byte Row ID(1807) (182) (0x40-0x2 = 0x3E = 62) Value Data Type Data Size 0 NULL 0 N (N=1-4) N 5 Signed Integer 6 6 8 7 IEEE float 8 8-11 Reserved N>12 (N:even) BLOB (N-12)/2 N>13 (N:odd) TEXT (N-13)/2 forensicinsight.org Page 24 / 22
Cells Leaf Cell Simulation(Cell parsing) mmssms.db Cell Header Row ID Field 1 Field 2... Field N Data of Field 1 Data of Field 2... Data of Field N Variable length integer 2 Byte Row ID(1807) (182) (0x40-0x2 = 0x3E = 62) 0x02 2 byte Signed Integer Data of Fields Area forensicinsight.org Page 25 / 22
Cells Leaf Cell Simulation(Cell parsing) mmssms.db Cell Header Row ID Field 1 Field 2... Field N Data of Field 1 Data of Field 2... Data of Field N Variable length integer 2 Byte Row ID(1807) (182) (0x40-0x2 = 0x3E = 62) 0x23 35 = (N*2)+13 N = 11 11 Byte text value Data of Fields Area forensicinsight.org Page 26 / 22
Cells Leaf Cell Simulation(Cell parsing) mmssms.db Cell Header Row ID Field 1 Field 2... Field N Data of Field 1 Data of Field 2... Data of Field N Variable length integer 2 Byte Row ID(1807) (182) (0x40-0x2 = 0x3E = 62) 0x05 6 byte Signed Integer Data of Fields Area forensicinsight.org Page 27 / 22
Cells Leaf Cell Simulation(Cell parsing) mmssms.db Cell Header Row ID Field 1 Field 2... Field N Data of Field 1 Data of Field 2... Data of Field N Variable length integer 2 Byte Row ID(1807) (182) (0x40-0x2 = 0x3E = 62) 0x01 1 byte Signed Integer Data of Fields Area forensicinsight.org Page 28 / 22
Cells Leaf Cell Simulation(Cell parsing) mmssms.db Cell Header Row ID Field 1 Field 2... Field N Data of Field 1 Data of Field 2... Data of Field N Variable length integer 2 Byte Row ID(1807) (182) (0x40-0x2 = 0x3E = 62) 0x8101(val int) 10000001 000000012(val int) 100000012 129 = (N*2)+13 N = 58 58 Byte text value Data of Fields Area forensicinsight.org Page 29 / 22
Index B-Tree Index B-Tree Index 생성시생성 Table B-Tree 와 Leaf Cell 의구조를제외할경우, 동일 Leaf Cell 셀최상단의길이정보가자기자신을포함 RowID 가최하단에위치 Cell Length of Cell Field 1 Field 2... Field N Data of Field 1 Data of Field 2... Data of Field N RowID Var Int 2 Byte forensicinsight.org Page 30 / 22
Overflow Page Overflow Page 연속된필드의값을한페이지안에전부담을수없는경우생성되는페이지 데이터를계산시그길이에미처도달하기전에끝나고마지막 4바이트가정수형태일경우 해당정수가가리키는페이지 (Overflow Page) 에잔여값존재 Overflow Page 의최상위 4 바이트는다음 Overflow Page 의번호 (0 일경우마지막 ) 레코드의길이에도달치못하고 4 바이트정수가나타난경우 해당정수의 page 에나머지데이터존재 0x200 * (3-1) = 0x400 Next overflow page number forensicinsight.org Page 31 / 22
Free Page Free Page Overflow page 와연결된필드가삭제된경우 Overflow page 는 free page 로전환 헤더에 Free page 관리를위한정보기록 forensicinsight.org Page 32 / 22
Thank you for listenig Q&A forensicinsight.org Page 33 / 22