1# 2007 May 10 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# This file implements regression tests for SQLite library. The 12# focus of this file is generating semi-random strings of SQL 13# (a.k.a. "fuzz") and sending it into the parser to try to generate 14# errors. 15# 16# $Id: fuzz.test,v 1.7 2007/05/11 16:58:04 danielk1977 Exp $ 17 18set testdir [file dirname $argv0] 19source $testdir/tester.tcl 20 21set ::REPEATS 20 22set ::REPEATS 5000 23 24proc fuzz {TemplateList} { 25 set n [llength $TemplateList] 26 set i [expr {int(rand()*$n)}] 27 return [uplevel 1 subst -novar [list [lindex $TemplateList $i]]] 28} 29 30# Fuzzy generation primitives: 31# 32# Literal 33# UnaryOp 34# BinaryOp 35# Expr 36# Table 37# Select 38# Insert 39# 40 41# Returns a string representing an SQL literal. 42# 43proc Literal {} { 44 set TemplateList { 45 456 0 -456 1 -1 46 2147483648 2147483647 2147483649 -2147483647 -2147483648 -2147483649 47 'The' 'first' 'experiments' 'in' 'hardware' 'fault' 'injection' 48 zeroblob(1000) 49 NULL 50 56.1 -56.1 51 123456789.1234567899 52 } 53 fuzz $TemplateList 54} 55 56# Returns a string containing an SQL unary operator (e.g. "+" or "NOT"). 57# 58proc UnaryOp {} { 59 set TemplateList {+ - NOT ~} 60 fuzz $TemplateList 61} 62 63# Returns a string containing an SQL binary operator (e.g. "*" or "/"). 64# 65proc BinaryOp {} { 66 set TemplateList { 67 || * / % + - << >> & | < <= > >= = == != <> AND OR 68 LIKE GLOB {NOT LIKE} 69 } 70 fuzz $TemplateList 71} 72 73# Return the complete text of an SQL expression. 74# 75set ::ExprDepth 0 76proc Expr { {c {}} } { 77 incr ::ExprDepth 78 79 set TemplateList [concat $c {[Literal]}] 80 if {$::ExprDepth < 5} { 81 lappend TemplateList \ 82 {[Expr $c] [BinaryOp] [Expr $c]} \ 83 {[UnaryOp] [Expr $c]} \ 84 {[Expr $c] ISNULL} \ 85 {[Expr $c] NOTNULL} \ 86 {CAST([Expr $c] AS blob)} \ 87 {CAST([Expr $c] AS text)} \ 88 {CAST([Expr $c] AS integer)} \ 89 {CAST([Expr $c] AS real)} \ 90 {abs([Expr])} \ 91 {coalesce([Expr], [Expr])} \ 92 {hex([Expr])} \ 93 {length([Expr])} \ 94 {lower([Expr])} \ 95 {upper([Expr])} \ 96 {quote([Expr])} \ 97 {random()} \ 98 {randomblob(min(max([Expr],1), 500))} \ 99 {typeof([Expr])} \ 100 {substr([Expr],[Expr],[Expr])} \ 101 {CASE WHEN [Expr $c] THEN [Expr $c] ELSE [Expr $c] END} \ 102 {[Literal]} {[Literal]} {[Literal]} 103 } 104 if {$::SelectDepth < 10} { 105 lappend TemplateList \ 106 {([Select 1])} \ 107 {[Expr $c] IN ([Select 1])} \ 108 {[Expr $c] NOT IN ([Select 1])} \ 109 {EXISTS ([Select 1])} \ 110 } 111 set res [fuzz $TemplateList] 112 incr ::ExprDepth -1 113 return $res 114} 115 116# Return a valid table name. 117# 118set ::TableList [list] 119proc Table {} { 120 set TemplateList [concat sqlite_master $::TableList] 121 fuzz $TemplateList 122} 123 124# Return a SELECT statement. 125# 126set ::SelectDepth 0 127set ::ColumnList [list] 128proc Select {{isExpr 0}} { 129 incr ::SelectDepth 130 set TemplateList { 131 {SELECT [Expr]} 132 {SELECT [Literal]} 133 } 134 if {$::SelectDepth < 5} { 135 lappend TemplateList \ 136 {SELECT [Expr] FROM ([Select])} \ 137 {SELECT [Expr $::ColumnList] FROM [Table]} \ 138 139 if {0 == $isExpr} { 140 lappend TemplateList \ 141 {SELECT [Expr], [Expr] FROM ([Select]) ORDER BY [Expr]} \ 142 {SELECT * FROM ([Select]) ORDER BY [Expr]} \ 143 {SELECT * FROM [Table]} \ 144 {SELECT * FROM [Table] WHERE [Expr $::ColumnList]} \ 145{SELECT * FROM [Table],[Table] AS t2 WHERE [Expr $::ColumnList] LIMIT 1} 146 } 147 } 148 set res [fuzz $TemplateList] 149 incr ::SelectDepth -1 150 set res 151} 152 153# Generate and return a fuzzy INSERT statement. 154# 155proc Insert {} { 156 set TemplateList { 157 {INSERT INTO [Table] VALUES([Expr], [Expr], [Expr]);} 158 {INSERT INTO [Table] VALUES([Expr], [Expr], [Expr], [Expr]);} 159 {INSERT INTO [Table] VALUES([Expr], [Expr]);} 160 } 161 fuzz $TemplateList 162} 163 164proc Column {} { 165 fuzz $::ColumnList 166} 167 168# Generate and return a fuzzy UPDATE statement. 169# 170proc Update {} { 171 set TemplateList { 172 {UPDATE [Table] 173 SET [Column] = [Expr $::ColumnList] 174 WHERE [Expr $::ColumnList]} 175 } 176 fuzz $TemplateList 177} 178 179proc Delete {} { 180 set TemplateList { 181 {DELETE FROM [Table] WHERE [Expr $::ColumnList]} 182 } 183 fuzz $TemplateList 184} 185 186proc Statement {} { 187 set TemplateList { 188 {[Update]} 189 {[Insert]} 190 {[Select]} 191 {[Delete]} 192 } 193 fuzz $TemplateList 194} 195 196######################################################################## 197 198set ::log [open fuzzy.log w] 199 200# 201# Usage: do_fuzzy_test <testname> ?<options>? 202# 203# -template 204# -errorlist 205# 206proc do_fuzzy_test {testname args} { 207 set ::fuzzyopts(-errorlist) [list] 208 array set ::fuzzyopts $args 209 lappend ::fuzzyopts(-errorlist) {parser stack overflow} {ORDER BY column} 210 211 for {set ii 0} {$ii < $::REPEATS} {incr ii} { 212 do_test ${testname}.$ii { 213 set ::sql [subst $::fuzzyopts(-template)] 214 puts $::log $::sql 215 flush $::log 216 set rc [catch {execsql $::sql} msg] 217 set e 1 218 if {$rc} { 219 set e 0 220 foreach error $::fuzzyopts(-errorlist) { 221 if {0 == [string first $error $msg]} { 222 set e 1 223 break 224 } 225 } 226 } 227 if {$e == 0} { 228 puts "" 229 puts $::sql 230 puts $msg 231 } 232 set e 233 } {1} 234 } 235} 236 237#---------------------------------------------------------------- 238# These tests caused errors that were first caught by the tests 239# in this file. They are still here. 240do_test fuzz-1.1 { 241 execsql { 242 SELECT 'abc' LIKE X'ABCD'; 243 } 244} {0} 245do_test fuzz-1.2 { 246 execsql { 247 SELECT 'abc' LIKE zeroblob(10); 248 } 249} {0} 250do_test fuzz-1.3 { 251 execsql { 252 SELECT zeroblob(10) LIKE 'abc'; 253 } 254} {0} 255do_test fuzz-1.4 { 256 execsql { 257 SELECT (- -21) % NOT (456 LIKE zeroblob(10)); 258 } 259} {0} 260do_test fuzz-1.5 { 261 execsql { 262 SELECT (SELECT ( 263 SELECT (SELECT -2147483648) FROM (SELECT 1) ORDER BY 1 264 )) 265 } 266} {-2147483648} 267do_test fuzz-1.6 { 268 execsql { 269 SELECT 'abc', zeroblob(1) FROM (SELECT 1) ORDER BY 1 270 } 271} [execsql {SELECT 'abc', zeroblob(1)}] 272 273do_test fuzz-1.7 { 274 execsql { 275 SELECT ( SELECT zeroblob(1000) FROM ( 276 SELECT * FROM (SELECT 'first') ORDER BY NOT 'in') 277 ) 278 } 279} [execsql {SELECT zeroblob(1000)}] 280 281do_test fuzz-1.8 { 282 # Problems with opcode OP_ToText (did not account for MEM_Zero). 283 # Also MemExpandBlob() was marking expanded blobs as nul-terminated. 284 # They are not. 285 execsql { 286 SELECT CAST(zeroblob(1000) AS text); 287 } 288} {{}} 289 290do_test fuzz-1.9 { 291 # This was causing a NULL pointer dereference of Expr.pList. 292 execsql { 293 SELECT 1 FROM (SELECT * FROM sqlite_master WHERE random()) 294 } 295} {} 296 297do_test fuzz-1.10 { 298 # Bug in calculation of Parse.ckOffset causing an assert() 299 # to fail. Probably harmless. 300 execsql { 301 SELECT coalesce(1, substr( 1, 2, length('in' IN (SELECT 1)))) 302 } 303} {1} 304 305#---------------------------------------------------------------- 306# Test some fuzzily generated expressions. 307# 308do_fuzzy_test fuzz-2 -template { SELECT [Expr] } 309 310do_test fuzz-3.1 { 311 execsql { 312 CREATE TABLE abc(a, b, c); 313 CREATE TABLE def(a, b, c); 314 CREATE TABLE ghi(a, b, c); 315 } 316} {} 317set ::TableList [list abc def ghi] 318 319#---------------------------------------------------------------- 320# Test some fuzzily generated SELECT statements. 321# 322do_fuzzy_test fuzz-3.2 -template {[Select]} 323 324#---------------------------------------------------------------- 325# Insert a small amount of data into the database and then run 326# some more generated SELECT statements. 327# 328do_test fuzz-4.1 { 329 execsql { 330 INSERT INTO abc VALUES(1, 2, 3); 331 INSERT INTO abc VALUES(4, 5, 6); 332 INSERT INTO abc VALUES(7, 8, 9); 333 INSERT INTO def VALUES(1, 2, 3); 334 INSERT INTO def VALUES(4, 5, 6); 335 INSERT INTO def VALUES(7, 8, 9); 336 INSERT INTO ghi VALUES(1, 2, 3); 337 INSERT INTO ghi VALUES(4, 5, 6); 338 INSERT INTO ghi VALUES(7, 8, 9); 339 CREATE INDEX abc_i ON abc(a, b, c); 340 CREATE INDEX def_i ON def(c, a, b); 341 CREATE INDEX ghi_i ON ghi(b, c, a); 342 } 343} {} 344do_fuzzy_test fuzz-4.2 -template {[Select]} 345 346#---------------------------------------------------------------- 347# Test some fuzzy INSERT statements: 348# 349do_test fuzz-5.1 {execsql BEGIN} {} 350do_fuzzy_test fuzz-5.2 -template {[Insert]} -errorlist table 351integrity_check fuzz-5.2.integrity 352do_test fuzz-5.3 {execsql COMMIT} {} 353integrity_check fuzz-5.4.integrity 354 355#---------------------------------------------------------------- 356# Now that there is data in the datbase, run some more SELECT 357# statements 358# 359set ::ColumnList [list a b c] 360set E {{no such col} {ambiguous column name}} 361do_fuzzy_test fuzz-6.1 -template {[Select]} -errorlist $E 362 363#---------------------------------------------------------------- 364# Run some SELECTs, INSERTs, UPDATEs and DELETEs in a transaction. 365# 366set E {{no such col} {ambiguous column name} {table}} 367do_test fuzz-7.1 {execsql BEGIN} {} 368do_fuzzy_test fuzz-7.2 -template {[Statement]} -errorlist $E 369integrity_check fuzz-7.3.integrity 370do_test fuzz-7.4 {execsql COMMIT} {} 371integrity_check fuzz-7.5.integrity 372 373close $::log 374finish_test 375