diff options
Diffstat (limited to 'ext/fts5/test/fts5secure.test')
| -rw-r--r-- | ext/fts5/test/fts5secure.test | 348 |
1 files changed, 348 insertions, 0 deletions
diff --git a/ext/fts5/test/fts5secure.test b/ext/fts5/test/fts5secure.test new file mode 100644 index 0000000..7314946 --- /dev/null +++ b/ext/fts5/test/fts5secure.test @@ -0,0 +1,348 @@ +# 2023 Feb 17 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#************************************************************************* +# + +source [file join [file dirname [info script]] fts5_common.tcl] +ifcapable !fts5 { finish_test ; return } +set ::testprefix fts5secure + +proc dump {tname} { + execsql_pp "SELECT * FROM ${tname}_idx" + execsql_pp "SELECT id, quote(block), fts5_decode(id,block) FROM ${tname}_data" +} + + +do_execsql_test 0.0 { + CREATE VIRTUAL TABLE t1 USING fts5(ab); + CREATE VIRTUAL TABLE v1 USING fts5vocab('t1', 'instance'); + INSERT INTO t1(rowid, ab) VALUES + (0,'abc'), (1,'abc'), (2,'abc'), (3,'abc'), (4,'def'); +} + +do_execsql_test 0.1 { + INSERT INTO t1(t1, rank) VALUES('secure-delete', 1); +} + +do_execsql_test 0.2 { + DELETE FROM t1 WHERE rowid=2; +} + +do_execsql_test 0.3 { + SELECT count(*) FROM t1_data +} 3 + +do_execsql_test 0.4 { + INSERT INTO t1(t1) VALUES('integrity-check'); +} + +do_execsql_test 0.5 { + DELETE FROM t1 WHERE rowid=3; +} + +do_execsql_test 0.6 { + INSERT INTO t1(t1) VALUES('integrity-check'); +} + +do_execsql_test 0.7 { + DELETE FROM t1 WHERE rowid=0; +} + +do_execsql_test 0.8 { + INSERT INTO t1(t1) VALUES('integrity-check'); +} + +#---------------------------------- + +do_execsql_test 1.0 { + CREATE VIRTUAL TABLE t2 USING fts5(ab); + INSERT INTO t2(rowid, ab) VALUES (5, 'key'), (6, 'value'); + INSERT INTO t2(t2, rank) VALUES('secure-delete', 1); +} + +#execsql_pp { SELECT id, quote(block) FROM t1_data } +#execsql_pp { SELECT segid, quote(term), pgno FROM t1_idx } + +do_execsql_test 1.1 { + DELETE FROM t2 WHERE rowid = 5; +} + +do_execsql_test 1.2 { + INSERT INTO t2(t2) VALUES('integrity-check'); +} + +do_execsql_test 1.3 { + DELETE FROM t2 WHERE rowid = 6; +} + +do_execsql_test 1.4 { + INSERT INTO t2(t2) VALUES('integrity-check'); +} + +do_execsql_test 1.5 { + SELECT * FROM t2('value'); + SELECT * FROM t2('v*'); +} + +do_execsql_test 1.6 { + SELECT * FROM t2('value') ORDER BY rowid DESC; + SELECT * FROM t2('v*') ORDER BY rowid DESC; +} +execsql_pp { + SELECT id, quote(block) FROM t2_data; +} + +#---------------------------------- + +do_execsql_test 2.0 { + CREATE VIRTUAL TABLE ft USING fts5(ab); + CREATE VIRTUAL TABLE vocab USING fts5vocab('ft', 'instance'); + INSERT INTO ft(rowid, ab) VALUES + (1, 'one'), + (2, 'two'), + (3, 'three'), + (4, 'four'), + (5, 'one one'), + (6, 'one two'), + (7, 'one three'), + (8, 'one four'), + (9, 'two one'), + (10, 'two two'), + (11, 'two three'), + (12, 'two four'), + (13, 'three one'), + (14, 'three two'), + (15, 'three three'), + (16, 'three four'); +} + +do_execsql_test 2.1 { + SELECT count(*) FROM ft_data; +} {3} + +do_execsql_test 2.2 { + INSERT INTO ft(ft, rank) VALUES('secure-delete', 1); +} + +do_execsql_test 2.3 { + DELETE FROM ft WHERE rowid=9; +} + +do_execsql_test 2.4 { + INSERT INTO ft(ft) VALUES('integrity-check'); +} + +do_execsql_test 2.5 { + DELETE FROM ft WHERE ab LIKE '%two%' +} + +do_execsql_test 2.6 { + INSERT INTO ft(ft) VALUES('integrity-check'); +} + +do_execsql_test 2.7 { + SELECT count(*) FROM ft_data; +} {3} + +#---------------------------------- +reset_db + +set ::vocab { + one two three four five six seven eight nine ten + eleven twelve thirteen fourteen fifteen sixteen + seventeen eighteen nineteen twenty +} +proc rnddoc {} { + set nVocab [llength $::vocab] + set ret [list] + for {set ii 0} {$ii < 8} {incr ii} { + lappend ret [lindex $::vocab [expr int(abs(rand()) * $nVocab)]] + } + set ret +} + +proc contains {list val} { + expr {[lsearch $list $val]>=0} +} + +foreach {tn pgsz} { + 2 64 + 1 1000 +} { + reset_db + db function rnddoc rnddoc + db function contains contains + + expr srand(1) + + do_execsql_test 3.$tn.0 { + CREATE VIRTUAL TABLE t1 USING fts5(x); + INSERT INTO t1(t1, rank) VALUES('pgsz', $pgsz); + WITH s(i) AS ( + VALUES(1) UNION SELECT i+1 FROM s WHERE i<20 + ) + INSERT INTO t1 SELECT rnddoc() FROM s; + } + + do_execsql_test 3.$tn.1 { + INSERT INTO t1(t1, rank) VALUES('secure-delete', 1); + } + + foreach {rowid} { + 6 16 3 4 9 14 13 7 20 15 19 10 11 2 5 18 17 1 12 8 + } { + + do_execsql_test 3.$tn.2.$rowid { + DELETE FROM t1 WHERE rowid=$rowid; + } + do_execsql_test 3.$tn.2.$rowid.ic { + INSERT INTO t1(t1) VALUES('integrity-check'); + } + + foreach v $::vocab { + do_execsql_test 3.$tn.2.$rowid.q.$v { + SELECT rowid FROM t1($v) + } [db eval {SELECT rowid FROM t1 WHERE contains(x, $v)}] + + do_execsql_test 3.$tn.2.$rowid.q.$v.DESC { + SELECT rowid FROM t1($v) ORDER BY 1 DESC + } [db eval {SELECT rowid FROM t1 WHERE contains(x, $v) ORDER BY 1 DESC}] + } + } +} + +do_execsql_test 3.3 { + INSERT INTO t1(x) VALUES('optimize'); + INSERT INTO t1(t1) VALUES('optimize'); + SELECT count(*) FROM t1_data; +} {3} + +#---------------------------------- +reset_db +do_execsql_test 4.0 { + CREATE VIRTUAL TABLE t1 USING fts5(x); + INSERT INTO t1(t1, rank) VALUES('pgsz', 32); + INSERT INTO t1(t1, rank) VALUES('secure-delete', 1); +} + +set L1 [string repeat abcdefghij 10] +set L2 [string repeat 1234567890 10] + +do_execsql_test 4.1 { + INSERT INTO t1 VALUES('aa' || $L1 || ' ' || $L2); +} +do_execsql_test 4.2 { + DELETE FROM t1 WHERE rowid=1 +} +do_execsql_test 4.3 { + INSERT INTO t1(t1) VALUES('integrity-check'); +} + +#---------------------------------- +reset_db +do_execsql_test 5.0 { + CREATE VIRTUAL TABLE t1 USING fts5(x); + INSERT INTO t1(t1, rank) VALUES('pgsz', 32); + INSERT INTO t1(t1, rank) VALUES('secure-delete', 1); +} + +set doc "aa [string repeat {abc } 60]" + +do_execsql_test 5.1 { + BEGIN; + INSERT INTO t1 VALUES($doc); + INSERT INTO t1 VALUES('aa abc'); + COMMIT; +} + +do_execsql_test 5.2 { + DELETE FROM t1 WHERE rowid = 1; +} + +do_execsql_test 5.3 { + INSERT INTO t1(t1) VALUES('integrity-check'); +} + +do_execsql_test 5.4 { SELECT rowid FROM t1('abc'); } 2 +do_execsql_test 5.5 { SELECT rowid FROM t1('aa'); } 2 + +#------------------------------------------------------------------------- +# Tests for the bug fixed by https://sqlite.org/src/info/4b60a1c3 +# +reset_db +do_execsql_test 6.0 { + CREATE VIRTUAL TABLE fts USING fts5(content); + INSERT INTO fts(fts, rank) VALUES ('secure-delete', 1); + INSERT INTO fts(rowid, content) VALUES + (3407, 'profile profile profile profile profile profile profile profile pull pulling pulling really'); + DELETE FROM fts WHERE rowid IS 3407; + INSERT INTO fts(fts) VALUES ('integrity-check'); +} + +foreach {tn detail} { + 1 full + 2 column + 3 none +} { + do_execsql_test 6.1.$detail " + DROP TABLE IF EXISTS t1; + CREATE VIRTUAL TABLE t1 USING fts5(x, detail=$detail); + " + + do_execsql_test 6.2.$detail { + INSERT INTO t1(t1, rank) VALUES('secure-delete', 1); + } + + for {set ii 1} {$ii < 100} {incr ii} { + do_execsql_test 6.3.$detail.$ii.1 { + BEGIN; + INSERT INTO t1(rowid, x) VALUES(10, 'word1'); + WITH s(i) AS ( + SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<CAST($ii AS integer) + ) + INSERT INTO t1(x) SELECT 'word3' FROM s; + COMMIT; + INSERT INTO t1(t1) VALUES('optimize'); + } + + do_execsql_test 6.3.$detail.$ii.2 { + DELETE FROM t1 WHERE rowid=10; + INSERT INTO t1(t1) VALUES ('integrity-check'); + } + + do_execsql_test 6.3.$detail.$ii.3 { + DELETE FROM t1; + } + + do_execsql_test 6.3.$detail.$ii.4 { + BEGIN; + INSERT INTO t1(rowid, x) VALUES(10, 'tokenA'); + WITH s(i) AS ( + SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<CAST($ii AS integer) + ) + INSERT INTO t1(x) SELECT group_concat('tokenB ') FROM s; + COMMIT; + INSERT INTO t1(t1) VALUES('optimize'); + } + + do_execsql_test 6.3.$detail.$ii.5 { + DELETE FROM t1 WHERE rowid=10; + INSERT INTO t1(t1) VALUES ('integrity-check'); + } + + do_execsql_test 6.3.$detail.$ii.6 { + DELETE FROM t1; + } + } +} + + +finish_test + |