diff options
author | Paul Duncan <pabs@pablotron.org> | 2022-03-16 08:07:26 -0400 |
---|---|---|
committer | Paul Duncan <pabs@pablotron.org> | 2022-03-16 08:07:26 -0400 |
commit | fea7af49a9da16574646b8e307115f9c65d23489 (patch) | |
tree | 1c6f047b876d92e89e3d575ca4274a45c9ae32bf /dbstore/sql/cisa | |
parent | ff4548f06a056661142d714596efe266fa78814e (diff) | |
download | cvez-fea7af49a9da16574646b8e307115f9c65d23489.tar.bz2 cvez-fea7af49a9da16574646b8e307115f9c65d23489.zip |
add dbstore/sql/cisa
Diffstat (limited to 'dbstore/sql/cisa')
-rw-r--r-- | dbstore/sql/cisa/insert-fts-refresh.sql | 2 | ||||
-rw-r--r-- | dbstore/sql/cisa/insert-product.sql | 3 | ||||
-rw-r--r-- | dbstore/sql/cisa/insert-vendor.sql | 3 | ||||
-rw-r--r-- | dbstore/sql/cisa/insert-vuln.sql | 24 | ||||
-rw-r--r-- | dbstore/sql/cisa/insert.sql | 1 | ||||
-rw-r--r-- | dbstore/sql/cisa/search.sql | 26 |
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; |