1 /*
2 ** 2018-03-21
3 **
4 ** The author disclaims copyright to this source code. In place of
5 ** a legal notice, here is a blessing:
6 **
7 ** May you do good and not evil.
8 ** May you find forgiveness for yourself and forgive others.
9 ** May you share freely, never taking more than you give.
10 **
11 *************************************************************************
12 **
13 ** This program attempts to verify the correctness of the SQLite query
14 ** optimizer by fuzzing.
15 **
16 ** The input is an SQL script, presumably generated by a fuzzer. The
17 ** argument is the name of the input. If no files are named, standard
18 ** input is read.
19 **
20 ** The SQL script is run twice, once with optimization enabled, and again
21 ** with optimization disabled. If the output is not equivalent, an error
22 ** is printed and the program returns non-zero.
23 */
24
25 /* Include the SQLite amalgamation, after making appropriate #defines.
26 */
27 #define SQLITE_THREADSAFE 0
28 #define SQLITE_OMIT_LOAD_EXTENSION 1
29 #include "sqlite3.c"
30
31 /* Content of the read-only test database */
32 #include "optfuzz-db01.c"
33
34 /*
35 ** Prepare a single SQL statement. Panic if anything goes wrong
36 */
prepare_sql(sqlite3 * db,const char * zFormat,...)37 static sqlite3_stmt *prepare_sql(sqlite3 *db, const char *zFormat, ...){
38 char *zSql;
39 int rc;
40 sqlite3_stmt *pStmt = 0;
41 va_list ap;
42
43 va_start(ap, zFormat);
44 zSql = sqlite3_vmprintf(zFormat, ap);
45 va_end(ap);
46 rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
47 if( rc ){
48 printf("Error: %s\nSQL: %s\n",
49 sqlite3_errmsg(db), zSql);
50 exit(1);
51 }
52 sqlite3_free(zSql);
53 return pStmt;
54 }
55
56 /*
57 ** Run SQL. Panic if anything goes wrong
58 */
run_sql(sqlite3 * db,const char * zFormat,...)59 static void run_sql(sqlite3 *db, const char *zFormat, ...){
60 char *zSql;
61 int rc;
62 char *zErr = 0;
63 va_list ap;
64
65 va_start(ap, zFormat);
66 zSql = sqlite3_vmprintf(zFormat, ap);
67 va_end(ap);
68 rc = sqlite3_exec(db, zSql, 0, 0, &zErr);
69 if( rc || zErr ){
70 printf("Error: %s\nsqlite3_errmsg: %s\nSQL: %s\n",
71 zErr, sqlite3_errmsg(db), zSql);
72 exit(1);
73 }
74 sqlite3_free(zSql);
75 }
76
77 /*
78 ** Run one or more SQL statements contained in zSql against database dbRun.
79 ** Store the input in database dbOut.
80 */
optfuzz_exec(sqlite3 * dbRun,const char * zSql,sqlite3 * dbOut,const char * zOutTab,int * pnStmt,int * pnRow,int bTrace)81 static int optfuzz_exec(
82 sqlite3 *dbRun, /* The database on which the SQL executes */
83 const char *zSql, /* The SQL to be executed */
84 sqlite3 *dbOut, /* Store results in this database */
85 const char *zOutTab, /* Store results in this table of dbOut */
86 int *pnStmt, /* Write the number of statements here */
87 int *pnRow, /* Write the number of rows here */
88 int bTrace /* Print query results if true */
89 ){
90 int rc = SQLITE_OK; /* Return code */
91 const char *zLeftover; /* Tail of unprocessed SQL */
92 sqlite3_stmt *pStmt = 0; /* The current SQL statement */
93 sqlite3_stmt *pIns = 0; /* Statement to insert into dbOut */
94 const char *zCol; /* Single column value */
95 int nCol; /* Number of output columns */
96 char zLine[4000]; /* Complete row value */
97
98 run_sql(dbOut, "BEGIN");
99 run_sql(dbOut, "CREATE TABLE IF NOT EXISTS staging(x TEXT)");
100 run_sql(dbOut, "CREATE TABLE IF NOT EXISTS \"%w\"(x TEXT)", zOutTab);
101 pIns = prepare_sql(dbOut, "INSERT INTO staging(x) VALUES(?1)");
102 *pnRow = *pnStmt = 0;
103 while( rc==SQLITE_OK && zSql && zSql[0] ){
104 zLeftover = 0;
105 rc = sqlite3_prepare_v2(dbRun, zSql, -1, &pStmt, &zLeftover);
106 zSql = zLeftover;
107 assert( rc==SQLITE_OK || pStmt==0 );
108 if( rc!=SQLITE_OK ){
109 printf("Error with [%s]\n%s\n", zSql, sqlite3_errmsg(dbRun));
110 break;
111 }
112 if( !pStmt ) continue;
113 (*pnStmt)++;
114 nCol = sqlite3_column_count(pStmt);
115 run_sql(dbOut, "DELETE FROM staging;");
116 while( sqlite3_step(pStmt)==SQLITE_ROW ){
117 int i, j;
118 for(i=j=0; i<nCol && j<sizeof(zLine)-50; i++){
119 int eType = sqlite3_column_type(pStmt, i);
120 if( eType==SQLITE_NULL ){
121 zCol = "NULL";
122 }else{
123 zCol = (const char*)sqlite3_column_text(pStmt, i);
124 }
125 if( i ) zLine[j++] = ',';
126 if( eType==SQLITE_TEXT ){
127 sqlite3_snprintf(sizeof(zLine)-j, zLine+j, "'%q'", zCol);
128 }else{
129 sqlite3_snprintf(sizeof(zLine)-j, zLine+j, "%s", zCol);
130 }
131 j += (int)strlen(zLine+j);
132 }
133 /* Detect if any row is too large and throw an error, because we will
134 ** want to go back and look more closely at that case */
135 if( j>=sizeof(zLine)-100 ){
136 printf("Excessively long output line: %d bytes\n" ,j);
137 exit(1);
138 }
139 if( bTrace ){
140 printf("%s\n", zLine);
141 }
142 (*pnRow)++;
143 sqlite3_bind_text(pIns, 1, zLine, j, SQLITE_TRANSIENT);
144 rc = sqlite3_step(pIns);
145 assert( rc==SQLITE_DONE );
146 rc = sqlite3_reset(pIns);
147 }
148 run_sql(dbOut,
149 "INSERT INTO \"%w\"(x) VALUES('### %q ###')",
150 zOutTab, sqlite3_sql(pStmt)
151 );
152 run_sql(dbOut,
153 "INSERT INTO \"%w\"(x) SELECT group_concat(x,char(10))"
154 " FROM (SELECT x FROM staging ORDER BY x)",
155 zOutTab
156 );
157 run_sql(dbOut, "COMMIT");
158 sqlite3_finalize(pStmt);
159 pStmt = 0;
160 }
161 sqlite3_finalize(pStmt);
162 sqlite3_finalize(pIns);
163 return rc;
164 }
165
166 /*
167 ** Read the content of file zName into memory obtained from sqlite3_malloc64()
168 ** and return a pointer to the buffer. The caller is responsible for freeing
169 ** the memory.
170 **
171 ** If parameter pnByte is not NULL, (*pnByte) is set to the number of bytes
172 ** read.
173 **
174 ** For convenience, a nul-terminator byte is always appended to the data read
175 ** from the file before the buffer is returned. This byte is not included in
176 ** the final value of (*pnByte), if applicable.
177 **
178 ** NULL is returned if any error is encountered. The final value of *pnByte
179 ** is undefined in this case.
180 */
readFile(const char * zName,int * pnByte)181 static char *readFile(const char *zName, int *pnByte){
182 FILE *in = fopen(zName, "rb");
183 long nIn;
184 size_t nRead;
185 char *pBuf;
186 if( in==0 ) return 0;
187 fseek(in, 0, SEEK_END);
188 nIn = ftell(in);
189 rewind(in);
190 pBuf = sqlite3_malloc64( nIn+1 );
191 if( pBuf==0 ) return 0;
192 nRead = fread(pBuf, nIn, 1, in);
193 fclose(in);
194 if( nRead!=1 ){
195 sqlite3_free(pBuf);
196 return 0;
197 }
198 pBuf[nIn] = 0;
199 if( pnByte ) *pnByte = nIn;
200 return pBuf;
201 }
202
main(int argc,char ** argv)203 int main(int argc, char **argv){
204 int nIn = 0; /* Number of input files */
205 char **azIn = 0; /* Names of input files */
206 sqlite3 *dbOut = 0; /* Database to hold results */
207 sqlite3 *dbRun = 0; /* Database used for tests */
208 int bTrace = 0; /* Show query results */
209 int bShowValid = 0; /* Just list inputs that are valid SQL */
210 int nRow, nStmt; /* Number of rows and statements */
211 int i, rc;
212
213 for(i=1; i<argc; i++){
214 const char *z = argv[i];
215 if( z[0]=='-' && z[1]=='-' ) z++;
216 if( strcmp(z,"-help")==0 ){
217 printf("Usage: %s [OPTIONS] FILENAME ...\n", argv[0]);
218 printf("Options:\n");
219 printf(" --help Show his message\n");
220 printf(" --output-trace Show each line of SQL output\n");
221 printf(" --valid-sql List FILEs that are valid SQL\n");
222 return 0;
223 }
224 else if( strcmp(z,"-output-trace")==0 ){
225 bTrace = 1;
226 }
227 else if( strcmp(z,"-valid-sql")==0 ){
228 bShowValid = 1;
229 }
230 else if( z[0]=='-' ){
231 printf("unknown option \"%s\". Use --help for details\n", argv[i]);
232 return 1;
233 }
234 else {
235 nIn++;
236 azIn = realloc(azIn, sizeof(azIn[0])*nIn);
237 if( azIn==0 ){
238 printf("out of memory\n");
239 exit(1);
240 }
241 azIn[nIn-1] = argv[i];
242 }
243 }
244
245 sqlite3_open(":memory:", &dbOut);
246 sqlite3_open(":memory:", &dbRun);
247 sqlite3_deserialize(dbRun, "main", data001, sizeof(data001),
248 sizeof(data001), SQLITE_DESERIALIZE_READONLY);
249 for(i=0; i<nIn; i++){
250 char *zSql = readFile(azIn[i], 0);
251 sqlite3_stmt *pCk;
252 sqlite3_exec(dbRun, "ROLLBACK", 0, 0, 0);
253 if( bShowValid ){
254 rc = sqlite3_exec(dbRun, zSql, 0, 0, 0);
255 if( rc==SQLITE_OK ) printf("%s\n", azIn[i]);
256 sqlite3_free(zSql);
257 continue;
258 }
259 sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS, dbRun, 0);
260 if( bTrace ) printf("%s: Optimized\n", azIn[i]);
261 rc = optfuzz_exec(dbRun, zSql, dbOut, "opt", &nStmt, &nRow, bTrace);
262 if( rc ){
263 printf("%s: optimized run failed: %s\n",
264 azIn[i], sqlite3_errmsg(dbRun));
265 }else{
266 sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS, dbRun, 0xffff);
267 if( bTrace ) printf("%s: Non-optimized\n", azIn[i]);
268 rc = optfuzz_exec(dbRun, zSql, dbOut, "noopt", &nStmt, &nRow, bTrace);
269 if( rc ){
270 printf("%s: non-optimized run failed: %s\n",
271 azIn[i], sqlite3_errmsg(dbRun));
272 exit(1);
273 }
274 pCk = prepare_sql(dbOut,
275 "SELECT (SELECT group_concat(x,char(10)) FROM opt)=="
276 " (SELECT group_concat(x,char(10)) FROM noopt)");
277 rc = sqlite3_step(pCk);
278 if( rc!=SQLITE_ROW ){
279 printf("%s: comparison failed\n", sqlite3_errmsg(dbOut));
280 exit(1);
281 }
282 if( !sqlite3_column_int(pCk, 0) ){
283 printf("%s: opt/no-opt outputs differ\n", azIn[i]);
284 pCk = prepare_sql(dbOut,
285 "SELECT group_concat(x,char(10)) FROM opt "
286 "UNION ALL "
287 "SELECT group_concat(x,char(10)) FROM noopt");
288 sqlite3_step(pCk);
289 printf("opt:\n%s\n", sqlite3_column_text(pCk,0));
290 sqlite3_step(pCk);
291 printf("noopt:\n%s\n", sqlite3_column_text(pCk,0));
292 exit(1);
293 }else{
294 printf("%s: %d stmts %d rows ok\n", azIn[i], nStmt, nRow);
295 }
296 sqlite3_finalize(pCk);
297 }
298 sqlite3_free(zSql);
299 }
300 sqlite3_close(dbRun);
301 sqlite3_close(dbOut);
302 free(azIn);
303 if( sqlite3_memory_used() ){
304 printf("Memory leak of %lld bytes\n", sqlite3_memory_used());
305 exit(1);
306 }
307 return 0;
308 }
309