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>

















































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

<li><p>
A maliciously crafted database might be able to inject SQL by defining new
[CREATE TRIGGER|triggers] or [CREATE VIEW|views] in the schema that the
application does not anticipate.
There are multiple defenses:
<ol type="a">
<li><p>
If the application does not use triggers or views, then disable the
unused capabilities using:
<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>
If the application does use triggers or views, then use queries to
scan the [sqlite_master] table to verify that the triggers and views
found there are expected, that there are no surplus triggers or views,
existing triggers and views have not been tampered with,
and that no existing ordinary tables have been replaced by
malicious views.
</p>
<li><p>
If the application uses [application-defined SQL functions] that have
side effects, then it is recommended to set the [SQLITE_DIRECTONLY] flag
on those SQL functions to prevent them from being used inside triggers
and views.  To illustrate the importance of this flag, consider an
application that implements an SQL function "send_money(...)".  Without
the SQLITE_DIRECTONLY flag, an attacker might be able to add a trigger
or view that uses that custom function, then trick a
high-privilege application to run an otherwise harmless query that invokes
that malicious trigger or view.  The SQLITE_DIRECTONLY flag prevents the
attack by requiring the "send_money()" function to be invoked directly
by the application, rather than indirectly through a trigger or view.
</ol>
</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>

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


<h2>Creating New Virtual Table Implementations</h2>

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

<p>
<ol>
<li> Write all necessary methods.
................................................................................
     [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.

There are several virtual table implementations in the SQLite source tree 
(for testing purposes). You might use one of those as a guide. Locate 



















these test virtual table implementations by searching 
for "sqlite3_create_module".

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

<h1>Virtual Table Methods</h1>

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

<codeblock>

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