Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Enhancements to the documentation regarding security and shadow tables. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
7b6b7a791b8de988a5bdb3c90c9be377 |
User & Date: | drh 2018-11-12 15:24:40.772 |
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
Changes to pages/dbpage.in.
︙ | ︙ | |||
14 15 16 17 18 19 20 | </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 | | > | 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 | 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> | | | 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 | <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 | | | 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 <name> with the name of the virtual table to find the actual shadow |
︙ | ︙ |
Changes to pages/pragma.in.
︙ | ︙ | |||
1785 1786 1787 1788 1789 1790 1791 | ^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> | | > | | 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 | CREATE VIRTUAL TABLE <em><name></em> USING rtree(<em><column-names></em>); </codeblock> <p> The <em><name></em> is the name your application chooses for the R*Tree index and <em><column-names></em> is a comma separated list of between 3 and 11 columns. | | | 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 | CREATE VIRTUAL TABLE <em><name></em> USING rtree(<em><column-names></em>); </codeblock> <p> The <em><name></em> is the name your application chooses for the R*Tree index and <em><column-names></em> is a comma separated list of between 3 and 11 columns. ^(The virtual <name> table creates three [shadow tables] to actually store its content. The names of these shadow tables are: </p> <codeblock> <em><name></em><strong>_node</strong><br> <em><name></em><strong>_rowid</strong><br> <em><name></em><strong>_parent</strong> |
︙ | ︙ | |||
614 615 616 617 618 619 620 621 622 623 624 | <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 | > | | 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 | <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 | 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, | > > > | | | | 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 | 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> |
︙ | ︙ | |||
1419 1420 1421 1422 1423 1424 1425 | </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> | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | </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. |