diff options
| -rw-r--r-- | dbstore/dbstore.go | 15 | ||||
| -rw-r--r-- | dbstore/sql/feed/insert-cve.sql | 5 | ||||
| -rw-r--r-- | dbstore/sql/init.sql | 32 |
3 files changed, 44 insertions, 8 deletions
diff --git a/dbstore/dbstore.go b/dbstore/dbstore.go index b8cf990..98f3b61 100644 --- a/dbstore/dbstore.go +++ b/dbstore/dbstore.go @@ -397,6 +397,12 @@ func (me DbStore) addDescriptions(ctx context.Context, tx Tx, ds []nvd_feed.Desc return r, nil } +// Add assigner if they are not already present in the database +func (me DbStore) addAssigner(ctx context.Context, tx Tx, itemId int64, assigner string) error { + _, err := tx.Exec(ctx, "feed/insert-assigner", db_sql.Named("assigner", assigner)) + return err +} + // Add CVE. func (me DbStore) addCve(ctx context.Context, tx Tx, itemId int64, cve nvd_feed.Cve) error { // cve ID @@ -407,8 +413,14 @@ func (me DbStore) addCve(ctx context.Context, tx Tx, itemId int64, cve nvd_feed. return err } + // add cve assigner + if err := me.addAssigner(ctx, tx, itemId, cve.Metadata.Assigner); err != nil { + return err + } + // add cve - rs, err := tx.Exec(ctx, "feed/insert-cve", itemId, cve.Metadata.Id.String(), cve.Metadata.Assigner) + nvdId := cve.Metadata.Id + rs, err := tx.Exec(ctx, "feed/insert-cve", itemId, nvdId.Year(), nvdId.Number(), cve.Metadata.Assigner) if err != nil { return err } @@ -660,6 +672,7 @@ func (me DbStore) addFeed(ctx context.Context, tx Tx, feed nvd_feed.Feed) (int64 // query IDs used by AddCpeMatches() var addCveFeedQueryIds = []string { "feed/insert", + "feed/insert-assigner", "feed/insert-cve", "feed/insert-cve-desc", "feed/insert-cve-problem", diff --git a/dbstore/sql/feed/insert-cve.sql b/dbstore/sql/feed/insert-cve.sql index 02c0a8a..9e62048 100644 --- a/dbstore/sql/feed/insert-cve.sql +++ b/dbstore/sql/feed/insert-cve.sql @@ -1,2 +1,5 @@ -- add cve -INSERT INTO cves(item_id, nvd_cve_id, assigner) VALUES (?, ?, ?); +INSERT INTO cves(item_id, cve_year, cve_num, assigner_id) VALUES ( + ?, ?, ?, + (SELECT assigner_id FROM assigners WHERE assigner = ?) +); diff --git a/dbstore/sql/init.sql b/dbstore/sql/init.sql index 9ea7aa6..82d8561 100644 --- a/dbstore/sql/init.sql +++ b/dbstore/sql/init.sql @@ -336,6 +336,19 @@ CREATE TABLE feed_items ( CREATE INDEX in_feed_items_feed_id ON feed_items(feed_id); -- +-- CVE assigners +-- +CREATE TABLE assigners ( + -- assigner unique ID + assigner_id INTEGER PRIMARY KEY, + + -- assigner value + assigner TEXT UNIQUE NOT NULL CHECK ( + LENGTH(assigner) > 0 + ) +); + +-- -- CVEs -- -- NOTE: this combines the concept of an "item" and a "cve" from the @@ -349,14 +362,21 @@ CREATE TABLE cves ( item_id INTEGER NOT NULL REFERENCES feed_items(item_id), - -- NVD CVE ID (e.g. "CVE-YYYY-XXXX) - nvd_cve_id TEXT NOT NULL CHECK ( - LENGTH(nvd_cve_id) BETWEEN 1 AND 17 AND - nvd_cve_id LIKE 'CVE-____-%' + -- year of NVD CVE ID, (e.g. the "XXXX" in "CVE-XXXX-YYYY") + cve_year SMALLINT NOT NULL CHECK ( + cve_year BETWEEN 1999 AND 2126 + ), + + -- number of NVD CVE ID, (e.g. the "YYYY" in "CVE-XXXX-YYYY") + cve_num SMALLINT NOT NULL CHECK ( + -- there is no upper bound, but let's pick + -- something sane + cve_num BETWEEN 1 AND 33554432 ), - -- assigner, or empty string - assigner TEXT NOT NULL DEFAULT '' + -- assigner ID + assigner_id INTEGER NOT NULL + REFERENCES assigners(assigner_id) ); -- create index |