Read SQLite database from Notes, extract notes content to files

README.md

Links

Notes database location

Forensic tool

go.mod

module recup

go 1.15

require github.com/mattn/go-sqlite3 v1.14.5

main.go

package main

import (
	"bytes"
	"compress/gzip"
	"database/sql"
	"fmt"
	_ "github.com/mattn/go-sqlite3"
	"log"
	"os"
)

// length, skip
func ReadLengthField(blob []byte) (int, int) {

	length := 0
	skip := 0
	dataLength := int(blob[0])
	length = dataLength & 0x7F
	for dataLength > 0x7F {

		skip += 1
		dataLength = int(blob[skip])
		length = ((dataLength & 0x7F) << (skip * 7)) + length
	}

	skip += 1
	return length, skip

}

func ProcessNoteBodyBlob(blob []byte) []byte {
	data := []byte{}

	pos := 0
	//b'\x08\x00\x12': // header
	if !bytes.Equal(blob[0:3], []byte{0x08, 0x00, 0x12}) {
		fmt.Println("error missing or wrong header 1")
		return nil
	}

	pos += 3
	length, skip := ReadLengthField(blob[pos:])
	pos += skip

	//b'\x08\x00\x10': // header
	if !bytes.Equal(blob[pos:pos+3], []byte{0x08, 0x00, 0x10}) {
		fmt.Println("error missing or wrong header 2")
		return nil
	}

	pos += 3
	length, skip = ReadLengthField(blob[pos:])
	pos += skip

	// Now text data begins
	if blob[pos] != 0x1A {
		fmt.Println("unexpected byte in text header pos")
		return nil
	}

	pos += 1
	length, skip = ReadLengthField(blob[pos:])
	pos += skip

	// Read text tag next
	if blob[pos] != 0x12 {
		fmt.Println("unexpected byte in pos")
		return nil
	}

	pos += 1
	length, skip = ReadLengthField(blob[pos:])
	pos += skip
	data = blob[pos : pos+length]

	return data
}

func main() {

	db, err := sql.Open("sqlite3", "./notes.db")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	rows, err := db.Query("select ZDATA from ZICNOTEDATA WHERE ZDATA IS NOT NULL ORDER BY Z_PK ASC;")
	if err != nil {
		log.Fatal(err)
	}

	i := 0
	defer rows.Close()
	for rows.Next() {
		var note []byte
		err = rows.Scan(&note)
		if err != nil {
			log.Fatal(err)
		}

		b := bytes.NewBuffer(note)
		r, err := gzip.NewReader(b)
		if err != nil {
			log.Fatal(err)
		}

		buf := new(bytes.Buffer)
		buf.ReadFrom(r)
		ub := buf.Bytes()
		txt := ProcessNoteBodyBlob(ub)

		fmt.Printf("raw data =%v\n", string(ub))
		fmt.Printf("decoded data=%v\n", string(txt))

		if txt == nil {
			fmt.Println("error while reading record")
			continue
		}

		f, err := os.Create(fmt.Sprintf("note_%03d.txt", i))
		i++
		if err != nil {
			log.Fatal(err)
		}

		_, err = f.Write(txt)
		if err != nil {
			log.Fatal(err)
		}

		r.Close()
		f.Close()

	}
	err = rows.Err()
	if err != nil {
		log.Fatal(err)
	}
}