Documentation Source Text

Check-in [7b6b7a791b]
Login

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

Overview
Comment:Enhancements to the documentation regarding security and shadow tables.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256:7b6b7a791b8de988a5bdb3c90c9be377953d4335d0a912c91374d1e1d9063538
User & Date: drh 2018-11-12 15:24:40
Context
2018-11-13
19:55
Improvements to documentation associated with new features in version 3.26.0. check-in: 3d96456389 user: drh tags: trunk
2018-11-12
15:24
Enhancements to the documentation regarding security and shadow tables. check-in: 7b6b7a791b user: drh tags: trunk
13:39
Begin adding a change log for version 3.26.0. check-in: a74a421c12 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/dbpage.in.

14
15
16
17
18
19
20
21

22
23
24
25
26
27
28
</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







|
>







14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
</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

Changes to pages/fts3.in.

2477
2478
2479
2480
2481
2482
2483
2484
2485
2486
2487
2488
2489
2490
2491
  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.







|







2477
2478
2479
2480
2481
2482
2483
2484
2485
2486
2487
2488
2489
2490
2491
  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.

Changes to pages/fts5.in.

1966
1967
1968
1969
1970
1971
1972
1973
1974
1975
1976
1977
1978
1979
1980

<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







|







1966
1967
1968
1969
1970
1971
1972
1973
1974
1975
1976
1977
1978
1979
1980

<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

Changes to pages/pragma.in.

1785
1786
1787
1788
1789
1790
1791
1792

1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
    ^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.)^








|
>



|







1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
    ^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.)^

Changes to pages/rtree.in.

88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
...
614
615
616
617
618
619
620

621
622
623
624
625
626
627
628
629
630
631
632
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>








|







 







>




|







88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
...
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
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>

Added pages/security.in.





























































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
<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.

Changes to pages/vtab.in.

293
294
295
296
297
298
299



300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
....
1419
1420
1421
1422
1423
1424
1425



























































                       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>


































































>
>
>






|
|
|







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
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
....
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
                       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.