From fea7af49a9da16574646b8e307115f9c65d23489 Mon Sep 17 00:00:00 2001
From: Paul Duncan <pabs@pablotron.org>
Date: Wed, 16 Mar 2022 08:07:26 -0400
Subject: add dbstore/sql/cisa

---
 dbstore/sql/cisa/insert-fts-refresh.sql |  2 ++
 dbstore/sql/cisa/insert-product.sql     |  3 +++
 dbstore/sql/cisa/insert-vendor.sql      |  3 +++
 dbstore/sql/cisa/insert-vuln.sql        | 24 ++++++++++++++++++++++++
 dbstore/sql/cisa/insert.sql             |  1 +
 dbstore/sql/cisa/search.sql             | 26 ++++++++++++++++++++++++++
 6 files changed, 59 insertions(+)
 create mode 100644 dbstore/sql/cisa/insert-fts-refresh.sql
 create mode 100644 dbstore/sql/cisa/insert-product.sql
 create mode 100644 dbstore/sql/cisa/insert-vendor.sql
 create mode 100644 dbstore/sql/cisa/insert-vuln.sql
 create mode 100644 dbstore/sql/cisa/insert.sql
 create mode 100644 dbstore/sql/cisa/search.sql

diff --git a/dbstore/sql/cisa/insert-fts-refresh.sql b/dbstore/sql/cisa/insert-fts-refresh.sql
new file mode 100644
index 0000000..62a4ffe
--- /dev/null
+++ b/dbstore/sql/cisa/insert-fts-refresh.sql
@@ -0,0 +1,2 @@
+-- trigger cisa_vulns_fts refresh
+INSERT INTO cisa_vulns_fts_refresh DEFAULT VALUES;
diff --git a/dbstore/sql/cisa/insert-product.sql b/dbstore/sql/cisa/insert-product.sql
new file mode 100644
index 0000000..cbf9103
--- /dev/null
+++ b/dbstore/sql/cisa/insert-product.sql
@@ -0,0 +1,3 @@
+-- insert product if it does not exist, or do nothing if the the product
+-- name is already present.
+INSERT INTO cisa_products(name) VALUES (?) ON CONFLICT(name) DO NOTHING;
diff --git a/dbstore/sql/cisa/insert-vendor.sql b/dbstore/sql/cisa/insert-vendor.sql
new file mode 100644
index 0000000..a5f1a12
--- /dev/null
+++ b/dbstore/sql/cisa/insert-vendor.sql
@@ -0,0 +1,3 @@
+-- insert vendor/project if it does not exist, or do nothing if the the
+-- name is already present.
+INSERT INTO cisa_vendors(name) VALUES (?) ON CONFLICT(name) DO NOTHING;
diff --git a/dbstore/sql/cisa/insert-vuln.sql b/dbstore/sql/cisa/insert-vuln.sql
new file mode 100644
index 0000000..8d3d448
--- /dev/null
+++ b/dbstore/sql/cisa/insert-vuln.sql
@@ -0,0 +1,24 @@
+-- insert CISA catalog vulnerability
+INSERT INTO cisa_vulns (
+  cat_id,
+  cve_year,
+  cve_num,
+  vendor_id,
+  product_id,
+  name,
+  added_at,
+  description,
+  action,
+  due_at
+) VALUES (
+  ?, -- cat id
+  ?, -- cve year
+  ?, -- cve num
+  (SELECT vendor_id FROM cisa_vendors WHERE name = ?), -- vendor ID
+  (SELECT product_id FROM cisa_products WHERE name = ?), -- product ID
+  ?, -- name
+  ?, -- added at
+  ?, -- description
+  ?, -- action
+  ?  -- due at
+);
diff --git a/dbstore/sql/cisa/insert.sql b/dbstore/sql/cisa/insert.sql
new file mode 100644
index 0000000..5f677b0
--- /dev/null
+++ b/dbstore/sql/cisa/insert.sql
@@ -0,0 +1 @@
+INSERT INTO cisa_catalogs(title, version, released_at) VALUES (?, ?, ?);
diff --git a/dbstore/sql/cisa/search.sql b/dbstore/sql/cisa/search.sql
new file mode 100644
index 0000000..daaf5cf
--- /dev/null
+++ b/dbstore/sql/cisa/search.sql
@@ -0,0 +1,26 @@
+-- search CISA vulnerabilities
+SELECT COALESCE(json_group_array(json_object(
+         'vuln_id', a.vuln_id,
+         'cve_id', c.nvd_id,
+         'vendor', b.vendor,
+         'product', b.product,
+         'name', b.name,
+         'added_at', b.added_at,
+         'action', b.action,
+         'due_at', b.due_at,
+         'rank', a.min_rank
+       )), '[]')
+
+  FROM (
+    SELECT vuln_id,
+           MIN(rank) AS min_rank
+      FROM cisa_vulns_fts
+     WHERE cisa_vulns_fts MATCH :q
+     GROUP BY vuln_id
+  ) a
+  JOIN cisa_vulns b
+    ON (b.vuln_id = a.vuln_id)
+  JOIN cisa_vuln_cve_ids c
+    ON (c.vuln_id = b.vuln_id)
+
+ ORDER BY a.min_rank;
-- 
cgit v1.2.3