dbstore: add AddCveFeeds(), fix bugs, fix TestAddCveFeed

4 files changed, 75 insertions, 25 deletions

diff --git a/dbstore/dbstore.go b/dbstore/dbstore.go
index d1e44ba..840f268 100644
--- a/dbstore/dbstore.go
+++ b/dbstore/dbstore.go
@@ -693,6 +693,7 @@ var addCveFeedQueryIds = []string {
   "feed/insert-assigner",
   "feed/insert-cve",
   "feed/insert-cve-desc",
+  "feed/insert-cve-fts-refresh",
   "feed/insert-cve-problem",
   "feed/insert-cve-problem-desc",
   "feed/insert-cve-ref",
@@ -703,29 +704,47 @@ var addCveFeedQueryIds = []string {
   "feed/insert-item-cvss-v3",
 }
 
-// Import CVE feed.
-func (me DbStore) AddCveFeed(ctx context.Context, feed nvd_feed.Feed) (int64, error) {
-  // feed ID
-  var feedId int64
+// Refresh CVE FTS index.
+func (me DbStore) refreshCveFts(ctx context.Context, tx Tx) error {
+  _, err := tx.Exec(ctx, "feed/insert-cve-fts-refresh")
+  return err
+}
+
+// Import CVE feeds.
+func (me DbStore) AddCveFeeds(ctx context.Context, feeds []nvd_feed.Feed) ([]int64, error) {
+  feedIds := make([]int64, len(feeds))
 
   // lazy-init db
   if err := me.Init(ctx); err != nil {
-    return feedId, err
+    return feedIds, err
   }
 
   // begin transaction
   err := me.Tx(ctx, addCveFeedQueryIds, func(tx Tx) error {
-    // add feed, get feed ID
-    if id, err := me.addFeed(ctx, tx, feed); err != nil {
-      return err
-    } else {
-      feedId = id
-      return nil
+    for i, feed := range(feeds) {
+      // add feed, get feed ID
+      if id, err := me.addFeed(ctx, tx, feed); err != nil {
+        return err
+      } else {
+        feedIds[i] = id
+      }
     }
+
+    // refresh the cve fts index
+    return me.refreshCveFts(ctx, tx)
   })
 
-  // return result
-  return feedId, err
+  // return results
+  return feedIds, err
+}
+
+// Import single CVE feed.
+func (me DbStore) AddCveFeed(ctx context.Context, feed nvd_feed.Feed) (int64, error) {
+  if r, err := me.AddCveFeeds(ctx, []nvd_feed.Feed { feed }); err != nil {
+    return 0, err
+  } else {
+    return r[0], nil
+  }
 }
 
 // search CVEs
@@ -755,4 +774,3 @@ func (me DbStore) CveSearch(
   // return results
   return r, err
 }
-
diff --git a/dbstore/dbstore_test.go b/dbstore/dbstore_test.go
index 2dcadfa..e6e901e 100644
--- a/dbstore/dbstore_test.go
+++ b/dbstore/dbstore_test.go
@@ -1006,13 +1006,43 @@ func TestAddCveFeed(t *testing.T) {
   }
 }
 
+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 := []string {
-    "cisco anyconnect ipc dll mobility secure",
-  }
-// [{"nvd_cve_id":"CVE-2021-1366","rank":-36.4874758136269,"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."},{"nvd_cve_id":"CVE-2021-1567","rank":-34.6220092956805,"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."}]
+  tests := []struct {
+    val string
+    exp []CveSearchRow
+  } {{
+    val: "cisco anyconnect ipc dll mobility secure",
+    exp: []CveSearchRow {
+      CveSearchRow {
+        Id: 9315,
+        CveId: 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: -36.487476,
+      },
+
+      CveSearchRow {
+        Id: 9495,
+        CveId: 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: -34.62201,
+      },
+    },
+  }}
 
   // open test db
   db, err := Open("testdata/cvesearch-test.db")
@@ -1023,12 +1053,12 @@ func TestCveSearch(t *testing.T) {
 
   // run tests
   for _, test := range(tests) {
-    t.Run(test, func(t *testing.T) {
-      rows, err := db.CveSearch(ctx, test)
+    t.Run(test.val, func(t *testing.T) {
+      got, err := db.CveSearch(ctx, test.val)
       if err != nil {
         t.Error(err)
-      } else {
-        t.Errorf("success: got %v", rows)
+      } else if !reflect.DeepEqual(got, test.exp) {
+        t.Errorf("got \"%v\", exp \"%v\"", got, test.exp)
       }
     })
   }
diff --git a/dbstore/sql/feed/insert-cve-fts-refresh.sql b/dbstore/sql/feed/insert-cve-fts-refresh.sql
new file mode 100644
index 0000000..66cd38a
--- /dev/null
+++ b/dbstore/sql/feed/insert-cve-fts-refresh.sql
@@ -0,0 +1,2 @@
+-- trigger cve_fts_all refresh
+INSERT INTO cve_fts_refresh DEFAULT VALUES;
diff --git a/dbstore/sql/init.sql b/dbstore/sql/init.sql
index d38ca37..a54f278 100644
--- a/dbstore/sql/init.sql
+++ b/dbstore/sql/init.sql
@@ -387,7 +387,7 @@ CREATE INDEX in_cves_item_id ON cves(item_id);
 --
 CREATE VIEW cve_nvd_ids AS
   SELECT cve_id,
-         'CVE-' || a.cve_year || '-' || printf('%04d', a.cve_num) AS nvd_cve_id
+         printf('CVE-%04-%04d', cve_year, cve_num) AS nvd_id
     FROM cves;
 
 --
@@ -1225,7 +1225,7 @@ CREATE TRIGGER tr_cve_fts_refresh_after_insert
              h.cve_ref_id, -- cve_ref_id
              j.v2_severity_id, -- v2_severity_id
              l.v3_severity_id, -- v3_severity_id
-             n.nvd_cve_id, -- nvd_cve_id
+             n.nvd_id AS nvd_cve_id, -- nvd_cve_id
              b.assigner, -- assigner
              COALESCE(d.value, ''), -- cve_desc
              COALESCE(g.value, ''), -- pt_desc
@@ -1261,6 +1261,6 @@ CREATE TRIGGER tr_cve_fts_refresh_after_insert
           ON (l.item_id = a.item_id)
         LEFT JOIN severities m
           ON (m.severity_id = l.v3_severity_id)
-        JOIN nvd_cve_ids n
+        JOIN cve_nvd_ids n
           ON (n.cve_id = a.cve_id);
   END;