1.Dd August 24, 2023 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.Nd function flags 10.Sh SYNOPSIS 11.In sqlite3.h 12.Fd #define SQLITE_DETERMINISTIC 13.Fd #define SQLITE_DIRECTONLY 14.Fd #define SQLITE_SUBTYPE 15.Fd #define SQLITE_INNOCUOUS 16.Sh DESCRIPTION 17These constants may be ORed together with the preferred text encoding 18as the fourth argument to 19.Fn sqlite3_create_function , 20.Fn sqlite3_create_function16 , 21or 22.Fn sqlite3_create_function_v2 . 23.Bl -tag -width Ds 24.It SQLITE_DETERMINISTIC 25The SQLITE_DETERMINISTIC flag means that the new function always gives 26the same output when the input parameters are the same. 27The abs() function is deterministic, for example, but 28randomblob() is not. 29Functions must be deterministic in order to be used in certain contexts 30such as with the WHERE clause of partial indexes or 31in generated columns. 32SQLite might also optimize deterministic functions by factoring them 33out of inner loops. 34.It SQLITE_DIRECTONLY 35The SQLITE_DIRECTONLY flag means that the function may only be invoked 36from top-level SQL, and cannot be used in VIEWs or TRIGGERs nor in 37schema structures such as CHECK constraints, DEFAULT clauses, 38expression indexes, partial indexes, 39or generated columns. 40.Pp 41The SQLITE_DIRECTONLY flag is recommended for any application-defined SQL function 42that has side-effects or that could potentially leak sensitive information. 43This will prevent attacks in which an application is tricked into using 44a database file that has had its schema surreptitiously modified to 45invoke the application-defined function in ways that are harmful. 46.Pp 47Some people say it is good practice to set SQLITE_DIRECTONLY on all 48application-defined SQL functions, 49regardless of whether or not they are security sensitive, as doing 50so prevents those functions from being used inside of the database 51schema, and thus ensures that the database can be inspected and modified 52using generic tools (such as the CLI) that do not have access to 53the application-defined functions. 54.It SQLITE_INNOCUOUS 55The SQLITE_INNOCUOUS flag means that the function is unlikely to cause 56problems even if misused. 57An innocuous function should have no side effects and should not depend 58on any values other than its input parameters. 59The abs() function is an example of an innocuous function. 60The load_extension() SQL function is not 61innocuous because of its side effects. 62.Pp 63SQLITE_INNOCUOUS is similar to SQLITE_DETERMINISTIC, but is not exactly 64the same. 65The random() function is an example of a function 66that is innocuous but not deterministic. 67.Pp 68Some heightened security settings (SQLITE_DBCONFIG_TRUSTED_SCHEMA 69and PRAGMA trusted_schema=OFF) disable the 70use of SQL functions inside views and triggers and in schema structures 71such as CHECK constraints, DEFAULT clauses, 72expression indexes, partial indexes, 73and generated columns unless the function is tagged 74with SQLITE_INNOCUOUS. 75Most built-in functions are innocuous. 76Developers are advised to avoid using the SQLITE_INNOCUOUS flag for 77application-defined functions unless the function has been carefully 78audited and found to be free of potentially security-adverse side-effects 79and information-leaks. 80.It SQLITE_SUBTYPE 81The SQLITE_SUBTYPE flag indicates to SQLite that a function may call 82.Fn sqlite3_value_subtype 83to inspect the sub-types of its arguments. 84Specifying this flag makes no difference for scalar or aggregate user 85functions. 86However, if it is not specified for a user-defined window function, 87then any sub-types belonging to arguments passed to the window function 88may be discarded before the window function is called (i.e. sqlite3_value_subtype() 89will always return 0). 90.El 91.Pp 92.Sh IMPLEMENTATION NOTES 93These declarations were extracted from the 94interface documentation at line 5509. 95.Bd -literal 96#define SQLITE_DETERMINISTIC 0x000000800 97#define SQLITE_DIRECTONLY 0x000080000 98#define SQLITE_SUBTYPE 0x000100000 99#define SQLITE_INNOCUOUS 0x000200000 100.Ed 101.Sh SEE ALSO 102.Xr sqlite3_create_function 3 , 103.Xr sqlite3_value_subtype 3 , 104.Xr SQLITE_DBCONFIG_MAINDBNAME 3 , 105.Xr SQLITE_UTF8 3 106