aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorPaul Duncan <pabs@pablotron.org>2022-02-05 04:37:15 -0500
committerPaul Duncan <pabs@pablotron.org>2022-02-05 04:37:15 -0500
commitbc8fbe94897373c34fbe09fcbb4932b173dee032 (patch)
tree7265c76726f52581f5a2ecdb0cf8f80344fe6171
parentdd2de2882f707d84be0b7b8f4b47b49ce5b0e2d3 (diff)
downloadcvez-bc8fbe94897373c34fbe09fcbb4932b173dee032.tar.bz2
cvez-bc8fbe94897373c34fbe09fcbb4932b173dee032.zip
dbstore/sql/init.sql: constrain text cols
-rw-r--r--dbstore/sql/init.sql16
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