Documentation Source Text

Check-in [264d9eef33]
Login

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: 264d9eef33e14a76ba90adcd3b0f6a657bc9ce2a2868712faf821c1d81ae8efe
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
Unified Diff Ignore Whitespace Patch
Changes to pages/appfunc.in.
9
10
11
12
13
14
15






16


17
18
19





20













































































































































































































21
22








23
24
25
26
27
28
<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>






<p><i>TBD....</i>














































































































































































































<h1>Callbacks</h1>









<p><i>TBD....</i>

<h1>Security Implications</h1>

<p><i>TBD....</i>







>
>
>
>
>
>

>
>



>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

|
>
>
>
>
>
>
>
>

|




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> &rarr;
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> &rarr;
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> &rarr;
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> &rarr;
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> &rarr;
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> &rarr;
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> &rarr;
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> &rarr;
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> &rarr;
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&#91;0&#93;);
}
</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
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
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 accept untrusted database files should do the following:






<ol>
<li value="7"><p>
If an 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:
<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.



</ol>
<p>Any one of the above actions is sufficient to protect the application
from malicious constructs added into the database schema.  The only reason
to do two or more of the above is redundancy.
<p>To facilitate testing, applications should also consider adding an
easter-egg or other testing interface that displays the value of
[PRAGMA trusted_schema] just to confirm that it really is turned off.
</li>

<li><p>
If the application does not use triggers or views should 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>


<li><p>
[application-defined SQL functions|Custom SQL functions] that
have side-effects or that might be used by an attacker to leak
information about the system should tags those SQL functions with
[SQLITE_DIRECTONLY].  This will prohibit the use of those functions
inside triggers and views or other schema constructs, and thus
prevent an attacker from surreptiously invoking the function by
including it in part of the schema of a database file.

<li><p>
[custom virtual tables|Custom virtual tables] that

have side-effects or that might be used by an attacker to leak
information about the system should tags those virtual tables with
[SQLITE_VTAB_DIRECTONLY].  This will prohibit the use of those
virtual tables inside non-TEMP triggers and views, and thus
prevent an attacker from surreptiously querying the virtual table
using triggers and views in the database schema.


<li><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.


<li><p>
Do not enable memory-mapped I/O.
In other words, make sure that [PRAGMA mmap_size=0].
</ol>

<p>Even if the application does not deliberately accept database files
from untrusted sources, beware of attacks in which a local database file
is surreptitiously altered to contain harmful content.

<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







|








>
>
|
>
>
>
>
>



|




|






>
>
>

<
<
<
<
<
<



|






>

|
<
<
<
<
<
<
|
|
<
<
>
|
<
<
|
<
<

>
|







>






<
<
<







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