Documentation Source Text

the number of arguments to the SQL function itself and the values for
each argument of the SQL function.
Argument values can be of any datatype and are thus stored in
instances of the [sqlite3_value] object.
Specific C-language values can be extracted from this object using
the [sqlite3_value_int|sqlite3_value() family of interfaces].

<h3>The Aggregate Function Callbacks</h3>

<p>Aggregate SQL functions are implemented by using two callback
functions, <b>xStep</b> and <b>xFinal</b>.  The xStep() function 
is called for each row of the aggregate and the xFinal() function
is invoked to compute the final answer at the end.
The following (slightly simplified) version of the built-in
count() function illustrates:

<codeblock>
typedef struct CountCtx CountCtx;
struct CountCtx {
  i64 n;
};
static void countStep(sqlite3_context *context, int argc, sqlite3_value **argv){
  CountCtx *p;
  p = sqlite3_aggregate_context(context, sizeof(*p));
  if( (argc==0 || SQLITE_NULL!=sqlite3_value_type(argv[0])) && p ){
    p->n++;
  }
}   
static void countFinalize(sqlite3_context *context){
  CountCtx *p;
  p = sqlite3_aggregate_context(context, 0);
  sqlite3_result_int64(context, p ? p->n : 0);
}
</codeblock>

<p>Recall that there are two versions of the count() aggregate.
With zero arguments, count() returns a count of the number of rows.
With one argument, count() returns the number of times that the
argument was non-NULL.

<p>The countStep() callback is invoked once for each row in the aggregate.
As you can see, the count is incremented if either there are no arguments,
or if the one argument is not NULL.

<p>The step function for an aggregate should always begin with a call
to the [sqlite3_aggregate_context()] routine to fetch the persistent
state of the aggregate function.  On the first invocation of the step()
function, the aggregate context is initialized to a block of memory
that is N bytes in size, where N is the second parameter to
sqlite3_aggregate_context() and that memory is zeroed.  On all subsequent
calls to the step() function, the same block of memory is returned.
Except, sqlite3_aggregate_context() might return NULL in the case of
an out-of-memory error, so aggregate functions should be prepared to
deal with that case.

<p>After all rows are processed the countFinalize() routine is called
exactly once.  This routine computes the final result and invokes
one of the [sqlite3_result_blob|sqlite3_result()] family of functions
to set the final result.  The aggregate context will be freed automatically
by SQLite, though the xFinalize() routine must clean up any substructure
associated with the aggregate context before it returns.  If the xStep()
method is called one or more times, then SQLite guarantees thta the
xFinal() method will be called at once, even if the query aborts.

<h3>The Window Function Callbacks</h3>

<p>[Window functions] use the same xStep() and xFinal() callbacks that
aggregate functions use, plus two others: <b>xValue</b> and <b>xInverse</b>.
See the documentation on
[application-defined window functions] for further details.

<h3>Examples</h3>

<p>There are dozens and dozens of SQL function implementations scattered
throughout the SQLite source code that can be used as example applications.
The built-in SQL functions use the same interface as application-defined
SQL functions, so built-in functions can be used as examples too.
Search for "sqlite3_context" in the SQLite source code to find examples.

<tcl>
hd_fragment sec {application-defined function attacks}
</tcl>
<h1>Security Implications</h1>

<p>
Application-defined SQL functions can become security vulnerabilities if
not carefully managed.  Suppose, for example, an application defines
a new "system(X)" SQL function that runs its argument X as a command and
returns the integer result code.  Perhaps the implementation is like this:

<codeblock>
static void systemFunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  const char *zCmd = (const char*)sqlite3_value_text(argv[0]);
  if( zCmd!=0 ){
    int rc = system(zCmd);
    sqlite3_result_int(context, rc);
  }
}
</codeblock>

<p>
This is a function with powerful side-effects.  Most programmers would
be naturally cautious about using it, but probably would not see the
harm in merely having it available.  But there is great risk in merely
defining such a function, even if the application itself never invokes
it!

<p>
Suppose the application normally does a query against table TAB1
when it starts up.  If an attacker can gain access to the database
file and modify the schema like this:

<codeblock>
ALTER TABLE tab1 RENAME TO tab1_real;
CREATE VIEW tab1 AS SELECT * FROM tab1 WHERE system('rm -rf *') IS NOT NULL;
</codeblock>

<p>
Then, when the application attempts to open the database, register the
system() function, then run an innocent query against the "tab1" table,
it instead deletes all the files in its working directory.  Yikes!

<p>
To prevent this kind of mischief, applications that create their own
custom SQL functions should take one or more of the following safety
precautions.  The more precautions taken the better:

<ol>
<li><p>
Invoke [sqlite3_db_config](db,[SQLITE_DBCONFIG_TRUSTED_SCHEMA],0,0)
on each [database connection] as soon as it is opened.
This prevents application-defined functions from being used in places
where an attacker might be able to surreptiously invoke them by modifying
a database schema:
<ul>
<li> In in VIEWs.
<li> In in TRIGGERSs.
<li> In CHECK constraints of a table definition.
<li> In DEFAULT constraints of a table definition.
<li> In the definitions of generated columns.
<li> In the expression part of an index on an expression.
<li> In the WHERE clause of a partial index.
</ul>
<p>
To put it another way, this setting requires that application-defined
functions only be run directly by top-level SQL invoked from the application
itself, not as a consequence of doing some other innocent-looking query.

<li><p>
Use the [PRAGMA trusted_schema=OFF] SQL statement to disable trusted
schema.  This has the same effect as the previous bullet, but does not
require the use of C-code and hence can be performed in programs written
in another programming language and that do not have access SQLite
C-language APIs.

<li><p>
Compile SQLite using the [-DSQLITE_TRUSTED_SCHEMA=0] compile-time option.
This make SQLite distrust application-defined functions inside of
the schema by default.

<li><p>
If any application-defined SQL functions have potentially dangerous
side-effects, or if they could potentially leak sensitive information
to an attacker if misused, then tag those functions using the
[SQLITE_DIRECTONLY] option on the "enc" parameter.  This means
that the function can never be run from schema-code even if the
trusted-schema option is on.

<li><p>
Never tag an application-defined SQL function with [SQLITE_INNOCUOUS]
unless you really need to and you have checked the implementation closely
and are certain that it can do no harm even if it falls under the
control of an attacker.
</ol>

    [PRAGMA hard_heap_limit] command.
<li>Enhance the [function_list pragma] to show the number of arguments on each
    function, the type of function (scalar, aggregate, window), and the function
    property flags [SQLITE_DETERMINISTIC], [SQLITE_DIRECTONLY],
    [SQLITE_INNOCUOUS], and/or [SQLITE_SUBTYPE].
<li>Add the [DBSTAT aggregated mode|aggregated mode] feature to the
    [DBSTAT virtual table].
<li>Add the [OPEN_NOFOLLOW|SQLITE_OPEN_NOFOLLOW] option to [sqlite3_open_v2()] that
    prevents SQLite from opening symbolic links.
<li>Added the "#-N" array notation for [JSON function path arguments].
<li>Added the [SQLITE_DBCONFIG_TRUSTED_SCHEMA] connection setting which is
    also controllable via the new [trusted_schema pragma] and at compile-time
    using the [-DSQLITE_TRUSTED_SCHEMA] compile-time option.
<li>Added APIs [sqlite3_filename_database()], [sqlite3_filename_journal()], and
    [sqlite3_filename_wal()] which are useful for specialized extensions.

<tcl>hd_keywords {window functions} {window function} {Window functions}</tcl>

<title>Window Functions</title>
<table_of_contents>

<h1>Introduction to Window Functions</h1>

<p>A window function is an SQL function where the input