path: root/dbstore/dbstore_test.go

package dbstore

import (
  "compress/gzip"
  "context"
  db_sql "database/sql"
  "encoding/json"
  "encoding/xml"
  "embed"
  "errors"
  "fmt"
  _ "github.com/mattn/go-sqlite3"
  "github.com/pablotron/cvez/cisa"
  "github.com/pablotron/cvez/cpedict"
  "github.com/pablotron/cvez/cpematch"
  nvd_feed "github.com/pablotron/cvez/feed"
  "github.com/pablotron/cvez/rfc3339"
  io_fs "io/fs"
  "os"
  "path/filepath"
  "reflect"
  "testing"
  "time"
)

// Load gzip-compressed test feed.
func getFeed(path string) (nvd_feed.Feed, error) {
  var feed nvd_feed.Feed

  // open file for reading
  file, err := os.Open(path)
  if err != nil {
    return feed, err
  }

  // wrap in reader, return success
  src, err := gzip.NewReader(file)
  if err != nil {
    return feed, err
  }

  // create decoder, decode feed, return result
  d := json.NewDecoder(src)
  return feed, d.Decode(&feed)
}

// Load gzip-compressed test CISA catalog.
func getCisaCatalog(path string) (cisa.Catalog, error) {
  var cat cisa.Catalog

  // open file for reading
  file, err := os.Open(path)
  if err != nil {
    return cat, err
  }

  // wrap in reader, return success
  src, err := gzip.NewReader(file)
  if err != nil {
    return cat, err
  }

  // create decoder, decode catalog, return result
  d := json.NewDecoder(src)
  return cat, d.Decode(&cat)
}

func getTestDictionary(path string) (cpedict.Dictionary, error) {
  var dict cpedict.Dictionary

  // open test data
  f, err := os.Open(path)
  if err != nil {
    return dict, err
  }
  defer f.Close()

  // create zip reader
  gz, err := gzip.NewReader(f)
  if err != nil {
    return dict, err
  }
  defer gz.Close()

  // create xml decoder
  d := xml.NewDecoder(gz)

  // decode xml
  if err = d.Decode(&dict); err != nil {
    return dict, err
  }

  // return success
  return dict, nil
}
//go:embed testdata/sql/*.sql
var testSqlFs embed.FS

var testSqlIds = map[string]bool {
  "init": false,
  "insert-cpe": true,
  "insert-title": true,
  "insert-ref": true,
}

func getTestQueries() (map[string]string, error) {
  r := make(map[string]string)

  for id, _ := range(testSqlIds) {
    path := fmt.Sprintf("testdata/sql/%s.sql", id)
    if data, err := testSqlFs.ReadFile(path); err != nil {
      return r, err
    } else {
      r[id] = string(data)
    }
  }

  return r, nil
}

func ignoreTestSimple(t *testing.T) {
  testDbPath := "./testdata/foo.db"
  // get queries
  queries, err := getTestQueries()
  if err != nil {
    t.Error(err)
    return
  }

  // load test CPEs
  dict, err := getTestDictionary("testdata/test-0.xml.gz")
  if err != nil {
    t.Error(err)
    return
  }

  // does test db exist?
  if _, err = os.Stat(testDbPath); err != nil {
    if !errors.Is(err, io_fs.ErrNotExist) {
      t.Error(err)
      return
    }
  } else if err == nil {
    // remove test db
    if err = os.Remove(testDbPath); err != nil {
      t.Error(err)
      return
    }
  }

  // init db
  db, err := db_sql.Open("sqlite3", testDbPath)
  if err != nil {
    t.Error(err)
    return
  }
  defer db.Close()

  // init tables
  if _, err := db.Exec(queries["init"]); err != nil {
    t.Error(err)
    return
  }

  tx, err := db.Begin()
  if err != nil {
    t.Error(err)
    return
  }

  // build statements
  sts := make(map[string]*db_sql.Stmt)
  for id, use := range(testSqlIds) {
    if use {
      if st, err := tx.Prepare(queries[id]); err != nil {
        t.Error(err)
        return
      } else {
        sts[id] = st
        defer sts[id].Close()
      }
    }
  }

  // add items
  for _, item := range(dict.Items) {
    // add cpe
    rs, err := sts["insert-cpe"].Exec(item.CpeUri, item.Cpe23Item.Name);
    if err != nil {
      t.Error(err)
      return
    }

    // get last row ID
    id, err := rs.LastInsertId()
    if err != nil {
      t.Error(err)
      return
    }

    // add titles
    for _, title := range(item.Titles) {
      if _, err := sts["insert-title"].Exec(id, title.Lang, title.Text); err != nil {
        t.Error(err)
        return
      }
    }

    // add refs
    for _, ref := range(item.References) {
      if _, err := sts["insert-ref"].Exec(id, ref.Href, ref.Text); err != nil {
        t.Error(err)
        return
      }
    }
  }

  // commit changes
  if err = tx.Commit(); err != nil {
    t.Error(err)
    return
  }
}

func createTestDb(ctx context.Context, path string) (DbStore, error) {
  // remove existing file
  err := os.Remove(path)
  if err != nil && !errors.Is(err, io_fs.ErrNotExist) {
    return DbStore{}, err
  }

  // open db
  return Open(path)
}

func seedTestDb(ctx context.Context, db DbStore) error {
  // load test CPEs
  dict, err := getTestDictionary("testdata/test-0.xml.gz")
  if err != nil {
    return err
  }

  // add cpe dictionary
  return db.AddCpeDictionary(ctx, dict)

  // TODO: seed with other data
}

func TestOpenFull(t *testing.T) {
  tests := []struct {
    name string
    dbType string
    path string
    exp bool
  } {
    { "pass", "sqlite3", "./testdata/test-open.db", true },
    { "fail", "invalid driver", "file://invalid/foobar", false },
  }

  for _, test := range(tests) {
    t.Run(test.name, func(t *testing.T) {
      got, err := openFull(test.dbType, test.path)
      if test.exp && err != nil {
        t.Error(err)
      } else if !test.exp && err == nil {
        t.Errorf("got %v, exp error", got)
      }
    })
  }
}

func TestInitFail(t *testing.T) {
  // set deadline to 2 hours ago
  deadline := time.Now().Add(-2 * time.Hour)
  ctx, _ := context.WithDeadline(context.Background(), deadline)

  db, err := createTestDb(ctx, "./testdata/test-init-fail.db")
  if err != nil {
    t.Errorf("createTestDb(): got %v, exp error", db)
  }

  if err = db.Init(ctx); err == nil {
    t.Errorf("Init(): got %v, exp error", db)
  }
}

func TestTx(t *testing.T) {
  ctx := context.Background()
  expCtx, _ := context.WithTimeout(ctx, 0)
  nopeErr := fmt.Errorf("nope")

  // create temp dir
  dir, err := os.MkdirTemp("", "")
  if err != nil {
    t.Error(err)
    return
  }
  defer os.RemoveAll(dir)

  // create dbstore
  db, err := Open(filepath.Join(dir, "test-tx.db"))
  if err != nil {
    t.Error(err)
    return
  }

  passTests := []struct {
    id string
    exp error
    fn func(Tx) error
  } {
    { "commit", nil, func(_ Tx) error { return nil } },
    { "rollback", nopeErr, func(_ Tx) error { return nopeErr } },
  }

  for _, test := range(passTests) {
    t.Run(test.id, func(t *testing.T) {
      got := db.Tx(ctx, []string {}, test.fn)
      if got != test.exp {
        t.Errorf("got %v, exp %v", got, test.exp)
      }
    })
  }

  // null transaction function
  nullTxFn := func(_ Tx) error { return nil }
  expSoonCtx, _ := context.WithTimeout(ctx, 100 * time.Millisecond)

  failTests := []struct {
    id  string // test ID
    ctx context.Context // context
    ids []string // query IDs
    fn  func(Tx) error // transaction callback
  } {{
    id:  "ctx",
    ctx: expCtx,
    ids: []string {},
    fn: nullTxFn,
  }, {
    id:  "ids",
    ctx: ctx,
    ids: []string {"bad query"},
    fn:  nullTxFn,
  }, {
    id:  "rollback", // FIXME: doesn't work
    ctx: expSoonCtx,
    ids: []string {},
    fn:  func(_ Tx) error {
      time.Sleep(200 * time.Millisecond)
      return nil
    },
  }}

  for _, test := range(failTests) {
    t.Run(test.id, func(t *testing.T) {
      if db.Tx(test.ctx, test.ids, test.fn) == nil {
        t.Errorf("got success, exp err")
      }
    })
  }
}

func TestQuery(t *testing.T) {
  ctx := context.Background()

  // create temp dir
  dir, err := os.MkdirTemp("", "")
  if err != nil {
    t.Error(err)
    return
  }
  defer os.RemoveAll(dir)

  // create dbstore
  db, err := Open(filepath.Join(dir, "test-query.db"))
  if err != nil {
    t.Error(err)
    return
  }

  passTests := []struct {
    id   string // query ID
    args []interface{} // query args
    exp  []string // expected results
  } {{
    id:   "test/query/ids",
    args: []interface{} {},
    exp:  []string { "foo", "bar", "baz" },
  }, {
    id:   "test/query/ids-arg-named",
    args: []interface{} { db_sql.Named("q", "bar") },
    exp:  []string { "bar" },
  }, {
    id:   "test/query/ids-arg-pos",
    args: []interface{} { "baz" },
    exp:  []string { "baz" },
  }}

  for _, test := range(passTests) {
    t.Run(fmt.Sprintf("pass/%s", test.id), func(t *testing.T) {
      var got []string

      // exec query, build result
      err := db.Query(ctx, test.id, test.args, func(rows *db_sql.Rows) error {
        var s string
        if err := rows.Scan(&s); err != nil {
          return err
        } else {
          got = append(got, s)
          return nil
        }
      })

      // check for error
      if err != nil {
        t.Error(err)
        return
      }

      // check for expected value
      if !reflect.DeepEqual(got, test.exp) {
        t.Errorf("got %v, exp %v", got, test.exp)
      }
    })
  }

  failTests := []struct {
    id    string // query ID
    args  []interface{} // query args
  } {{
    id:   "test/query/ids-arg-named",
    // args: []interface{} { "foo", true },
  }, {
    id:   "test/query/ids-arg-pos",
    // args: []interface{} { "foo", true },
  }}

  var s string
  someErr := errors.New("an error")
  scanFn := func(rows *db_sql.Rows) error { return rows.Scan(&s) }
  failFn := func(_ *db_sql.Rows) error { return someErr }

  // run fail tests
  for _, test := range(failTests) {
    t.Run(fmt.Sprintf("fail/%s", test.id), func(t *testing.T) {
      if db.Query(ctx, test.id, test.args, scanFn) == nil {
        t.Errorf("got success, exp error")
      }
    })
  }

  // test callback func error
  t.Run("fail-func", func(t *testing.T) {
    if db.Query(ctx, "test/query/ids", nil, failFn) == nil {
      t.Errorf("got success, exp error")
    }
  })
}

func TestQueryRow(t *testing.T) {
  ctx := context.Background()

  // create temp dir
  dir, err := os.MkdirTemp("", "")
  if err != nil {
    t.Error(err)
    return
  }
  defer os.RemoveAll(dir)

  // create dbstore
  db, err := Open(filepath.Join(dir, "test-queryrow.db"))
  if err != nil {
    t.Error(err)
    return
  }

  passTests := []struct {
    id   string // query ID
    args []interface{} // query args
    exp  string // expected results
  } {{
    id:   "test/queryrow/ids",
    exp:  "foo",
  }, {
    id:   "test/queryrow/ids-arg-named",
    args: []interface{} { db_sql.Named("q", "bar") },
    exp:  "bar",
  }, {
    id:   "test/queryrow/ids-arg-pos",
    args: []interface{} { "baz" },
    exp:  "baz",
  }}

  for _, test := range(passTests) {
    t.Run(fmt.Sprintf("pass/%s", test.id), func(t *testing.T) {
      // exec query, get result
      var got string
      err := db.QueryRow(ctx, test.id, test.args, func(row *db_sql.Row) error {
        return row.Scan(&got)
      })

      // check for error, check result
      if err != nil {
        t.Error(err)
        return
      } else if got != test.exp {
        t.Errorf("got %v, exp %v", got, test.exp)
      }
    })
  }

  failTests := []string {
    "invalid query",
    "test/queryrow/ids-arg-named",
    "test/queryrow/ids-arg-pos",
  }

  var got string
  someErr := errors.New("an error")
  scanFn := func(row *db_sql.Row) error { return row.Scan(&got) }
  failFn := func(_ *db_sql.Row) error { return someErr }

  // run fail tests
  for _, test := range(failTests) {
    t.Run(fmt.Sprintf("fail/%s", test), func(t *testing.T) {
      if db.QueryRow(ctx, test, nil, scanFn) == nil {
        t.Errorf("got %s, exp error", got)
      }
    })
  }

  // test callback func error
  t.Run("fail-func", func(t *testing.T) {
    if db.QueryRow(ctx, "test/queryrow/ids", nil, failFn) == nil {
      t.Errorf("got success, exp error")
    }
  })
}

func TestAddCpeDictionaryPass(t *testing.T) {
  if testing.Short() {
    t.Skip("skipping TestAddCpeDictionaryPass() in short mode")
    return
  }

  path := "./testdata/test-addcpedict.db"
  ctx := context.Background()

  // create db
  db, err := createTestDb(ctx, path)
  if err != nil {
    t.Error(err)
    return
  }

  // load test CPEs
  dict, err := getTestDictionary("testdata/test-0.xml.gz")
  if err != nil {
    t.Error(err)
    return
  }

  // add cpe dictionary
  if err := db.AddCpeDictionary(ctx, dict); err != nil {
    t.Error(err)
    return
  }
}

func TestAddCpeDictionaryFail(t *testing.T) {
  if testing.Short() {
    t.Skip("skipping TestAddCpeDictionaryFail() in short mode")
    return
  }

  // load test CPEs
  dict, err := getTestDictionary("testdata/test-0.xml.gz")
  if err != nil {
    t.Error(err)
    return
  }

  funcTests := []struct {
    name string
    fn func(string) func(*testing.T)
  } {{
    name: "deadline",
    fn: func(path string) func(*testing.T) {
      return func(t *testing.T) {
        deadline := time.Now().Add(-2 * time.Hour)
        ctx, _ := context.WithDeadline(context.Background(), deadline)

        // create db
        db, err := createTestDb(ctx, path)
        if err != nil {
          t.Error(err)
          return
        }

        // add cpe dictionary
        if err := db.AddCpeDictionary(ctx, dict); err == nil {
          t.Errorf("got success, exp error")
        }
      }
    },
  }, {
    name: "tx",
    fn: func(path string) func(*testing.T) {
      return func(t *testing.T) {
        ctx := context.Background()

        // create db
        db, err := createTestDb(ctx, path)
        if err != nil {
          t.Error(err)
          return
        }

        // begin transaction
        if _, err = db.db.BeginTx(ctx, nil); err != nil {
          t.Error(err)
          return
        }

// FIXME: busted
//         // add cpe dictionary
//         if err := db.AddCpeDictionary(ctx, dict); err == nil {
//           t.Errorf("got success, exp error")
//         }
      }
    },
  }}

  for _, test := range(funcTests) {
    path := fmt.Sprintf("./testdata/test-addcpedict-fail-%s.db", test.name)
    t.Run(test.name, test.fn(path))
  }

  dictTests := []struct {
    name string
    dict cpedict.Dictionary
  } {{
    name: "bad-cpe23",
    dict: cpedict.Dictionary {
      Items: []cpedict.Item { cpedict.Item{} },
    },
  }, {
    name: "bad-title",
    dict: cpedict.Dictionary {
      Items: []cpedict.Item {
        cpedict.Item {
          CpeUri: "cpe:/a",

          Cpe23Item: cpedict.Cpe23Item {
            Name: "cpe:2.3:*:*:*:*:*:*:*:*:*:*:*",
          },

          Titles: []cpedict.Title {
            cpedict.Title {},
          },
        },
      },
    },
  }, {
    name: "bad-ref",
    dict: cpedict.Dictionary {
      Items: []cpedict.Item {
        cpedict.Item {
          CpeUri: "cpe:/a",

          Cpe23Item: cpedict.Cpe23Item {
            Name: "cpe:2.3:*:*:*:*:*:*:*:*:*:*:*",
          },

          Titles: []cpedict.Title {
            cpedict.Title { Lang: "en-US", Text: "foo" },
          },

          References: []cpedict.Reference {
            cpedict.Reference {},
          },
        },
      },
    },
  }}

  for _, test := range(dictTests) {
    t.Run(test.name, func(t *testing.T) {
      ctx := context.Background()
      path := fmt.Sprintf("./testdata/test-addcpedict-fail-%s.db", test.name)

      // create db
      db, err := createTestDb(ctx, path)
      if err != nil {
        t.Error(err)
        return
      }

      // add cpe dictionary
      if err := db.AddCpeDictionary(ctx, test.dict); err == nil {
        t.Errorf("got success, exp error")
      }
    })
  }
}

func TestCpeSearch(t *testing.T) {
  path := "./testdata/test-search.db"
  ctx := context.Background()

  // tests that are expected to pass
  passTests := []struct {
    t CpeSearchType // search type
    q string // query string
    exp []string // expected search results (cpe23s)
  } {{
    t: CpeSearchAll,
    q: "advisory AND book",
    exp: []string {
      "cpe:2.3:a:\\$0.99_kindle_books_project:\\$0.99_kindle_books:6:*:*:*:*:android:*:*",
    },
  }, {
    t: CpeSearchTitle,
    q: "project",
    exp: []string {
      "cpe:2.3:a:\\@thi.ng\\/egf_project:\\@thi.ng\\/egf:-:*:*:*:*:node.js:*:*",
      "cpe:2.3:a:\\@thi.ng\\/egf_project:\\@thi.ng\\/egf:0.1.0:*:*:*:*:node.js:*:*",
      "cpe:2.3:a:\\@thi.ng\\/egf_project:\\@thi.ng\\/egf:0.2.0:*:*:*:*:node.js:*:*",
      "cpe:2.3:a:\\@thi.ng\\/egf_project:\\@thi.ng\\/egf:0.2.1:*:*:*:*:node.js:*:*",
      "cpe:2.3:a:\\$0.99_kindle_books_project:\\$0.99_kindle_books:6:*:*:*:*:android:*:*",
    },
  }, {
    t: CpeSearchRef,
    q: "advisory",
    exp: []string {
      "cpe:2.3:a:\\@thi.ng\\/egf_project:\\@thi.ng\\/egf:-:*:*:*:*:node.js:*:*",
      "cpe:2.3:a:\\@thi.ng\\/egf_project:\\@thi.ng\\/egf:0.1.0:*:*:*:*:node.js:*:*",
      "cpe:2.3:a:\\@thi.ng\\/egf_project:\\@thi.ng\\/egf:0.2.0:*:*:*:*:node.js:*:*",
      "cpe:2.3:a:\\@thi.ng\\/egf_project:\\@thi.ng\\/egf:0.2.1:*:*:*:*:node.js:*:*",
      "cpe:2.3:a:360totalsecurity:360_total_security:12.1.0.1005:*:*:*:*:*:*:*",
      "cpe:2.3:a:\\$0.99_kindle_books_project:\\$0.99_kindle_books:6:*:*:*:*:android:*:*",
    },
  }}

  // create db
  db, err := createTestDb(ctx, path)
  if err != nil {
    t.Error(err)
    return
  }

  // seed test database
  if err = seedTestDb(ctx, db); err != nil {
    t.Error(err)
    return
  }

  for _, test := range(passTests) {
    t.Run(test.t.String(), func(t *testing.T) {
      rows, err := db.CpeSearch(ctx, test.t, test.q)
      if err != nil {
        t.Error(err)
        return
      }

      // build ids
      got := make([]string, len(rows))
      for i, row := range(rows) {
        got[i] = row.Cpe23
      }

      if !reflect.DeepEqual(got, test.exp) {
        t.Errorf("got \"%v\", exp \"%v\"", got, test.exp)
        return
      }
    })
  }

  // tests that are expected to fail
  failTests := []struct {
    name string // test name
    t CpeSearchType // search type
    q string // query string
  } {
    { "bad-search-type", CpeSearchType(255), "" },
  }

  for _, test := range(failTests) {
    t.Run(test.name, func(t *testing.T) {
      if got, err := db.CpeSearch(ctx, test.t, test.q); err == nil {
        t.Errorf("got \"%v\", exp error", got)
      }
    })
  }

  // readonly-db test (init failure)
  t.Run("readonly", func(t *testing.T) {
    q := "advisory AND book"
    if db, err := Open("testdata/readonly.db"); err != nil {
      t.Error(err)
    } else {
      if got, err := db.CpeSearch(ctx, CpeSearchAll, q); err == nil {
        t.Errorf("got %v, exp error", got)
      }
    }
  })
}

func TestAddCpeMatches(t *testing.T) {
  if testing.Short() {
    t.Skip("skipping TestAddCpeMatches() in short mode")
    return
  }

  // cache context, create temp dir
  ctx := context.Background()
  dir, err := os.MkdirTemp("", "")
  if err != nil {
    t.Error(err)
    return
  }
  defer os.RemoveAll(dir)

  vuln := true
  passTests := []struct {
    name string  // test name and path
    seed string // db seed query
    matches []cpematch.Match // matches
  } {{
    name: "pass-basic",

    seed: `
      INSERT INTO cpes(cpe_uri, cpe23) VALUES (
        'cpe:/1',
        'cpe:2.3:a:101_project:101:1.0.0:*:*:*:*:node.js:*:*'
      ), (
        'cpe:/2',
        'cpe:2.3:a:101_project:101:1.1.0:*:*:*:*:node.js:*:*'
      ), (
        'cpe:/3',
        'cpe:2.3:a:101_project:101:1.1.1:*:*:*:*:node.js:*:*'
      );
    `,

    matches: []cpematch.Match {
      cpematch.Match {
        Cpe23Uri: "cpe:2.3:a:101_project:101:*:*:*:*:*:node.js:*:*",

        Vulnerable: &vuln,

        VersionStartIncluding: "1.0.0",
        VersionEndIncluding: "1.6.3",

        Names: []cpematch.Name {
          cpematch.Name {
            Cpe23Uri: "cpe:2.3:a:101_project:101:1.0.0:*:*:*:*:node.js:*:*",
          },

          cpematch.Name {
            Cpe23Uri: "cpe:2.3:a:101_project:101:1.1.0:*:*:*:*:node.js:*:*",
          },

          cpematch.Name {
            Cpe23Uri: "cpe:2.3:a:101_project:101:1.1.1:*:*:*:*:node.js:*:*",
          },
        },
      },
    },
  }, {
    name: "pass-excluding",

    seed: `
      INSERT INTO cpes(cpe_uri, cpe23) VALUES (
        'cpe:/1',
        'cpe:2.3:a:101_project:101:1.0.0:*:*:*:*:node.js:*:*'
      ), (
        'cpe:/2',
        'cpe:2.3:a:101_project:101:1.1.0:*:*:*:*:node.js:*:*'
      ), (
        'cpe:/3',
        'cpe:2.3:a:101_project:101:1.1.1:*:*:*:*:node.js:*:*'
      );
    `,

    matches: []cpematch.Match {
      cpematch.Match {
        Cpe23Uri: "cpe:2.3:a:101_project:101:*:*:*:*:*:node.js:*:*",

        Vulnerable: &vuln,

        VersionStartExcluding: "1.0.0",
        VersionEndExcluding: "1.6.3",

        Names: []cpematch.Name {
          cpematch.Name {
            Cpe23Uri: "cpe:2.3:a:101_project:101:1.0.0:*:*:*:*:node.js:*:*",
          },

          cpematch.Name {
            Cpe23Uri: "cpe:2.3:a:101_project:101:1.1.0:*:*:*:*:node.js:*:*",
          },

          cpematch.Name {
            Cpe23Uri: "cpe:2.3:a:101_project:101:1.1.1:*:*:*:*:node.js:*:*",
          },
        },
      },
    },
  }}

  for _, test := range(passTests) {
    t.Run(test.name, func(t *testing.T) {
      // build test matches
      matches := cpematch.Matches { Matches: test.matches }
      // open db
      db, err := Open(filepath.Join(dir, fmt.Sprintf("%s.db", test.name)))
      if err != nil {
        t.Error(err)
        return
      }

      // init db
      if err := db.Init(ctx); err != nil {
        t.Error(err)
        return
      }

      // seed db
      if _, err = db.db.ExecContext(ctx, test.seed); err != nil {
        t.Error(err)
        return
      }

      // add matches
      if err = db.AddCpeMatches(ctx, matches); err != nil {
        t.Error(err)
      }
    })
  }

  failTests := []struct {
    name string  // test name (path inferred)
    seed string // db seed query
    matches []cpematch.Match // matches
  } {{
    name: "bad-match-cpe23",

    seed: `
      INSERT INTO cpes(cpe_uri, cpe23) VALUES (
        'cpe:/1',
        'cpe:2.3:a:101_project:101:1.0.0:*:*:*:*:node.js:*:*'
      ), (
        'cpe:/2',
        'cpe:2.3:a:101_project:101:1.1.0:*:*:*:*:node.js:*:*'
      ), (
        'cpe:/3',
        'cpe:2.3:a:101_project:101:1.1.1:*:*:*:*:node.js:*:*'
      );
    `,

    matches: []cpematch.Match {
      cpematch.Match {
        Cpe23Uri: "cpe:",

        VersionStartIncluding: "1.0.0",
        VersionEndIncluding: "1.6.3",

        Names: []cpematch.Name {
          cpematch.Name {
            Cpe23Uri: "cpe:2.3:a:101_project:101:1.0.0:*:*:*:*:node.js:*:*",
          },

          cpematch.Name {
            Cpe23Uri: "cpe:2.3:a:101_project:101:1.1.0:*:*:*:*:node.js:*:*",
          },

          cpematch.Name {
            Cpe23Uri: "cpe:2.3:a:101_project:101:1.1.1:*:*:*:*:node.js:*:*",
          },
        },
      },
    },
  }, {
    name: "bad-cpe",

    seed: `
      INSERT INTO cpes(cpe_uri, cpe23) VALUES (
        'cpe:/1',
        'cpe:2.3:a:101_project:101:1.0.0:*:*:*:*:node.js:*:*'
      ), (
        'cpe:/2',
        'cpe:2.3:a:101_project:101:1.1.0:*:*:*:*:node.js:*:*'
      ), (
        'cpe:/3',
        'cpe:2.3:a:101_project:101:1.1.1:*:*:*:*:node.js:*:*'
      );
    `,

    matches: []cpematch.Match {
      cpematch.Match {
        Cpe23Uri: "cpe:2.3:a:101_project:101:*:*:*:*:*:node.js:*:*",

        VersionStartIncluding: "1.0.0",
        VersionEndIncluding: "1.6.3",

        Names: []cpematch.Name {
          cpematch.Name {
            Cpe23Uri: "cpe:2.3",
          },

          cpematch.Name {
            Cpe23Uri: "cpe:2.3:a:101_project:101:1.1.0:*:*:*:*:node.js:*:*",
          },

          cpematch.Name {
            Cpe23Uri: "cpe:2.3:a:101_project:101:1.1.1:*:*:*:*:node.js:*:*",
          },
        },
      },
    },
  }, {
    name: "dup-versionstart",

    seed: `
      INSERT INTO cpes(cpe_uri, cpe23) VALUES (
        'cpe:/1',
        'cpe:2.3:a:101_project:101:1.0.0:*:*:*:*:node.js:*:*'
      ), (
        'cpe:/2',
        'cpe:2.3:a:101_project:101:1.1.0:*:*:*:*:node.js:*:*'
      ), (
        'cpe:/3',
        'cpe:2.3:a:101_project:101:1.1.1:*:*:*:*:node.js:*:*'
      );
    `,

    matches: []cpematch.Match {
      cpematch.Match {
        Cpe23Uri: "cpe:2.3:a:101_project:101:*:*:*:*:*:node.js:*:*",

        VersionStartIncluding: "1.0.0",
        VersionStartExcluding: "1.1.0",

        Names: []cpematch.Name {
          cpematch.Name {
            Cpe23Uri: "cpe:2.3:a:101_project:101:1.0.0:*:*:*:*:node.js:*:*",
          },

          cpematch.Name {
            Cpe23Uri: "cpe:2.3:a:101_project:101:1.1.0:*:*:*:*:node.js:*:*",
          },

          cpematch.Name {
            Cpe23Uri: "cpe:2.3:a:101_project:101:1.1.1:*:*:*:*:node.js:*:*",
          },
        },
      },
    },
  }, {
    name: "dup-versionend",

    seed: `
      INSERT INTO cpes(cpe_uri, cpe23) VALUES (
        'cpe:/1',
        'cpe:2.3:a:101_project:101:1.0.0:*:*:*:*:node.js:*:*'
      ), (
        'cpe:/2',
        'cpe:2.3:a:101_project:101:1.1.0:*:*:*:*:node.js:*:*'
      ), (
        'cpe:/3',
        'cpe:2.3:a:101_project:101:1.1.1:*:*:*:*:node.js:*:*'
      );
    `,

    matches: []cpematch.Match {
      cpematch.Match {
        Cpe23Uri: "cpe:2.3:a:101_project:101:*:*:*:*:*:node.js:*:*",

        VersionEndIncluding: "1.0.0",
        VersionEndExcluding: "1.1.0",

        Names: []cpematch.Name {
          cpematch.Name {
            Cpe23Uri: "cpe:2.3:a:101_project:101:1.0.0:*:*:*:*:node.js:*:*",
          },

          cpematch.Name {
            Cpe23Uri: "cpe:2.3:a:101_project:101:1.1.0:*:*:*:*:node.js:*:*",
          },

          cpematch.Name {
            Cpe23Uri: "cpe:2.3:a:101_project:101:1.1.1:*:*:*:*:node.js:*:*",
          },
        },
      },
    },
  }}

  for _, test := range(failTests) {
    t.Run(test.name, func(t *testing.T) {
      // build test matches
      matches := cpematch.Matches { Matches: test.matches }
      // open db
      db, err := Open(filepath.Join(dir, fmt.Sprintf("%s.db", test.name)))
      if err != nil {
        t.Error(err)
        return
      }

      // init db
      if err := db.Init(ctx); err != nil {
        t.Error(err)
        return
      }

      // seed db
      if _, err = db.db.ExecContext(ctx, test.seed); err != nil {
        t.Error(err)
        return
      }

      // add matches
      if err = db.AddCpeMatches(ctx, matches); err == nil {
        t.Error("got success, exp error")
      }
    })
  }

  // readonly-db test (init failure)
  t.Run("readonly", func(t *testing.T) {
    matches := cpematch.Matches { Matches: passTests[0].matches }

    if db, err := Open("testdata/readonly.db"); err != nil {
      t.Error(err)
    } else if err = db.AddCpeMatches(ctx, matches); err == nil {
      t.Errorf("got success, exp error")
    }
  })
}

func TestCpeMatchSearch(t *testing.T) {
  // cache context, create temp dir
  ctx := context.Background()
  dir, err := os.MkdirTemp("", "")
  if err != nil {
    t.Error(err)
    return
  }
  defer os.RemoveAll(dir)

  // db cpe seed query
  seed :=  `
    INSERT INTO cpes(cpe_uri, cpe23) VALUES (
      'cpe:/1',
      'cpe:2.3:a:101_project:101:1.0.0:*:*:*:*:node.js:*:*'
    ), (
      'cpe:/2',
      'cpe:2.3:a:101_project:101:1.1.0:*:*:*:*:node.js:*:*'
    ), (
      'cpe:/3',
      'cpe:2.3:a:101_project:101:1.1.1:*:*:*:*:node.js:*:*'
    );
  `

  matches := cpematch.Matches {
    Matches: []cpematch.Match {
      cpematch.Match {
        Cpe23Uri: "cpe:2.3:a:101_project:101:*:*:*:*:*:node.js:*:*",

        VersionStartIncluding: "1.0.0",
        VersionEndIncluding: "1.6.3",

        Names: []cpematch.Name {
          cpematch.Name {
            Cpe23Uri: "cpe:2.3:a:101_project:101:1.0.0:*:*:*:*:node.js:*:*",
          },

          cpematch.Name {
            Cpe23Uri: "cpe:2.3:a:101_project:101:1.1.0:*:*:*:*:node.js:*:*",
          },

          cpematch.Name {
            Cpe23Uri: "cpe:2.3:a:101_project:101:1.1.1:*:*:*:*:node.js:*:*",
          },
        },
      },
    },
  }

  // build test matches
  // open db
  db, err := Open(filepath.Join(dir, "TestCpeMatchSearch.db"))
  if err != nil {
    t.Error(err)
    return
  }

  // init db
  if err := db.Init(ctx); err != nil {
    t.Error(err)
    return
  }

  // seed db
  if _, err = db.db.ExecContext(ctx, seed); err != nil {
    t.Error(err)
    return
  }

  // add matches
  if err = db.AddCpeMatches(ctx, matches); err != nil {
    t.Error(err)
    return
  }

  tests := []struct {
    val string    // search val
    exp []string  // expected results
  } {{
    val: "nothing",
    exp: []string {},
  }, {
    val: "cpe:2.3:a:101_project:101:*:*:*:*:*:node.js:*:*",
    exp: []string {
      "cpe:2.3:a:101_project:101:1.0.0:*:*:*:*:node.js:*:*",
      "cpe:2.3:a:101_project:101:1.1.0:*:*:*:*:node.js:*:*",
      "cpe:2.3:a:101_project:101:1.1.1:*:*:*:*:node.js:*:*",
    },
  }}

  for _, test := range(tests) {
    t.Run(test.val, func(t *testing.T) {
      if got, err := db.CpeMatchSearch(ctx, test.val); err != nil {
        t.Error(err)
      } else if ((len(got) > 0) || (len(test.exp) > 0)) &&
                !reflect.DeepEqual(got, test.exp) {
        t.Errorf("got %v, exp %v", got, test.exp)
      }
    })
  }

  // readonly-db test (init failure)
  t.Run("readonly", func(t *testing.T) {
    q := "cpe:2.3:a:101_project:101:*:*:*:*:*:node.js:*:*"

    if db, err := Open("testdata/readonly.db"); err != nil {
      t.Error(err)
    } else if got, err := db.CpeMatchSearch(ctx, q); err == nil {
      t.Errorf("got %v, exp error", got)
    }
  })
}

func TestAddCveFeeds(t *testing.T) {
  ctx := context.Background()

  if testing.Short() {
    t.Skip("skipping TestAddCveFeeds() in short mode")
    return
  }

  tests := []string {
    "nvdcve-1.1-2002",
    "nvdcve-1.1-2003",
    "nvdcve-1.1-2021",
  }

  // create test db
  db, err := createTestDb(ctx, "testdata/test-addcvefeeds.db")
  if err != nil {
    t.Error(err)
    return
  }

  // load feeds
  feeds := make([]nvd_feed.Feed, len(tests))
  for i, test := range(tests) {
    // get feed path
    path := fmt.Sprintf("testdata/%s.json.gz", test)

    // get feed
    if feed, err := getFeed(path); err != nil {
      t.Error(err)
      return
    } else {
      feeds[i] = feed
    }
  }

  // add feed
  if _, err = db.AddCveFeeds(ctx, feeds); err != nil {
    t.Error(err)
  }
}

func TestAddCveFeed(t *testing.T) {
  ctx := context.Background()

  if testing.Short() {
    t.Skip("skipping TestAddCveFeed() in short mode")
    return
  }

  // get feed
  feed, err := getFeed("testdata/nvdcve-1.1-2002.json.gz")
  if err != nil {
    t.Error(err)
  }

  t.Run("pass", func(t *testing.T) {
    // create test db
    db, err := createTestDb(ctx, "testdata/test-addcvefeed.db")
    if err != nil {
      t.Error(err)
      return
    }

    // add feed
    if _, err = db.AddCveFeed(ctx, feed); err != nil {
      t.Error(err)
    }
  })

  t.Run("fail", func(t *testing.T) {
    if db, err := Open("testdata/readonly.db"); err != nil {
      t.Error(err)
    } else if _, err = db.AddCveFeed(ctx, feed); err == nil {
      t.Errorf("got success, exp err")
    }
  })
}

func getTestCveId(t *testing.T, s string) nvd_feed.CveId {
  r, err := nvd_feed.NewCveId(s)
  if err != nil {
    // raise test error
    t.Error(err)
  }
  return r
}

func TestCveSearch(t *testing.T) {
  ctx := context.Background()

  tests := []struct {
    val string
    exp []CveSearchRow
  } {{
    val: "cisco anyconnect ipc dll mobility secure",
    exp: []CveSearchRow {
      CveSearchRow {
        Id: 9315,
        NvdId: getTestCveId(t, "CVE-2021-1366"),
        Description: "A vulnerability in the interprocess communication (IPC) channel of Cisco AnyConnect Secure Mobility Client for Windows could allow an authenticated, local attacker to perform a DLL hijacking attack on an affected device if the VPN Posture (HostScan) Module is installed on the AnyConnect client. This vulnerability is due to insufficient validation of resources that are loaded by the application at run time. An attacker could exploit this vulnerability by sending a crafted IPC message to the AnyConnect process. A successful exploit could allow the attacker to execute arbitrary code on the affected machine with SYSTEM privileges. To exploit this vulnerability, the attacker needs valid credentials on the Windows system.",
        V3BaseScore: nvd_feed.Score(uint8(78)),
        V3Severity: nvd_feed.SeverityHigh,
        Rank: -37.51353,
      },

      CveSearchRow {
        Id: 9495,
        NvdId: getTestCveId(t, "CVE-2021-1567"),
        Description: "A vulnerability in the DLL loading mechanism of Cisco AnyConnect Secure Mobility Client for Windows could allow an authenticated, local attacker to perform a DLL hijacking attack on an affected device if the VPN Posture (HostScan) Module is installed on the AnyConnect client. This vulnerability is due to a race condition in the signature verification process for DLL files that are loaded on an affected device. An attacker could exploit this vulnerability by sending a series of crafted interprocess communication (IPC) messages to the AnyConnect process. A successful exploit could allow the attacker to execute arbitrary code on the affected device with SYSTEM privileges. To exploit this vulnerability, the attacker must have valid credentials on the Windows system.",
        V3BaseScore: nvd_feed.Score(uint8(67)),
        V3Severity: nvd_feed.SeverityMedium,
        Rank: -35.5376,
      },
    },
  }}

  // open test db
  db, err := Open("testdata/cvesearch-test.db")
  if err != nil {
    t.Error(err)
    return
  }

  // run tests
  for _, test := range(tests) {
    t.Run(test.val, func(t *testing.T) {
      got, err := db.CveSearch(ctx, test.val)
      if err != nil {
        t.Error(err)
      } else if !reflect.DeepEqual(got, test.exp) {
        t.Errorf("got \"%v\", exp \"%v\"", got, test.exp)
      }
    })
  }

  // readonly-db test (init failure)
  t.Run("readonly", func(t *testing.T) {
    q := "cisco anyconnect ipc dll mobility secure"

    if db, err := Open("testdata/readonly.db"); err != nil {
      t.Error(err)
    } else if got, err := db.CveSearch(ctx, q); err == nil {
      t.Errorf("got %v, exp error", got)
    }
  })
}

func TestAddCisaCatalogs(t *testing.T) {
  ctx := context.Background()

  tests := []struct {
    name  string // test name
    files []string // test files
    fast  bool // is this test fast?
  } {{
    name:   "fast",
    files:  []string { "cisa-kevc-20220313-tiny", },
    fast:   true,
  }, {
    name:   "slow",
    files:  []string { "cisa-kevc-20220313-tiny", "cisa-kevc-20220313" },
    fast:   false,
  }}

  for _, test := range(tests) {
    t.Run(test.name, func(t *testing.T) {
      // skip slow tests in short mode
      if !test.fast && testing.Short() {
        t.Skip("skipping in short mode")
        return
      }

      // build db path
      dbPath := fmt.Sprintf("testdata/test-addcisacatalogs-%s.db", test.name)

      // create test db
      db, err := createTestDb(ctx, dbPath)
      if err != nil {
        t.Error(err)
        return
      }

      // load test catalogs
      cats := make([]cisa.Catalog, len(test.files))
      for i, name := range(test.files) {
        // build catalog path
        path := fmt.Sprintf("testdata/%s.json.gz", name)

        // get catalog
        if cat, err := getCisaCatalog(path); err != nil {
          t.Error(err)
          return
        } else {
          cats[i] = cat
        }
      }

      // add catalogs
      if _, err = db.AddCisaCatalogs(ctx, cats); err != nil {
        t.Error(err)
        return
      }
    })
  }
}

// TODO: TestCisaSearch()
// test db: dbstore/testdata/mock-testcisasearch.db
// test cases: "wordpad" (1 result), "microsoft excel" (4 results)
func TestCisaSearch(t *testing.T) {
  ctx := context.Background()

  tests := []struct {
    val  string // search value
    cves []string // cve id strings
    adds []string // add date strings
    dues []string // due date strings
    exp  []CisaSearchRow // expected rows
  } {{
    val: "wordpad",
    cves: []string { "CVE-2017-0199" },
    adds: []string { "2021-11-03" },
    dues: []string { "2022-05-03" },
    exp: []CisaSearchRow {
      CisaSearchRow {
        Id: 184,
        CatId: 0,
        Vendor: "Microsoft",
        Product: "Windows, Windows Server, Microsoft Office",
        Name: "Microsoft Office/WordPad Remote Code Execution Vulnerability with Windows API",
        Description: "Allows remote attackers to execute arbitrary code via a crafted document, aka \"Microsoft Office/WordPad Remote Code Execution Vulnerability w/Windows API.\"",
        Action: "Apply updates per vendor instructions.",
        Rank: -8.090183,
      },
    },
  }, {
    val: "microsoft excel",
    cves: []string { "CVE-2019-1297", "CVE-2009-3129", "CVE-2021-42292", "CVE-2016-7262" },
    adds: []string { "2022-03-03", "2022-03-03", "2021-11-17", "2022-03-03" },
    dues: []string { "2022-03-17", "2022-03-24", "2021-12-01", "2022-03-24" },
    exp: []CisaSearchRow {
      CisaSearchRow {
        Id: 398,
        CatId: 0,
        Vendor: "Microsoft",
        Product: "Excel",
        Name: "Microsoft Excel Remote Code Execution Vulnerability ",
        Description: "A remote code execution vulnerability exists in Microsoft Excel when the software fails to properly handle objects in memory.",
        Action: "Apply updates per vendor instructions.",
        Rank: -9.54369,
      },

      CisaSearchRow {
        Id: 477,
        CatId: 0,
        Vendor: "Microsoft",
        Product: "Excel",
        Name: "Microsoft Excel Featheader Record Memory Corruption Vulnerability",
        Description: "Microsoft Office Excel allows remote attackers to execute arbitrary code via a spreadsheet with a FEATHEADER record containing an invalid cbHdrData size element that affects a pointer offset.",
        Action: "Apply updates per vendor instructions.",
        Rank: -9.063994,
      },

      CisaSearchRow {
        Id: 295,
        CatId: 0,
        Vendor: "Microsoft",
        Product: "Office",
        Name: "Microsoft Excel Security Feature Bypass",
        Description: "A security feature bypass vulnerability in Microsoft Excel would allow a local user to perform arbitrary code execution.",
        Action: "Apply updates per vendor instructions.",
        Rank: -8.8327875,
      },

      CisaSearchRow {
        Id: 440,
        CatId: 0,
        Vendor: "Microsoft",
        Product: "Excel",
        Name: "Microsoft Office Security Feature Bypass Vulnerability",
        Description: "A security feature bypass vulnerability exists when Microsoft Office improperly handles input. An attacker who successfully exploited the vulnerability could execute arbitrary commands.",
        Action: "Apply updates per vendor instructions.",
        Rank: -6.7629704,
      },
    },
  }}

  // connect to test db
  db, err := Open("testdata/mock-cisasearch.db")
  if err != nil {
    t.Error(err)
    return
  }

  for _, test := range(tests) {
    t.Run(test.val, func(t *testing.T) {
      // parse cve ids
      for i, val := range(test.cves) {
        if cve, err := nvd_feed.NewCveId(val); err != nil {
          t.Error(err)
          return
        } else {
          test.exp[i].CveId = cve
        }
      }

      // parse add dates
      for i, val := range(test.adds) {
        if d, err := rfc3339.NewDate([]byte(val)); err != nil {
          t.Error(err)
          return
        } else {
          test.exp[i].AddedAt = d
        }
      }

      // parse due dates
      for i, val := range(test.dues) {
        if d, err := rfc3339.NewDate([]byte(val)); err != nil {
          t.Error(err)
          return
        } else {
          test.exp[i].DueAt = d
        }
      }

      if got, err := db.CisaSearch(ctx, test.val); err != nil {
        t.Error(err)
      } else if !reflect.DeepEqual(got, test.exp) {
        t.Errorf("got %#v, exp %#v", got, test.exp )
      }
    })
  }
}