1# 2020-01-08 2# 3# The author disclaims copyright to this source code. In place of 4# a legal notice, here is a blessing: 5# 6# May you do good and not evil. 7# May you find forgiveness for yourself and forgive others. 8# May you share freely, never taking more than you give. 9# 10#*********************************************************************** 11# 12# Test cases for managing execution of code snippets found in untrusted 13# schemas. 14# 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18set testprefix trustschema1 19 20# edgy functions used in generated columns 21# 22proc f1 {x} {return $x} 23do_test 1.100 { 24 db function f1 -innocuous -deterministic f1 25 db function f2 -deterministic f1 26 db function f3 -directonly -deterministic f1 27 db eval { 28 CREATE TABLE t1(a, b AS (f1(a+1)), c AS (f2(a+2))); 29 INSERT INTO t1 VALUES(100),(200); 30 } 31} {} 32do_catchsql_test 1.110 { 33 SELECT a, b, c FROM t1; 34} {0 {100 101 102 200 201 202}} 35do_execsql_test 1.120 { 36 PRAGMA trusted_schema=OFF; 37} {} 38do_catchsql_test 1.130 { 39 SELECT a, b FROM t1; 40} {0 {100 101 200 201}} 41do_catchsql_test 1.140 { 42 SELECT a, b, c FROM t1; 43} {1 {unsafe use of f2()}} 44do_catchsql_test 1.150 { 45 PRAGMA trusted_schema=ON; 46 DROP TABLE t1; 47 CREATE TABLE t1(a, b AS (f3(a+1))); 48} {1 {unsafe use of f3()}} 49do_execsql_test 1.160 { 50 PRAGMA trusted_schema=OFF; 51 CREATE TEMP TABLE temp1(a,b AS (f3(a+1))); 52 INSERT INTO temp1(a) VALUES(100),(900); 53 SELECT * FROM temp1; 54} {100 101 900 901} 55 56# edgy functions used in CHECK constraints 57# 58do_catchsql_test 1.200 { 59 PRAGMA trusted_schema=ON; 60 CREATE TABLE t2(a,b,c,CHECK(f3(c)==c)); 61} {1 {unsafe use of f3()}} 62do_catchsql_test 1.210 { 63 PRAGMA trusted_schema=Off; 64 CREATE TABLE t2(a,b,c,CHECK(f2(c)==c)); 65} {1 {unsafe use of f2()}} 66do_catchsql_test 1.211 { 67 PRAGMA trusted_schema=On; 68 CREATE TABLE t2(a,b,c,CHECK(f2(c)==c)); 69} {0 {}} 70do_catchsql_test 1.220 { 71 INSERT INTO t2 VALUES(1,2,3); 72 SELECT * FROM t2; 73} {0 {1 2 3}} 74do_catchsql_test 1.230 { 75 PRAGMA trusted_schema=off; 76 INSERT INTO t2 VALUES(4,5,6); 77} {1 {unsafe use of f2()}} 78do_execsql_test 1.231 { 79 SELECT * FROM t2; 80} {1 2 3} 81# Ok to put as many edgy functions as you want in a 82# TEMP table. 83do_execsql_test 1.240 { 84 PRAGMA trusted_schema=OFF; 85 CREATE TEMP TABLE temp2(a, b, CHECK(f3(b)==b)); 86 INSERT INTO temp2(a,b) VALUES(1,2),('x','y'); 87 SELECT * FROM temp2; 88} {1 2 x y} 89 90# edgy functions used in DEFAULT constraints 91# 92do_catchsql_test 1.300 { 93 CREATE TABLE t3(a,b DEFAULT(f2(25))); 94} {0 {}} 95do_catchsql_test 1.310 { 96 PRAGMA trusted_schema=Off; 97 INSERT INTO t3(a) VALUES(1); 98} {1 {unsafe use of f2()}} 99do_catchsql_test 1.311 { 100 INSERT INTO t3(a,b) VALUES(1,2); 101} {0 {}} 102do_execsql_test 1.320 { 103 CREATE TEMP TABLE temp3(a, b DEFAULT(f3(31))); 104 INSERT INTO temp3(a) VALUES(22); 105 SELECT * FROM temp3; 106} {22 31} 107 108# edgy functions used in partial indexes. 109# 110do_execsql_test 1.400 { 111 CREATE TABLE t4(a,b,c); 112 INSERT INTO t4 VALUES(1,2,3),('a','b','c'),(4,'d',0); 113 SELECT * FROM t4; 114 CREATE TEMP TABLE temp4(a,b,c); 115 INSERT INTO temp4 SELECT * FROM t4; 116} {1 2 3 a b c 4 d 0} 117do_catchsql_test 1.410 { 118 CREATE INDEX t4a ON t4(a) WHERE f3(c); 119} {1 {unsafe use of f3()}} 120do_catchsql_test 1.420 { 121 PRAGMA trusted_schema=OFF; 122 CREATE INDEX t4a ON t4(a) WHERE f2(c); 123} {1 {unsafe use of f2()}} 124do_execsql_test 1.421 { 125 CREATE INDEX t4a ON t4(a) WHERE f1(c); 126 SELECT a FROM t4 WHERE f1(c) ORDER BY a; 127} {1} 128do_execsql_test 1.430 { 129 PRAGMA trusted_schema=ON; 130 CREATE INDEX t4b ON t4(b) WHERE f2(c); 131 SELECT b FROM t4 WHERE f2(c) ORDER BY b; 132} {2} 133do_execsql_test 1.440 { 134 PRAGMA trusted_schema=OFF; 135 CREATE INDEX temp4a ON temp4(a) WHERE f3(c); 136 SELECT a FROM temp4 WHERE f2(c) ORDER BY a; 137} {1} 138 139# edgy functions used in index expressions 140# 141do_execsql_test 1.500 { 142 CREATE TABLE t5(a,b,c); 143 INSERT INTO t5 VALUES(1,2,3),(4,5,6),(7,0,-3); 144 SELECT * FROM t5; 145 CREATE TEMP TABLE temp5(a,b,c); 146 INSERT INTO temp5 SELECT * FROM t5; 147} {1 2 3 4 5 6 7 0 -3} 148do_catchsql_test 1.510 { 149 CREATE INDEX t5x1 ON t5(a+f3(b)); 150} {1 {unsafe use of f3()}} 151do_catchsql_test 1.520 { 152 PRAGMA trusted_schema=OFF; 153 CREATE INDEX t5x1 ON t5(a+f2(b)); 154} {1 {unsafe use of f2()}} 155do_execsql_test 1.521 { 156 CREATE INDEX t5x1 ON t5(a+f1(b)); 157 SELECT * FROM t5 INDEXED BY t5x1 WHERE a+f1(b)=3; 158} {1 2 3} 159do_execsql_test 1.530 { 160 PRAGMA trusted_schema=ON; 161 CREATE INDEX t5x2 ON t5(b+f2(c)); 162 SELECT * FROM t5 INDEXED BY t5x2 WHERE b+f2(c)=11; 163} {4 5 6} 164do_execsql_test 1.540 { 165 PRAGMA trusted_schema=OFF; 166 CREATE INDEX temp5x1 ON temp5(a+f3(b)); 167 SELECT * FROM temp5 INDEXED BY temp5x1 WHERE a+f3(b)=7; 168} {7 0 -3} 169 170# edgy functions in VIEWs 171# 172reset_db 173db function f1 -innocuous -deterministic f1 174db function f2 -deterministic f1 175db function f3 -directonly -deterministic f1 176do_execsql_test 2.100 { 177 CREATE TABLE t1(a,b,c); 178 INSERT INTO t1 VALUES(1,2,3),(100,50,75),(-11,22,-33); 179 CREATE VIEW v1a AS SELECT f3(a+b) FROM t1; 180 SELECT f3(a+b) FROM t1; 181} {3 150 11} 182do_catchsql_test 2.110 { 183 PRAGMA trusted_schema=ON; 184 SELECT * FROM v1a; 185} {1 {unsafe use of f3()}} 186do_catchsql_test 2.111 { 187 PRAGMA trusted_schema=OFF; 188 SELECT * FROM v1a; 189} {1 {unsafe use of f3()}} 190do_execsql_test 2.120 { 191 DROP VIEW v1a; 192 CREATE TEMP VIEW v1a AS SELECT f3(a+b) FROM t1; 193 SELECT * FROM v1a; 194} {3 150 11} 195do_execsql_test 2.130 { 196 CREATE VIEW v1b AS SELECT f2(b+c) FROM t1; 197 SELECT f2(b+c) FROM t1; 198} {5 125 -11} 199do_catchsql_test 2.140 { 200 PRAGMA trusted_schema=ON; 201 SELECT * FROM v1b; 202} {0 {5 125 -11}} 203do_catchsql_test 2.141 { 204 PRAGMA trusted_schema=OFF; 205 SELECT * FROM v1b; 206} {1 {unsafe use of f2()}} 207do_execsql_test 2.150 { 208 DROP VIEW v1b; 209 CREATE TEMP VIEW v1b AS SELECT f2(b+c) FROM t1; 210 SELECT * FROM v1b; 211} {5 125 -11} 212 213# edgy functions inside of triggers 214# 215do_execsql_test 3.100 { 216 DELETE FROM t1; 217 CREATE TABLE t2(x); 218 CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN 219 INSERT INTO t2(x) SELECT f3(new.a); 220 END; 221} {} 222do_catchsql_test 3.110 { 223 INSERT INTO t1 VALUES(7,6,5); 224} {1 {unsafe use of f3()}} 225do_execsql_test 3.111 { 226 SELECT * FROM t1; 227 SELECT * FROM t2; 228} {} 229 230do_execsql_test 3.120 { 231 DROP TRIGGER r1; 232 CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN 233 INSERT INTO t2(x) SELECT f2(new.a)+100; 234 END; 235 PRAGMA trusted_schema=ON; 236 INSERT INTO t1 VALUES(7,6,5); 237 SELECT * FROM t1, t2; 238} {7 6 5 107} 239do_catchsql_test 3.130 { 240 DELETE FROM t1; 241 DELETE FROM t2; 242 PRAGMA trusted_schema=OFF; 243 INSERT INTO t1 VALUES(7,6,5); 244} {1 {unsafe use of f2()}} 245do_execsql_test 3.131 { 246 SELECT * FROM t1; 247 SELECT * FROM t2; 248} {} 249 250 251finish_test 252