Debian Codegraph @

www/appfunc.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>Application-Defined SQL Functions</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118 <div class=fancy>
119 <div class=nosearch>
120 <div class="fancy_title">
121 Application-Defined SQL Functions
122 </div>
123 <details class="fancy_toc">
124 <summary>Table Of Contents</summary>
125 <div id="toc_sub"><div class="fancy-toc1"><a href="#executive_summary">1. Executive Summary</a></div>
126 <div class="fancy-toc1"><a href="#defining_new_sql_functions">2. Defining New SQL Functions</a></div>
127 <div class="fancy-toc2"><a href="#common_parameters">2.1. Common Parameters</a></div>
128 <div class="fancy-toc2"><a href="#multiple_calls_to_sqlite3_create_function_for_the_same_function">2.2. Multiple Calls To sqlite3_create_function() For The Same Function</a></div>
129 <div class="fancy-toc2"><a href="#callbacks">2.3. Callbacks</a></div>
130 <div class="fancy-toc3"><a href="#the_scalar_function_callback">2.3.1. The Scalar Function Callback</a></div>
131 <div class="fancy-toc3"><a href="#the_aggregate_function_callbacks">2.3.2. The Aggregate Function Callbacks</a></div>
132 <div class="fancy-toc3"><a href="#the_window_function_callbacks">2.3.3. The Window Function Callbacks</a></div>
133 <div class="fancy-toc3"><a href="#examples">2.3.4. Examples</a></div>
134 <div class="fancy-toc1"><a href="#security_implications">3. Security Implications</a></div>
135 </div>
136 </details>
137 </div>
138
139
140
141
142
143 <h1 id="executive_summary"><span>1. </span>Executive Summary</h1>
144
145 <p>Applications that use SQLite can define custom SQL functions that call
146 back into application code to compute their results. The custom SQL
147 function implementations can be embedded in the application code itself,
148 or can be <a href="loadext.html">loadable extensions</a>.
149
150 </p><p>Application-defined or custom SQL functions are created using the
151 <a href="c3ref/create_function.html">sqlite3_create_function()</a> family of interfaces.
152 Custom SQL functions can be scalar functions, aggregate functions,
153 or <a href="windowfunctions.html">window functions</a>.
154 Custom SQL functions can have any number of arguments from 0 up to
155 <a href="limits.html#max_function_arg">SQLITE_MAX_FUNCTION_ARG</a>.
156 The <a href="c3ref/create_function.html">sqlite3_create_function()</a> interface specifies callbacks that are
157 invoked to carry out the processing for the new SQL function.
158
159 </p><p>SQLite also supports custom <a href="vtab.html#tabfunc2">table-valued functions</a>, but they are
160 implemented by a different mechanism that is not covered in this document.
161
162 </p><h1 id="defining_new_sql_functions"><span>2. </span>Defining New SQL Functions</h1>
163
164 <p>
165 The <a href="c3ref/create_function.html">sqlite3_create_function()</a> family of interfaces is used to create
166 new custom SQL functions. Each member of this family is a wrapper around
167 a common core. All family members accomplish the same thing; they merely
168 have different calling signatures.
169
170 </p><ul>
171 <li><p><b><a href="c3ref/create_function.html">sqlite3_create_function()</a></b> &rarr;
172 The original version of sqlite3_create_function() allows the application
173 to create a single new SQL function that can be either a scalar or an
174 aggregate. The name of the function is specified using UTF8.
175
176 </p></li><li><p><b><a href="c3ref/create_function.html">sqlite3_create_function16()</a></b> &rarr;
177 This variant works exactly like the sqlite3_create_function() original
178 except that the name of the function itself is specified as a UTF16
179 string rather than as a UTF8 string.
180
181 </p></li><li><p><b><a href="c3ref/create_function.html">sqlite3_create_function_v2()</a></b> &rarr;
182 This variant works like the original sqlite3_create_function() except
183 that it includes an additional parameter that is a pointer to a
184 destructor for the <a href="c3ref/user_data.html">sqlite3_user_data()</a> pointer that is passed in
185 as the 5th argument to all of the sqlite3_create_function() variants.
186 That destructor function (if it is non-NULL) is called when the
187 custom function is deleted - usually when the database connection is
188 closing.
189
190 </p></li><li><p><b><a href="c3ref/create_function.html">sqlite3_create_window_function()</a></b> &rarr;
191 This variant works like the original sqlite3_create_function() except
192 that it accepts a different set of callback pointers - the callback
193 pointers used by <a href="windowfunctions.html">window function</a> definitions.
194 </p></li></ul>
195
196 <h2 id="common_parameters"><span>2.1. </span>Common Parameters</h2>
197
198 <p>Many of the parameters passed to the <a href="c3ref/create_function.html">sqlite3_create_function()</a>
199 family of interfaces are common across the entire family.
200
201 </p><ol>
202 <li><p><b>db</b> &rarr;
203 The 1st parameter is always a pointer to the <a href="c3ref/sqlite3.html">database connection</a>
204 on which the custom SQL function will work. Custom SQL functions are
205 created separately for each database connection. There is no short-hand
206 mechanism for creating SQL functions that work across all database
207 connections.
208
209 </p></li><li><p><b>zFunctionName</b> &rarr;
210 The 2nd parameter is the name of the SQL function that is being
211 created. The name is usually in UTF8, except that the name should
212 be in UTF16 in the native byte order for <a href="c3ref/create_function.html">sqlite3_create_function16()</a>.
213 </p><p>
214 The maximum length of a SQL function name is 255 bytes of UTF8.
215 Any attempt to create a function with a longer name will result in
216 an <a href="rescode.html#misuse">SQLITE_MISUSE</a> error.
217 </p>
218 The SQL function creation interfaces may be called multiple
219 times with the same function name.
220 If two calls have the same function number but a different number of
221 arguments, for example, then two variants of the SQL function will
222 be registered, each taking a different number of arguments.
223
224 </li><li><p><b>nArg</b> &rarr;
225 The 3rd parameter is always the number of arguments that the function
226 accepts. The value must be an integer between -1 and
227 <a href="limits.html#max_function_arg">SQLITE_MAX_FUNCTION_ARG</a> (default value: 127). A value of -1 means
228 that the SQL function is a variadic function that can take any number
229 of arguments between 0 and <a href="limits.html#max_function_arg">SQLITE_MAX_FUNCTION_ARG</a>.
230
231 </p></li><li><p><b>eTextRep</b> &rarr;
232 The 4th parameter is a 32-bit integer flag whose bits convey various
233 properties about the new function. The original purpose of this
234 parameter was to specify the preferred text encoding for the function,
235 using one of the following constants:
236 </p><ul>
237 <li> <a href="c3ref/c_any.html#sqliteutf8">SQLITE_UTF8</a>
238 </li><li> <a href="c3ref/c_any.html#sqliteutf16be">SQLITE_UTF16BE</a>
239 </li><li> <a href="c3ref/c_any.html#sqliteutf16le">SQLITE_UTF16LE</a>
240 </li></ul>
241 All custom SQL functions will accept text in any encoding. Encoding
242 conversions will happen automatically. The preferred encoding merely
243 specifies the encoding for which the function implementation is optimized.
244 It is possible to specify multiple functions with the same name and the
245 same number of arguments, but different preferred encodings and different
246 callbacks used to implement the function, and SQLite will choose the
247 set of callbacks for which the input encodings most closely match the
248 preferred encoding.
249 <p>
250 The preferred text encoding flag may be OR-ed together with zero or more
251 of the following property flags:
252 </p><ul>
253 <li> <a href="c3ref/c_deterministic.html#sqlitedeterministic">SQLITE_DETERMINISTIC</a>
254 </li><li> <a href="c3ref/c_deterministic.html#sqlitedirectonly">SQLITE_DIRECTONLY</a>
255 </li><li> <a href="c3ref/c_deterministic.html#sqliteinnocuous">SQLITE_INNOCUOUS</a>
256 </li><li> <a href="c3ref/c_deterministic.html#sqlitesubtype">SQLITE_SUBTYPE</a>
257 </li></ul>
258 <p>
259 Additional function property flags bits may be added in future versions
260 of SQLite.
261
262 </p></li><li><p><b>pApp</b> &rarr;
263 The 5th parameter is an arbitrary pointer that is passed through
264 into the callback routines. SQLite itself does nothing with this
265 pointer, except to make it available to the callbacks, and to pass
266 it into the destructor when the function is unregistered.
267 </p></li></ol>
268
269 <h2 id="multiple_calls_to_sqlite3_create_function_for_the_same_function"><span>2.2. </span>Multiple Calls To sqlite3_create_function() For The Same Function</h2>
270
271 <p>
272 It is common for an application to invoke sqlite3_create_function() multiple
273 times for the same SQL function. For example, if an SQL function can take
274 either 2 or 3 arguments, then sqlite3_create_function() would be invoked
275 once for the 2-argument version and a second time for the 3-argument version.
276 The underlying implementation (the callbacks) can be different for both
277 variants.
278
279 </p><p>
280 An application can also register multiple SQL functions with the same name
281 and same number of arguments, but a different preferred text encoding.
282 In that case, SQLite will invoke the function using the callbacks for
283 the version whose preferred text encoding most closely matches the database
284 text encoding. In this way, multiple implementations of the same function
285 can be provided that are optimized for UTF8 or UTF16.
286
287 </p><p>
288 If multiple calls to sqlite3_create_function() specify the same function name,
289 and the same number of arguments, and the same preferred text encoding, then
290 the callbacks and other parameters of the second call overwrite the first,
291 and the destructor callback from the first call (if it exists) is invoked.
292
293
294 </p><h2 id="callbacks"><span>2.3. </span>Callbacks</h2>
295
296 <p>
297 SQLite evaluates an SQL function by invoking callback routines.
298
299 </p><h3 id="the_scalar_function_callback"><span>2.3.1. </span>The Scalar Function Callback</h3>
300
301 <p>Scalar SQL functions are implemented by a single callback in the
302 <b>xFunc</b> parameter to sqlite3_create_function().
303 The following code demonstrates the implementation of a "noop(X)"
304 scalar SQL function that merely returns its argument:
305
306 </p><div class="codeblock"><pre>static void noopfunc(
307 sqlite3_context *context,
308 int argc,
309 sqlite3_value **argv
310 ){
311 assert( argc==1 );
312 sqlite3_result_value(context, argv&#91;0&#93;);
313 }
314 </pre></div>
315
316 <p>
317 The 1st parameter, <b>context</b>, is a pointer to an opaque object
318 that describes the content from which the SQL function was invoked. This
319 context pointer becomes the first parameter to many other routines that
320 the function implementation might want to invoke, including:
321
322 <div class='columns' style='columns: 15em auto;'>
323 <ul style='padding-top:0;'>
324 <li><a href='c3ref/aggregate_context.html'>sqlite3_aggregate_context</a></li>
325 <li><a href='c3ref/context_db_handle.html'>sqlite3_context_db_handle</a></li>
326 <li><a href='c3ref/get_auxdata.html'>sqlite3_get_auxdata</a></li>
327 <li><a href='c3ref/result_blob.html'>sqlite3_result_blob</a></li>
328 <li><a href='c3ref/result_blob.html'>sqlite3_result_blob64</a></li>
329 <li><a href='c3ref/result_blob.html'>sqlite3_result_double</a></li>
330 <li><a href='c3ref/result_blob.html'>sqlite3_result_error</a></li>
331 <li><a href='c3ref/result_blob.html'>sqlite3_result_error16</a></li>
332 <li><a href='c3ref/result_blob.html'>sqlite3_result_error_code</a></li>
333 <li><a href='c3ref/result_blob.html'>sqlite3_result_error_nomem</a></li>
334 <li><a href='c3ref/result_blob.html'>sqlite3_result_error_toobig</a></li>
335 <li><a href='c3ref/result_blob.html'>sqlite3_result_int</a></li>
336 <li><a href='c3ref/result_blob.html'>sqlite3_result_int64</a></li>
337 <li><a href='c3ref/result_blob.html'>sqlite3_result_null</a></li>
338 <li><a href='c3ref/result_blob.html'>sqlite3_result_pointer</a></li>
339 <li><a href='c3ref/result_subtype.html'>sqlite3_result_subtype</a></li>
340 <li><a href='c3ref/result_blob.html'>sqlite3_result_text</a></li>
341 <li><a href='c3ref/result_blob.html'>sqlite3_result_text16</a></li>
342 <li><a href='c3ref/result_blob.html'>sqlite3_result_text16be</a></li>
343 <li><a href='c3ref/result_blob.html'>sqlite3_result_text16le</a></li>
344 <li><a href='c3ref/result_blob.html'>sqlite3_result_text64</a></li>
345 <li><a href='c3ref/result_blob.html'>sqlite3_result_value</a></li>
346 <li><a href='c3ref/result_blob.html'>sqlite3_result_zeroblob</a></li>
347 <li><a href='c3ref/result_blob.html'>sqlite3_result_zeroblob64</a></li>
348 <li><a href='c3ref/get_auxdata.html'>sqlite3_set_auxdata</a></li>
349 <li><a href='c3ref/user_data.html'>sqlite3_user_data</a></li>
350 </ul>
351 </div>
352
353
354 </p><p>The <a href="c3ref/result_blob.html">sqlite3_result() family of functions</a> are
355 used to specify the result of the scalar SQL function. One or more of
356 these should be invoked by the callback to set the function return value.
357 If none of these routines are invoked for a specific callback, then the
358 return value will be NULL.
359
360 </p><p>The <a href="c3ref/user_data.html">sqlite3_user_data()</a> routine returns a copy of the <b>pArg</b>
361 pointer that was given to <a href="c3ref/create_function.html">sqlite3_create_function()</a> when the SQL
362 function was created.
363
364 </p><p>The <a href="c3ref/context_db_handle.html">sqlite3_context_db_handle()</a> routine returns a pointer to the
365 <a href="c3ref/sqlite3.html">database connection</a> object.
366
367 </p><p>The <a href="c3ref/aggregate_context.html">sqlite3_aggregate_context()</a> routine is used only in the
368 implementations of aggregate and window functions. Scalar functions
369 may not use <a href="c3ref/aggregate_context.html">sqlite3_aggregate_context()</a>. The <a href="c3ref/aggregate_context.html">sqlite3_aggregate_context()</a>
370 function is included in the interface list only for completeness.
371
372 </p><p>
373 The 2nd and 3rd arguments to the scalar SQL function implementation,
374 <b>argc</b> and <b>argv</b>, are
375 the number of arguments to the SQL function itself and the values for
376 each argument of the SQL function.
377 Argument values can be of any datatype and are thus stored in
378 instances of the <a href="c3ref/value.html">sqlite3_value</a> object.
379 Specific C-language values can be extracted from this object using
380 the <a href="c3ref/value_blob.html">sqlite3_value() family of interfaces</a>.
381
382 </p><h3 id="the_aggregate_function_callbacks"><span>2.3.2. </span>The Aggregate Function Callbacks</h3>
383
384 <p>Aggregate SQL functions are implemented by using two callback
385 functions, <b>xStep</b> and <b>xFinal</b>. The xStep() function
386 is called for each row of the aggregate and the xFinal() function
387 is invoked to compute the final answer at the end.
388 The following (slightly simplified) version of the built-in
389 count() function illustrates:
390
391 </p><div class="codeblock"><pre>typedef struct CountCtx CountCtx;
392 struct CountCtx {
393 i64 n;
394 };
395 static void countStep(sqlite3_context *context, int argc, sqlite3_value **argv){
396 CountCtx *p;
397 p = sqlite3_aggregate_context(context, sizeof(*p));
398 if( (argc==0 || SQLITE_NULL!=sqlite3_value_type(argv&#91;0&#93;)) && p ){
399 p->n++;
400 }
401 }
402 static void countFinalize(sqlite3_context *context){
403 CountCtx *p;
404 p = sqlite3_aggregate_context(context, 0);
405 sqlite3_result_int64(context, p ? p->n : 0);
406 }
407 </pre></div>
408
409 <p>Recall that there are two versions of the count() aggregate.
410 With zero arguments, count() returns a count of the number of rows.
411 With one argument, count() returns the number of times that the
412 argument was non-NULL.
413
414 </p><p>The countStep() callback is invoked once for each row in the aggregate.
415 As you can see, the count is incremented if either there are no arguments,
416 or if the one argument is not NULL.
417
418 </p><p>The step function for an aggregate should always begin with a call
419 to the <a href="c3ref/aggregate_context.html">sqlite3_aggregate_context()</a> routine to fetch the persistent
420 state of the aggregate function. On the first invocation of the step()
421 function, the aggregate context is initialized to a block of memory
422 that is N bytes in size, where N is the second parameter to
423 sqlite3_aggregate_context() and that memory is zeroed. On all subsequent
424 calls to the step() function, the same block of memory is returned.
425 Except, sqlite3_aggregate_context() might return NULL in the case of
426 an out-of-memory error, so aggregate functions should be prepared to
427 deal with that case.
428
429 </p><p>After all rows are processed the countFinalize() routine is called
430 exactly once. This routine computes the final result and invokes
431 one of the <a href="c3ref/result_blob.html">sqlite3_result()</a> family of functions
432 to set the final result. The aggregate context will be freed automatically
433 by SQLite, though the xFinalize() routine must clean up any substructure
434 associated with the aggregate context before it returns. If the xStep()
435 method is called one or more times, then SQLite guarantees that the
436 xFinal() method will be called at once, even if the query aborts.
437
438 </p><h3 id="the_window_function_callbacks"><span>2.3.3. </span>The Window Function Callbacks</h3>
439
440 <p><a href="windowfunctions.html">Window functions</a> use the same xStep() and xFinal() callbacks that
441 aggregate functions use, plus two others: <b>xValue</b> and <b>xInverse</b>.
442 See the documentation on
443 <a href="windowfunctions.html#udfwinfunc">application-defined window functions</a> for further details.
444
445 </p><h3 id="examples"><span>2.3.4. </span>Examples</h3>
446
447 <p>There are dozens and dozens of SQL function implementations scattered
448 throughout the SQLite source code that can be used as example applications.
449 The built-in SQL functions use the same interface as application-defined
450 SQL functions, so built-in functions can be used as examples too.
451 Search for "sqlite3_context" in the SQLite source code to find examples.
452
453 <a name="sec"></a>
454
455 </p><h1 id="security_implications"><span>3. </span>Security Implications</h1>
456
457 <p>
458 Application-defined SQL functions can become security vulnerabilities if
459 not carefully managed. Suppose, for example, an application defines
460 a new "system(X)" SQL function that runs its argument X as a command and
461 returns the integer result code. Perhaps the implementation is like this:
462
463 </p><div class="codeblock"><pre>static void systemFunc(
464 sqlite3_context *context,
465 int argc,
466 sqlite3_value **argv
467 ){
468 const char *zCmd = (const char*)sqlite3_value_text(argv&#91;0&#93;);
469 if( zCmd!=0 ){
470 int rc = system(zCmd);
471 sqlite3_result_int(context, rc);
472 }
473 }
474 </pre></div>
475
476 <p>
477 This is a function with powerful side-effects. Most programmers would
478 be naturally cautious about using it, but probably would not see the
479 harm in merely having it available. But there is great risk in merely
480 defining such a function, even if the application itself never invokes
481 it!
482
483 </p><p>
484 Suppose the application normally does a query against table TAB1
485 when it starts up. If an attacker can gain access to the database
486 file and modify the schema like this:
487
488 </p><div class="codeblock"><pre>ALTER TABLE tab1 RENAME TO tab1_real;
489 CREATE VIEW tab1 AS
490 SELECT * FROM tab1_real
491 WHERE system('rm -rf *') IS NOT NULL;
492 </pre></div>
493
494 <p>
495 Then, when the application attempts to open the database, register the
496 system() function, then run an innocent query against the "tab1" table,
497 it instead deletes all the files in its working directory. Yikes!
498
499 </p><p>
500 To prevent this kind of mischief, applications that create their own
501 custom SQL functions should take one or more of the following safety
502 precautions. The more precautions taken the better:
503
504 </p><ol>
505 <li><p>
506 Invoke <a href="c3ref/db_config.html">sqlite3_db_config</a>(db,<a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigtrustedschema">SQLITE_DBCONFIG_TRUSTED_SCHEMA</a>,0,0)
507 on each <a href="c3ref/sqlite3.html">database connection</a> as soon as it is opened.
508 This prevents application-defined functions from being used in places
509 where an attacker might be able to surreptiously invoke them by modifying
510 a database schema:
511 </p><ul>
512 <li> In VIEWs.
513 </li><li> In TRIGGERs.
514 </li><li> In CHECK constraints of a table definition.
515 </li><li> In DEFAULT constraints of a table definition.
516 </li><li> In the definitions of generated columns.
517 </li><li> In the expression part of an index on an expression.
518 </li><li> In the WHERE clause of a partial index.
519 </li></ul>
520 <p>
521 To put it another way, this setting requires that application-defined
522 functions only be run directly by top-level SQL invoked from the application
523 itself, not as a consequence of doing some other innocent-looking query.
524
525 </p></li><li><p>
526 Use the <a href="pragma.html#pragma_trusted_schema">PRAGMA trusted_schema=OFF</a> SQL statement to disable trusted
527 schema. This has the same effect as the previous bullet, but does not
528 require the use of C-code and hence can be performed in programs written
529 in another programming language and that do not have access to SQLite
530 C-language APIs.
531
532 </p></li><li><p>
533 Compile SQLite using the <a href="compile.html#trusted_schema">-DSQLITE_TRUSTED_SCHEMA=0</a> compile-time option.
534 This makes SQLite distrust application-defined functions inside of
535 the schema by default.
536
537 </p></li><li><p>
538 If any application-defined SQL functions have potentially dangerous
539 side-effects, or if they could potentially leak sensitive information
540 to an attacker if misused, then tag those functions using the
541 <a href="c3ref/c_deterministic.html#sqlitedirectonly">SQLITE_DIRECTONLY</a> option on the "enc" parameter. This means
542 that the function can never be run from schema-code even if the
543 trusted-schema option is on.
544
545 </p></li><li><p>
546 Never tag an application-defined SQL function with <a href="c3ref/c_deterministic.html#sqliteinnocuous">SQLITE_INNOCUOUS</a>
547 unless you really need to and you have checked the implementation closely
548 and are certain that it can do no harm even if it falls under the
549 control of an attacker.
550 </p></li></ol>
551 <p align="center"><small><i>This page was last updated on 2026-04-21 10:28:59Z </small></i></p>
552