From bc8fbe94897373c34fbe09fcbb4932b173dee032 Mon Sep 17 00:00:00 2001 From: Paul Duncan Date: Sat, 5 Feb 2022 04:37:15 -0500 Subject: dbstore/sql/init.sql: constrain text cols --- dbstore/sql/init.sql | 16 ++++++++++++---- 1 file changed, 12 insertions(+), 4 deletions(-) (limited to 'dbstore') 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 -- cgit v1.2.3