diff options
author | Paul Duncan <pabs@pablotron.org> | 2022-02-05 04:37:15 -0500 |
---|---|---|
committer | Paul Duncan <pabs@pablotron.org> | 2022-02-05 04:37:15 -0500 |
commit | bc8fbe94897373c34fbe09fcbb4932b173dee032 (patch) | |
tree | 7265c76726f52581f5a2ecdb0cf8f80344fe6171 | |
parent | dd2de2882f707d84be0b7b8f4b47b49ce5b0e2d3 (diff) | |
download | cvez-bc8fbe94897373c34fbe09fcbb4932b173dee032.tar.bz2 cvez-bc8fbe94897373c34fbe09fcbb4932b173dee032.zip |
dbstore/sql/init.sql: constrain text cols
-rw-r--r-- | dbstore/sql/init.sql | 16 |
1 files changed, 12 insertions, 4 deletions
diff --git a/dbstore/sql/init.sql b/dbstore/sql/init.sql index 37eef54..af5aed3 100644 --- a/dbstore/sql/init.sql +++ b/dbstore/sql/init.sql @@ -7,10 +7,16 @@ CREATE TABLE cpes ( cpe_id INTEGER PRIMARY KEY, -- cpe uri - cpe_uri TEXT UNIQUE NOT NULL, + cpe_uri TEXT UNIQUE NOT NULL CHECK( + LENGTH(cpe_uri) > 5 AND + cpe_uri LIKE 'cpe:/%' + ), -- cpe 2.3 formatting string - cpe23 TEXT UNIQUE NOT NULL + cpe23 TEXT UNIQUE NOT NULL CHECK( + LENGTH(cpe23) > 28 AND + cpe23 LIKE 'cpe:2.3:%' + ) ); -- create all fts table (cross join across all @@ -113,8 +119,10 @@ CREATE TABLE cpe_refs ( REFERENCES cpes(cpe_id), -- url - href TEXT NOT NULL - CHECK (LENGTH(href) > 0), + href TEXT NOT NULL CHECK ( + LENGTH(href) > 0 AND + (href LIKE 'http://%' OR href LIKE 'https://%') + ), -- text description val TEXT NOT NULL |