Debian Codegraph @

test/trustschema1.test

raw
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 # 2023-01-09 https://sqlite.org/forum/forumpost/c88a671ad083d153
251 #
252 do_execsql_test 4.1 {
253 PRAGMA trusted_schema=OFF;
254 CREATE VIEW test41(x) AS SELECT json_extract('{"a":123}','$.a');
255 SELECT * FROM test41;
256 } 123
257 do_execsql_test 4.2 {
258 PRAGMA trusted_schema=ON;
259 SELECT * FROM test41;
260 } 123
261
262 finish_test