1# The new-security-options branch 2 3## The problem that the [new-security-options](/timeline?r=new-security-options) branch tries to solve 4 5An attacker might modify the schema of an SQLite database by adding 6structures that cause code to run when some other application opens and 7reads the database. For example, the attacker might replace a table 8definition with a view. Or the attacker might add triggers to tables 9or views, or add new CHECK constraints or generated columns or indexes 10with expressions in the index list or in the WHERE clause. If the 11added features invoke SQL functions or virtual tables with side effects, 12that might cause harm to the system if run by a high-privilege victim. 13Or, the added features might exfiltrate information if the database is 14read by a high-privilege victim. 15 16The changes in this branch strive to make it easier for high-privilege 17applications to safely read SQLite database files that might have been 18maliciously corrupted by an attacker. 19 20## Overview of changes in [new-security-options](/timeline?r=new-security-options) 21 22The basic idea is to tag every SQL function and virtual table with one 23of three risk levels: 24 25 1. Innocuous 26 2. Normal 27 3. Direct-Only 28 29Innocuous functions/vtabs are safe and can be used at any time. 30Direct-only elements, in contrast, might have cause side-effects and 31should only be used from top-level SQL, not from within triggers or views nor 32in elements of the schema such as CHECK constraint, DEFAULT values, 33generated columns, index expressions, or in the WHERE clause of a 34partial index that are potentially under the control of an attacker. 35Normal elements behave like Innocuous if TRUSTED\_SCHEMA=on 36and behave like direct-only if TRUSTED\_SCHEMA=off. 37 38Application-defined functions and virtual tables go in as Normal unless 39the application takes deliberate steps to change the risk level. 40 41For backwards compatibility, the default is TRUSTED\_SCHEMA=on. Documentation 42will be updated to recommend applications turn TRUSTED\_SCHEMA to off. 43 44An innocuous function or virtual table is one that can only read content 45from the database file in which it resides, and can only alter the database 46in which it resides. Most SQL functions are innocuous. For example, there 47is no harm in an attacker running the abs() function. 48 49Direct-only elements that have side-effects that go outside the database file 50in which it lives, or return information from outside of the database file. 51Examples of direct-only elements include: 52 53 1. The fts3\_tokenizer() function 54 2. The writefile() function 55 3. The readfile() function 56 4. The zipvfs virtual table 57 5. The csv virtual table 58 59We do not want an attacker to be able to add these kinds of things to 60the database schema and possibly trick a high-privilege application 61from performing any of these actions. Therefore, functions and vtabs 62with side-effects are marked as Direct-Only. 63 64Legacy applications might add other risky functions or vtabs. Those will 65go in as "Normal" by default. For optimal security, we want those risky 66app-defined functions and vtabs to be direct-only, but making that the 67default might break some legacy applications. Hence, all app-defined 68functions and vtabs go in as Normal, but the application can switch them 69over to "Direct-Only" behavior using a single pragma. 70 71The restrictions on the use of functions and virtual tables do not apply 72to TEMP. A TEMP VIEW or a TEMP TRIGGER can use any valid SQL function 73or virtual table. The idea is that TEMP views and triggers must be 74directly created by the application and are thus under the control of the 75application. TEMP views and triggers cannot be created by an attacker who 76corrupts the schema of a persistent database file. Hence TEMP views and 77triggers are safe. 78 79## Specific changes 80 81 1. New sqlite3\_db\_config() option SQLITE\_DBCONFIG\_TRUSTED\_SCHEMA for 82 turning TRUSTED\_SCHEMA on and off. It defaults to ON. 83 84 2. Compile-time option -DSQLITE\_TRUSTED\_SCHEMA=0 causes the default 85 TRUSTED\_SCHEMA setting to be off. 86 87 3. New pragma "PRAGMA trusted\_schema=(ON\|OFF);". This provides access 88 to the TRUSTED_SCHEMA setting for application coded using scripting 89 languages or other secondary languages where they are unable to make 90 calls to sqlite3\_db\_config(). 91 92 4. New options for the "enc" parameter to sqlite3\_create\_function() and 93 its kin: 94 <ol type="a"> 95 <li> _SQLITE\_INNOCUOUS_ → tags the new functions as Innocuous 96 <li> _SQLITE\_DIRECTONLY_ → tags the new functions as Direct-Only 97 </ol> 98 99 5. New options to sqlite3\_vtab\_config(): 100 <ol type="a"> 101 <li> _SQLITE\_VTAB\_INNOCUOUS_ → tags the vtab as Innocuous 102 <li> _SQLITE\_VTAB\_DIRECTONLY_ → tags the vtab as Direct-Only 103 </ol> 104 105 6. Change many of the functions and virtual tables in the SQLite source 106 tree to use one of the tags above. 107 108 7. Enhanced PRAGMA function\_list and virtual-table "pragma\_function\_list" 109 with additional columns. The columns now are: 110 <ul> 111 <li> _name_ → Name of the function 112 <li> _builtin_ → 1 for built-in functions. 0 otherwise. 113 <li> _type_ → 's'=Scalar, 'a'=Aggregate, 'w'=Window 114 <li> _enc_ → 'utf8', 'utf16le', or 'utf16be' 115 <li> _narg_ → number of argument 116 <li> _flags_ → Bitmask of SQLITE\_INNOCUOUS, SQLITE\_DIRECTONLY, 117 SQLITE\_DETERMINISTIC, SQLITE\_SUBTYPE, and 118 SQLITE\_FUNC\_INTERNAL flags. 119 </ul> 120 <p>The last four columns are new. 121 122 8. The function\_list PRAGMA now also shows all entries for each function. 123 So, for example, if a function can take either 2 or 3 arguments, 124 there are separate rows for the 2-argument and 3-argument versions of 125 the function. 126 127## Additional Notes 128 129The function_list enhancements allow the application to query the set 130of SQL functions that meet various criteria. For example, to see all 131SQL functions that are never allowed to be used in the schema or in 132trigger or views: 133 134~~~ 135 SELECT DISTINCT name FROM pragma_function_list 136 WHERE (flags & 0x80000)!=0 137 ORDER BY name; 138~~~ 139 140Doing the same is not possible for virtual tables, as a virtual table 141might be Innocuous, Normal, or Direct-Only depending on the arguments 142passed into the xConnect method. 143