1
2
3
4
5
6
7
8
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
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
|
<!DOCTYPE html>
<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link href="sqlite.css" rel="stylesheet">
<title>Application-Defined SQL Functions</title>
<!-- path= -->
</head>
<body>
<div class=nosearch>
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
</a>
<div><!-- IE hack to prevent disappearing logo --></div>
<div class="tagline desktoponly">
Small. Fast. Reliable.<br>Choose any three.
</div>
<div class="menu mainmenu">
<ul>
<li><a href="index.html">Home</a>
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
<li class='wideonly'><a href='about.html'>About</a>
<li class='desktoponly'><a href="docs.html">Documentation</a>
<li class='desktoponly'><a href="download.html">Download</a>
<li class='wideonly'><a href='copyright.html'>License</a>
<li class='desktoponly'><a href="support.html">Support</a>
<li class='desktoponly'><a href="prosupport.html">Purchase</a>
<li class='search' id='search_menubutton'>
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
</ul>
</div>
<div class="menu submenu" id="submenu">
<ul>
<li><a href='about.html'>About</a>
<li><a href='docs.html'>Documentation</a>
<li><a href='download.html'>Download</a>
<li><a href='support.html'>Support</a>
<li><a href='prosupport.html'>Purchase</a>
</ul>
</div>
<div class="searchmenu" id="searchmenu">
<form method="GET" action="search">
<select name="s" id="searchtype">
<option value="d">Search Documentation</option>
<option value="c">Search Changelog</option>
</select>
<input type="text" name="q" id="searchbox" value="">
<input type="submit" value="Go">
</form>
</div>
</div>
<script>
function toggle_div(nm) {
var w = document.getElementById(nm);
if( w.style.display=="block" ){
w.style.display = "none";
}else{
w.style.display = "block";
}
}
function toggle_search() {
var w = document.getElementById("searchmenu");
if( w.style.display=="block" ){
w.style.display = "none";
} else {
w.style.display = "block";
setTimeout(function(){
document.getElementById("searchbox").focus()
}, 30);
}
}
function div_off(nm){document.getElementById(nm).style.display="none";}
window.onbeforeunload = function(e){div_off("submenu");}
/* Disable the Search feature if we are not operating from CGI, since */
/* Search is accomplished using CGI and will not work without it. */
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
document.getElementById("search_menubutton").style.display = "none";
}
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
var antiRobot = 0;
function antiRobotGo(){
if( antiRobot!=3 ) return;
antiRobot = 7;
var j = document.getElementById("mtimelink");
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
}
function antiRobotDefense(){
document.body.onmousedown=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousedown=null;
}
document.body.onmousemove=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousemove=null;
}
setTimeout(function(){
antiRobot |= 1;
antiRobotGo();
}, 100)
antiRobotGo();
}
antiRobotDefense();
</script>
<div class=fancy>
<div class=nosearch>
<div class="fancy_title">
Application-Defined SQL Functions
</div>
<div class="fancy_toc">
<a onclick="toggle_toc()">
<span class="fancy_toc_mark" id="toc_mk">►</span>
Table Of Contents
</a>
<div id="toc_sub"><div class="fancy-toc1"><a href="#executive_summary">1. Executive Summary</a></div>
<div class="fancy-toc1"><a href="#defining_new_sql_functions">2. Defining New SQL Functions</a></div>
<div class="fancy-toc2"><a href="#common_parameters">2.1. Common Parameters</a></div>
<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>
<div class="fancy-toc2"><a href="#callbacks">2.3. Callbacks</a></div>
<div class="fancy-toc3"><a href="#the_scalar_function_callback">2.3.1. The Scalar Function Callback</a></div>
<div class="fancy-toc3"><a href="#the_aggregate_function_callbacks">2.3.2. The Aggregate Function Callbacks</a></div>
<div class="fancy-toc3"><a href="#the_window_function_callbacks">2.3.3. The Window Function Callbacks</a></div>
<div class="fancy-toc3"><a href="#examples">2.3.4. Examples</a></div>
<div class="fancy-toc1"><a href="#security_implications">3. Security Implications</a></div>
</div>
</div>
<script>
function toggle_toc(){
var sub = document.getElementById("toc_sub")
var mk = document.getElementById("toc_mk")
if( sub.style.display!="block" ){
sub.style.display = "block";
mk.innerHTML = "▼";
} else {
sub.style.display = "none";
mk.innerHTML = "►";
}
}
</script>
</div>
<h1 id="executive_summary"><span>1. </span>Executive Summary</h1>
<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 <a href="loadext.html">loadable extensions</a>.
</p><p>Application-defined or custom SQL functions are created using the
<a href="c3ref/create_function.html">sqlite3_create_function()</a> family of interfaces.
Custom SQL functions can be scalar functions, aggregate functions,
or <a href="windowfunctions.html">window functions</a>.
Custom SQL functions can have any number of arguments from 0 up to
<a href="limits.html#max_function_arg">SQLITE_MAX_FUNCTION_ARG</a>.
The <a href="c3ref/create_function.html">sqlite3_create_function()</a> interface specifies callbacks that are
invoked to carry out the processing for the new SQL function.
</p><p>SQLite also supports custom <a href="vtab.html#tabfunc2">table-valued functions</a>, but they are
implemented by a different mechanism that is not covered in this document.
</p><h1 id="defining_new_sql_functions"><span>2. </span>Defining New SQL Functions</h1>
<p>
The <a href="c3ref/create_function.html">sqlite3_create_function()</a> 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.
</p><ul>
<li><p><b><a href="c3ref/create_function.html">sqlite3_create_function()</a></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.
</p></li><li><p><b><a href="c3ref/create_function.html">sqlite3_create_function16()</a></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.
</p></li><li><p><b><a href="c3ref/create_function.html">sqlite3_create_function_v2()</a></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 <a href="c3ref/user_data.html">sqlite3_user_data()</a> 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.
</p></li><li><p><b><a href="c3ref/create_function.html">sqlite3_create_window_function()</a></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 <a href="windowfunctions.html">window function</a> definitions.
</p></li></ul>
<h2 id="common_parameters"><span>2.1. </span>Common Parameters</h2>
<p>Many of the parameters passed to the <a href="c3ref/create_function.html">sqlite3_create_function()</a>
family of interfaces are common across the entire family.
</p><ol>
<li><p><b>db</b> →
The 1st parameter is always a pointer to the <a href="c3ref/sqlite3.html">database connection</a>
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.
</p></li><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 <a href="c3ref/create_function.html">sqlite3_create_function16()</a>.
</p><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 <a href="rescode.html#misuse">SQLITE_MISUSE</a> 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><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
<a href="limits.html#max_function_arg">SQLITE_MAX_FUNCTION_ARG</a> (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 <a href="limits.html#max_function_arg">SQLITE_MAX_FUNCTION_ARG</a>.
</p></li><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:
</p><ul>
<li> <a href="c3ref/c_any.html">SQLITE_UTF8</a>
</li><li> <a href="c3ref/c_any.html">SQLITE_UTF16BE</a>
</li><li> <a href="c3ref/c_any.html">SQLITE_UTF16LE</a>
</li></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:
</p><ul>
<li> <a href="c3ref/c_deterministic.html#sqlitedeterministic">SQLITE_DETERMINISTIC</a>
</li><li> <a href="c3ref/c_deterministic.html#sqlitedirectonly">SQLITE_DIRECTONLY</a>
</li><li> <a href="c3ref/c_deterministic.html#sqliteinnocuous">SQLITE_INNOCUOUS</a>
</li><li> <a href="c3ref/c_deterministic.html#sqlitesubtype">SQLITE_SUBTYPE</a>
</li></ul>
<p>
Additional bits may be added in future versions of SQLite.
</p></li><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.
</p></li></ol>
<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>
<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><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><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.
</p><h2 id="callbacks"><span>2.3. </span>Callbacks</h2>
<p>
SQLite evaluates an SQL function by invoking callback routines.
</p><h3 id="the_scalar_function_callback"><span>2.3.1. </span>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:
</p><div class="codeblock"><pre>static void noopfunc(
sqlite3_context *context,
int argc,
sqlite3_value **argv
){
assert( argc==1 );
sqlite3_result_value(context, argv[0]);
}
</pre></div>
<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:
<div class='columns' style='columns: 15em auto;'>
<ul style='padding-top:0;'>
<li><a href='c3ref/aggregate_context.html'>sqlite3_aggregate_context</a></li>
<li><a href='c3ref/context_db_handle.html'>sqlite3_context_db_handle</a></li>
<li><a href='c3ref/get_auxdata.html'>sqlite3_get_auxdata</a></li>
<li><a href='c3ref/result_blob.html'>sqlite3_result_blob</a></li>
<li><a href='c3ref/result_blob.html'>sqlite3_result_blob64</a></li>
<li><a href='c3ref/result_blob.html'>sqlite3_result_double</a></li>
<li><a href='c3ref/result_blob.html'>sqlite3_result_error</a></li>
<li><a href='c3ref/result_blob.html'>sqlite3_result_error16</a></li>
<li><a href='c3ref/result_blob.html'>sqlite3_result_error_code</a></li>
<li><a href='c3ref/result_blob.html'>sqlite3_result_error_nomem</a></li>
<li><a href='c3ref/result_blob.html'>sqlite3_result_error_toobig</a></li>
<li><a href='c3ref/result_blob.html'>sqlite3_result_int</a></li>
<li><a href='c3ref/result_blob.html'>sqlite3_result_int64</a></li>
<li><a href='c3ref/result_blob.html'>sqlite3_result_null</a></li>
<li><a href='c3ref/result_blob.html'>sqlite3_result_pointer</a></li>
<li><a href='c3ref/result_subtype.html'>sqlite3_result_subtype</a></li>
<li><a href='c3ref/result_blob.html'>sqlite3_result_text</a></li>
<li><a href='c3ref/result_blob.html'>sqlite3_result_text16</a></li>
<li><a href='c3ref/result_blob.html'>sqlite3_result_text16be</a></li>
<li><a href='c3ref/result_blob.html'>sqlite3_result_text16le</a></li>
<li><a href='c3ref/result_blob.html'>sqlite3_result_text64</a></li>
<li><a href='c3ref/result_blob.html'>sqlite3_result_value</a></li>
<li><a href='c3ref/result_blob.html'>sqlite3_result_zeroblob</a></li>
<li><a href='c3ref/result_blob.html'>sqlite3_result_zeroblob64</a></li>
<li><a href='c3ref/get_auxdata.html'>sqlite3_set_auxdata</a></li>
<li><a href='c3ref/user_data.html'>sqlite3_user_data</a></li>
</ul>
</div>
</p><p>The <a href="c3ref/result_blob.html">sqlite3_result() family of functions</a> 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><p>The <a href="c3ref/user_data.html">sqlite3_user_data()</a> routine returns a copy of the <b>pArg</b>
pointer that was given to <a href="c3ref/create_function.html">sqlite3_create_function()</a> when the SQL
function was created.
</p><p>The <a href="c3ref/context_db_handle.html">sqlite3_context_db_handle()</a> routine returns a pointer to the
<a href="c3ref/sqlite3.html">database connection</a> object.
</p><p>The <a href="c3ref/aggregate_context.html">sqlite3_aggregate_context()</a> routine is used only in the
implementations of aggregate and window functions. Scalar functions
may not use <a href="c3ref/aggregate_context.html">sqlite3_aggregate_context()</a>. The <a href="c3ref/aggregate_context.html">sqlite3_aggregate_context()</a>
function is included in the interface list only for completeness.
</p><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 <a href="c3ref/value.html">sqlite3_value</a> object.
Specific C-language values can be extracted from this object using
the <a href="c3ref/value_blob.html">sqlite3_value() family of interfaces</a>.
</p><h3 id="the_aggregate_function_callbacks"><span>2.3.2. </span>The Aggregate Function Callbacks</h3>
<p>Aggregate SQL functions are implemented by using two callback
functions, <b>xStep</b> and <b>xFinal</b>. The xStep() function
is called for each row of the aggregate and the xFinal() function
is invoked to compute the final answer at the end.
The following (slightly simplified) version of the built-in
count() function illustrates:
</p><div class="codeblock"><pre>typedef struct CountCtx CountCtx;
struct CountCtx {
i64 n;
};
static void countStep(sqlite3_context *context, int argc, sqlite3_value **argv){
CountCtx *p;
p = sqlite3_aggregate_context(context, sizeof(*p));
if( (argc==0 || SQLITE_NULL!=sqlite3_value_type(argv[0])) && p ){
p->n++;
}
}
static void countFinalize(sqlite3_context *context){
CountCtx *p;
p = sqlite3_aggregate_context(context, 0);
sqlite3_result_int64(context, p ? p->n : 0);
}
</pre></div>
<p>Recall that there are two versions of the count() aggregate.
With zero arguments, count() returns a count of the number of rows.
With one argument, count() returns the number of times that the
argument was non-NULL.
</p><p>The countStep() callback is invoked once for each row in the aggregate.
As you can see, the count is incremented if either there are no arguments,
or if the one argument is not NULL.
</p><p>The step function for an aggregate should always begin with a call
to the <a href="c3ref/aggregate_context.html">sqlite3_aggregate_context()</a> routine to fetch the persistent
state of the aggregate function. On the first invocation of the step()
function, the aggregate context is initialized to a block of memory
that is N bytes in size, where N is the second parameter to
sqlite3_aggregate_context() and that memory is zeroed. On all subsequent
calls to the step() function, the same block of memory is returned.
Except, sqlite3_aggregate_context() might return NULL in the case of
an out-of-memory error, so aggregate functions should be prepared to
deal with that case.
</p><p>After all rows are processed the countFinalize() routine is called
exactly once. This routine computes the final result and invokes
one of the <a href="c3ref/result_blob.html">sqlite3_result()</a> family of functions
to set the final result. The aggregate context will be freed automatically
by SQLite, though the xFinalize() routine must clean up any substructure
associated with the aggregate context before it returns. If the xStep()
method is called one or more times, then SQLite guarantees thta the
xFinal() method will be called at once, even if the query aborts.
</p><h3 id="the_window_function_callbacks"><span>2.3.3. </span>The Window Function Callbacks</h3>
<p><a href="windowfunctions.html">Window functions</a> use the same xStep() and xFinal() callbacks that
aggregate functions use, plus two others: <b>xValue</b> and <b>xInverse</b>.
See the documentation on
<a href="windowfunctions.html#udfwinfunc">application-defined window functions</a> for further details.
</p><h3 id="examples"><span>2.3.4. </span>Examples</h3>
<p>There are dozens and dozens of SQL function implementations scattered
throughout the SQLite source code that can be used as example applications.
The built-in SQL functions use the same interface as application-defined
SQL functions, so built-in functions can be used as examples too.
Search for "sqlite3_context" in the SQLite source code to find examples.
<a name="sec"></a>
</p><h1 id="security_implications"><span>3. </span>Security Implications</h1>
<p>
Application-defined SQL functions can become security vulnerabilities if
not carefully managed. Suppose, for example, an application defines
a new "system(X)" SQL function that runs its argument X as a command and
returns the integer result code. Perhaps the implementation is like this:
</p><div class="codeblock"><pre>static void systemFunc(
sqlite3_context *context,
int argc,
sqlite3_value **argv
){
const char *zCmd = (const char*)sqlite3_value_text(argv[0]);
if( zCmd!=0 ){
int rc = system(zCmd);
sqlite3_result_int(context, rc);
}
}
</pre></div>
<p>
This is a function with powerful side-effects. Most programmers would
be naturally cautious about using it, but probably would not see the
harm in merely having it available. But there is great risk in merely
defining such a function, even if the application itself never invokes
it!
</p><p>
Suppose the application normally does a query against table TAB1
when it starts up. If an attacker can gain access to the database
file and modify the schema like this:
</p><div class="codeblock"><pre>ALTER TABLE tab1 RENAME TO tab1_real;
CREATE VIEW tab1 AS SELECT * FROM tab1 WHERE system('rm -rf *') IS NOT NULL;
</pre></div>
<p>
Then, when the application attempts to open the database, register the
system() function, then run an innocent query against the "tab1" table,
it instead deletes all the files in its working directory. Yikes!
</p><p>
To prevent this kind of mischief, applications that create their own
custom SQL functions should take one or more of the following safety
precautions. The more precautions taken the better:
</p><ol>
<li><p>
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)
on each <a href="c3ref/sqlite3.html">database connection</a> as soon as it is opened.
This prevents application-defined functions from being used in places
where an attacker might be able to surreptiously invoke them by modifying
a database schema:
</p><ul>
<li> In in VIEWs.
</li><li> In in TRIGGERSs.
</li><li> In CHECK constraints of a table definition.
</li><li> In DEFAULT constraints of a table definition.
</li><li> In the definitions of generated columns.
</li><li> In the expression part of an index on an expression.
</li><li> In the WHERE clause of a partial index.
</li></ul>
<p>
To put it another way, this setting requires that application-defined
functions only be run directly by top-level SQL invoked from the application
itself, not as a consequence of doing some other innocent-looking query.
</p></li><li><p>
Use the <a href="pragma.html#pragma_trusted_schema">PRAGMA trusted_schema=OFF</a> SQL statement to disable trusted
schema. This has the same effect as the previous bullet, but does not
require the use of C-code and hence can be performed in programs written
in another programming language and that do not have access SQLite
C-language APIs.
</p></li><li><p>
Compile SQLite using the <a href="compile.html#trusted_schema">-DSQLITE_TRUSTED_SCHEMA=0</a> compile-time option.
This make SQLite distrust application-defined functions inside of
the schema by default.
</p></li><li><p>
If any application-defined SQL functions have potentially dangerous
side-effects, or if they could potentially leak sensitive information
to an attacker if misused, then tag those functions using the
<a href="c3ref/c_deterministic.html#sqlitedirectonly">SQLITE_DIRECTONLY</a> option on the "enc" parameter. This means
that the function can never be run from schema-code even if the
trusted-schema option is on.
</p></li><li><p>
Never tag an application-defined SQL function with <a href="c3ref/c_deterministic.html#sqliteinnocuous">SQLITE_INNOCUOUS</a>
unless you really need to and you have checked the implementation closely
and are certain that it can do no harm even if it falls under the
control of an attacker.
</p></li></ol>
<p align="center"><small><i>This page last modified on <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink" data-href="https://sqlite.org/docsrc/finfo/pages/appfunc.in?m=10c9a7ddcd203576e">2020-09-20 12:35:50</a> UTC </small></i></p>
|
