Documentation Source Text

</p>

<p>
<b>Warning:</b> writing to the SQLITE_DBPAGE virtual table can very easily
cause unrecoverably database corruption.  Do not allow untrusted components
to access the SQLITE_DBPAGE table.  Use appropriate care while using the
SQLITE_DBPAGE table.  Back up important data prior to experimenting with the
SQLITE_DBPAGE table.


<p>
The SQLITE_DBPAGE extension is included in the [amalgamation] though 
it is disabled
by default.  Use the [SQLITE_ENABLE_DBPAGE_VTAB] compile-time option to enable
the SQLITE_DBPAGE extension.  The SQLITE_DBPAGE extension makes use of
unpublished internal interfaces and is not run-time loadable.  The only way

</p>

<p>
<b>Warning:</b> writing to the SQLITE_DBPAGE virtual table can very easily
cause unrecoverably database corruption.  Do not allow untrusted components
to access the SQLITE_DBPAGE table.  Use appropriate care while using the
SQLITE_DBPAGE table.  Back up important data prior to experimenting with the
SQLITE_DBPAGE table.  Writes to the SQLITE_DBPAGE virtual table are
disabled when the [SQLITE_DBCONFIG_DEFENSIVE] flag is set.

<p>
The SQLITE_DBPAGE extension is included in the [amalgamation] though 
it is disabled
by default.  Use the [SQLITE_ENABLE_DBPAGE_VTAB] compile-time option to enable
the SQLITE_DBPAGE extension.  The SQLITE_DBPAGE extension makes use of
unpublished internal interfaces and is not run-time loadable.  The only way

  index and content in the database. It is <b>not necessary to read or 
  understand the material in this section in order to use FTS</b> in an 
  application. However, it may be useful to application developers attempting 
  to analyze and understand FTS performance characteristics, or to developers 
  contemplating enhancements to the existing FTS feature set.
</p>

<tcl>hd_fragment *shadowtab {FTS shadow tables} {shadow tables}</tcl>
<h2 tags="shadowtabs">Shadow Tables</h2>
<p>
  For each FTS virtual table in a database, three to five real (non-virtual) tables
  are created to store the underlying data.  These real tables are called "shadow tables".
  The real tables are named "%_content",
  "%_segdir", "%_segments", "%_stat", and "%_docsize", where "%" is replaced by the name
  of the FTS virtual table.

  index and content in the database. It is <b>not necessary to read or 
  understand the material in this section in order to use FTS</b> in an 
  application. However, it may be useful to application developers attempting 
  to analyze and understand FTS performance characteristics, or to developers 
  contemplating enhancements to the existing FTS feature set.
</p>

<tcl>hd_fragment *shadowtab {FTS shadow tables}</tcl>
<h2 tags="shadowtabs">Shadow Tables</h2>
<p>
  For each FTS virtual table in a database, three to five real (non-virtual) tables
  are created to store the underlying data.  These real tables are called "shadow tables".
  The real tables are named "%_content",
  "%_segdir", "%_segments", "%_stat", and "%_docsize", where "%" is replaced by the name
  of the FTS virtual table.

<h1 id=appendix_b nonumber tags="fts5 shadow tables">
  Appendix B: Shadow tables created by FTS5
</h1>

<p>
When an FTS5 virtual table is created in a database, between 3 and 5 real
tables are created in the database. These are known as "shadow" tables, and are
used by the virtual table module to store persistent data. They should not
be accessed directly by the user. Many other virtual table modules, including
[FTS3] and [rtree], also create and use shadow tables.

<p>FTS5 creates the following shadow tables. In each case the actual table name
is based on the name of the FTS5 virtual table (in the following table, replace
&lt;name&gt; with the name of the virtual table to find the actual shadow

<h1 id=appendix_b nonumber tags="fts5 shadow tables">
  Appendix B: Shadow tables created by FTS5
</h1>

<p>
When an FTS5 virtual table is created in a database, between 3 and 5 real
tables are created in the database. These are known as "[shadow tables]", and are
used by the virtual table module to store persistent data. They should not
be accessed directly by the user. Many other virtual table modules, including
[FTS3] and [rtree], also create and use shadow tables.

<p>FTS5 creates the following shadow tables. In each case the actual table name
is based on the name of the FTS5 virtual table (in the following table, replace
&lt;name&gt; with the name of the virtual table to find the actual shadow

    ^The default setting is off, meaning that CHECK constraints are
    enforced by default.</p>
}

DangerousPragma writable_schema {
    <p>^(<b>PRAGMA writable_schema  = </b><i>boolean</i><b>;</b></p>

    <p>When this pragma is on, the SQLITE_MASTER tables in which database

    can be changed using ordinary [UPDATE], [INSERT], and [DELETE]
    statements.)^  ^<warning><b>Warning:</b>
    misuse of this pragma can easily result in
    a [cfgerrors|corrupt database file].</warning></p>
}

EnablePragma function_list {
    <p>^(<b>PRAGMA function_list;</b>
    <p>This pragma returns a list of SQL functions
    known to the database connection.)^

    ^The default setting is off, meaning that CHECK constraints are
    enforced by default.</p>
}

DangerousPragma writable_schema {
    <p>^(<b>PRAGMA writable_schema  = </b><i>boolean</i><b>;</b></p>

    <p>When this pragma is on, and the [SQLITE_DBCONFIG_DEFENSIVE] flag
    is off, then the [sqlite_master] table
    can be changed using ordinary [UPDATE], [INSERT], and [DELETE]
    statements.)^  ^<warning><b>Warning:</b>
    misuse of this pragma can easily result in
    a [cfgerrors|corrupt database file].</warning>
}

EnablePragma function_list {
    <p>^(<b>PRAGMA function_list;</b>
    <p>This pragma returns a list of SQL functions
    known to the database connection.)^

CREATE VIRTUAL TABLE <em>&lt;name&gt;</em> USING rtree(<em>&lt;column-names&gt;</em>);
</codeblock>

<p>
The <em>&lt;name&gt;</em> is the name your application chooses for the
R*Tree index and <em>&lt;column-names&gt;</em> is a comma separated list
of between 3 and 11 columns.
^(The virtual &lt;name&gt; table creates three "shadow" tables to actually
store its content.  The names of these shadow tables are:
</p>

<codeblock>
<em>&lt;name&gt;</em><strong>_node</strong><br>
<em>&lt;name&gt;</em><strong>_rowid</strong><br>
<em>&lt;name&gt;</em><strong>_parent</strong>
................................................................................

<h1>Implementation Details</h1>

<p>
The following sections describe some low-level details of the R*Tree implementation,
that might be useful for trouble-shooting or performance analysis.


<h2>Shadow Tables</h2>

<p>The content of an R*Tree index is actually stored in three ordinary
SQLite tables with names derived from the name of the R*Tree.  These
three tables are called "shadow tables".  This is their schema:

<codeblock>
CREATE TABLE %_node(nodeno INTEGER PRIMARY KEY, data BLOB)
CREATE TABLE %_parent(nodeno INTEGER PRIMARY KEY, parentnode INTEGER)
CREATE TABLE %_rowid(rowid INTEGER PRIMARY KEY, nodeno INTEGER)
</codeblock>

CREATE VIRTUAL TABLE <em>&lt;name&gt;</em> USING rtree(<em>&lt;column-names&gt;</em>);
</codeblock>

<p>
The <em>&lt;name&gt;</em> is the name your application chooses for the
R*Tree index and <em>&lt;column-names&gt;</em> is a comma separated list
of between 3 and 11 columns.
^(The virtual &lt;name&gt; table creates three [shadow tables] to actually
store its content.  The names of these shadow tables are:
</p>

<codeblock>
<em>&lt;name&gt;</em><strong>_node</strong><br>
<em>&lt;name&gt;</em><strong>_rowid</strong><br>
<em>&lt;name&gt;</em><strong>_parent</strong>
................................................................................

<h1>Implementation Details</h1>

<p>
The following sections describe some low-level details of the R*Tree implementation,
that might be useful for trouble-shooting or performance analysis.

<tcl>hd_fragment xshadow {rtree shadow tables}</tcl>
<h2>Shadow Tables</h2>

<p>The content of an R*Tree index is actually stored in three ordinary
SQLite tables with names derived from the name of the R*Tree.  These
three tables are called "[shadow tables]".  This is their schema:

<codeblock>
CREATE TABLE %_node(nodeno INTEGER PRIMARY KEY, data BLOB)
CREATE TABLE %_parent(nodeno INTEGER PRIMARY KEY, parentnode INTEGER)
CREATE TABLE %_rowid(rowid INTEGER PRIMARY KEY, nodeno INTEGER)
</codeblock>

<title>Resistance To Attack</title>
<tcl>hd_keywords security {attack resistance}</tcl>
<fancy_format>

<h1>SQLite Always Validates Its Inputs</h1>

<p>
SQLite should never crash, overflow a buffer, leak memory,
or exhibit any other harmful behavior, even with presented with
maliciously malformed SQL inputs or database files.  SQLite should
always detected erroneous inputs and raise an error, not crash or
corrupt memory.
Any malfunction caused by an SQL input or database file
is considered a serious bug and will be promptly addressed when
brought to the attention of the SQLite developers.  SQLite is
extensively fuzz-tested to help ensure that it is highly resistant
to these kinds of errors.

<p>
Nevertheless, bugs happen.
If you are writing an application that sends untrusted SQL inputs
or database files to SQLite, there are additional steps you can take
to help prevent zero-day exploits caused by undetected bugs:

<h2>Untrusted SQL Inputs</h2>
<p>
Applications that accept untrusted SQL inputs should take the following
precautions:

<ol>
<li><p>
Set the [SQLITE_DBCONFIG_DEFENSIVE] flag.
This prevents ordinary SQL statements from corrupted the database
file.

<li><p>
Consider using the [sqlite3_set_authorizer()] interface to limit
the scope of SQL that will be processed.
</ol>

<h2>Untrusted SQLite Database Files</h2>

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

<ol>
<li value="3"><p>
Run [PRAGMA integrity_check] or [PRAGMA quick_check] on the database
first, prior to running any other SQLite, and reject the file if any
errors are detected.

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

<h1>Summary</h1>

<p>
The precautions above are not required in order to use SQLite safely
with potentially hostile inputs.
However, they do provide an extra layer of defense against zero-day
exploits and are encouraged for applications that pass data from
untrusted sources into SQLite.

                       void **ppArg);
    int (*Rename)(sqlite3_vtab *pVtab, const char *zNew);
    /* The methods above are in version 1 of the sqlite_module object. Those 
    ** below are for version 2 and greater. */
    int (*xSavepoint)(sqlite3_vtab *pVTab, int);
    int (*xRelease)(sqlite3_vtab *pVTab, int);
    int (*xRollbackTo)(sqlite3_vtab *pVTab, int);



  };
</codeblock>

<p>The module structure defines all of the methods for each virtual 
table object. The module structure also contains the iVersion field which
defines the particular edition of the module table structure. Currently, 
iVersion is always 1, but in future releases of SQLite the module structure 
definition might be extended with additional methods and in that case 
the iVersion value will be increased.

<p>The rest of the module structure consists of methods used to implement
various features of the virtual table. Details on what each of these 
methods do are provided in the sequel.

<h2>Virtual Tables And Shared Cache</h2>

................................................................................
</p>

<p>
^None of the xSavepoint(), xRelease(), or xRollbackTo() methods will ever
be called except in between calls to xBegin() and 
either xCommit() or xRollback().
</p>

                       void **ppArg);
    int (*Rename)(sqlite3_vtab *pVtab, const char *zNew);
    /* The methods above are in version 1 of the sqlite_module object. Those 
    ** below are for version 2 and greater. */
    int (*xSavepoint)(sqlite3_vtab *pVTab, int);
    int (*xRelease)(sqlite3_vtab *pVTab, int);
    int (*xRollbackTo)(sqlite3_vtab *pVTab, int);
    /* The methods above are in versions 1 and 2 of the sqlite_module object.
    ** Those below are for version 3 and greater. */
    int (*xShadowName)(const char*);
  };
</codeblock>

<p>The module structure defines all of the methods for each virtual 
table object. The module structure also contains the iVersion field which
defines the particular edition of the module table structure. Currently, 
iVersion is always 3 or less, but in future releases of SQLite the module
structure definition might be extended with additional methods and in 
that case the maximum iVersion value will be increased.

<p>The rest of the module structure consists of methods used to implement
various features of the virtual table. Details on what each of these 
methods do are provided in the sequel.

<h2>Virtual Tables And Shared Cache</h2>

................................................................................
</p>

<p>
^None of the xSavepoint(), xRelease(), or xRollbackTo() methods will ever
be called except in between calls to xBegin() and 
either xCommit() or xRollback().
</p>

<tcl>############################################################# xShadowName
hd_fragment xshadowname {sqlite3_module.xShadowName} {xShadowName} \
     {shadow tables}</tcl>
<h2>The xShadowName Method</h2>

<p>Some virtual table implementations (ex: [FTS3], [FTS5], and [RTREE]) make
use of real (non-virtual) database tables to store content.  For example,
when content is inserted into the FTS3 virtual table, the data is ultimately
stored in real tables named "%_content", "%_segdir", "%_segments", "%_stat",
and "%_docsize" where "%" is the name of the original virtual table.  This
auxiliary real tables that store content for a virtual table are called
"shadow tables".  See
([FTS shadow tables|1]),
([fts5 shadow tables|2]), and
([rtree shadow tables|3]) for additional information.

<p>The xShadowName method exists to allow SQLite to determine whether a
certain real table is in fact a shadow table for a virtual table.

<p>SQLite understands a real table to be a shadow table if all of
the following are true:
<p>
<ul>
<li> The name of the table contains one or more "_" characters.
<li> The part of the name prior to the last "_" exactly matches
     the name of a virtual table that was created using [CREATE VIRTUAL TABLE].
     (Shadow tables are not recognized for [eponymous virtual tables]
     and [table-valued functions].)
<li> The virtual table contains an xShadowName method.
<li> The xShadowName method returns true when its input is the part
     of the table name past the last "_" character.
</ul>

<p>
If SQLite recognizes a table as a shadow table, and if the
[SQLITE_DBCONFIG_DEFENSIVE] flag is set, then the shadow table is read-only
for ordinary SQL statements.  The shadow table can still be written, but
only by SQL that is invoked from within one of the methods of
some virtual table implementation.

<p>
The whole point of the xShadowName method is to protect the content of
shadow tables from being corrupted by hostile SQL.  Every virtual table
implementation that uses shadow tables should be able to detect and cope
with corrupted shadow table content.  However, bugs in particular virtual 
table implementation might allow a deliberately corrupted shadow table to
cause a crash or other malfunction.  The xShadowName mechanism seeks to 
avoid zero-day exploits by preventing ordinary SQL statements from
deliberately corrupting shadow tables.

<p>
Shadow tables are read/write by default.
Shadow tables only become read-only when the [SQLITE_DBCONFIG_DEFENSIVE]
flag is set using [sqlite3_db_config()].
Shadow tables need to be read/write by default in order to maintain
backwards compatibility.
For example, the SQL text generated by the [.dump] command of the [CLI]
writes directly into shadow tables.