2 # codec.test developed by Stephen Lombardo (Zetetic LLC)
3 # sjlombardo at zetetic dot net
6 # Copyright (c) 2018, ZETETIC LLC
7 # Redistribution and use in source and binary forms, with or without
8 # modification, are permitted provided that the following conditions are met:
9 # * Redistributions of source code must retain the above copyright
10 # notice, this list of conditions and the following disclaimer.
11 # * Redistributions in binary form must reproduce the above copyright
12 # notice, this list of conditions and the following disclaimer in the
13 # documentation and/or other materials provided with the distribution.
14 # * Neither the name of the ZETETIC LLC nor the
15 # names of its contributors may be used to endorse or promote products
16 # derived from this software without specific prior written permission.
18 # THIS SOFTWARE IS PROVIDED BY ZETETIC LLC ''AS IS'' AND ANY
19 # EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
20 # WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
21 # DISCLAIMED. IN NO EVENT SHALL ZETETIC LLC BE LIABLE FOR ANY
22 # DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
23 # (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
24 # LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
25 # ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
26 # (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
27 # SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
29 # This file implements regression tests for SQLite library. The
30 # focus of this script is testing code cipher features.
32 # NOTE: tester.tcl has overridden the definition of sqlite3 to
33 # automatically pass in a key value. Thus tests in this file
34 # should explicitly close and open db with sqlite_orig in order
35 # to bypass default key assignment.
37 set testdir [file dirname $argv0]
38 source $testdir/tester.tcl
39 source $testdir/sqlcipher.tcl
41 set hexkeyspec "\"x'98483C6EB40B6C31A448C22A66DED3B5E5E8D5119CAC8327B655C8B5C483648101010101010101010101010101010101'\""
43 # verify pragma cipher_salt returns the first 16 bytes
44 # of an existing database
45 do_test test-pragma-salt-get {
46 sqlite_orig db test.db
47 execsql { PRAGMA key = 'test'; }
52 set header [string tolower [hexio_read test.db 0 16]]
53 string equal $header $salt
55 file delete -force test.db
57 # explicitly set the salt of a new database
58 do_test test-pragma-salt-set {
60 sqlite_orig db test.db
63 PRAGMA cipher_salt = "x'01010101010101010101010101010101'";
65 INSERT INTO t1(a,b) VALUES (1,2);
69 lappend rc [hexio_read test.db 0 16]
71 sqlite_orig db test.db
74 SELECT count(*) FROM t1;
78 } {01010101010101010101010101010101 {ok 1 01010101010101010101010101010101}}
79 file delete -force test.db
82 # verify that a raw key with a fixed salt will work
83 # the first 16 bytes of database should be equal to the specified salt
84 # which is the last 32 characters of the hex key spec.
85 # also verify return value of cipher_salt
86 do_test test-raw-key-with-salt-spec {
88 sqlite_orig db test.db
90 PRAGMA key = $hexkeyspec;
92 INSERT INTO t1(a,b) VALUES (1,2);
96 lappend rc [hexio_read test.db 0 16]
98 sqlite_orig db test.db
100 PRAGMA key = $hexkeyspec;
101 SELECT count(*) FROM t1;
104 } {01010101010101010101010101010101 {ok 1 01010101010101010101010101010101}}
106 file delete -force test.db
108 # verify that a raw key with an invalid salt will not work to
109 # open an existing database.
110 # should cause hmac failure due to invalid generated HMAC key
111 do_test test-raw-key-with-invalid-salt-spec {
112 sqlite_orig db test.db
114 PRAGMA key = $hexkeyspec;
115 CREATE TABLE t1(a,b);
116 INSERT INTO t1(a,b) VALUES (1,2);
120 sqlite_orig db test.db
122 PRAGMA key="x'98483C6EB40B6C31A448C22A66DED3B5E5E8D5119CAC8327B655C8B5C483648100000000000000000000000000000001'";
123 SELECT count(*) FROM t1;
125 } {1 {file is not a database}}
127 file delete -force test.db
129 # verify that a raw key with a bad salt *will* work if page HMAC is disabled
130 # in this case the salt will not actually be used for anything
131 # because the encryption key is provided explicitly
132 do_test test-raw-key-with-invalid-salt-spec-no-hmac {
133 sqlite_orig db test.db
135 PRAGMA key = $hexkeyspec;
136 PRAGMA cipher_use_hmac = OFF;
137 CREATE TABLE t1(a,b);
138 INSERT INTO t1(a,b) VALUES (1,2);
142 sqlite_orig db test.db
144 PRAGMA key="x'98483C6EB40B6C31A448C22A66DED3B5E5E8D5119CAC8327B655C8B5C483648100000000000000000000000000000001'";
145 PRAGMA cipher_use_hmac = OFF;
146 SELECT count(*) FROM t1;
150 file delete -force test.db
152 # verify that invalid cipher_plaintext_header_sizes don't work
154 # 2. Larger than available page size
155 # 2. Not a multiple of block size
156 do_test test-invalid-plaintext-header-sizes {
158 sqlite_orig db test.db
159 lappend rc [catchsql "
160 PRAGMA key = $hexkeyspec;
161 PRAGMA cipher_plaintext_header_size = -1;
162 CREATE TABLE t1(a,b);
165 sqlite_orig db test.db
166 lappend rc [catchsql "
167 PRAGMA key = $hexkeyspec;
168 PRAGMA cipher_plaintext_header_size = 4096;
169 CREATE TABLE t1(a,b);
172 sqlite_orig db test.db
173 lappend rc [catchsql "
174 PRAGMA key = $hexkeyspec;
175 PRAGMA cipher_plaintext_header_size = 24;
176 CREATE TABLE t1(a,b);
178 } {{1 {SQL logic error}} {1 {SQL logic error}} {1 {SQL logic error}}}
180 file delete -force test.db
182 # verify that a valid cipher_plaintext_header_size leaves the
183 # start of the database unencrypted, i.e. "SQLite format 3\0"
184 do_test test-valid-plaintext-header-size {
186 sqlite_orig db test.db
188 PRAGMA key = $hexkeyspec;
189 PRAGMA cipher_plaintext_header_size = 16;
190 CREATE TABLE t1(a,b);
191 INSERT INTO t1(a,b) VALUES (1,2);
195 lappend rc [hexio_read test.db 0 16]
197 sqlite_orig db test.db
198 lappend rc [execsql "
199 PRAGMA key = $hexkeyspec;
200 PRAGMA cipher_plaintext_header_size = 16;
201 SELECT count(*) FROM t1;
202 PRAGMA cipher_plaintext_header_size;
204 } {53514C69746520666F726D6174203300 {ok 1 16}}
206 file delete -force test.db
208 # when using a standard mode database and 32 byte
209 # plaintext header, ensure that bytes 16 - 19
210 # corresponding to the page size and file versions, and reserve size
211 # are readable and equal to 1024, 1, 1, and 80 respectively
212 do_test test-plaintext-header-journal-delete-mode-readable {
213 sqlite_orig db test.db
216 PRAGMA cipher_plaintext_header_size = 32;
217 CREATE TABLE t1(a,b);
218 INSERT INTO t1(a,b) VALUES (1,2);
221 string equal [hexio_read test.db 16 5] "1000010150"
223 file delete -force test.db
226 # when using a WAL mode database and 32 byte
227 # plaintext header, ensure that bytes 16 - 19
228 # corresponding to the page size and file versions, and reserve size
229 # are readable and equal to 1024, 2, 2 and 80 respectively
230 do_test test-plaintext-header-journal-wal-mode-readable {
231 sqlite_orig db test.db
234 PRAGMA cipher_plaintext_header_size = 32;
235 PRAGMA journal_mode = WAL;
236 CREATE TABLE t1(a,b);
237 INSERT INTO t1(a,b) VALUES (1,2);
240 string equal [hexio_read test.db 16 5] "1000020250"
242 file delete -force test.db
244 # verify that a valid default_cipher_plaintext_header_size leaves the
245 # start of the database unencrypted right from the start
246 # , i.e. "SQLite format 3\0"
247 do_test test-valid-default-plaintext-header-size {
249 sqlite_orig db test.db
251 PRAGMA cipher_default_plaintext_header_size = 16;
256 CREATE TABLE t1(a,b);
257 INSERT INTO t1(a,b) VALUES (1,2);
262 lappend rc [hexio_read test.db 0 16]
264 sqlite_orig db test.db
265 execsql { PRAGMA key = 'test'; }
266 lappend rc [execsql "
267 PRAGMA cipher_salt = \"x'$salt'\";
268 SELECT count(*) FROM t1;
269 PRAGMA cipher_plaintext_header_size;
272 # reset the default back to 0 or subsequent tests will fail
273 execsql "PRAGMA cipher_default_plaintext_header_size = 0;"
275 lappend rc [string equal $salt "53514c69746520666f726d6174203300"]
276 } {53514C69746520666F726D6174203300 {1 16} 0}
278 file delete -force test.db
280 # verify that a valid default_cipher_plaintext_header_size
281 # operates properly on an attached database, and that the
282 # salt pragma operates on the attached database as well
283 do_test test-valid-default-plaintext-header-size-attach {
285 sqlite_orig db test.db
287 PRAGMA cipher_default_plaintext_header_size = 16;
291 CREATE TABLE temp(a);
292 ATTACH DATABASE 'test2.db' as db2;
293 CREATE TABLE db2.t2(a,b);
294 INSERT INTO db2.t2(a,b) VALUES (1,2);
295 PRAGMA db2.cipher_salt;
299 lappend rc [hexio_read test2.db 0 16]
301 sqlite_orig db test2.db
302 execsql { PRAGMA key = 'test'; }
303 lappend rc [execsql "
304 PRAGMA cipher_salt = \"x'$salt'\";
305 SELECT count(*) FROM t2;
306 PRAGMA cipher_plaintext_header_size;
309 # reset the default back to 0 or subsequent tests will fail
310 execsql "PRAGMA cipher_default_plaintext_header_size = 0;"
312 lappend rc [string equal $salt "53514c69746520666f726d6174203300"]
313 } {53514C69746520666F726D6174203300 {1 16} 0}
315 file delete -force test.db
316 file delete -force test2.db
319 # migrate a standard database in place to use a
320 # plaintext header offset by opening it, adjusting
321 # the pragma, and rewriting the first page
322 do_test test-plaintext-header-migrate-journal-delete {
324 sqlite_orig db test.db
326 PRAGMA key = $hexkeyspec;
327 CREATE TABLE t1(a,b);
328 INSERT INTO t1(a,b) VALUES (1,2);
332 lappend rc [hexio_read test.db 0 16]
334 sqlite_orig db test.db
336 PRAGMA key = $hexkeyspec;
337 SELECT count(*) FROM t1;
338 PRAGMA cipher_plaintext_header_size = 32;
339 PRAGMA user_version = 1;
342 lappend rc [hexio_read test.db 0 21]
344 sqlite_orig db test.db
345 lappend rc [execsql "
346 PRAGMA key = $hexkeyspec;
347 PRAGMA cipher_plaintext_header_size = 32;
348 SELECT count(*) FROM t1;
351 } {01010101010101010101010101010101 53514C69746520666F726D61742033001000010150 {ok 1}}
353 file delete -force test.db
355 # migrate a wal mode database in place to use a
356 # plaintext header offset by opening it, adjusting
357 # the pragma, and rewriting the first page
358 do_test test-plaintext-header-migrate-journal-wal {
360 sqlite_orig db test.db
362 PRAGMA key = $hexkeyspec;
363 PRAGMA journal_mode = WAL;
364 CREATE TABLE t1(a,b);
365 INSERT INTO t1(a,b) VALUES (1,2);
369 lappend rc [hexio_read test.db 0 16]
371 sqlite_orig db test.db
372 lappend rc [execsql "
373 PRAGMA key = $hexkeyspec;
374 SELECT count(*) FROM t1;
376 PRAGMA cipher_plaintext_header_size = 32;
377 PRAGMA user_version = 1;
378 PRAGMA wal_checkpoint(FULL);
381 lappend rc [hexio_read test.db 0 21]
383 sqlite_orig db test.db
384 lappend rc [execsql "
385 PRAGMA key = $hexkeyspec;
386 PRAGMA cipher_plaintext_header_size = 32;
387 SELECT count(*) FROM t1;
391 } {01010101010101010101010101010101 {ok 1 wal 0 1 1} 53514C69746520666F726D61742033001000020250 {ok 1 wal}}
393 file delete -force test.db
395 # migrate a wal mode database in place to use a plaintext header
396 # but instead of using a raw key syntax, use a derived key
397 # but explicitly set the salt using cipher_salt
398 do_test test-plaintext-header-migrate-journal-wal-string-key-random-salt {
400 sqlite_orig db test.db
403 PRAGMA journal_mode = WAL;
404 CREATE TABLE t1(a,b);
405 INSERT INTO t1(a,b) VALUES (1,2);
409 set salt [hexio_read test.db 0 16]
411 sqlite_orig db test.db
412 lappend rc [execsql "
414 SELECT count(*) FROM t1;
416 PRAGMA cipher_plaintext_header_size = 32;
417 PRAGMA user_version = 1;
418 PRAGMA wal_checkpoint(FULL);
422 lappend rc [hexio_read test.db 0 21]
424 sqlite_orig db test.db
425 lappend rc [execsql "
427 PRAGMA cipher_salt = \"x'$salt'\";
428 PRAGMA cipher_plaintext_header_size = 32;
429 SELECT count(*) FROM t1;
434 } {{ok 1 wal 0 1 1} 53514C69746520666F726D61742033001000020250 {ok 1 wal}}
436 file delete -force test.db
438 # when cipher_salt is the first statement a new salt should be generated
439 # and it should match the salt after key derviation occurs. At no point
440 # should the salt be zero
441 do_test plaintext-header-size-salt-first-op {
443 sqlite_orig db test.db
444 execsql { PRAGMA key = 'test'; }
446 PRAGMA cipher_plaintext_header_size = 16;
451 CREATE TABLE t1(a,b);
452 INSERT INTO t1(a,b) VALUES (1,2);
456 lappend rc [string equal $salt1 "00000000000000000000000000000000"]
457 lappend rc [string equal $salt2 "00000000000000000000000000000000"]
458 lappend rc [string equal $salt1 $salt2]
461 file delete -force test.db