AryaWu/sqlite
0
1/*2** 2018-03-213**4** The author disclaims copyright to this source code. In place of5** 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 query14** optimizer by fuzzing.15**16** The input is an SQL script, presumably generated by a fuzzer. The17** argument is the name of the input. If no files are named, standard18** input is read.19**20** The SQL script is run twice, once with optimization enabled, and again21** with optimization disabled. If the output is not equivalent, an error22** is printed and the program returns non-zero.23*/24 25/* Include the SQLite amalgamation, after making appropriate #defines.26*/27#define SQLITE_THREADSAFE 028#define SQLITE_OMIT_LOAD_EXTENSION 129#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 wrong36*/37static 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 wrong58*/59static 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*/81static 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 will134 ** 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 zOutTab156 );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 freeing169** the memory.170**171** If parameter pnByte is not NULL, (*pnByte) is set to the number of bytes172** read.173**174** For convenience, a nul-terminator byte is always appended to the data read175** from the file before the buffer is returned. This byte is not included in176** the final value of (*pnByte), if applicable.177**178** NULL is returned if any error is encountered. The final value of *pnByte179** is undefined in this case.180*/181static 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 203int 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