Documentation Source Text

Check-in [66d5ec93a6]
Login

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

Overview
Comment:Finish a first draft of the application-defined function document.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 66d5ec93a681db159ad94301827d473267db4cc87b61730566dfc4974703e82e
User & Date: drh 2020-01-14 01:57:58
Context
2020-01-15
02:44
Update the speed-and-size chart. check-in: 09b9c916d4 user: drh tags: trunk
2020-01-14
01:57
Finish a first draft of the application-defined function document. check-in: 66d5ec93a6 user: drh tags: trunk
2020-01-13
20:33
Continue working to improve the documentation. check-in: 264d9eef33 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/appfunc.in.

243
244
245
246
247
248
249

250






251


































































252
253
254






























































































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].












































































<h1>Security Implications</h1>

<p><i>TBD....</i>





































































































>

>
>
>
>
>
>

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


<
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326

327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
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>

Changes to pages/changes.in.

27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
    [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 [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.







|







27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
    [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.

Changes to pages/windowfunctions.in.

1
2
3
4
5
6
7
8
<tcl>hd_keywords {window functions} {window function}</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
|







1
2
3
4
5
6
7
8
<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