Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Continue working to improve the documentation. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
264d9eef33e14a76ba90adcd3b0f6a65 |
User & Date: | drh 2020-01-13 20:33:33.314 |
Context
2020-01-14
| ||
01:57 | Finish a first draft of the application-defined function document. (check-in: 66d5ec93a6 user: drh tags: trunk) | |
2020-01-13
| ||
20:33 | Continue working to improve the documentation. (check-in: 264d9eef33 user: drh tags: trunk) | |
15:26 | Template for the new "application-defined SQL function page". Improvements to security documentation. (check-in: 36399c3c34 user: drh tags: trunk) | |
Changes
Changes to pages/appfunc.in.
︙ | ︙ | |||
9 10 11 12 13 14 15 16 17 18 19 | <p>Applications that use SQLite can define custom SQL functions that call back into application code to compute their results. The custom SQL function implementations can be embedded in the application code itself, or can be [loadable extensions]. <p>Application-defined or custom SQL functions are created using the [sqlite3_create_function()] family of interfaces. <h1>Defining New SQL Functions</h1> | > > > > > > > > > > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | > > > > > > > > | | 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 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 | <p>Applications that use SQLite can define custom SQL functions that call back into application code to compute their results. The custom SQL function implementations can be embedded in the application code itself, or can be [loadable extensions]. <p>Application-defined or custom SQL functions are created using the [sqlite3_create_function()] family of interfaces. Custom SQL functions can be scalar functions, aggregate functions, or [window functions]. Custom SQL functions can have any number of arguments from 0 up to [SQLITE_MAX_FUNCTION_ARG]. The [sqlite3_create_function()] interface specifies callbacks that are invoked to carry out the processing for the new SQL function. <p>SQLite also supports custom [table-valued functions], but they are implemented by a different mechanism that is not covered in this document. <h1>Defining New SQL Functions</h1> <p> The [sqlite3_create_function()] family of interfaces is used to create new custom SQL functions. Each member of this family is a wrapper around a common core. All family members accomplish the same thing; they merely have different calling signatures. <ul> <li><p><b>[sqlite3_create_function()]</b> → The original version of sqlite3_create_function() allows the application to create a single new SQL function that can be either a scalar or an aggregate. The name of the function is specified using UTF8. <li><p><b>[sqlite3_create_function16()]</b> → This variant works exactly like the sqlite3_create_function() original except that the name of the function itself is specified as a UTF16 string rather than as a UTF8 string. <li><p><b>[sqlite3_create_function_v2()]</b> → This variant works like the original sqlite3_create_function() except that it includes an additional parameter that is a pointer to a destructor for the [sqlite3_user_data()] pointer that is passed in as the 5th argument to all of the sqlite3_create_function() variants. That destructor function (if it is non-NULL) is called when the custom function is deleted - usually when the database connection is closing. <li><p><b>[sqlite3_create_window_function()]</b> → This variant works like the original sqlite3_create_function() except that it accepts a different set of callback pointers - the callback pointers used by [window function] definitions. </ul> <h2>Common Parameters</h2> <p>Many of the parameters passed to the [sqlite3_create_function()] family of interfaces are common across the entire family. <ol> <li><p><b>db</b> → The 1st parameter is always a pointer to the [database connection] on which the custom SQL function will work. Custom SQL functions are created separately for each database connection. There is no short-hand mechanism for creating SQL functions that work across all database connections. <li><p><b>zFunctionName</b> → The 2nd parameter is the name of the SQL function that is being created. The name is usually in UTF8, except that the name should be in UTF16 in the native byte order for [sqlite3_create_function16()]. <p> The maximum length of a SQL function name is 255 bytes of UTF8. Any attempt to create a function with a longer name will result in an [SQLITE_MISUSE] error. </p> The SQL function creation interfaces may be called multiple times with the same function name. If two calls have the same function number but a different number of arguments, for example, then two variants of the SQL function will be registered, each taking a different number of arguments. <li><p><b>nArg</b> → The 3rd parameter is always the number of arguments that the function accepts. The value must be an integer between -1 and [SQLITE_MAX_FUNCTION_ARG] (default value: 127). A value of -1 means that the SQL function is a variadic function that can take any number of arguments between 0 and [SQLITE_MAX_FUNCTION_ARG]. <li><p><b>eTextRep</b> → The 4th parameter is a 32-bit integer flag whose bits convey various properties about the new function. The original purpose of this parameter was to specify the preferred text encoding for the function, using one of the following constants: <ul> <li> [SQLITE_UTF8] <li> [SQLITE_UTF16BE] <li> [SQLITE_UTF16LE] </ul> All custom SQL functions will accept text in any encoding. Encoding conversions will happen automatically. The preferred encoding merely specifies the encoding for which the function implementation is optimized. It is possible to specify multiple functions with the same name and the same number of arguments, but different preferred encodings and different callbacks used to implement the function, and SQLite will chose the set of callbacks for which the input encodings most closely match the preferred encoding. <p> The 4th parameter as more recently be extended with additional flag bits to convey additional information about the function. The additional bits include: <ul> <li> [SQLITE_DETERMINISTIC] <li> [SQLITE_DIRECTONLY] <li> [SQLITE_INNOCUOUS] <li> [SQLITE_SUBTYPE] </ul> <p> Additional bits may be added in future versions of SQLite. <li><p><b>pApp</b> → The 5th parameter is an arbitrary pointer that is passed through into the callback routines. SQLite itself does nothing with this pointer, except to make it available to the callbacks, and to pass it into the destructor when the function is unregistered. </ol> <h2>Multiple Calls To sqlite3_create_function() For The Same Function</h2> <p> It is common for an application to invoke sqlite3_create_function() multiple times for the same SQL function. For example, if an SQL function can take either 2 or 3 arguments, then sqlite3_create_function() would be invoked once for the 2-argument version and a second time for the 3-argument version. The underlying implementation (the callbacks) can be different for both variants. <p> An application can also register multiple SQL functions with the same name and same number of arguments, but a different preferred text encoding. In that case, SQLite will invoke the function using the callbacks for the version whose preferred text encoding most closely matches the database text encoding. In this way, multiple implementations of the same function can be provided that are optimized for UTF8 or UTF16. <p> If multiple calls to sqlite3_create_function() specify the same function name, and the same number of arguments, and the same preferred text encoding, then the callbacks and other parameters of the second call overwrite the first, and the destructor callback from the first call (if it exists) is invoked. <h2>Callbacks</h2> <p> SQLite evaluates an SQL function by invoking callback routines. <h3>The Scalar Function Callback</h3> <p>Scalar SQL functions are implemented by a single callback in the <b>xFunc</b> parameter to sqlite3_create_function(). The following code demonstrations the implementation of a "noop(X)" scalar SQL function that merely returns its argument: <codeblock> static void noopfunc( sqlite3_context *context, int argc, sqlite3_value **argv ){ assert( argc==1 ); sqlite3_result_value(context, argv[0]); } </codeblock> <p> The 1st parameter, <b>context</b>, is a pointer to an opaque object that describes the content from which the SQL function was invoked. This context point becomes the first parameter to many other routines that the function implement might to invoke, including: <tcl> set clist {} foreach x [lsort { sqlite3_aggregate_context sqlite3_user_data sqlite3_context_db_handle sqlite3_get_auxdata sqlite3_set_auxdata sqlite3_result_blob sqlite3_result_blob64 sqlite3_result_double sqlite3_result_error sqlite3_result_error16 sqlite3_result_error_toobig sqlite3_result_error_nomem sqlite3_result_error_code sqlite3_result_int sqlite3_result_int64 sqlite3_result_null sqlite3_result_text sqlite3_result_text64 sqlite3_result_text16 sqlite3_result_text16le sqlite3_result_text16be sqlite3_result_value sqlite3_result_pointer sqlite3_result_zeroblob sqlite3_result_zeroblob64 sqlite3_result_subtype }] { lappend clist [list $x $x 0] } hd_list_of_links {} 300 $clist </tcl> <p>The [sqlite3_result_blob|sqlite3_result() family of functions] are used to specify the result of the scalar SQL function. One or more of these should be invoked by the callback to set the function return value. If none of these routines are invoked for a specific callback, then the return value will be NULL. <p>The [sqlite3_user_data()] routine returns a copy of the <b>pArg</b> pointer that was given to [sqlite3_create_function()] when the SQL function was created. <p>The [sqlite3_context_db_handle()] routine returns a pointer to the [database connection] object. <p>The [sqlite3_aggregate_context()] routine is used only in the implementations of aggregate and window functions. Scalar functions may not use [sqlite3_aggregate_context()]. The [sqlite3_aggregate_context()] function is included in the interface list only for completeness. <p> The 2nd and 3rd arguments to the scalar SQL function implemenetation, <b>argc</b> and <b>argv</b>, are the number of arguments to the SQL function itself and the values for each argument of the SQL function. Argument values can be of any datatype and are thus stored in instances of the [sqlite3_value] object. Specific C-language values can be extracted from this object using the [sqlite3_value_int|sqlite3_value() family of interfaces]. <h1>Security Implications</h1> <p><i>TBD....</i> |
Changes to pages/capi3ref.in.
︙ | ︙ | |||
442 443 444 445 446 447 448 449 450 451 452 453 454 455 | hd_putsnl {<a href="intro.html"><h2>SQLite C Interface</h2></a>} hd_enable_main 1 </tcl> <h2>List Of Constants:</h2> <p>Also available: [error codes|list of error codes]</p> <tcl> set clist [lsort -index 1 $clist] hd_list_of_links {} 400 $clist hd_enable_main 0 hd_putsnl {<p>Other lists: <a href="objlist.html">Objects</a> and <a href="funclist.html">Functions</a> and <a href="../rescode.html">Result Codes</a>.</p>} hd_enable_main 1 | > | 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 | hd_putsnl {<a href="intro.html"><h2>SQLite C Interface</h2></a>} hd_enable_main 1 </tcl> <h2>List Of Constants:</h2> <p>Also available: [error codes|list of error codes]</p> <tcl> set clist [lsort -index 1 $clist] #puts clist=[list $clist] hd_list_of_links {} 400 $clist hd_enable_main 0 hd_putsnl {<p>Other lists: <a href="objlist.html">Objects</a> and <a href="funclist.html">Functions</a> and <a href="../rescode.html">Result Codes</a>.</p>} hd_enable_main 1 |
︙ | ︙ |
Changes to pages/security.in.
︙ | ︙ | |||
94 95 96 97 98 99 100 | In extreme cases, consider compiling SQLite with the [-DSQLITE_ENABLE_MEMSYS5] option and then providing SQLite with a fixed chunk of memory to use as its heap via the [sqlite3_config]([SQLITE_CONFIG_HEAP]) interface. This will prevent malicious SQL from executing a denial-of-service attack by using an excessive amount of memory. If (say) 5 MB of memory is provided for SQLite to use, once that much has been consumed, | | > > | > > > > > | | > > > < < < < < < | > | < < < < < < | | < < > | < < | < < > | > < < < | 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 | In extreme cases, consider compiling SQLite with the [-DSQLITE_ENABLE_MEMSYS5] option and then providing SQLite with a fixed chunk of memory to use as its heap via the [sqlite3_config]([SQLITE_CONFIG_HEAP]) interface. This will prevent malicious SQL from executing a denial-of-service attack by using an excessive amount of memory. If (say) 5 MB of memory is provided for SQLite to use, once that much has been consumed, SQLite will start returning SQLITE_NOMEM errors rather than soaking up memory needed by other parts of the application. This also sandboxes SQLite's memory so that a write-after-free error in some other part of the application will not cause problems for SQLite, or vice versa. </ol> <h2>Untrusted SQLite Database Files</h2> <p>Applications that read or write SQLite database files of uncertain provenance should take precautions enumerated below. <p>Even if the application does not deliberately accept database files from untrusted sources, beware of attacks in which a local database file is altered. For best security, any database file which might have ever been writable by an agent in a different security domain should be treated as suspect. <ol> <li value="7"><p> If the application includes any [custom SQL functions] or [custom virtual tables] that have side effects or that might leak privileged information, then the application should use one or more of the techniques below to prevent a maliciously crafted database schema from surreptiously running those SQL functions and/or virtual tables for neferious purposes: <ol type="a"> <li> Invoke [sqlite3_db_config](db,[SQLITE_DBCONFIG_TRUSTED_SCHEMA],0,0) on each [database connection] as soon as it is opened. <li> Run the [PRAGMA trusted_schema=OFF] statement on each database connection as soon as it is opened. <li> Compile SQLite using the [-DSQLITE_TRUSTED_SCHEMA=0] compile-time option. <li> Disable the surreptious use of custom SQL functions and virtual tables by setting the [SQLITE_DIRECTONLY] flag on all custom SQL functions and the [SQLITE_VTAB_DIRECTONLY] flag on all custom virtual tables. </ol> </li> <li><p> If the application does not use triggers or views, consider disabling the unused capabilities with: <blockquote><pre> [sqlite3_db_config](db,[SQLITE_DBCONFIG_ENABLE_TRIGGER],0,0); [sqlite3_db_config](db,[SQLITE_DBCONFIG_ENABLE_VIEW],0,0); </pre></blockquote> </p> </ol> <p> For reading database files that are unusually high-risk, such as database files that are received from remote machines, and possibly from anonymous contributors, the following extra precautions might be justifed. These added defenses come with performance costs, however, and so are probably not appropriate in every situation: <ol> <li value="9"><p> Run [PRAGMA integrity_check] or [PRAGMA quick_check] on the database as the first SQL statement after opening the database files and prior to running any other SQL statements. Reject and refuse to process any database file containing errors. <li><p> Enable the [PRAGMA cell_size_check=ON] setting. <p> <li><p> Do not enable memory-mapped I/O. In other words, make sure that [PRAGMA mmap_size=0]. </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 |
︙ | ︙ |