github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/testdata/tcl/trustschema1.test (about) 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 16 set testdir [file dirname $argv0] 17 source $testdir/tester.tcl 18 set testprefix trustschema1 19 20 # edgy functions used in generated columns 21 # 22 proc f1 {x} {return $x} 23 do_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 } {} 32 do_catchsql_test 1.110 { 33 SELECT a, b, c FROM t1; 34 } {0 {100 101 102 200 201 202}} 35 do_execsql_test 1.120 { 36 PRAGMA trusted_schema=OFF; 37 } {} 38 do_catchsql_test 1.130 { 39 SELECT a, b FROM t1; 40 } {0 {100 101 200 201}} 41 do_catchsql_test 1.140 { 42 SELECT a, b, c FROM t1; 43 } {1 {unsafe use of f2()}} 44 do_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()}} 49 do_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 # 58 do_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()}} 62 do_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()}} 66 do_catchsql_test 1.211 { 67 PRAGMA trusted_schema=On; 68 CREATE TABLE t2(a,b,c,CHECK(f2(c)==c)); 69 } {0 {}} 70 do_catchsql_test 1.220 { 71 INSERT INTO t2 VALUES(1,2,3); 72 SELECT * FROM t2; 73 } {0 {1 2 3}} 74 do_catchsql_test 1.230 { 75 PRAGMA trusted_schema=off; 76 INSERT INTO t2 VALUES(4,5,6); 77 } {1 {unsafe use of f2()}} 78 do_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. 83 do_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 # 92 do_catchsql_test 1.300 { 93 CREATE TABLE t3(a,b DEFAULT(f2(25))); 94 } {0 {}} 95 do_catchsql_test 1.310 { 96 PRAGMA trusted_schema=Off; 97 INSERT INTO t3(a) VALUES(1); 98 } {1 {unsafe use of f2()}} 99 do_catchsql_test 1.311 { 100 INSERT INTO t3(a,b) VALUES(1,2); 101 } {0 {}} 102 do_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 # 110 do_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} 117 do_catchsql_test 1.410 { 118 CREATE INDEX t4a ON t4(a) WHERE f3(c); 119 } {1 {unsafe use of f3()}} 120 do_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()}} 124 do_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} 128 do_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} 133 do_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 # 141 do_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} 148 do_catchsql_test 1.510 { 149 CREATE INDEX t5x1 ON t5(a+f3(b)); 150 } {1 {unsafe use of f3()}} 151 do_catchsql_test 1.520 { 152 PRAGMA trusted_schema=OFF; 153 CREATE INDEX t5x1 ON t5(a+f2(b)); 154 } {1 {unsafe use of f2()}} 155 do_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} 159 do_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} 164 do_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 # 172 reset_db 173 db function f1 -innocuous -deterministic f1 174 db function f2 -deterministic f1 175 db function f3 -directonly -deterministic f1 176 do_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} 182 do_catchsql_test 2.110 { 183 PRAGMA trusted_schema=ON; 184 SELECT * FROM v1a; 185 } {1 {unsafe use of f3()}} 186 do_catchsql_test 2.111 { 187 PRAGMA trusted_schema=OFF; 188 SELECT * FROM v1a; 189 } {1 {unsafe use of f3()}} 190 do_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} 195 do_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} 199 do_catchsql_test 2.140 { 200 PRAGMA trusted_schema=ON; 201 SELECT * FROM v1b; 202 } {0 {5 125 -11}} 203 do_catchsql_test 2.141 { 204 PRAGMA trusted_schema=OFF; 205 SELECT * FROM v1b; 206 } {1 {unsafe use of f2()}} 207 do_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 # 215 do_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 } {} 222 do_catchsql_test 3.110 { 223 INSERT INTO t1 VALUES(7,6,5); 224 } {1 {unsafe use of f3()}} 225 do_execsql_test 3.111 { 226 SELECT * FROM t1; 227 SELECT * FROM t2; 228 } {} 229 230 do_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} 239 do_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()}} 245 do_execsql_test 3.131 { 246 SELECT * FROM t1; 247 SELECT * FROM t2; 248 } {} 249 250 251 finish_test