1.Dd January 24, 2024 2.Dt SQLITE_DETERMINISTIC 3 3.Os 4.Sh NAME 5.Nm SQLITE_DETERMINISTIC , 6.Nm SQLITE_DIRECTONLY , 7.Nm SQLITE_SUBTYPE , 8.Nm SQLITE_INNOCUOUS , 9.Nm SQLITE_RESULT_SUBTYPE 10.Nd function flags 11.Sh SYNOPSIS 12.In sqlite3.h 13.Fd #define SQLITE_DETERMINISTIC 14.Fd #define SQLITE_DIRECTONLY 15.Fd #define SQLITE_SUBTYPE 16.Fd #define SQLITE_INNOCUOUS 17.Fd #define SQLITE_RESULT_SUBTYPE 18.Sh DESCRIPTION 19These constants may be ORed together with the preferred text encoding 20as the fourth argument to 21.Fn sqlite3_create_function , 22.Fn sqlite3_create_function16 , 23or 24.Fn sqlite3_create_function_v2 . 25.Bl -tag -width Ds 26.It SQLITE_DETERMINISTIC 27The SQLITE_DETERMINISTIC flag means that the new function always gives 28the same output when the input parameters are the same. 29The abs() function is deterministic, for example, but 30randomblob() is not. 31Functions must be deterministic in order to be used in certain contexts 32such as with the WHERE clause of partial indexes or 33in generated columns. 34SQLite might also optimize deterministic functions by factoring them 35out of inner loops. 36.It SQLITE_DIRECTONLY 37The SQLITE_DIRECTONLY flag means that the function may only be invoked 38from top-level SQL, and cannot be used in VIEWs or TRIGGERs nor in 39schema structures such as CHECK constraints, DEFAULT clauses, 40expression indexes, partial indexes, 41or generated columns. 42.Pp 43The SQLITE_DIRECTONLY flag is recommended for any application-defined SQL function 44that has side-effects or that could potentially leak sensitive information. 45This will prevent attacks in which an application is tricked into using 46a database file that has had its schema surreptitiously modified to 47invoke the application-defined function in ways that are harmful. 48.Pp 49Some people say it is good practice to set SQLITE_DIRECTONLY on all 50application-defined SQL functions, 51regardless of whether or not they are security sensitive, as doing 52so prevents those functions from being used inside of the database 53schema, and thus ensures that the database can be inspected and modified 54using generic tools (such as the CLI) that do not have access to 55the application-defined functions. 56.It SQLITE_INNOCUOUS 57The SQLITE_INNOCUOUS flag means that the function is unlikely to cause 58problems even if misused. 59An innocuous function should have no side effects and should not depend 60on any values other than its input parameters. 61The abs() function is an example of an innocuous function. 62The load_extension() SQL function is not 63innocuous because of its side effects. 64.Pp 65SQLITE_INNOCUOUS is similar to SQLITE_DETERMINISTIC, but is not exactly 66the same. 67The random() function is an example of a function 68that is innocuous but not deterministic. 69.Pp 70Some heightened security settings (SQLITE_DBCONFIG_TRUSTED_SCHEMA 71and PRAGMA trusted_schema=OFF) disable the 72use of SQL functions inside views and triggers and in schema structures 73such as CHECK constraints, DEFAULT clauses, 74expression indexes, partial indexes, 75and generated columns unless the function is tagged 76with SQLITE_INNOCUOUS. 77Most built-in functions are innocuous. 78Developers are advised to avoid using the SQLITE_INNOCUOUS flag for 79application-defined functions unless the function has been carefully 80audited and found to be free of potentially security-adverse side-effects 81and information-leaks. 82.It SQLITE_SUBTYPE 83The SQLITE_SUBTYPE flag indicates to SQLite that a function might call 84.Fn sqlite3_value_subtype 85to inspect the sub-types of its arguments. 86This flag instructs SQLite to omit some corner-case optimizations that 87might disrupt the operation of the 88.Fn sqlite3_value_subtype 89function, causing it to return zero rather than the correct subtype(). 90SQL functions that invokes 91.Fn sqlite3_value_subtype 92should have this property. 93If the SQLITE_SUBTYPE property is omitted, then the return value from 94.Fn sqlite3_value_subtype 95might sometimes be zero even though a non-zero subtype was specified 96by the function argument expression. 97.It SQLITE_RESULT_SUBTYPE 98The SQLITE_RESULT_SUBTYPE flag indicates to SQLite that a function 99might call 100.Fn sqlite3_result_subtype 101to cause a sub-type to be associated with its result. 102Every function that invokes 103.Fn sqlite3_result_subtype 104should have this property. 105If it does not, then the call to 106.Fn sqlite3_result_subtype 107might become a no-op if the function is used as term in an expression index. 108On the other hand, SQL functions that never invoke 109.Fn sqlite3_result_subtype 110should avoid setting this property, as the purpose of this property 111is to disable certain optimizations that are incompatible with subtypes. 112.El 113.Pp 114.Sh IMPLEMENTATION NOTES 115These declarations were extracted from the 116interface documentation at line 5513. 117.Bd -literal 118#define SQLITE_DETERMINISTIC 0x000000800 119#define SQLITE_DIRECTONLY 0x000080000 120#define SQLITE_SUBTYPE 0x000100000 121#define SQLITE_INNOCUOUS 0x000200000 122#define SQLITE_RESULT_SUBTYPE 0x001000000 123.Ed 124.Sh SEE ALSO 125.Xr sqlite3_create_function 3 , 126.Xr sqlite3_result_subtype 3 , 127.Xr sqlite3_value_subtype 3 , 128.Xr SQLITE_DBCONFIG_MAINDBNAME 3 , 129.Xr SQLITE_UTF8 3 130