ADDED doc/trusted-schema.md Index: doc/trusted-schema.md ================================================================== --- /dev/null +++ doc/trusted-schema.md @@ -0,0 +1,142 @@ +# The new-security-options branch + +## The problem that the [new-security-options](/timeline?r=new-security-options) branch tries to solve + +An attacker might modify the schema of an SQLite database by adding +structures that cause code to run when some other application opens and +reads the database. For example, the attacker might replace a table +definition with a view. Or the attacker might add triggers to tables +or views, or add new CHECK constraints or generated columns or indexes +with expressions in the index list or in the WHERE clause. If the +added features invoke SQL functions or virtual tables with side effects, +that might cause harm to the system if run by a high-privilege victim. +Or, the added features might exfiltrate information if the database is +read by a high-privilege victim. + +The changes in this branch strive to make it easier for high-privilege +applications to safely read SQLite database files that might have been +maliciously corrupted by an attacker. + +## Overview of changes in [new-security-options](/timeline?r=new-security-options) + +The basic idea is to tag every SQL function and virtual table with one +of three risk levels: + + 1. Innocuous + 2. Normal + 3. Direct-Only + +Innocuous functions/vtabs are safe and can be used at any time. +Direct-only elements, in contrast, might have cause side-effects and +should only be used from top-level SQL, not from within triggers or views nor +in elements of the schema such as CHECK constraint, DEFAULT values, +generated columns, index expressions, or in the WHERE clause of a +partial index that are potentially under the control of an attacker. +Normal elements behave like Innocuous if TRUSTED\_SCHEMA=on +and behave like direct-only if TRUSTED\_SCHEMA=off. + +Application-defined functions and virtual tables go in as Normal unless +the application takes deliberate steps to change the risk level. + +For backwards compatibility, the default is TRUSTED\_SCHEMA=on. Documentation +will be updated to recommend applications turn TRUSTED\_SCHEMA to off. + +An innocuous function or virtual table is one that can only read content +from the database file in which it resides, and can only alter the database +in which it resides. Most SQL functions are innocuous. For example, there +is no harm in an attacker running the abs() function. + +Direct-only elements that have side-effects that go outside the database file +in which it lives, or return information from outside of the database file. +Examples of direct-only elements include: + + 1. The fts3\_tokenizer() function + 2. The writefile() function + 3. The readfile() function + 4. The zipvfs virtual table + 5. The csv virtual table + +We do not want an attacker to be able to add these kinds of things to +the database schema and possibly trick a high-privilege application +from performing any of these actions. Therefore, functions and vtabs +with side-effects are marked as Direct-Only. + +Legacy applications might add other risky functions or vtabs. Those will +go in as "Normal" by default. For optimal security, we want those risky +app-defined functions and vtabs to be direct-only, but making that the +default might break some legacy applications. Hence, all app-defined +functions and vtabs go in as Normal, but the application can switch them +over to "Direct-Only" behavior using a single pragma. + +The restrictions on the use of functions and virtual tables do not apply +to TEMP. A TEMP VIEW or a TEMP TRIGGER can use any valid SQL function +or virtual table. The idea is that TEMP views and triggers must be +directly created by the application and are thus under the control of the +application. TEMP views and triggers cannot be created by an attacker who +corrupts the schema of a persistent database file. Hence TEMP views and +triggers are safe. + +## Specific changes + + 1. New sqlite3\_db\_config() option SQLITE\_DBCONFIG\_TRUSTED\_SCHEMA for + turning TRUSTED\_SCHEMA on and off. It defaults to ON. + + 2. Compile-time option -DSQLITE\_TRUSTED\_SCHEMA=0 causes the default + TRUSTED\_SCHEMA setting to be off. + + 3. New pragma "PRAGMA trusted\_schema=(ON\|OFF);". This provides access + to the TRUSTED_SCHEMA setting for application coded using scripting + languages or other secondary languages where they are unable to make + calls to sqlite3\_db\_config(). + + 4. New options for the "enc" parameter to sqlite3\_create\_function() and + its kin: +
    +
  1. _SQLITE\_INNOCUOUS_ → tags the new functions as Innocuous +
  2. _SQLITE\_DIRECTONLY_ → tags the new functions as Direct-Only +
+ + 5. New options to sqlite3\_vtab\_config(): +
    +
  1. _SQLITE\_VTAB\_INNOCUOUS_ → tags the vtab as Innocuous +
  2. _SQLITE\_VTAB\_DIRECTONLY_ → tags the vtab as Direct-Only +
+ + 6. Change many of the functions and virtual tables in the SQLite source + tree to use one of the tags above. + + 7. Enhanced PRAGMA function\_list and virtual-table "pragma\_function\_list" + with additional columns. The columns now are: + +

The last four columns are new. + + 8. The function\_list PRAGMA now also shows all entries for each function. + So, for example, if a function can take either 2 or 3 arguments, + there are separate rows for the 2-argument and 3-argument versions of + the function. + +## Additional Notes + +The function_list enhancements allow the application to query the set +of SQL functions that meet various criteria. For example, to see all +SQL functions that are never allowed to be used in the schema or in +trigger or views: + +~~~ + SELECT DISTINCT name FROM pragma_function_list + WHERE (flags & 0x80000)!=0 + ORDER BY name; +~~~ + +Doing the same is not possible for virtual tables, as a virtual table +might be Innocuous, Normal, or Direct-Only depending on the arguments +passed into the xConnect method. Index: ext/icu/icu.c ================================================================== --- ext/icu/icu.c +++ ext/icu/icu.c @@ -497,30 +497,31 @@ /* ** Register the ICU extension functions with database db. */ int sqlite3IcuInit(sqlite3 *db){ +# define SQLITEICU_EXTRAFLAGS (SQLITE_DETERMINISTIC|SQLITE_INNOCUOUS) static const struct IcuScalar { const char *zName; /* Function name */ unsigned char nArg; /* Number of arguments */ - unsigned short enc; /* Optimal text encoding */ + unsigned int enc; /* Optimal text encoding */ unsigned char iContext; /* sqlite3_user_data() context */ void (*xFunc)(sqlite3_context*,int,sqlite3_value**); } scalars[] = { - {"icu_load_collation", 2, SQLITE_UTF8, 1, icuLoadCollation}, + {"icu_load_collation",2,SQLITE_UTF8|SQLITE_DIRECTONLY,1, icuLoadCollation}, #if !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_ICU) - {"regexp", 2, SQLITE_ANY|SQLITE_DETERMINISTIC, 0, icuRegexpFunc}, - {"lower", 1, SQLITE_UTF16|SQLITE_DETERMINISTIC, 0, icuCaseFunc16}, - {"lower", 2, SQLITE_UTF16|SQLITE_DETERMINISTIC, 0, icuCaseFunc16}, - {"upper", 1, SQLITE_UTF16|SQLITE_DETERMINISTIC, 1, icuCaseFunc16}, - {"upper", 2, SQLITE_UTF16|SQLITE_DETERMINISTIC, 1, icuCaseFunc16}, - {"lower", 1, SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, icuCaseFunc16}, - {"lower", 2, SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, icuCaseFunc16}, - {"upper", 1, SQLITE_UTF8|SQLITE_DETERMINISTIC, 1, icuCaseFunc16}, - {"upper", 2, SQLITE_UTF8|SQLITE_DETERMINISTIC, 1, icuCaseFunc16}, - {"like", 2, SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, icuLikeFunc}, - {"like", 3, SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, icuLikeFunc}, + {"regexp", 2, SQLITE_ANY|SQLITEICU_EXTRAFLAGS, 0, icuRegexpFunc}, + {"lower", 1, SQLITE_UTF16|SQLITEICU_EXTRAFLAGS, 0, icuCaseFunc16}, + {"lower", 2, SQLITE_UTF16|SQLITEICU_EXTRAFLAGS, 0, icuCaseFunc16}, + {"upper", 1, SQLITE_UTF16|SQLITEICU_EXTRAFLAGS, 1, icuCaseFunc16}, + {"upper", 2, SQLITE_UTF16|SQLITEICU_EXTRAFLAGS, 1, icuCaseFunc16}, + {"lower", 1, SQLITE_UTF8|SQLITEICU_EXTRAFLAGS, 0, icuCaseFunc16}, + {"lower", 2, SQLITE_UTF8|SQLITEICU_EXTRAFLAGS, 0, icuCaseFunc16}, + {"upper", 1, SQLITE_UTF8|SQLITEICU_EXTRAFLAGS, 1, icuCaseFunc16}, + {"upper", 2, SQLITE_UTF8|SQLITEICU_EXTRAFLAGS, 1, icuCaseFunc16}, + {"like", 2, SQLITE_UTF8|SQLITEICU_EXTRAFLAGS, 0, icuLikeFunc}, + {"like", 3, SQLITE_UTF8|SQLITEICU_EXTRAFLAGS, 0, icuLikeFunc}, #endif /* !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_ICU) */ }; int rc = SQLITE_OK; int i; Index: ext/misc/amatch.c ================================================================== --- ext/misc/amatch.c +++ ext/misc/amatch.c @@ -898,10 +898,11 @@ rc = SQLITE_ERROR; }else{ rc = amatchLoadRules(db, pNew, pzErr); } if( rc==SQLITE_OK ){ + sqlite3_vtab_config(db, SQLITE_VTAB_INNOCUOUS); rc = sqlite3_declare_vtab(db, "CREATE TABLE x(word,distance,language," "command HIDDEN,nword HIDDEN)" ); #define AMATCH_COL_WORD 0 Index: ext/misc/completion.c ================================================================== --- ext/misc/completion.c +++ ext/misc/completion.c @@ -116,10 +116,11 @@ #define COMPLETION_COLUMN_CANDIDATE 0 /* Suggested completion of the input */ #define COMPLETION_COLUMN_PREFIX 1 /* Prefix of the word to be completed */ #define COMPLETION_COLUMN_WHOLELINE 2 /* Entire line seen so far */ #define COMPLETION_COLUMN_PHASE 3 /* ePhase - used for debugging only */ + sqlite3_vtab_config(db, SQLITE_VTAB_INNOCUOUS); rc = sqlite3_declare_vtab(db, "CREATE TABLE x(" " candidate TEXT," " prefix TEXT HIDDEN," " wholeline TEXT HIDDEN," Index: ext/misc/compress.c ================================================================== --- ext/misc/compress.c +++ ext/misc/compress.c @@ -117,13 +117,15 @@ const sqlite3_api_routines *pApi ){ int rc = SQLITE_OK; SQLITE_EXTENSION_INIT2(pApi); (void)pzErrMsg; /* Unused parameter */ - rc = sqlite3_create_function(db, "compress", 1, SQLITE_UTF8, 0, - compressFunc, 0, 0); + rc = sqlite3_create_function(db, "compress", 1, + SQLITE_UTF8 | SQLITE_INNOCUOUS | SQLITE_DETERMINISTIC, + 0, compressFunc, 0, 0); if( rc==SQLITE_OK ){ - rc = sqlite3_create_function(db, "uncompress", 1, SQLITE_UTF8, 0, - uncompressFunc, 0, 0); + rc = sqlite3_create_function(db, "uncompress", 1, + SQLITE_UTF8 | SQLITE_INNOCUOUS | SQLITE_DETERMINISTIC, + 0, uncompressFunc, 0, 0); } return rc; } Index: ext/misc/csv.c ================================================================== --- ext/misc/csv.c +++ ext/misc/csv.c @@ -630,10 +630,19 @@ goto csvtab_connect_error; } for(i=0; i +#include + +/* +** Implementation of the noop() function. +** +** The function returns its argument, unchanged. +*/ +static void noopfunc( + sqlite3_context *context, + int argc, + sqlite3_value **argv +){ + assert( argc==1 ); + sqlite3_result_value(context, argv[0]); +} + +#ifdef _WIN32 +__declspec(dllexport) +#endif +int sqlite3_noop_init( + sqlite3 *db, + char **pzErrMsg, + const sqlite3_api_routines *pApi +){ + int rc = SQLITE_OK; + SQLITE_EXTENSION_INIT2(pApi); + (void)pzErrMsg; /* Unused parameter */ + rc = sqlite3_create_function(db, "noop", 1, + SQLITE_UTF8 | SQLITE_DETERMINISTIC, + 0, noopfunc, 0, 0); + if( rc ) return rc; + rc = sqlite3_create_function(db, "noop_i", 1, + SQLITE_UTF8 | SQLITE_DETERMINISTIC | SQLITE_INNOCUOUS, + 0, noopfunc, 0, 0); + if( rc ) return rc; + rc = sqlite3_create_function(db, "noop_do", 1, + SQLITE_UTF8 | SQLITE_DETERMINISTIC | SQLITE_DIRECTONLY, + 0, noopfunc, 0, 0); + return rc; +} Index: ext/misc/percentile.c ================================================================== --- ext/misc/percentile.c +++ ext/misc/percentile.c @@ -211,9 +211,10 @@ const sqlite3_api_routines *pApi ){ int rc = SQLITE_OK; SQLITE_EXTENSION_INIT2(pApi); (void)pzErrMsg; /* Unused parameter */ - rc = sqlite3_create_function(db, "percentile", 2, SQLITE_UTF8, 0, + rc = sqlite3_create_function(db, "percentile", 2, + SQLITE_UTF8|SQLITE_INNOCUOUS, 0, 0, percentStep, percentFinal); return rc; } Index: ext/misc/prefixes.c ================================================================== --- ext/misc/prefixes.c +++ ext/misc/prefixes.c @@ -77,10 +77,11 @@ if( rc==SQLITE_OK ){ pNew = sqlite3_malloc( sizeof(*pNew) ); *ppVtab = (sqlite3_vtab*)pNew; if( pNew==0 ) return SQLITE_NOMEM; memset(pNew, 0, sizeof(*pNew)); + sqlite3_vtab_config(db, SQLITE_VTAB_INNOCUOUS); } return rc; } /* Index: ext/misc/regexp.c ================================================================== --- ext/misc/regexp.c +++ ext/misc/regexp.c @@ -752,9 +752,9 @@ char **pzErrMsg, const sqlite3_api_routines *pApi ){ int rc = SQLITE_OK; SQLITE_EXTENSION_INIT2(pApi); - rc = sqlite3_create_function(db, "regexp", 2, SQLITE_UTF8, 0, - re_sql_func, 0, 0); + rc = sqlite3_create_function(db, "regexp", 2, SQLITE_UTF8|SQLITE_INNOCUOUS, + 0, re_sql_func, 0, 0); return rc; } Index: ext/misc/rot13.c ================================================================== --- ext/misc/rot13.c +++ ext/misc/rot13.c @@ -103,12 +103,13 @@ const sqlite3_api_routines *pApi ){ int rc = SQLITE_OK; SQLITE_EXTENSION_INIT2(pApi); (void)pzErrMsg; /* Unused parameter */ - rc = sqlite3_create_function(db, "rot13", 1, SQLITE_UTF8, 0, - rot13func, 0, 0); + rc = sqlite3_create_function(db, "rot13", 1, + SQLITE_UTF8|SQLITE_INNOCUOUS|SQLITE_DETERMINISTIC, + 0, rot13func, 0, 0); if( rc==SQLITE_OK ){ rc = sqlite3_create_collation(db, "rot13", SQLITE_UTF8, 0, rot13CollFunc); } return rc; } Index: ext/misc/series.c ================================================================== --- ext/misc/series.c +++ ext/misc/series.c @@ -124,10 +124,11 @@ "CREATE TABLE x(value,start hidden,stop hidden,step hidden)"); if( rc==SQLITE_OK ){ pNew = *ppVtab = sqlite3_malloc( sizeof(*pNew) ); if( pNew==0 ) return SQLITE_NOMEM; memset(pNew, 0, sizeof(*pNew)); + sqlite3_vtab_config(db, SQLITE_INNOCUOUS); } return rc; } /* Index: ext/misc/sha1.c ================================================================== --- ext/misc/sha1.c +++ ext/misc/sha1.c @@ -379,13 +379,14 @@ const sqlite3_api_routines *pApi ){ int rc = SQLITE_OK; SQLITE_EXTENSION_INIT2(pApi); (void)pzErrMsg; /* Unused parameter */ - rc = sqlite3_create_function(db, "sha1", 1, SQLITE_UTF8, 0, + rc = sqlite3_create_function(db, "sha1", 1, SQLITE_UTF8|SQLITE_INNOCUOUS, 0, sha1Func, 0, 0); if( rc==SQLITE_OK ){ - rc = sqlite3_create_function(db, "sha1_query", 1, SQLITE_UTF8, 0, + rc = sqlite3_create_function(db, "sha1_query", 1, + SQLITE_UTF8|SQLITE_DIRECTONLY, 0, sha1QueryFunc, 0, 0); } return rc; } Index: ext/misc/shathree.c ================================================================== --- ext/misc/shathree.c +++ ext/misc/shathree.c @@ -694,21 +694,25 @@ const sqlite3_api_routines *pApi ){ int rc = SQLITE_OK; SQLITE_EXTENSION_INIT2(pApi); (void)pzErrMsg; /* Unused parameter */ - rc = sqlite3_create_function(db, "sha3", 1, SQLITE_UTF8, 0, - sha3Func, 0, 0); + rc = sqlite3_create_function(db, "sha3", 1, + SQLITE_UTF8 | SQLITE_INNOCUOUS | SQLITE_DETERMINISTIC, + 0, sha3Func, 0, 0); + if( rc==SQLITE_OK ){ + rc = sqlite3_create_function(db, "sha3", 2, + SQLITE_UTF8 | SQLITE_INNOCUOUS | SQLITE_DETERMINISTIC, + 0, sha3Func, 0, 0); + } if( rc==SQLITE_OK ){ - rc = sqlite3_create_function(db, "sha3", 2, SQLITE_UTF8, 0, - sha3Func, 0, 0); + rc = sqlite3_create_function(db, "sha3_query", 1, + SQLITE_UTF8 | SQLITE_DIRECTONLY, + 0, sha3QueryFunc, 0, 0); } if( rc==SQLITE_OK ){ - rc = sqlite3_create_function(db, "sha3_query", 1, SQLITE_UTF8, 0, - sha3QueryFunc, 0, 0); - } - if( rc==SQLITE_OK ){ - rc = sqlite3_create_function(db, "sha3_query", 2, SQLITE_UTF8, 0, - sha3QueryFunc, 0, 0); + rc = sqlite3_create_function(db, "sha3_query", 2, + SQLITE_UTF8 | SQLITE_DIRECTONLY, + 0, sha3QueryFunc, 0, 0); } return rc; } Index: ext/misc/spellfix.c ================================================================== --- ext/misc/spellfix.c +++ ext/misc/spellfix.c @@ -2067,10 +2067,11 @@ pNew->zTableName = sqlite3_mprintf("%s", zTableName); pNew->db = db; if( pNew->zTableName==0 ){ rc = SQLITE_NOMEM; }else{ + sqlite3_vtab_config(db, SQLITE_INNOCUOUS); rc = sqlite3_declare_vtab(db, "CREATE TABLE x(word,rank,distance,langid, " "score, matchlen, phonehash HIDDEN, " "top HIDDEN, scope HIDDEN, srchcnt HIDDEN, " "soundslike HIDDEN, command HIDDEN)" Index: ext/misc/sqlar.c ================================================================== --- ext/misc/sqlar.c +++ ext/misc/sqlar.c @@ -109,13 +109,15 @@ const sqlite3_api_routines *pApi ){ int rc = SQLITE_OK; SQLITE_EXTENSION_INIT2(pApi); (void)pzErrMsg; /* Unused parameter */ - rc = sqlite3_create_function(db, "sqlar_compress", 1, SQLITE_UTF8, 0, + rc = sqlite3_create_function(db, "sqlar_compress", 1, + SQLITE_UTF8|SQLITE_INNOCUOUS, 0, sqlarCompressFunc, 0, 0); if( rc==SQLITE_OK ){ - rc = sqlite3_create_function(db, "sqlar_uncompress", 2, SQLITE_UTF8, 0, + rc = sqlite3_create_function(db, "sqlar_uncompress", 2, + SQLITE_UTF8|SQLITE_INNOCUOUS, 0, sqlarUncompressFunc, 0, 0); } return rc; } Index: ext/misc/totype.c ================================================================== --- ext/misc/totype.c +++ ext/misc/totype.c @@ -501,14 +501,14 @@ ){ int rc = SQLITE_OK; SQLITE_EXTENSION_INIT2(pApi); (void)pzErrMsg; /* Unused parameter */ rc = sqlite3_create_function(db, "tointeger", 1, - SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, + SQLITE_UTF8 | SQLITE_DETERMINISTIC | SQLITE_INNOCUOUS, 0, tointegerFunc, 0, 0); if( rc==SQLITE_OK ){ rc = sqlite3_create_function(db, "toreal", 1, - SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, + SQLITE_UTF8 | SQLITE_DETERMINISTIC | SQLITE_INNOCUOUS, 0, torealFunc, 0, 0); } return rc; } Index: ext/misc/uuid.c ================================================================== --- ext/misc/uuid.c +++ ext/misc/uuid.c @@ -215,17 +215,19 @@ const sqlite3_api_routines *pApi ){ int rc = SQLITE_OK; SQLITE_EXTENSION_INIT2(pApi); (void)pzErrMsg; /* Unused parameter */ - rc = sqlite3_create_function(db, "uuid", 0, SQLITE_UTF8, 0, + rc = sqlite3_create_function(db, "uuid", 0, SQLITE_UTF8|SQLITE_INNOCUOUS, 0, sqlite3UuidFunc, 0, 0); if( rc==SQLITE_OK ){ - rc = sqlite3_create_function(db, "uuid_str", 1, SQLITE_UTF8, 0, - sqlite3UuidStrFunc, 0, 0); + rc = sqlite3_create_function(db, "uuid_str", 1, + SQLITE_UTF8|SQLITE_INNOCUOUS|SQLITE_DETERMINISTIC, + 0, sqlite3UuidStrFunc, 0, 0); } if( rc==SQLITE_OK ){ - rc = sqlite3_create_function(db, "uuid_blob", 1, SQLITE_UTF8, 0, - sqlite3UuidBlobFunc, 0, 0); + rc = sqlite3_create_function(db, "uuid_blob", 1, + SQLITE_UTF8|SQLITE_INNOCUOUS|SQLITE_DETERMINISTIC, + 0, sqlite3UuidBlobFunc, 0, 0); } return rc; } Index: ext/misc/wholenumber.c ================================================================== --- ext/misc/wholenumber.c +++ ext/misc/wholenumber.c @@ -48,10 +48,11 @@ ){ sqlite3_vtab *pNew; pNew = *ppVtab = sqlite3_malloc( sizeof(*pNew) ); if( pNew==0 ) return SQLITE_NOMEM; sqlite3_declare_vtab(db, "CREATE TABLE x(value)"); + sqlite3_vtab_config(db, SQLITE_VTAB_INNOCUOUS); memset(pNew, 0, sizeof(*pNew)); return SQLITE_OK; } /* Note that for this virtual table, the xCreate and xConnect ** methods are identical. */ Index: ext/misc/zipfile.c ================================================================== --- ext/misc/zipfile.c +++ ext/misc/zipfile.c @@ -367,10 +367,11 @@ pNew->zFile = (char*)&pNew->aBuffer[ZIPFILE_BUFFER_SIZE]; memcpy(pNew->zFile, zFile, nFile); zipfileDequote(pNew->zFile); } } + sqlite3_vtab_config(db, SQLITE_VTAB_DIRECTONLY); *ppVtab = (sqlite3_vtab*)pNew; return rc; } /* Index: ext/rtree/geopoly.c ================================================================== --- ext/rtree/geopoly.c +++ ext/rtree/geopoly.c @@ -1784,18 +1784,24 @@ } aAgg[] = { { geopolyBBoxStep, geopolyBBoxFinal, "geopoly_group_bbox" }, }; int i; for(i=0; ipParse = pParse; pFix->zDb = db->aDb[iDb].zDbSName; pFix->pSchema = db->aDb[iDb].pSchema; pFix->zType = zType; pFix->pName = pName; - pFix->bVarOnly = (iDb==1); + pFix->bTemp = (iDb==1); } /* ** The following set of routines walk through the parse tree and assign ** a specific database to all table references where the database name @@ -503,20 +503,21 @@ struct SrcList_item *pItem; if( NEVER(pList==0) ) return 0; zDb = pFix->zDb; for(i=0, pItem=pList->a; inSrc; i++, pItem++){ - if( pFix->bVarOnly==0 ){ + if( pFix->bTemp==0 ){ if( pItem->zDatabase && sqlite3StrICmp(pItem->zDatabase, zDb) ){ sqlite3ErrorMsg(pFix->pParse, "%s %T cannot reference objects in database %s", pFix->zType, pFix->pName, pItem->zDatabase); return 1; } sqlite3DbFree(pFix->pParse->db, pItem->zDatabase); pItem->zDatabase = 0; pItem->pSchema = pFix->pSchema; + pItem->fg.fromDDL = 1; } #if !defined(SQLITE_OMIT_VIEW) || !defined(SQLITE_OMIT_TRIGGER) if( sqlite3FixSelect(pFix, pItem->pSelect) ) return 1; if( sqlite3FixExpr(pFix, pItem->pOn) ) return 1; #endif @@ -568,11 +569,11 @@ int sqlite3FixExpr( DbFixer *pFix, /* Context of the fixation */ Expr *pExpr /* The expression to be fixed to one database */ ){ while( pExpr ){ - ExprSetProperty(pExpr, EP_Indirect); + if( !pFix->bTemp ) ExprSetProperty(pExpr, EP_FromDDL); if( pExpr->op==TK_VARIABLE ){ if( pFix->pParse->db->init.busy ){ pExpr->op = TK_NULL; }else{ sqlite3ErrorMsg(pFix->pParse, "%s cannot use variables", pFix->zType); Index: src/build.c ================================================================== --- src/build.c +++ src/build.c @@ -1402,12 +1402,13 @@ Table *p; Column *pCol; sqlite3 *db = pParse->db; p = pParse->pNewTable; if( p!=0 ){ + int isInit = db->init.busy && db->init.iDb!=1; pCol = &(p->aCol[p->nCol-1]); - if( !sqlite3ExprIsConstantOrFunction(pExpr, db->init.busy) ){ + if( !sqlite3ExprIsConstantOrFunction(pExpr, isInit) ){ sqlite3ErrorMsg(pParse, "default value of column [%s] is not constant", pCol->zName); #ifndef SQLITE_OMIT_GENERATED_COLUMNS }else if( pCol->colFlags & COLFLAG_GENERATED ){ testcase( pCol->colFlags & COLFLAG_VIRTUAL ); Index: src/callback.c ================================================================== --- src/callback.c +++ src/callback.c @@ -286,16 +286,17 @@ FuncDef *p, /* The function we are evaluating for match quality */ int nArg, /* Desired number of arguments. (-1)==any */ u8 enc /* Desired text encoding */ ){ int match; - - /* nArg of -2 is a special case */ - if( nArg==(-2) ) return (p->xSFunc==0) ? 0 : FUNC_PERFECT_MATCH; + assert( p->nArg>=-1 ); /* Wrong number of arguments means "no match" */ - if( p->nArg!=nArg && p->nArg>=0 ) return 0; + if( p->nArg!=nArg ){ + if( nArg==(-2) ) return (p->xSFunc==0) ? 0 : FUNC_PERFECT_MATCH; + if( p->nArg>=0 ) return 0; + } /* Give a better score to a function with a specific number of arguments ** than to function that accepts any number of arguments. */ if( p->nArg==nArg ){ match = 4; Index: src/dbpage.c ================================================================== --- src/dbpage.c +++ src/dbpage.c @@ -71,10 +71,11 @@ char **pzErr ){ DbpageTable *pTab = 0; int rc = SQLITE_OK; + sqlite3_vtab_config(db, SQLITE_VTAB_DIRECTONLY); rc = sqlite3_declare_vtab(db, "CREATE TABLE x(pgno INTEGER PRIMARY KEY, data BLOB, schema HIDDEN)"); if( rc==SQLITE_OK ){ pTab = (DbpageTable *)sqlite3_malloc64(sizeof(DbpageTable)); if( pTab==0 ) rc = SQLITE_NOMEM_BKPT; Index: src/dbstat.c ================================================================== --- src/dbstat.c +++ src/dbstat.c @@ -165,10 +165,11 @@ return SQLITE_ERROR; } }else{ iDb = 0; } + sqlite3_vtab_config(db, SQLITE_VTAB_DIRECTONLY); rc = sqlite3_declare_vtab(db, zDbstatSchema); if( rc==SQLITE_OK ){ pTab = (StatTable *)sqlite3_malloc64(sizeof(StatTable)); if( pTab==0 ) rc = SQLITE_NOMEM_BKPT; } Index: src/expr.c ================================================================== --- src/expr.c +++ src/expr.c @@ -970,10 +970,44 @@ assert( !ExprHasProperty(pNew, EP_xIsSelect) ); sqlite3ExprSetHeightAndFlags(pParse, pNew); if( eDistinct==SF_Distinct ) ExprSetProperty(pNew, EP_Distinct); return pNew; } + +/* +** Check to see if a function is usable according to current access +** rules: +** +** SQLITE_FUNC_DIRECT - Only usable from top-level SQL +** +** SQLITE_FUNC_UNSAFE - Usable if TRUSTED_SCHEMA or from +** top-level SQL +** +** If the function is not usable, create an error. +*/ +void sqlite3ExprFunctionUsable( + Parse *pParse, /* Parsing and code generating context */ + Expr *pExpr, /* The function invocation */ + FuncDef *pDef /* The function being invoked */ +){ + assert( !IN_RENAME_OBJECT ); + assert( (pDef->funcFlags & (SQLITE_FUNC_DIRECT|SQLITE_FUNC_UNSAFE))!=0 ); + if( ExprHasProperty(pExpr, EP_FromDDL) ){ + if( (pDef->funcFlags & SQLITE_FUNC_DIRECT)!=0 + || (pParse->db->flags & SQLITE_TrustedSchema)==0 + ){ + /* Functions prohibited in triggers and views if: + ** (1) tagged with SQLITE_DIRECTONLY + ** (2) not tagged with SQLITE_INNOCUOUS (which means it + ** is tagged with SQLITE_FUNC_UNSAFE) and + ** SQLITE_DBCONFIG_TRUSTED_SCHEMA is off (meaning + ** that the schema is possibly tainted). + */ + sqlite3ErrorMsg(pParse, "unsafe use of %s()", pDef->zName); + } + } +} /* ** Assign a variable number to an expression that encodes a wildcard ** in the original SQL statement. ** @@ -1935,14 +1969,15 @@ ** sqlite3ExprIsConstantOrFunction() pWalker->eCode==4 or 5 ** ** In all cases, the callbacks set Walker.eCode=0 and abort if the expression ** is found to not be a constant. ** -** The sqlite3ExprIsConstantOrFunction() is used for evaluating expressions -** in a CREATE TABLE statement. The Walker.eCode value is 5 when parsing -** an existing schema and 4 when processing a new statement. A bound -** parameter raises an error for new statements, but is silently converted +** The sqlite3ExprIsConstantOrFunction() is used for evaluating DEFAULT +** expressions in a CREATE TABLE statement. The Walker.eCode value is 5 +** when parsing an existing schema out of the sqlite_master table and 4 +** when processing a new CREATE TABLE statement. A bound parameter raises +** an error for new statements, but is silently converted ** to NULL for existing schemas. This allows sqlite_master tables that ** contain a bound parameter because they were generated by older versions ** of SQLite to be parsed by newer versions of SQLite without raising a ** malformed schema error. */ @@ -1962,10 +1997,11 @@ ** SQLITE_FUNC_CONST flag. */ case TK_FUNCTION: if( (pWalker->eCode>=4 || ExprHasProperty(pExpr,EP_ConstFunc)) && !ExprHasProperty(pExpr, EP_WinFunc) ){ + if( pWalker->eCode==5 ) ExprSetProperty(pExpr, EP_FromDDL); return WRC_Continue; }else{ pWalker->eCode = 0; return WRC_Abort; } @@ -2125,13 +2161,25 @@ sqlite3WalkExpr(&w, p); return w.eCode; } /* -** Walk an expression tree. Return non-zero if the expression is constant -** or a function call with constant arguments. Return and 0 if there -** are any variables. +** Walk an expression tree for the DEFAULT field of a column definition +** in a CREATE TABLE statement. Return non-zero if the expression is +** acceptable for use as a DEFAULT. That is to say, return non-zero if +** the expression is constant or a function call with constant arguments. +** Return and 0 if there are any variables. +** +** isInit is true when parsing from sqlite_master. isInit is false when +** processing a new CREATE TABLE statement. When isInit is true, parameters +** (such as ? or $abc) in the expression are converted into NULL. When +** isInit is false, parameters raise an error. Parameters should not be +** allowed in a CREATE TABLE statement, but some legacy versions of SQLite +** allowed it, so we need to support it when reading sqlite_master for +** backwards compatibility. +** +** If isInit is true, set EP_FromDDL on every TK_FUNCTION node. ** ** For the purposes of this function, a double-quoted string (ex: "abc") ** is considered a variable but a single-quoted string (ex: 'abc') is ** a constant. */ @@ -4071,12 +4119,16 @@ if( pDef==0 || pDef->xFinalize!=0 ){ sqlite3ErrorMsg(pParse, "unknown function: %s()", zId); break; } if( pDef->funcFlags & SQLITE_FUNC_INLINE ){ + assert( (pDef->funcFlags & SQLITE_FUNC_UNSAFE)==0 ); + assert( (pDef->funcFlags & SQLITE_FUNC_DIRECT)==0 ); return exprCodeInlineFunction(pParse, pFarg, SQLITE_PTR_TO_INT(pDef->pUserData), target); + }else if( pDef->funcFlags & (SQLITE_FUNC_DIRECT|SQLITE_FUNC_UNSAFE) ){ + sqlite3ExprFunctionUsable(pParse, pExpr, pDef); } for(i=0; ia[i].pExpr) ){ testcase( i==31 ); Index: src/main.c ================================================================== --- src/main.c +++ src/main.c @@ -885,10 +885,11 @@ SQLITE_NoSchemaError }, { SQLITE_DBCONFIG_LEGACY_ALTER_TABLE, SQLITE_LegacyAlter }, { SQLITE_DBCONFIG_DQS_DDL, SQLITE_DqsDDL }, { SQLITE_DBCONFIG_DQS_DML, SQLITE_DqsDML }, { SQLITE_DBCONFIG_LEGACY_FILE_FORMAT, SQLITE_LegacyFileFmt }, + { SQLITE_DBCONFIG_TRUSTED_SCHEMA, SQLITE_TrustedSchema }, }; unsigned int i; rc = SQLITE_ERROR; /* IMP: R-42790-23372 */ for(i=0; inMaxSorterMmap = 0x7FFFFFFF; db->flags |= SQLITE_ShortColNames | SQLITE_EnableTrigger | SQLITE_EnableView | SQLITE_CacheSpill - +#if !defined(SQLITE_TRUSTED_SCHEMA) || SQLITE_TRUSTED_SCHEMA+0!=0 + | SQLITE_TrustedSchema +#endif /* The SQLITE_DQS compile-time option determines the default settings ** for SQLITE_DBCONFIG_DQS_DDL and SQLITE_DBCONFIG_DQS_DML. ** ** SQLITE_DQS SQLITE_DBCONFIG_DQS_DDL SQLITE_DBCONFIG_DQS_DML ** ---------- ----------------------- ----------------------- Index: src/pragma.c ================================================================== --- src/pragma.c +++ src/pragma.c @@ -292,10 +292,59 @@ lwr = mid + 1; } } return lwr>upr ? 0 : &aPragmaName[mid]; } + +/* +** Create zero or more entries in the output for the SQL functions +** defined by FuncDef p. +*/ +static void pragmaFunclistLine( + Vdbe *v, /* The prepared statement being created */ + FuncDef *p, /* A particular function definition */ + int isBuiltin, /* True if this is a built-in function */ + int showInternFuncs /* True if showing internal functions */ +){ + for(; p; p=p->pNext){ + const char *zType; + static const u32 mask = + SQLITE_DETERMINISTIC | + SQLITE_DIRECTONLY | + SQLITE_SUBTYPE | + SQLITE_INNOCUOUS | + SQLITE_FUNC_INTERNAL + ; + static const char *azEnc[] = { 0, "utf8", "utf16le", "utf16be" }; + + assert( SQLITE_FUNC_ENCMASK==0x3 ); + assert( strcmp(azEnc[SQLITE_UTF8],"utf8")==0 ); + assert( strcmp(azEnc[SQLITE_UTF16LE],"utf16le")==0 ); + assert( strcmp(azEnc[SQLITE_UTF16BE],"utf16be")==0 ); + + if( p->xSFunc==0 ) continue; + if( (p->funcFlags & SQLITE_FUNC_INTERNAL)!=0 + && showInternFuncs==0 + ){ + continue; + } + if( p->xValue!=0 ){ + zType = "w"; + }else if( p->xFinalize!=0 ){ + zType = "a"; + }else{ + zType = "s"; + } + sqlite3VdbeMultiLoad(v, 1, "sissii", + p->zName, isBuiltin, + zType, azEnc[p->funcFlags&SQLITE_FUNC_ENCMASK], + p->nArg, + (p->funcFlags & mask) ^ SQLITE_INNOCUOUS + ); + } +} + /* ** Helper subroutine for PRAGMA integrity_check: ** ** Generate code to output a single-column result row with a value of the @@ -1257,20 +1306,20 @@ #ifndef SQLITE_OMIT_INTROSPECTION_PRAGMAS case PragTyp_FUNCTION_LIST: { int i; HashElem *j; FuncDef *p; - pParse->nMem = 2; + int showInternFunc = (db->mDbFlags & DBFLAG_InternalFunc)!=0; + pParse->nMem = 6; for(i=0; iu.pHash ){ - if( p->funcFlags & SQLITE_FUNC_INTERNAL ) continue; - sqlite3VdbeMultiLoad(v, 1, "si", p->zName, 1); + pragmaFunclistLine(v, p, 1, showInternFunc); } } for(j=sqliteHashFirst(&db->aFunc); j; j=sqliteHashNext(j)){ p = (FuncDef*)sqliteHashData(j); - sqlite3VdbeMultiLoad(v, 1, "si", p->zName, 0); + pragmaFunclistLine(v, p, 0, showInternFunc); } } break; #ifndef SQLITE_OMIT_VIRTUALTABLE Index: src/pragma.h ================================================================== --- src/pragma.h +++ src/pragma.h @@ -86,39 +86,43 @@ /* 16 */ "cid", /* 17 */ "name", /* 18 */ "desc", /* 19 */ "coll", /* 20 */ "key", - /* 21 */ "tbl", /* Used by: stats */ - /* 22 */ "idx", - /* 23 */ "wdth", - /* 24 */ "hght", - /* 25 */ "flgs", - /* 26 */ "seq", /* Used by: index_list */ - /* 27 */ "name", - /* 28 */ "unique", - /* 29 */ "origin", - /* 30 */ "partial", - /* 31 */ "table", /* Used by: foreign_key_check */ - /* 32 */ "rowid", - /* 33 */ "parent", - /* 34 */ "fkid", + /* 21 */ "name", /* Used by: function_list */ + /* 22 */ "builtin", + /* 23 */ "type", + /* 24 */ "enc", + /* 25 */ "narg", + /* 26 */ "flags", + /* 27 */ "tbl", /* Used by: stats */ + /* 28 */ "idx", + /* 29 */ "wdth", + /* 30 */ "hght", + /* 31 */ "flgs", + /* 32 */ "seq", /* Used by: index_list */ + /* 33 */ "name", + /* 34 */ "unique", + /* 35 */ "origin", + /* 36 */ "partial", + /* 37 */ "table", /* Used by: foreign_key_check */ + /* 38 */ "rowid", + /* 39 */ "parent", + /* 40 */ "fkid", /* index_info reuses 15 */ - /* 35 */ "seq", /* Used by: database_list */ - /* 36 */ "name", - /* 37 */ "file", - /* 38 */ "busy", /* Used by: wal_checkpoint */ - /* 39 */ "log", - /* 40 */ "checkpointed", - /* 41 */ "name", /* Used by: function_list */ - /* 42 */ "builtin", - /* collation_list reuses 26 */ - /* 43 */ "database", /* Used by: lock_status */ - /* 44 */ "status", - /* 45 */ "cache_size", /* Used by: default_cache_size */ + /* 41 */ "seq", /* Used by: database_list */ + /* 42 */ "name", + /* 43 */ "file", + /* 44 */ "busy", /* Used by: wal_checkpoint */ + /* 45 */ "log", + /* 46 */ "checkpointed", + /* collation_list reuses 32 */ + /* 47 */ "database", /* Used by: lock_status */ + /* 48 */ "status", + /* 49 */ "cache_size", /* Used by: default_cache_size */ /* module_list pragma_list reuses 9 */ - /* 46 */ "timeout", /* Used by: busy_timeout */ + /* 50 */ "timeout", /* Used by: busy_timeout */ }; /* Definitions of all built-in pragmas */ typedef struct PragmaName { const char *const zName; /* Name of pragma */ @@ -160,11 +164,11 @@ #endif #endif {/* zName: */ "busy_timeout", /* ePragTyp: */ PragTyp_BUSY_TIMEOUT, /* ePragFlg: */ PragFlg_Result0, - /* ColNames: */ 46, 1, + /* ColNames: */ 50, 1, /* iArg: */ 0 }, #if !defined(SQLITE_OMIT_PAGER_PRAGMAS) {/* zName: */ "cache_size", /* ePragTyp: */ PragTyp_CACHE_SIZE, /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result0|PragFlg_SchemaReq|PragFlg_NoColumns1, @@ -199,11 +203,11 @@ #endif #if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) {/* zName: */ "collation_list", /* ePragTyp: */ PragTyp_COLLATION_LIST, /* ePragFlg: */ PragFlg_Result0, - /* ColNames: */ 26, 2, + /* ColNames: */ 32, 2, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_COMPILEOPTION_DIAGS) {/* zName: */ "compile_options", /* ePragTyp: */ PragTyp_COMPILE_OPTIONS, @@ -234,18 +238,18 @@ #endif #if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) {/* zName: */ "database_list", /* ePragTyp: */ PragTyp_DATABASE_LIST, /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result0, - /* ColNames: */ 35, 3, + /* ColNames: */ 41, 3, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_PAGER_PRAGMAS) && !defined(SQLITE_OMIT_DEPRECATED) {/* zName: */ "default_cache_size", /* ePragTyp: */ PragTyp_DEFAULT_CACHE_SIZE, /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result0|PragFlg_SchemaReq|PragFlg_NoColumns1, - /* ColNames: */ 45, 1, + /* ColNames: */ 49, 1, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_FLAG_PRAGMAS) #if !defined(SQLITE_OMIT_FOREIGN_KEY) && !defined(SQLITE_OMIT_TRIGGER) {/* zName: */ "defer_foreign_keys", @@ -271,11 +275,11 @@ #endif #if !defined(SQLITE_OMIT_FOREIGN_KEY) && !defined(SQLITE_OMIT_TRIGGER) {/* zName: */ "foreign_key_check", /* ePragTyp: */ PragTyp_FOREIGN_KEY_CHECK, /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result0, - /* ColNames: */ 31, 4, + /* ColNames: */ 37, 4, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_FOREIGN_KEY) {/* zName: */ "foreign_key_list", /* ePragTyp: */ PragTyp_FOREIGN_KEY_LIST, @@ -314,11 +318,11 @@ #if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) #if !defined(SQLITE_OMIT_INTROSPECTION_PRAGMAS) {/* zName: */ "function_list", /* ePragTyp: */ PragTyp_FUNCTION_LIST, /* ePragFlg: */ PragFlg_Result0, - /* ColNames: */ 41, 2, + /* ColNames: */ 21, 6, /* iArg: */ 0 }, #endif #endif {/* zName: */ "hard_heap_limit", /* ePragTyp: */ PragTyp_HARD_HEAP_LIMIT, @@ -360,11 +364,11 @@ /* ColNames: */ 15, 3, /* iArg: */ 0 }, {/* zName: */ "index_list", /* ePragTyp: */ PragTyp_INDEX_LIST, /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result1|PragFlg_SchemaOpt, - /* ColNames: */ 26, 5, + /* ColNames: */ 32, 5, /* iArg: */ 0 }, {/* zName: */ "index_xinfo", /* ePragTyp: */ PragTyp_INDEX_INFO, /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result1|PragFlg_SchemaOpt, /* ColNames: */ 15, 6, @@ -412,11 +416,11 @@ #endif #if defined(SQLITE_DEBUG) || defined(SQLITE_TEST) {/* zName: */ "lock_status", /* ePragTyp: */ PragTyp_LOCK_STATUS, /* ePragFlg: */ PragFlg_Result0, - /* ColNames: */ 43, 2, + /* ColNames: */ 47, 2, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_PAGER_PRAGMAS) {/* zName: */ "locking_mode", /* ePragTyp: */ PragTyp_LOCKING_MODE, @@ -560,11 +564,11 @@ #endif #if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) && defined(SQLITE_DEBUG) {/* zName: */ "stats", /* ePragTyp: */ PragTyp_STATS, /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result0|PragFlg_SchemaReq, - /* ColNames: */ 21, 5, + /* ColNames: */ 27, 5, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_PAGER_PRAGMAS) {/* zName: */ "synchronous", /* ePragTyp: */ PragTyp_SYNCHRONOUS, @@ -611,10 +615,17 @@ {/* zName: */ "threads", /* ePragTyp: */ PragTyp_THREADS, /* ePragFlg: */ PragFlg_Result0, /* ColNames: */ 0, 0, /* iArg: */ 0 }, +#if !defined(SQLITE_OMIT_FLAG_PRAGMAS) + {/* zName: */ "trusted_schema", + /* ePragTyp: */ PragTyp_FLAG, + /* ePragFlg: */ PragFlg_Result0|PragFlg_NoColumns1, + /* ColNames: */ 0, 0, + /* iArg: */ SQLITE_TrustedSchema }, +#endif #if !defined(SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS) {/* zName: */ "user_version", /* ePragTyp: */ PragTyp_HEADER_VALUE, /* ePragFlg: */ PragFlg_NoColumns1|PragFlg_Result0, /* ColNames: */ 0, 0, @@ -656,11 +667,11 @@ /* ColNames: */ 0, 0, /* iArg: */ 0 }, {/* zName: */ "wal_checkpoint", /* ePragTyp: */ PragTyp_WAL_CHECKPOINT, /* ePragFlg: */ PragFlg_NeedSchema, - /* ColNames: */ 38, 3, + /* ColNames: */ 44, 3, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_FLAG_PRAGMAS) {/* zName: */ "writable_schema", /* ePragTyp: */ PragTyp_FLAG, @@ -667,6 +678,6 @@ /* ePragFlg: */ PragFlg_Result0|PragFlg_NoColumns1, /* ColNames: */ 0, 0, /* iArg: */ SQLITE_WriteSchema|SQLITE_NoSchemaError }, #endif }; -/* Number of pragmas: 65 on by default, 81 total. */ +/* Number of pragmas: 66 on by default, 82 total. */ Index: src/resolve.c ================================================================== --- src/resolve.c +++ src/resolve.c @@ -874,28 +874,27 @@ sqlite3ResolveNotValid(pParse, pNC, "non-deterministic functions", NC_SelfRef, 0); }else{ assert( (NC_SelfRef & 0xff)==NC_SelfRef ); /* Must fit in 8 bits */ pExpr->op2 = pNC->ncFlags & NC_SelfRef; + if( pNC->ncFlags & NC_FromDDL ) ExprSetProperty(pExpr, EP_FromDDL); } if( (pDef->funcFlags & SQLITE_FUNC_INTERNAL)!=0 && pParse->nested==0 && (pParse->db->mDbFlags & DBFLAG_InternalFunc)==0 ){ /* Internal-use-only functions are disallowed unless the - ** SQL is being compiled using sqlite3NestedParse() */ + ** SQL is being compiled using sqlite3NestedParse() or + ** the SQLITE_TESTCTRL_INTERNAL_FUNCTIONS test-control has be + ** used to activate internal functionsn for testing purposes */ no_such_func = 1; pDef = 0; }else - if( (pDef->funcFlags & SQLITE_FUNC_DIRECT)!=0 - && ExprHasProperty(pExpr, EP_Indirect) + if( (pDef->funcFlags & (SQLITE_FUNC_DIRECT|SQLITE_FUNC_UNSAFE))!=0 && !IN_RENAME_OBJECT ){ - /* Functions tagged with SQLITE_DIRECTONLY may not be used - ** inside of triggers and views */ - sqlite3ErrorMsg(pParse, "%s() prohibited in triggers and views", - pDef->zName); + sqlite3ExprFunctionUsable(pParse, pExpr, pDef); } } if( 0==IN_RENAME_OBJECT ){ #ifndef SQLITE_OMIT_WINDOWFUNC @@ -1883,13 +1882,18 @@ if( pTab ){ sSrc.nSrc = 1; sSrc.a[0].zName = pTab->zName; sSrc.a[0].pTab = pTab; sSrc.a[0].iCursor = -1; + if( pTab->pSchema!=pParse->db->aDb[1].pSchema ){ + /* Cause EP_FromDDL to be set on TK_FUNCTION nodes of non-TEMP + ** schema elements */ + type |= NC_FromDDL; + } } sNC.pParse = pParse; sNC.pSrcList = &sSrc; sNC.ncFlags = type | NC_IsDDL; if( (rc = sqlite3ResolveExprNames(&sNC, pExpr))!=SQLITE_OK ) return rc; if( pList ) rc = sqlite3ResolveExprListNames(&sNC, pList); return rc; } Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -4968,11 +4968,19 @@ u8 eCodeOrig = pWalker->eCode; if( sqlite3ViewGetColumnNames(pParse, pTab) ) return WRC_Abort; assert( pFrom->pSelect==0 ); if( pTab->pSelect && (db->flags & SQLITE_EnableView)==0 ){ sqlite3ErrorMsg(pParse, "access to view \"%s\" prohibited", - pTab->zName); + pTab->zName); + } + if( IsVirtual(pTab) + && pFrom->fg.fromDDL + && ALWAYS(pTab->pVTable!=0) + && pTab->pVTable->eVtabRisk > ((db->flags & SQLITE_TrustedSchema)!=0) + ){ + sqlite3ErrorMsg(pParse, "unsafe use of virtual table \"%s\"", + pTab->zName); } pFrom->pSelect = sqlite3SelectDup(db, pTab->pSelect, 0); nCol = pTab->nCol; pTab->nCol = -1; pWalker->eCode = 1; /* Turn on Select.selId renumbering */ Index: src/shell.c.in ================================================================== --- src/shell.c.in +++ src/shell.c.in @@ -7157,35 +7157,36 @@ if( c=='d' && n>=3 && strncmp(azArg[0], "dbconfig", n)==0 ){ static const struct DbConfigChoices { const char *zName; int op; } aDbConfig[] = { + { "defensive", SQLITE_DBCONFIG_DEFENSIVE }, + { "dqs_ddl", SQLITE_DBCONFIG_DQS_DDL }, + { "dqs_dml", SQLITE_DBCONFIG_DQS_DML }, { "enable_fkey", SQLITE_DBCONFIG_ENABLE_FKEY }, + { "enable_qpsg", SQLITE_DBCONFIG_ENABLE_QPSG }, { "enable_trigger", SQLITE_DBCONFIG_ENABLE_TRIGGER }, { "enable_view", SQLITE_DBCONFIG_ENABLE_VIEW }, { "fts3_tokenizer", SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER }, + { "legacy_alter_table", SQLITE_DBCONFIG_LEGACY_ALTER_TABLE }, + { "legacy_file_format", SQLITE_DBCONFIG_LEGACY_FILE_FORMAT }, { "load_extension", SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION }, { "no_ckpt_on_close", SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE }, - { "enable_qpsg", SQLITE_DBCONFIG_ENABLE_QPSG }, + { "reset_database", SQLITE_DBCONFIG_RESET_DATABASE }, { "trigger_eqp", SQLITE_DBCONFIG_TRIGGER_EQP }, - { "reset_database", SQLITE_DBCONFIG_RESET_DATABASE }, - { "defensive", SQLITE_DBCONFIG_DEFENSIVE }, + { "trusted_schema", SQLITE_DBCONFIG_TRUSTED_SCHEMA }, { "writable_schema", SQLITE_DBCONFIG_WRITABLE_SCHEMA }, - { "legacy_alter_table", SQLITE_DBCONFIG_LEGACY_ALTER_TABLE }, - { "dqs_dml", SQLITE_DBCONFIG_DQS_DML }, - { "dqs_ddl", SQLITE_DBCONFIG_DQS_DDL }, - { "legacy_file_format", SQLITE_DBCONFIG_LEGACY_FILE_FORMAT }, }; int ii, v; open_db(p, 0); for(ii=0; ii1 && strcmp(azArg[1], aDbConfig[ii].zName)!=0 ) continue; if( nArg>=3 ){ sqlite3_db_config(p->db, aDbConfig[ii].op, booleanValue(azArg[2]), 0); } sqlite3_db_config(p->db, aDbConfig[ii].op, -1, &v); - utf8_printf(p->out, "%18s %s\n", aDbConfig[ii].zName, v ? "on" : "off"); + utf8_printf(p->out, "%19s %s\n", aDbConfig[ii].zName, v ? "on" : "off"); if( nArg>1 ) break; } if( nArg>1 && ii==ArraySize(aDbConfig) ){ utf8_printf(stderr, "Error: unknown dbconfig \"%s\"\n", azArg[1]); utf8_printf(stderr, "Enter \".dbconfig\" with no arguments for a list\n"); Index: src/sqlite.h.in ================================================================== --- src/sqlite.h.in +++ src/sqlite.h.in @@ -2262,10 +2262,29 @@ ** the legacy [double-quoted string literal] misfeature for DDL statements, ** such as CREATE TABLE and CREATE INDEX. The ** default value of this setting is determined by the [-DSQLITE_DQS] ** compile-time option. ** +** +** [[SQLITE_DBCONFIG_TRUSTED_SCHEMA]] +**

SQLITE_DBCONFIG_TRUSTED_SCHEMA +**
The SQLITE_DBCONFIG_TRUSTED_SCHEMA option tells the SQLite to +** assume that database schemas are untainted by malicious content. +** When the SQLITE_DBCONFIG_TRUSTED_SCHEMA option is disabled, SQLite +** takes additional defensive steps to protect the application from harm +** including, but not limited to, the following: +** +** This setting defaults to "on" for legacy compatibility, however +** all applications are advised to turn it off if possible. +**
** ** [[SQLITE_DBCONFIG_LEGACY_FILE_FORMAT]] **
SQLITE_DBCONFIG_LEGACY_FILE_FORMAT **
The SQLITE_DBCONFIG_LEGACY_FILE_FORMAT option activates or deactivates ** the legacy file format flag. When activated, this flag causes all newly @@ -2303,11 +2322,12 @@ #define SQLITE_DBCONFIG_LEGACY_ALTER_TABLE 1012 /* int int* */ #define SQLITE_DBCONFIG_DQS_DML 1013 /* int int* */ #define SQLITE_DBCONFIG_DQS_DDL 1014 /* int int* */ #define SQLITE_DBCONFIG_ENABLE_VIEW 1015 /* int int* */ #define SQLITE_DBCONFIG_LEGACY_FILE_FORMAT 1016 /* int int* */ -#define SQLITE_DBCONFIG_MAX 1016 /* Largest DBCONFIG */ +#define SQLITE_DBCONFIG_TRUSTED_SCHEMA 1017 /* int int* */ +#define SQLITE_DBCONFIG_MAX 1017 /* Largest DBCONFIG */ /* ** CAPI3REF: Enable Or Disable Extended Result Codes ** METHOD: sqlite3 ** @@ -4994,16 +5014,30 @@ ** [SQLITE_UTF8 | preferred text encoding] as the fourth argument ** to [sqlite3_create_function()], [sqlite3_create_function16()], or ** [sqlite3_create_function_v2()]. ** ** The SQLITE_DETERMINISTIC flag means that the new function always gives -** the same output when the input parameters are the same. The abs() function -** is deterministic, for example, but randomblob() is not. Functions must +** the same output when the input parameters are the same. +** The [abs|abs() function] is deterministic, for example, but +** [randomblob|randomblob()] is not. Functions must ** be deterministic in order to be used in certain contexts such as ** [CHECK constraints] or [generated columns]. SQLite might also optimize ** deterministic functions by factoring them out of inner loops. ** +** The SQLITE_INNOCUOUS flag means that the new function is unlikely +** to cause problems even if misused. An innocuous function should have +** no side effects and consume few resources. The [abs|abs() function] +** is an example of an innocuous function. +** The [load_extension() SQL function] is not innocuous because of its +** side effects. Some heightened security settings +** ([SQLITE_DBCONFIG_UNSAFE_FUNC_IN_VIEW]) +** disable the use of SQLlfunctions inside views and triggers unless +** the function is tagged with SQLITE_INNOCUOUS. Most built-in functions +** are innocuous. Developers are advised to avoid using the +** SQLITE_INNOCUOUS flag for application-defined functions unless the +** function is specifically intended for use inside of views and triggers. +** ** The SQLITE_DIRECTONLY flag means that the function may only be invoked ** from top-level SQL, and cannot be used in VIEWs or TRIGGERs. This is ** a security feature which is recommended for all ** [application-defined SQL functions] that have side-effects. This flag ** prevents an attacker from adding triggers and views to a schema then @@ -5019,10 +5053,11 @@ ** sqlite3_value_subtype() will always return 0). */ #define SQLITE_DETERMINISTIC 0x000000800 #define SQLITE_DIRECTONLY 0x000080000 #define SQLITE_SUBTYPE 0x000100000 +#define SQLITE_INNOCUOUS 0x000200000 /* ** CAPI3REF: Deprecated Functions ** DEPRECATED ** @@ -8867,11 +8902,11 @@ ** [sqlite3_vtab_config()] interface that [virtual table] implementations ** can use to customize and optimize their behavior. ** **
** [[SQLITE_VTAB_CONSTRAINT_SUPPORT]] -**
SQLITE_VTAB_CONSTRAINT_SUPPORT +**
SQLITE_VTAB_CONSTRAINT_SUPPORT
**
Calls of the form ** [sqlite3_vtab_config](db,SQLITE_VTAB_CONSTRAINT_SUPPORT,X) are supported, ** where X is an integer. If X is zero, then the [virtual table] whose ** [xCreate] or [xConnect] method invoked [sqlite3_vtab_config()] does not ** support constraints. In this configuration (which is the default) if @@ -8896,13 +8931,35 @@ ** CONFLICT policy is REPLACE, the virtual table implementation should ** silently replace the appropriate rows within the xUpdate callback and ** return SQLITE_OK. Or, if this is not possible, it may return ** SQLITE_CONSTRAINT, in which case SQLite falls back to OR ABORT ** constraint handling. +**
+** +** [[SQLITE_VTAB_INNOCUOUS]]
SQLITE_VTAB_INNOCUOUS
+**
Calls of the form +** [sqlite3_vtab_config](db,SQLITE_VTAB_INNOCUOUS) from within the +** the [xConnect] or [xCreate] methods of a [virtual table] implmentation +** identify that virtual table as being safe to use from within triggers +** and views. Conceptually, the SQLITE_VTAB_INNOCUOUS tag means that the +** virtual table can do no serious harm even if it is controlled by a +** malicious hacker. Developers should avoid setting the SQLITE_VTAB_INNOCUOUS +** flag unless absolutely necessary. +**
+** +** [[SQLITE_VTAB_DIRECTONLY]]
SQLITE_VTAB_DIRECTONLY
+**
Calls of the form +** [sqlite3_vtab_config](db,SQLITE_VTAB_DIRECTONLY) from within the +** the [xConnect] or [xCreate] methods of a [virtual table] implmentation +** prohibits that virtual table from being used from within triggers and +** views. +**
**
*/ #define SQLITE_VTAB_CONSTRAINT_SUPPORT 1 +#define SQLITE_VTAB_INNOCUOUS 2 +#define SQLITE_VTAB_DIRECTONLY 3 /* ** CAPI3REF: Determine The Virtual Table Conflict Policy ** ** This function may only be called from within a call to the [xUpdate] method Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -1140,10 +1140,11 @@ /* ** A bit in a Bitmask */ #define MASKBIT(n) (((Bitmask)1)<<(n)) +#define MASKBIT64(n) (((u64)1)<<(n)) #define MASKBIT32(n) (((unsigned int)1)<<(n)) #define ALLBITS ((Bitmask)-1) /* A VList object records a mapping between parameters/variables/wildcards ** in the SQL statement (such as $abc, @pqr, or :xyz) and the integer @@ -1556,10 +1557,17 @@ ** A macro to discover the encoding of a database. */ #define SCHEMA_ENC(db) ((db)->aDb[0].pSchema->enc) #define ENC(db) ((db)->enc) +/* +** A u64 constant where the lower 32 bits are all zeros. Only the +** upper 32 bits are included in the argument. Necessary because some +** C-compilers still do not accept LL integer literals. +*/ +#define HI(X) ((u64)(X)<<32) + /* ** Possible values for the sqlite3.flags. ** ** Value constraints (enforced via assert()): ** SQLITE_FullFSync == PAGER_FULLFSYNC @@ -1571,13 +1579,12 @@ #define SQLITE_FullColNames 0x00000004 /* Show full column names on SELECT */ #define SQLITE_FullFSync 0x00000008 /* Use full fsync on the backend */ #define SQLITE_CkptFullFSync 0x00000010 /* Use full fsync for checkpoint */ #define SQLITE_CacheSpill 0x00000020 /* OK to spill pager cache */ #define SQLITE_ShortColNames 0x00000040 /* Show short columns names */ -#define SQLITE_CountRows 0x00000080 /* Count rows changed by INSERT, */ - /* DELETE, or UPDATE and return */ - /* the count using a callback. */ +#define SQLITE_TrustedSchema 0x00000080 /* Allow unsafe functions and + ** vtabs in the schema definition */ #define SQLITE_NullCallback 0x00000100 /* Invoke the callback once if the */ /* result set is empty */ #define SQLITE_IgnoreChecks 0x00000200 /* Do not enforce check constraints */ #define SQLITE_ReadUncommit 0x00000400 /* READ UNCOMMITTED in shared-cache */ #define SQLITE_NoCkptOnClose 0x00000800 /* No checkpoint on close()/DETACH */ @@ -1599,13 +1606,15 @@ #define SQLITE_NoSchemaError 0x08000000 /* Do not report schema parse errors*/ #define SQLITE_Defensive 0x10000000 /* Input SQL is likely hostile */ #define SQLITE_DqsDDL 0x20000000 /* dbl-quoted strings allowed in DDL*/ #define SQLITE_DqsDML 0x40000000 /* dbl-quoted strings allowed in DML*/ #define SQLITE_EnableView 0x80000000 /* Enable the use of views */ +#define SQLITE_CountRows HI(0x00001) /* Count rows changed by INSERT, */ + /* DELETE, or UPDATE and return */ + /* the count using a callback. */ /* Flags used only if debugging */ -#define HI(X) ((u64)(X)<<32) #ifdef SQLITE_DEBUG #define SQLITE_SqlTrace HI(0x0100000) /* Debug print SQL as it executes */ #define SQLITE_VdbeListing HI(0x0200000) /* Debug listings of VDBE progs */ #define SQLITE_VdbeTrace HI(0x0400000) /* True to trace VDBE execution */ #define SQLITE_VdbeAddopTrace HI(0x0800000) /* Trace sqlite3VdbeAddOp() calls */ @@ -1727,10 +1736,11 @@ ** SQLITE_FUNC_MINMAX == NC_MinMaxAgg == SF_MinMaxAgg ** SQLITE_FUNC_LENGTH == OPFLAG_LENGTHARG ** SQLITE_FUNC_TYPEOF == OPFLAG_TYPEOFARG ** SQLITE_FUNC_CONSTANT == SQLITE_DETERMINISTIC from the API ** SQLITE_FUNC_DIRECT == SQLITE_DIRECTONLY from the API +** SQLITE_FUNC_UNSAFE == SQLITE_INNOCUOUS ** SQLITE_FUNC_ENCMASK depends on SQLITE_UTF* macros in the API */ #define SQLITE_FUNC_ENCMASK 0x0003 /* SQLITE_UTF8, SQLITE_UTF16BE or UTF16LE */ #define SQLITE_FUNC_LIKE 0x0004 /* Candidate for the LIKE optimization */ #define SQLITE_FUNC_CASE 0x0008 /* Case-sensitive LIKE-type function */ @@ -1749,11 +1759,12 @@ #define SQLITE_FUNC_OFFSET 0x8000 /* Built-in sqlite_offset() function */ #define SQLITE_FUNC_WINDOW 0x00010000 /* Built-in window-only function */ #define SQLITE_FUNC_INTERNAL 0x00040000 /* For use by NestedParse() only */ #define SQLITE_FUNC_DIRECT 0x00080000 /* Not for use in TRIGGERs or VIEWs */ #define SQLITE_FUNC_SUBTYPE 0x00100000 /* Result likely to have sub-type */ -#define SQLITE_FUNC_INLINE 0x00200000 /* Functions implemented in-line */ +#define SQLITE_FUNC_UNSAFE 0x00200000 /* Function has side effects */ +#define SQLITE_FUNC_INLINE 0x00400000 /* Functions implemented in-line */ /* Identifier numbers for each in-line function */ #define INLINEFUNC_coalesce 0 #define INLINEFUNC_implies_nonnull_row 1 #define INLINEFUNC_expr_implies_expr 2 @@ -1829,11 +1840,11 @@ SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, 0, 0, #zName, {0} } #define VFUNCTION(zName, nArg, iArg, bNC, xFunc) \ {nArg, SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \ SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, 0, 0, #zName, {0} } #define SFUNCTION(zName, nArg, iArg, bNC, xFunc) \ - {nArg, SQLITE_UTF8|SQLITE_DIRECTONLY, \ + {nArg, SQLITE_UTF8|SQLITE_DIRECTONLY|SQLITE_FUNC_UNSAFE, \ SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, 0, 0, #zName, {0} } #define INLINE_FUNC(zName, nArg, iArg, mFlags) \ {nArg, SQLITE_UTF8|SQLITE_FUNC_INLINE|SQLITE_FUNC_CONSTANT|(mFlags), \ SQLITE_INT_TO_PTR(iArg), 0, noopFunc, 0, 0, 0, #zName, {0} } #define TEST_FUNC(zName, nArg, iArg, mFlags) \ @@ -2054,13 +2065,20 @@ sqlite3 *db; /* Database connection associated with this table */ Module *pMod; /* Pointer to module implementation */ sqlite3_vtab *pVtab; /* Pointer to vtab instance */ int nRef; /* Number of pointers to this structure */ u8 bConstraint; /* True if constraints are supported */ + u8 eVtabRisk; /* Riskiness of allowing hacker access */ int iSavepoint; /* Depth of the SAVEPOINT stack */ VTable *pNext; /* Next in linked list (see above) */ }; + +/* Allowed values for VTable.eVtabRisk +*/ +#define SQLITE_VTABRISK_Low 0 +#define SQLITE_VTABRISK_Normal 1 +#define SQLITE_VTABRISK_High 2 /* ** The schema for each SQL table and view is represented in memory ** by an instance of the following structure. */ @@ -2658,11 +2676,11 @@ #define EP_Subrtn 0x2000000 /* Uses Expr.y.sub. TK_IN, _SELECT, or _EXISTS */ #define EP_Quoted 0x4000000 /* TK_ID was originally quoted */ #define EP_Static 0x8000000 /* Held in memory not obtained from malloc() */ #define EP_IsTrue 0x10000000 /* Always has boolean value of TRUE */ #define EP_IsFalse 0x20000000 /* Always has boolean value of FALSE */ -#define EP_Indirect 0x40000000 /* Contained within a TRIGGER or a VIEW */ +#define EP_FromDDL 0x40000000 /* Originates from sqlite_master */ /* ** The EP_Propagate mask is a set of properties that automatically propagate ** upwards into parent nodes. */ @@ -2827,10 +2845,11 @@ unsigned isIndexedBy :1; /* True if there is an INDEXED BY clause */ unsigned isTabFunc :1; /* True if table-valued-function syntax */ unsigned isCorrelated :1; /* True if sub-query is correlated */ unsigned viaCoroutine :1; /* Implemented as a co-routine */ unsigned isRecursive :1; /* True for recursive reference in WITH */ + unsigned fromDDL :1; /* Comes from sqlite_master */ } fg; int iCursor; /* The VDBE cursor number used to access this table */ Expr *pOn; /* The ON clause of a join */ IdList *pUsing; /* The USING clause of a join */ Bitmask colUsed; /* Bit N (1<1 && strncmp(z, "-deterministic",n)==0 ){ flags |= SQLITE_DETERMINISTIC; }else if( n>1 && strncmp(z, "-directonly",n)==0 ){ flags |= SQLITE_DIRECTONLY; + }else + if( n>1 && strncmp(z, "-innocuous",n)==0 ){ + flags |= SQLITE_INNOCUOUS; }else if( n>1 && strncmp(z, "-returntype", n)==0 ){ const char *azType[] = {"integer", "real", "text", "blob", "any", 0}; assert( SQLITE_INTEGER==1 && SQLITE_FLOAT==2 && SQLITE_TEXT==3 ); assert( SQLITE_BLOB==4 && SQLITE_NULL==5 ); @@ -2868,11 +2872,11 @@ } eType++; }else{ Tcl_AppendResult(interp, "bad option \"", z, "\": must be -argcount, -deterministic, -directonly," - " or -returntype", (char*)0 + " -innocuous, or -returntype", (char*)0 ); return TCL_ERROR; } } Index: src/treeview.c ================================================================== --- src/treeview.c +++ src/treeview.c @@ -145,10 +145,13 @@ sqlite3_str_appendf(&x, " (AS %s)", pItem->zAlias); } if( pItem->fg.jointype & JT_LEFT ){ sqlite3_str_appendf(&x, " LEFT-JOIN"); } + if( pItem->fg.fromDDL ){ + sqlite3_str_appendf(&x, " DDL"); + } sqlite3StrAccumFinish(&x); sqlite3TreeViewItem(pView, zLine, inSrc-1); if( pItem->pSelect ){ sqlite3TreeViewSelect(pView, pItem->pSelect, 0); } @@ -401,18 +404,21 @@ sqlite3TreeViewLine(pView, "nil"); sqlite3TreeViewPop(pView); return; } if( pExpr->flags || pExpr->affExpr ){ + StrAccum x; + sqlite3StrAccumInit(&x, 0, zFlgs, sizeof(zFlgs), 0); + sqlite3_str_appendf(&x, " fg.af=%x.%c", + pExpr->flags, pExpr->affExpr ? pExpr->affExpr : 'n'); if( ExprHasProperty(pExpr, EP_FromJoin) ){ - sqlite3_snprintf(sizeof(zFlgs),zFlgs," fg.af=%x.%c iRJT=%d", - pExpr->flags, pExpr->affExpr ? pExpr->affExpr : 'n', - pExpr->iRightJoinTable); - }else{ - sqlite3_snprintf(sizeof(zFlgs),zFlgs," fg.af=%x.%c", - pExpr->flags, pExpr->affExpr ? pExpr->affExpr : 'n'); + sqlite3_str_appendf(&x, " iRJT=%d", pExpr->iRightJoinTable); + } + if( ExprHasProperty(pExpr, EP_FromDDL) ){ + sqlite3_str_appendf(&x, " DDL"); } + sqlite3StrAccumFinish(&x); }else{ zFlgs[0] = 0; } switch( pExpr->op ){ case TK_AGG_COLUMN: { @@ -564,11 +570,11 @@ pFarg = 0; pWin = 0; }else{ pFarg = pExpr->x.pList; #ifndef SQLITE_OMIT_WINDOWFUNC - pWin = pExpr->y.pWin; + pWin = ExprHasProperty(pExpr, EP_WinFunc) ? pExpr->y.pWin : 0; #else pWin = 0; #endif } if( pExpr->op==TK_AGG_FUNCTION ){ Index: src/vtab.c ================================================================== --- src/vtab.c +++ src/vtab.c @@ -585,10 +585,11 @@ sqlite3DbFree(db, zModuleName); return SQLITE_NOMEM_BKPT; } pVTable->db = db; pVTable->pMod = pMod; + pVTable->eVtabRisk = SQLITE_VTABRISK_Normal; iDb = sqlite3SchemaToIndex(db, pTab->pSchema); pTab->azModuleArg[1] = db->aDb[iDb].zDbSName; /* Invoke the virtual table constructor */ @@ -1274,34 +1275,44 @@ ** of the virtual table being implemented. */ int sqlite3_vtab_config(sqlite3 *db, int op, ...){ va_list ap; int rc = SQLITE_OK; + VtabCtx *p; #ifdef SQLITE_ENABLE_API_ARMOR if( !sqlite3SafetyCheckOk(db) ) return SQLITE_MISUSE_BKPT; #endif sqlite3_mutex_enter(db->mutex); - va_start(ap, op); - switch( op ){ - case SQLITE_VTAB_CONSTRAINT_SUPPORT: { - VtabCtx *p = db->pVtabCtx; - if( !p ){ - rc = SQLITE_MISUSE_BKPT; - }else{ - assert( p->pTab==0 || IsVirtual(p->pTab) ); + p = db->pVtabCtx; + if( !p ){ + rc = SQLITE_MISUSE_BKPT; + }else{ + assert( p->pTab==0 || IsVirtual(p->pTab) ); + va_start(ap, op); + switch( op ){ + case SQLITE_VTAB_CONSTRAINT_SUPPORT: { p->pVTable->bConstraint = (u8)va_arg(ap, int); - } - break; - } - default: - rc = SQLITE_MISUSE_BKPT; - break; - } - va_end(ap); + break; + } + case SQLITE_VTAB_INNOCUOUS: { + p->pVTable->eVtabRisk = SQLITE_VTABRISK_Low; + break; + } + case SQLITE_VTAB_DIRECTONLY: { + p->pVTable->eVtabRisk = SQLITE_VTABRISK_High; + break; + } + default: { + rc = SQLITE_MISUSE_BKPT; + break; + } + } + va_end(ap); + } if( rc!=SQLITE_OK ) sqlite3Error(db, rc); sqlite3_mutex_leave(db->mutex); return rc; } #endif /* SQLITE_OMIT_VIRTUALTABLE */ Index: test/fts3atoken.test ================================================================== --- test/fts3atoken.test +++ test/fts3atoken.test @@ -136,20 +136,20 @@ CREATE VIEW v110(x) AS SELECT fts3_tokenizer('tok110', fts3_tokenizer('simple')) IS NULL; } {0 {}} do_catchsql_test fts3atoken-1.11 { SELECT * FROM v110; -} {1 {fts3_tokenizer() prohibited in triggers and views}} +} {1 {unsafe use of fts3_tokenizer()}} do_catchsql_test fts3atoken-1.12 { CREATE TABLE t110(a,b); CREATE TRIGGER r110 AFTER INSERT ON t110 BEGIN SELECT fts3_tokenizer('tok110', fts3_tokenizer('simple')) IS NULL; END; } {0 {}} do_catchsql_test fts3atoken-1.13 { INSERT INTO t110(a,b) VALUES(1,2); -} {1 {fts3_tokenizer() prohibited in triggers and views}} +} {1 {unsafe use of fts3_tokenizer()}} do_catchsql_test fts3atoken-1.14 { SELECT * FROM t110; } {0 {}} #-------------------------------------------------------------------------- Index: test/func.test ================================================================== --- test/func.test +++ test/func.test @@ -1428,11 +1428,11 @@ db eval {SELECT testdirectonly(15)} } {30} do_catchsql_test func-33.2 { CREATE VIEW v33(y) AS SELECT testdirectonly(15); SELECT * FROM v33; -} {1 {testdirectonly() prohibited in triggers and views}} +} {1 {unsafe use of testdirectonly()}} do_execsql_test func-33.3 { SELECT * FROM (SELECT testdirectonly(15)) AS v33; } {30} do_execsql_test func-33.4 { WITH c(x) AS (SELECT testdirectonly(15)) @@ -1439,25 +1439,25 @@ SELECT * FROM c; } {30} do_catchsql_test func-33.5 { WITH c(x) AS (SELECT * FROM v33) SELECT * FROM c; -} {1 {testdirectonly() prohibited in triggers and views}} +} {1 {unsafe use of testdirectonly()}} do_execsql_test func-33.10 { CREATE TABLE t33a(a,b); CREATE TABLE t33b(x,y); CREATE TRIGGER r1 AFTER INSERT ON t33a BEGIN INSERT INTO t33b(x,y) VALUES(testdirectonly(new.a),new.b); END; } {} do_catchsql_test func-33.11 { INSERT INTO t33a VALUES(1,2); -} {1 {testdirectonly() prohibited in triggers and views}} +} {1 {unsafe use of testdirectonly()}} do_execsql_test func-33.20 { ALTER TABLE t33a RENAME COLUMN a TO aaa; SELECT sql FROM sqlite_master WHERE name='r1'; } {{CREATE TRIGGER r1 AFTER INSERT ON t33a BEGIN INSERT INTO t33b(x,y) VALUES(testdirectonly(new.aaa),new.b); END}} finish_test Index: test/pragma5.test ================================================================== --- test/pragma5.test +++ test/pragma5.test @@ -30,16 +30,22 @@ do_execsql_test 1.0 { PRAGMA table_info(pragma_function_list) } { 0 name {} 0 {} 0 1 builtin {} 0 {} 0 + 2 type {} 0 {} 0 + 3 enc {} 0 {} 0 + 4 narg {} 0 {} 0 + 5 flags {} 0 {} 0 } do_execsql_test 1.1 { - SELECT * FROM pragma_function_list WHERE name='upper' AND builtin + SELECT DISTINCT name, builtin + FROM pragma_function_list WHERE name='upper' AND builtin } {upper 1} do_execsql_test 1.2 { - SELECT * FROM pragma_function_list WHERE name LIKE 'exter%'; + SELECT DISTINCT name, builtin + FROM pragma_function_list WHERE name LIKE 'exter%'; } {external 0} ifcapable fts5 { do_execsql_test 2.0 { PRAGMA table_info(pragma_module_list) Index: test/tclsqlite.test ================================================================== --- test/tclsqlite.test +++ test/tclsqlite.test @@ -787,11 +787,11 @@ list [catch { db function xyz -return ret } msg] $msg } {1 {option requires an argument: -return}} do_test 17.6.3 { list [catch { db function xyz -n object ret } msg] $msg -} {1 {bad option "-n": must be -argcount, -deterministic, -directonly, or -returntype}} +} {1 {bad option "-n": must be -argcount, -deterministic, -directonly, -innocuous, or -returntype}} # 2019-02-28: The "bind_fallback" command. # do_test 18.100 { unset -nocomplain bindings abc def ghi jkl mno e01 e02 ADDED test/trustschema1.test Index: test/trustschema1.test ================================================================== --- /dev/null +++ test/trustschema1.test @@ -0,0 +1,251 @@ +# 2020-01-08 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# +# Test cases for managing execution of code snippets found in untrusted +# schemas. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix trustschema1 + +# edgy functions used in generated columns +# +proc f1 {x} {return $x} +do_test 1.100 { + db function f1 -innocuous -deterministic f1 + db function f2 -deterministic f1 + db function f3 -directonly -deterministic f1 + db eval { + CREATE TABLE t1(a, b AS (f1(a+1)), c AS (f2(a+2))); + INSERT INTO t1 VALUES(100),(200); + } +} {} +do_catchsql_test 1.110 { + SELECT a, b, c FROM t1; +} {0 {100 101 102 200 201 202}} +do_execsql_test 1.120 { + PRAGMA trusted_schema=OFF; +} {} +do_catchsql_test 1.130 { + SELECT a, b FROM t1; +} {0 {100 101 200 201}} +do_catchsql_test 1.140 { + SELECT a, b, c FROM t1; +} {1 {unsafe use of f2()}} +do_catchsql_test 1.150 { + PRAGMA trusted_schema=ON; + DROP TABLE t1; + CREATE TABLE t1(a, b AS (f3(a+1))); +} {1 {unsafe use of f3()}} +do_execsql_test 1.160 { + PRAGMA trusted_schema=OFF; + CREATE TEMP TABLE temp1(a,b AS (f3(a+1))); + INSERT INTO temp1(a) VALUES(100),(900); + SELECT * FROM temp1; +} {100 101 900 901} + +# edgy functions used in CHECK constraints +# +do_catchsql_test 1.200 { + PRAGMA trusted_schema=ON; + CREATE TABLE t2(a,b,c,CHECK(f3(c)==c)); +} {1 {unsafe use of f3()}} +do_catchsql_test 1.210 { + PRAGMA trusted_schema=Off; + CREATE TABLE t2(a,b,c,CHECK(f2(c)==c)); +} {1 {unsafe use of f2()}} +do_catchsql_test 1.211 { + PRAGMA trusted_schema=On; + CREATE TABLE t2(a,b,c,CHECK(f2(c)==c)); +} {0 {}} +do_catchsql_test 1.220 { + INSERT INTO t2 VALUES(1,2,3); + SELECT * FROM t2; +} {0 {1 2 3}} +do_catchsql_test 1.230 { + PRAGMA trusted_schema=off; + INSERT INTO t2 VALUES(4,5,6); +} {1 {unsafe use of f2()}} +do_execsql_test 1.231 { + SELECT * FROM t2; +} {1 2 3} +# Ok to put as many edgy functions as you want in a +# TEMP table. +do_execsql_test 1.240 { + PRAGMA trusted_schema=OFF; + CREATE TEMP TABLE temp2(a, b, CHECK(f3(b)==b)); + INSERT INTO temp2(a,b) VALUES(1,2),('x','y'); + SELECT * FROM temp2; +} {1 2 x y} + +# edgy functions used in DEFAULT constraints +# +do_catchsql_test 1.300 { + CREATE TABLE t3(a,b DEFAULT(f2(25))); +} {0 {}} +do_catchsql_test 1.310 { + PRAGMA trusted_schema=Off; + INSERT INTO t3(a) VALUES(1); +} {1 {unsafe use of f2()}} +do_catchsql_test 1.311 { + INSERT INTO t3(a,b) VALUES(1,2); +} {0 {}} +do_execsql_test 1.320 { + CREATE TEMP TABLE temp3(a, b DEFAULT(f3(31))); + INSERT INTO temp3(a) VALUES(22); + SELECT * FROM temp3; +} {22 31} + +# edgy functions used in partial indexes. +# +do_execsql_test 1.400 { + CREATE TABLE t4(a,b,c); + INSERT INTO t4 VALUES(1,2,3),('a','b','c'),(4,'d',0); + SELECT * FROM t4; + CREATE TEMP TABLE temp4(a,b,c); + INSERT INTO temp4 SELECT * FROM t4; +} {1 2 3 a b c 4 d 0} +do_catchsql_test 1.410 { + CREATE INDEX t4a ON t4(a) WHERE f3(c); +} {1 {unsafe use of f3()}} +do_catchsql_test 1.420 { + PRAGMA trusted_schema=OFF; + CREATE INDEX t4a ON t4(a) WHERE f2(c); +} {1 {unsafe use of f2()}} +do_execsql_test 1.421 { + CREATE INDEX t4a ON t4(a) WHERE f1(c); + SELECT a FROM t4 WHERE f1(c) ORDER BY a; +} {1} +do_execsql_test 1.430 { + PRAGMA trusted_schema=ON; + CREATE INDEX t4b ON t4(b) WHERE f2(c); + SELECT b FROM t4 WHERE f2(c) ORDER BY b; +} {2} +do_execsql_test 1.440 { + PRAGMA trusted_schema=OFF; + CREATE INDEX temp4a ON temp4(a) WHERE f3(c); + SELECT a FROM temp4 WHERE f2(c) ORDER BY a; +} {1} + +# edgy functions used in index expressions +# +do_execsql_test 1.500 { + CREATE TABLE t5(a,b,c); + INSERT INTO t5 VALUES(1,2,3),(4,5,6),(7,0,-3); + SELECT * FROM t5; + CREATE TEMP TABLE temp5(a,b,c); + INSERT INTO temp5 SELECT * FROM t5; +} {1 2 3 4 5 6 7 0 -3} +do_catchsql_test 1.510 { + CREATE INDEX t5x1 ON t5(a+f3(b)); +} {1 {unsafe use of f3()}} +do_catchsql_test 1.520 { + PRAGMA trusted_schema=OFF; + CREATE INDEX t5x1 ON t5(a+f2(b)); +} {1 {unsafe use of f2()}} +do_execsql_test 1.521 { + CREATE INDEX t5x1 ON t5(a+f1(b)); + SELECT * FROM t5 INDEXED BY t5x1 WHERE a+f1(b)=3; +} {1 2 3} +do_execsql_test 1.530 { + PRAGMA trusted_schema=ON; + CREATE INDEX t5x2 ON t5(b+f2(c)); + SELECT * FROM t5 INDEXED BY t5x2 WHERE b+f2(c)=11; +} {4 5 6} +do_execsql_test 1.540 { + PRAGMA trusted_schema=OFF; + CREATE INDEX temp5x1 ON temp5(a+f3(b)); + SELECT * FROM temp5 INDEXED BY temp5x1 WHERE a+f3(b)=7; +} {7 0 -3} + +# edgy functions in VIEWs +# +reset_db +db function f1 -innocuous -deterministic f1 +db function f2 -deterministic f1 +db function f3 -directonly -deterministic f1 +do_execsql_test 2.100 { + CREATE TABLE t1(a,b,c); + INSERT INTO t1 VALUES(1,2,3),(100,50,75),(-11,22,-33); + CREATE VIEW v1a AS SELECT f3(a+b) FROM t1; + SELECT f3(a+b) FROM t1; +} {3 150 11} +do_catchsql_test 2.110 { + PRAGMA trusted_schema=ON; + SELECT * FROM v1a; +} {1 {unsafe use of f3()}} +do_catchsql_test 2.111 { + PRAGMA trusted_schema=OFF; + SELECT * FROM v1a; +} {1 {unsafe use of f3()}} +do_execsql_test 2.120 { + DROP VIEW v1a; + CREATE TEMP VIEW v1a AS SELECT f3(a+b) FROM t1; + SELECT * FROM v1a; +} {3 150 11} +do_execsql_test 2.130 { + CREATE VIEW v1b AS SELECT f2(b+c) FROM t1; + SELECT f2(b+c) FROM t1; +} {5 125 -11} +do_catchsql_test 2.140 { + PRAGMA trusted_schema=ON; + SELECT * FROM v1b; +} {0 {5 125 -11}} +do_catchsql_test 2.141 { + PRAGMA trusted_schema=OFF; + SELECT * FROM v1b; +} {1 {unsafe use of f2()}} +do_execsql_test 2.150 { + DROP VIEW v1b; + CREATE TEMP VIEW v1b AS SELECT f2(b+c) FROM t1; + SELECT * FROM v1b; +} {5 125 -11} + +# edgy functions inside of triggers +# +do_execsql_test 3.100 { + DELETE FROM t1; + CREATE TABLE t2(x); + CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN + INSERT INTO t2(x) SELECT f3(new.a); + END; +} {} +do_catchsql_test 3.110 { + INSERT INTO t1 VALUES(7,6,5); +} {1 {unsafe use of f3()}} +do_execsql_test 3.111 { + SELECT * FROM t1; + SELECT * FROM t2; +} {} + +do_execsql_test 3.120 { + DROP TRIGGER r1; + CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN + INSERT INTO t2(x) SELECT f2(new.a)+100; + END; + PRAGMA trusted_schema=ON; + INSERT INTO t1 VALUES(7,6,5); + SELECT * FROM t1, t2; +} {7 6 5 107} +do_catchsql_test 3.130 { + DELETE FROM t1; + DELETE FROM t2; + PRAGMA trusted_schema=OFF; + INSERT INTO t1 VALUES(7,6,5); +} {1 {unsafe use of f2()}} +do_execsql_test 3.131 { + SELECT * FROM t1; + SELECT * FROM t2; +} {} + + +finish_test Index: tool/mkpragmatab.tcl ================================================================== --- tool/mkpragmatab.tcl +++ tool/mkpragmatab.tcl @@ -126,10 +126,15 @@ NAME: recursive_triggers TYPE: FLAG ARG: SQLITE_RecTriggers IF: !defined(SQLITE_OMIT_FLAG_PRAGMAS) + NAME: trusted_schema + TYPE: FLAG + ARG: SQLITE_TrustedSchema + IF: !defined(SQLITE_OMIT_FLAG_PRAGMAS) + NAME: foreign_keys TYPE: FLAG ARG: SQLITE_ForeignKeys IF: !defined(SQLITE_OMIT_FLAG_PRAGMAS) IF: !defined(SQLITE_OMIT_FOREIGN_KEY) && !defined(SQLITE_OMIT_TRIGGER) @@ -255,11 +260,11 @@ COLS: seq name file IF: !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) NAME: function_list FLAG: Result0 - COLS: name builtin + COLS: name builtin type enc narg flags IF: !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) IF: !defined(SQLITE_OMIT_INTROSPECTION_PRAGMAS) NAME: module_list FLAG: Result0