Documentation Source Text

<tcl>hd_keywords {application-defined SQL functions} {custom SQL functions}\
     {application-defined SQL function} {custom SQL function}</tcl>

<title>Application-Defined SQL Functions</title>
<table_of_contents>

<h1>Executive Summary</h1>

<p>Applications that use SQLite can define custom SQL functions that call
back into application code to compute their results.  The custom SQL
function implementations can be embedded in the application code itself,
or can be [loadable extensions].

<p>Application-defined or custom SQL functions are created using the
[sqlite3_create_function()] family of interfaces.


<h1>Defining New SQL Functions</h1>

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

<h1>Callbacks</h1>

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

<h1>Security Implications</h1>

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

<h2>Untrusted SQLite Database Files</h2>

<p>Applications that accept untrusted database files should do the following:

<ol>
<li value="7"><p>
If an application includes any [custom SQL functions] or 
[custom virtual tables] that have side effects or that might leak
privileged information, then the application should use one or more
of the techniques below to prevent a maliciously crafted database
schema from surreptiously running those SQL functions and/or
virtual tables:
<ol type="a">
<li> Invoke [sqlite3_db_config](db,[SQLITE_DBCONFIG_TRUSTED_SCHEMA],0,0)
     on each [database connection] as soon as it is opened.
<li> Run the [PRAGMA trusted_schema=OFF] statement on each database connection
     as soon as it is opened.
<li> Compile SQLite using the [-DSQLITE_TRUSTED_SCHEMA=0] compile-time option.
</ol>
<p>Any one of the above actions is sufficient to protect the application
from malicious constructs added into the database schema.  The only reason
to do two or more of the above is redundancy.
<p>To facilitate testing, applications should also consider adding an
easter-egg or other testing interface that displays the value of
[PRAGMA trusted_schema] just to confirm that it really is turned off.
</li>

<li><p>
If the application does not use triggers or views should consider disabling the
unused capabilities with:
<blockquote><pre>
[sqlite3_db_config](db,[SQLITE_DBCONFIG_ENABLE_TRIGGER],0,0);
[sqlite3_db_config](db,[SQLITE_DBCONFIG_ENABLE_VIEW],0,0);
</pre></blockquote>
</p>

<li><p>
[application-defined SQL functions|Custom SQL functions] that
have side-effects or that might be used by an attacker to leak
information about the system should tags those SQL functions with
[SQLITE_DIRECTONLY].  This will prohibit the use of those functions
inside triggers and views or other schema constructs, and thus
prevent an attacker from surreptiously invoking the function by
including it in part of the schema of a database file.

<li><p>
[custom virtual tables|Custom virtual tables] that
have side-effects or that might be used by an attacker to leak
information about the system should tags those virtual tables with
[SQLITE_VTAB_DIRECTONLY].  This will prohibit the use of those
virtual tables inside non-TEMP triggers and views, and thus
prevent an attacker from surreptiously querying the virtual table
using triggers and views in the database schema.

<li><p>
Run [PRAGMA integrity_check] or [PRAGMA quick_check] on the database
as the first SQL statement after opening the database files and
prior to running any other SQL statements.  Reject and refuse to
process any database file containing errors.

<li><p>
Enable the [PRAGMA cell_size_check=ON] setting.

<li><p>
Do not enable memory-mapped I/O.
In other words, make sure that [PRAGMA mmap_size=0].




































</ol>

<p>Even if the application does not deliberately accept database files
from untrusted sources, beware of attacks in which a local database file
is surreptitiously altered to contain harmful content.

<h1>Summary</h1>

that each [database connection] kept
its own copy of the database schema. Hence, the virtual table mechanism
could not be used in a database that has [shared cache mode] enabled. 
The [sqlite3_create_module()] interface would return an error if 
[shared cache mode] is enabled.  That restriction was relaxed
beginning with SQLite [version 3.6.17].

<tcl>hd_fragment customvtab {custom virtual tables}</tcl>
<h2>Creating New Virtual Table Implementations</h2>

<p>Follow these steps to create your own virtual table:

<p>
<ol>
<li> Write all necessary methods.
<li> Create an instance of the [sqlite3_module] structure containing pointers
     to all the methods from step 1.
<li> Register your [sqlite3_module] structure using one of the
     [sqlite3_create_module()] or [sqlite3_create_module_v2()] interfaces.
<li> Run a [CREATE VIRTUAL TABLE] command that specifies the new module in 
     the USING clause. 
</ol>

<p>The only really hard part is step 1. You might want to start with an 
existing virtual table implementation and modify it to suit your needs.
The [https://sqlite.org/src/dir?ci=trunk&type=tree | SQLite source tree]
contains many virtual table implementations that are suitable for copying,
including:

<p>
<ul>
<li> <b>[https://sqlite.org/src/file/ext/misc/templatevtab.c|templatevtab.c]</b>
&rarr; A virtual table created specifically to serve as a template for
other custom virtual tables.
<li> <b>[https://sqlite.org/src/file/ext/misc/series.c|series.c]</b>
&rarr; Implementation of the generate_series() table-valued function.
<li> <b>[https://sqlite.org/src/file/ext/misc/json1.c|json1.c]</b> &rarr;
Contains the sources for the [json_each()] and [json_tree()] table-valued
functions.
<li> <b>[https://sqlite.org/src/file/ext/misc/csv.c|csv.c]</b> &rarr;
A virtual table that reads CSV files.
</ul>
</p>

<p>There are [virtual table list|many other virtual table implementations]
in the SQLite source tree that can be used as examples.  Locate 
these other virtual table implementations by searching 
for "sqlite3_create_module".

<p>You might also want to implement your new virtual table as a 
[loadable extension].

<h1>Virtual Table Methods</h1>

<tcl>hd_fragment xcreate {sqlite3_module.xCreate} {xCreate}</tcl>
<h2>The xCreate Method</h2>

<codeblock>