diff options
| -rw-r--r-- | dbstore/cvesearchrow.go | 15 | ||||
| -rw-r--r-- | dbstore/sql/feed/search.sql | 32 |
2 files changed, 47 insertions, 0 deletions
diff --git a/dbstore/cvesearchrow.go b/dbstore/cvesearchrow.go new file mode 100644 index 0000000..4128347 --- /dev/null +++ b/dbstore/cvesearchrow.go @@ -0,0 +1,15 @@ +package dbstore + +import ( + "github.com/pablotron/cvez/feed" +) + +// CveSearch() result +type CveSearchRow struct { + Id int64 `json:"cve_id"` // internal database CVE ID + CveId feed.CveId `json:"nvd_cve_id"` // NVD CVE ID + Description string `json:"description"` // CVE description (english) + V3BaseScore feed.Score `json:"v3_base_score"` // CVSSv3 base score + V3Severity feed.Severity `json:"v3_severity"` // CVSSv3 severity + Rank float32 `json:"rank"` // search result rank +} diff --git a/dbstore/sql/feed/search.sql b/dbstore/sql/feed/search.sql new file mode 100644 index 0000000..cb80470 --- /dev/null +++ b/dbstore/sql/feed/search.sql @@ -0,0 +1,32 @@ +-- search CVEs +-- FIXME: should this be moved to cve/search +SELECT COALESCE(json_group_array(json_object( + 'cve_id', b.cve_id, + 'nvd_cve_id', printf('CVE-%4d-%04d', b.cve_year, b.cve_num), + 'description', d.value, + 'v3_base_score', e.v3_base_score / 10.0, + 'v3_severity', f.name, + 'rank', a.min_rank + )), '[]') + + FROM ( + SELECT cve_id, + MIN(rank) AS min_rank + FROM cve_fts_all + WHERE cve_fts_all MATCH :q + GROUP BY cve_id + ) a + JOIN cves b + ON (b.cve_id = a.cve_id) + JOIN cve_descriptions c + ON (c.cve_id = b.cve_id) + JOIN descriptions d + ON (d.desc_id = c.desc_id) + JOIN item_cvss_v3_impacts e + ON (e.item_id = b.item_id) + JOIN severities f + ON (f.severity_id = e.v3_severity_id) + + WHERE d.lang = 'en' + + ORDER BY a.min_rank; |