aboutsummaryrefslogtreecommitdiff
path: root/dbstore/sql/cisa
diff options
context:
space:
mode:
Diffstat (limited to 'dbstore/sql/cisa')
-rw-r--r--dbstore/sql/cisa/insert-fts-refresh.sql2
-rw-r--r--dbstore/sql/cisa/insert-product.sql3
-rw-r--r--dbstore/sql/cisa/insert-vendor.sql3
-rw-r--r--dbstore/sql/cisa/insert-vuln.sql24
-rw-r--r--dbstore/sql/cisa/insert.sql1
-rw-r--r--dbstore/sql/cisa/search.sql26
6 files changed, 59 insertions, 0 deletions
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;