/ Check-in [fc745845]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Provide the SQLITE_DIRECTONLY flag for app-defined functions that prohibits the use of those functions within triggers or views.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | directonly
Files: files | file ages | folders
SHA3-256: fc745845d8d76adc165575e2192f4176e3c28e614c72571d56f4011560499fe1
User & Date: drh 2019-08-15 20:04:09
Context
2019-08-17
00:53
The SQLITE_DIRECTONLY flag, when added to sqlite3_create_function() prevents the function from being used inside a trigger or view. check-in: de767376 user: drh tags: trunk
2019-08-15
20:04
Provide the SQLITE_DIRECTONLY flag for app-defined functions that prohibits the use of those functions within triggers or views. Closed-Leaf check-in: fc745845 user: drh tags: directonly
14:35
Ensure that the optional "sz=N" parameter that can be manually added to the end of an sqlite_stat1 entry does not have an N value that is too small. Ticket [e4598ecbdd18bd82] check-in: 98357d8c user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to src/attach.c.

   556    556     return 0;
   557    557   }
   558    558   int sqlite3FixExpr(
   559    559     DbFixer *pFix,     /* Context of the fixation */
   560    560     Expr *pExpr        /* The expression to be fixed to one database */
   561    561   ){
   562    562     while( pExpr ){
          563  +    ExprSetProperty(pExpr, EP_Indirect);
   563    564       if( pExpr->op==TK_VARIABLE ){
   564    565         if( pFix->pParse->db->init.busy ){
   565    566           pExpr->op = TK_NULL;
   566    567         }else{
   567    568           sqlite3ErrorMsg(pFix->pParse, "%s cannot use variables", pFix->zType);
   568    569           return 1;
   569    570         }

Changes to src/main.c.

  1716   1716      || (nArg<-1 || nArg>SQLITE_MAX_FUNCTION_ARG)
  1717   1717      || (255<(nName = sqlite3Strlen30( zFunctionName)))
  1718   1718     ){
  1719   1719       return SQLITE_MISUSE_BKPT;
  1720   1720     }
  1721   1721   
  1722   1722     assert( SQLITE_FUNC_CONSTANT==SQLITE_DETERMINISTIC );
  1723         -  extraFlags = enc &  SQLITE_DETERMINISTIC;
         1723  +  assert( SQLITE_FUNC_DIRECT==SQLITE_DIRECTONLY );
         1724  +  extraFlags = enc &  (SQLITE_DETERMINISTIC|SQLITE_DIRECTONLY);
  1724   1725     enc &= (SQLITE_FUNC_ENCMASK|SQLITE_ANY);
  1725   1726     
  1726   1727   #ifndef SQLITE_OMIT_UTF16
  1727   1728     /* If SQLITE_UTF16 is specified as the encoding type, transform this
  1728   1729     ** to one of SQLITE_UTF16LE or SQLITE_UTF16BE using the
  1729   1730     ** SQLITE_UTF16NATIVE macro. SQLITE_UTF16 is not used internally.
  1730   1731     **
................................................................................
  1779   1780   
  1780   1781     if( pDestructor ){
  1781   1782       pDestructor->nRef++;
  1782   1783     }
  1783   1784     p->u.pDestructor = pDestructor;
  1784   1785     p->funcFlags = (p->funcFlags & SQLITE_FUNC_ENCMASK) | extraFlags;
  1785   1786     testcase( p->funcFlags & SQLITE_DETERMINISTIC );
         1787  +  testcase( p->funcFlags & SQLITE_DIRECTONLY );
  1786   1788     p->xSFunc = xSFunc ? xSFunc : xStep;
  1787   1789     p->xFinalize = xFinal;
  1788   1790     p->xValue = xValue;
  1789   1791     p->xInverse = xInverse;
  1790   1792     p->pUserData = pUserData;
  1791   1793     p->nArg = (u16)nArg;
  1792   1794     return SQLITE_OK;

Changes to src/resolve.c.

   819    819            && pParse->nested==0
   820    820            && sqlite3Config.bInternalFunctions==0
   821    821           ){
   822    822             /* Internal-use-only functions are disallowed unless the
   823    823             ** SQL is being compiled using sqlite3NestedParse() */
   824    824             no_such_func = 1;
   825    825             pDef = 0;
          826  +        }else
          827  +        if( (pDef->funcFlags & SQLITE_FUNC_DIRECT)!=0
          828  +         && ExprHasProperty(pExpr, EP_Indirect)
          829  +         && !IN_RENAME_OBJECT
          830  +        ){
          831  +          /* Functions tagged with SQLITE_DIRECTONLY may not be used
          832  +          ** inside of triggers and views */
          833  +          sqlite3ErrorMsg(pParse, "%s() prohibited in triggers and views",
          834  +                          pDef->zName);
   826    835           }
   827    836         }
   828    837   
   829    838         if( 0==IN_RENAME_OBJECT ){
   830    839   #ifndef SQLITE_OMIT_WINDOWFUNC
   831    840           assert( is_agg==0 || (pDef->funcFlags & SQLITE_FUNC_MINMAX)
   832    841             || (pDef->xValue==0 && pDef->xInverse==0)

Changes to src/sqlite.h.in.

  4844   4844   ** ^The fourth parameter may optionally be ORed with [SQLITE_DETERMINISTIC]
  4845   4845   ** to signal that the function will always return the same result given
  4846   4846   ** the same inputs within a single SQL statement.  Most SQL functions are
  4847   4847   ** deterministic.  The built-in [random()] SQL function is an example of a
  4848   4848   ** function that is not deterministic.  The SQLite query planner is able to
  4849   4849   ** perform additional optimizations on deterministic functions, so use
  4850   4850   ** of the [SQLITE_DETERMINISTIC] flag is recommended where possible.
         4851  +** ^The fourth parameter may also optionally include the [SQLITE_DIRECTONLY]
         4852  +** flag, which if present prevents the function from being invoked from
         4853  +** within VIEWs or TRIGGERs.
  4851   4854   **
  4852   4855   ** ^(The fifth parameter is an arbitrary pointer.  The implementation of the
  4853   4856   ** function can gain access to this pointer using [sqlite3_user_data()].)^
  4854   4857   **
  4855   4858   ** ^The sixth, seventh and eighth parameters passed to the three
  4856   4859   ** "sqlite3_create_function*" functions, xFunc, xStep and xFinal, are
  4857   4860   ** pointers to C-language functions that implement the SQL function or
................................................................................
  4961   4964   /*
  4962   4965   ** CAPI3REF: Function Flags
  4963   4966   **
  4964   4967   ** These constants may be ORed together with the 
  4965   4968   ** [SQLITE_UTF8 | preferred text encoding] as the fourth argument
  4966   4969   ** to [sqlite3_create_function()], [sqlite3_create_function16()], or
  4967   4970   ** [sqlite3_create_function_v2()].
         4971  +**
         4972  +** The SQLITE_DETERMINISTIC flag means that the new function will always
         4973  +** maps the same inputs into the same output.  The abs() function is
         4974  +** deterministic, for example, but randomblob() is not.
         4975  +**
         4976  +** The SQLITE_DIRECTONLY flag means that the function may only be invoked
         4977  +** from top-level SQL, and cannot be used in VIEWs or TRIGGERs.
  4968   4978   */
  4969         -#define SQLITE_DETERMINISTIC    0x800
         4979  +#define SQLITE_DETERMINISTIC    0x000000800
         4980  +#define SQLITE_DIRECTONLY       0x000080000
  4970   4981   
  4971   4982   /*
  4972   4983   ** CAPI3REF: Deprecated Functions
  4973   4984   ** DEPRECATED
  4974   4985   **
  4975   4986   ** These functions are [deprecated].  In order to maintain
  4976   4987   ** backwards compatibility with older code, these functions continue 

Changes to src/sqliteInt.h.

  1659   1659   ** are assert() statements in the code to verify this.
  1660   1660   **
  1661   1661   ** Value constraints (enforced via assert()):
  1662   1662   **     SQLITE_FUNC_MINMAX    ==  NC_MinMaxAgg      == SF_MinMaxAgg
  1663   1663   **     SQLITE_FUNC_LENGTH    ==  OPFLAG_LENGTHARG
  1664   1664   **     SQLITE_FUNC_TYPEOF    ==  OPFLAG_TYPEOFARG
  1665   1665   **     SQLITE_FUNC_CONSTANT  ==  SQLITE_DETERMINISTIC from the API
         1666  +**     SQLITE_FUNC_DIRECT    ==  SQLITE_DIRECTONLY from the API
  1666   1667   **     SQLITE_FUNC_ENCMASK   depends on SQLITE_UTF* macros in the API
  1667   1668   */
  1668   1669   #define SQLITE_FUNC_ENCMASK  0x0003 /* SQLITE_UTF8, SQLITE_UTF16BE or UTF16LE */
  1669   1670   #define SQLITE_FUNC_LIKE     0x0004 /* Candidate for the LIKE optimization */
  1670   1671   #define SQLITE_FUNC_CASE     0x0008 /* Case-sensitive LIKE-type function */
  1671   1672   #define SQLITE_FUNC_EPHEM    0x0010 /* Ephemeral.  Delete with VDBE */
  1672   1673   #define SQLITE_FUNC_NEEDCOLL 0x0020 /* sqlite3GetFuncCollSeq() might be called*/
................................................................................
  1679   1680   #define SQLITE_FUNC_MINMAX   0x1000 /* True for min() and max() aggregates */
  1680   1681   #define SQLITE_FUNC_SLOCHNG  0x2000 /* "Slow Change". Value constant during a
  1681   1682                                       ** single query - might change over time */
  1682   1683   #define SQLITE_FUNC_AFFINITY 0x4000 /* Built-in affinity() function */
  1683   1684   #define SQLITE_FUNC_OFFSET   0x8000 /* Built-in sqlite_offset() function */
  1684   1685   #define SQLITE_FUNC_WINDOW   0x00010000 /* Built-in window-only function */
  1685   1686   #define SQLITE_FUNC_INTERNAL 0x00040000 /* For use by NestedParse() only */
         1687  +#define SQLITE_FUNC_DIRECT   0x00080000 /* Not for use in TRIGGERs or VIEWs */
  1686   1688   
  1687   1689   /*
  1688   1690   ** The following three macros, FUNCTION(), LIKEFUNC() and AGGREGATE() are
  1689   1691   ** used to create the initializers for the FuncDef structures.
  1690   1692   **
  1691   1693   **   FUNCTION(zName, nArg, iArg, bNC, xFunc)
  1692   1694   **     Used to create a scalar function definition of a function zName
................................................................................
  2520   2522   #define EP_Leaf      0x800000 /* Expr.pLeft, .pRight, .u.pSelect all NULL */
  2521   2523   #define EP_WinFunc  0x1000000 /* TK_FUNCTION with Expr.y.pWin set */
  2522   2524   #define EP_Subrtn   0x2000000 /* Uses Expr.y.sub. TK_IN, _SELECT, or _EXISTS */
  2523   2525   #define EP_Quoted   0x4000000 /* TK_ID was originally quoted */
  2524   2526   #define EP_Static   0x8000000 /* Held in memory not obtained from malloc() */
  2525   2527   #define EP_IsTrue  0x10000000 /* Always has boolean value of TRUE */
  2526   2528   #define EP_IsFalse 0x20000000 /* Always has boolean value of FALSE */
         2529  +#define EP_Indirect 0x40000000 /* Contained within a TRIGGER or a VIEW */
  2527   2530   
  2528   2531   /*
  2529   2532   ** The EP_Propagate mask is a set of properties that automatically propagate
  2530   2533   ** upwards into parent nodes.
  2531   2534   */
  2532   2535   #define EP_Propagate (EP_Collate|EP_Subquery|EP_HasFunc)
  2533   2536   

Changes to src/tclsqlite.c.

  2737   2737         cd2[1] = (void *)pScript;
  2738   2738         rc = DbEvalNextCmd(cd2, interp, TCL_OK);
  2739   2739       }
  2740   2740       break;
  2741   2741     }
  2742   2742   
  2743   2743     /*
  2744         -  **     $db function NAME [-argcount N] [-deterministic] SCRIPT
         2744  +  **     $db function NAME [OPTIONS] SCRIPT
  2745   2745     **
  2746   2746     ** Create a new SQL function called NAME.  Whenever that function is
  2747   2747     ** called, invoke SCRIPT to evaluate the function.
         2748  +  **
         2749  +  ** Options:
         2750  +  **         --argcount N           Function has exactly N arguments
         2751  +  **         --deterministic        The function is pure
         2752  +  **         --directonly           Prohibit use inside triggers and views
         2753  +  **         --returntype TYPE      Specify the return type of the function
  2748   2754     */
  2749   2755     case DB_FUNCTION: {
  2750   2756       int flags = SQLITE_UTF8;
  2751   2757       SqlFunc *pFunc;
  2752   2758       Tcl_Obj *pScript;
  2753   2759       char *zName;
  2754   2760       int nArg = -1;
................................................................................
  2772   2778                              (char*)0);
  2773   2779             return TCL_ERROR;
  2774   2780           }
  2775   2781           i++;
  2776   2782         }else
  2777   2783         if( n>1 && strncmp(z, "-deterministic",n)==0 ){
  2778   2784           flags |= SQLITE_DETERMINISTIC;
         2785  +      }else
         2786  +      if( n>1 && strncmp(z, "-directonly",n)==0 ){
         2787  +        flags |= SQLITE_DIRECTONLY;
  2779   2788         }else
  2780   2789         if( n>1 && strncmp(z, "-returntype", n)==0 ){
  2781   2790           const char *azType[] = {"integer", "real", "text", "blob", "any", 0};
  2782   2791           assert( SQLITE_INTEGER==1 && SQLITE_FLOAT==2 && SQLITE_TEXT==3 );
  2783   2792           assert( SQLITE_BLOB==4 && SQLITE_NULL==5 );
  2784   2793           if( i==(objc-2) ){
  2785   2794             Tcl_AppendResult(interp, "option requires an argument: ", z,(char*)0);
................................................................................
  2788   2797           i++;
  2789   2798           if( Tcl_GetIndexFromObj(interp, objv[i], azType, "type", 0, &eType) ){
  2790   2799             return TCL_ERROR;
  2791   2800           }
  2792   2801           eType++;
  2793   2802         }else{
  2794   2803           Tcl_AppendResult(interp, "bad option \"", z,
  2795         -            "\": must be -argcount, -deterministic or -returntype", (char*)0
         2804  +            "\": must be -argcount, -deterministic, -directonly,"
         2805  +            " or -returntype", (char*)0
  2796   2806           );
  2797   2807           return TCL_ERROR;
  2798   2808         }
  2799   2809       }
  2800   2810   
  2801   2811       pScript = objv[objc-1];
  2802   2812       zName = Tcl_GetStringFromObj(objv[2], 0);

Changes to src/treeview.c.

   553    553   #ifndef SQLITE_OMIT_WINDOWFUNC
   554    554           pWin = pExpr->y.pWin;
   555    555   #else
   556    556           pWin = 0;
   557    557   #endif 
   558    558         }
   559    559         if( pExpr->op==TK_AGG_FUNCTION ){
   560         -        sqlite3TreeViewLine(pView, "AGG_FUNCTION%d %Q",
   561         -                             pExpr->op2, pExpr->u.zToken);
          560  +        sqlite3TreeViewLine(pView, "AGG_FUNCTION%d %Q%s",
          561  +                             pExpr->op2, pExpr->u.zToken, zFlgs);
   562    562         }else{
   563         -        sqlite3TreeViewLine(pView, "FUNCTION %Q", pExpr->u.zToken);
          563  +        sqlite3TreeViewLine(pView, "FUNCTION %Q%s", pExpr->u.zToken, zFlgs);
   564    564         }
   565    565         if( pFarg ){
   566    566           sqlite3TreeViewExprList(pView, pFarg, pWin!=0, 0);
   567    567         }
   568    568   #ifndef SQLITE_OMIT_WINDOWFUNC
   569    569         if( pWin ){
   570    570           sqlite3TreeViewWindow(pView, pWin, 0);

Changes to test/func.test.

  1415   1415   do_execsql_test func-32.140 {
  1416   1416     SELECT test_frombind(a,b,c,e,f,$xyz+f) FROM t1;
  1417   1417   } {0}
  1418   1418   do_execsql_test func-32.150 {
  1419   1419     SELECT test_frombind(x.a,y.b,x.c,:123,y.e,x.f,$xyz+y.f) FROM t1 x, t1 y;
  1420   1420   } {8}
  1421   1421   
  1422         -
         1422  +# 2019-08-15
         1423  +# Direct-only functions.
         1424  +#
         1425  +proc testdirectonly {x} {return [expr {$x*2}]}
         1426  +do_test func-33.1 {
         1427  +  db func testdirectonly -directonly testdirectonly
         1428  +  db eval {SELECT testdirectonly(15)}
         1429  +} {30}
         1430  +do_catchsql_test func-33.2 {
         1431  +  CREATE VIEW v33(y) AS SELECT testdirectonly(15);
         1432  +  SELECT * FROM v33;
         1433  +} {1 {testdirectonly() prohibited in triggers and views}}
         1434  +do_execsql_test func-33.3 {
         1435  +  SELECT * FROM (SELECT testdirectonly(15)) AS v33;
         1436  +} {30}
         1437  +do_execsql_test func-33.4 {
         1438  +  WITH c(x) AS (SELECT testdirectonly(15))
         1439  +  SELECT * FROM c;
         1440  +} {30}
         1441  +do_catchsql_test func-33.5 {
         1442  +  WITH c(x) AS (SELECT * FROM v33)
         1443  +  SELECT * FROM c;
         1444  +} {1 {testdirectonly() prohibited in triggers and views}}
         1445  +do_execsql_test func-33.10 {
         1446  +  CREATE TABLE t33a(a,b);
         1447  +  CREATE TABLE t33b(x,y);
         1448  +  CREATE TRIGGER r1 AFTER INSERT ON t33a BEGIN
         1449  +    INSERT INTO t33b(x,y) VALUES(testdirectonly(new.a),new.b);
         1450  +  END;
         1451  +} {}
         1452  +do_catchsql_test func-33.11 {
         1453  +  INSERT INTO t33a VALUES(1,2);
         1454  +} {1 {testdirectonly() prohibited in triggers and views}}
         1455  +do_execsql_test func-33.20 {
         1456  +  ALTER TABLE t33a RENAME COLUMN a TO aaa;
         1457  +  SELECT sql FROM sqlite_master WHERE name='r1';
         1458  +} {{CREATE TRIGGER r1 AFTER INSERT ON t33a BEGIN
         1459  +    INSERT INTO t33b(x,y) VALUES(testdirectonly(new.aaa),new.b);
         1460  +  END}}
  1423   1461   
  1424   1462   
  1425   1463   finish_test

Changes to test/tclsqlite.test.

   785    785   
   786    786   do_test 17.6.2 {
   787    787     list [catch { db function xyz -return ret } msg] $msg
   788    788   } {1 {option requires an argument: -return}}
   789    789   
   790    790   do_test 17.6.3 {
   791    791     list [catch { db function xyz -n object ret } msg] $msg
   792         -} {1 {bad option "-n": must be -argcount, -deterministic or -returntype}}
          792  +} {1 {bad option "-n": must be -argcount, -deterministic, -directonly, or -returntype}}
   793    793   
   794    794   # 2019-02-28: The "bind_fallback" command.
   795    795   #
   796    796   do_test 18.100 {
   797    797     unset -nocomplain bindings abc def ghi jkl mno e01 e02
   798    798     set bindings(abc) [expr {1+2}]
   799    799     set bindings(def) {hello}