Documentation Source Text

Check-in [9bf45f99e1]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Improved documentation on the pointer-passing interfaces.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 9bf45f99e10d46d2a5c4f6b6a1cfde620e5d132999af64207403784dc2538dfc
User & Date: drh 2017-07-23 20:57:48
Context
2017-07-24
11:34
Typos and clarifications to the pointer-passing document. check-in: 8d02ad5577 user: drh tags: trunk
2017-07-23
20:57
Improved documentation on the pointer-passing interfaces. check-in: 9bf45f99e1 user: drh tags: trunk
2017-07-21
19:39
Further enhancements to the change log for 3.20.0. check-in: 4e0e4a8084 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Added pages/bindptr.in.

































































































































































































































































































































































































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
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
<title>Pointer Passing Interfaces</title>
<tcl>hd_keywords {pointer passing interfaces}
</tcl>

<fancy_format>

<h1>Overview</h1>

<p>
Three new "_pointer()" interfaces were added on to SQLite on
[https://sqlite.org/src/timeline?c=72de49f204277|2017-07-13]:
<ul>
<li> [sqlite3_bind_pointer()],
<li> [sqlite3_result_pointer()], and
<li> [sqlite3_value_pointer()].
</ul>

<p>Questions and confusion quickly arose
on the [mailing lists] about the purpose behind these new interfaces,
why they were introduced, and what problem they solve.  This essay attempts
to answer those questions and clear up the confusion.

<h1>A Brief History Of Pointer Passing In SQLite</h1>

<p>
It is sometimes convenient for SQLite extensions to
communicatin non-SQL values between subcomponents or between the extension
and the application.  Some examples:

<ul>
<li><p>
In the [FTS3] extension, the MATCH operator (that does the full-text search)
needs to communicate details of matching entrys to the [snippet()], [offsets()],
and [matchinfo()] functions so that those functions can convert the details
of the match into useful output.

<li><p>
In order for an application to 
[Extending FTS5|add new extensions to FTS5], such as new tokenizers, 
the application needs a pointer to the "fts5_api" object.

<li><p>
In the [carray|CARRAY extension], the application needs to tell the 
extension the
location of a C-language array that contains the data for the table-valued
function that the extension implements.
</ul>

<p>
The traditional way of communicating this information was to transform a
C-language pointer into a BLOB or a 64-bit integer, then move that BLOB or
integer through SQLite using the usual interfaces like
[sqlite3_bind_blob()], [sqlite3_result_blob()], [sqlite3_value_blob()] or
the integer equivalents.

<h2>Upping The Thread Level</h2>

<p>
Passing around pointers as if they were integers or BLOBs is easy,
effective, and works well in an environment where the application
components are all friendly toward one another.  However, passing pointers
as integers and BLOBs allows hostile SQL text to forge invalid pointers that
can carry out mischief.

<p>
For example, the first argument to the [snippet()] function is suppose to
be a special column of the FTS3 table that contains a pointer to an fts3cursor
object that contains information about the current full text search match.
That pointer was formerly passed as a BLOB.  
For example, if the FTS3 table is named "t1" and has a column named "cx",
one might write:

<codeblock>
SELECT snippet(t1) FROM t1 WHERE cx MATCH $pattern;
</codeblock>

<p>
But if a hacker is able to run arbitrary SQL, he might run a slightly
different query, like this:

<codeblock>
SELECT hex(t1) FROM t1 WHERE cx MATCH $pattern;
</codeblock>

<p>
Because the pointer is passed in the t1.t1 column as a BLOB (in older
versions of SQLite), such a query would have shown the value of the
pointer in hex.  The attacker could then modify that pointer to try to
get the snippet() function to modify memory in some other part of 
the application address space instead of the fts3cursor object it 
was suppose to be operating on:

<codeblock>
SELECT snippet(x'6092310100000000') FROM t1 WHERE cx MATCH $pattern;
</codeblock>

<p>
Historically, this was not considered a threat.  The argument was that if
a hostile agent is able to inject arbitrary SQL text into the application,
then that agent is already in full control of the application, so
letting the hostile agent forge a pointer is not giving the agent
any capability he does not already have.

<p>
For most cases, it is true that potential attackers have no way of injecting
arbitrary SQL, and so most uses of SQLite are immute to the attack above.
But there are some notable exceptions.  To wit:

<ul>
<li><p>
The [https://en.wikipedia.org/wiki/Web_SQL_Database|WebSQL] interface
to webkit allowed any webpage to to run arbitrary SQL in the browser
for Chrome and Safari.  That arbitrary SQL was suppose to be run inside
a sandbox where it could do not harm even if exploited, but that sandbox
turned out to be less secure than people supposed.  In the spring of 2017, 
one team of hackers was able to root an iMac using a long sequence of 
exploits, one of which involved corrupting the pointers passed as BLOB 
values to the snippet() FTS3 function of an SQLite database running via
the WebSQL interface inside of Safari.

<li><p>
On Android, we are told, there are many services that will blindly 
run arbitrary SQL that is passed to them by untrustworthy apps
that have been downloaded from dodgy corners of the internet.
Android services are suppose to be more guarded about running SQL
from unvetted sources.  This author does not have any specific examples
to the contrary, but he has heard rumors that they exist.  Even if
all Android services are more careful and properly vet all the SQL
they run, it would be difficult to audit them
all in order to verify that they are safe.  Hence, security-minded people
are keen to ensure that no exploits are possible by passing arbitrary
SQL text.

<li><p>
The [https://www.fossil-scm.org/|Fossil] version control system (designed
and written for the purpose of supporting SQLite development) allows
mildly trusted users to enter arbitrary SQL for generating trouble-ticket
reports.  That SQL is sanitized using the
[sqlite3_set_authorizer()] interface, and no exploits have ever been
found.  But this is an example of potentially hostile agents being able to
inject arbitrary SQL into the system.
</ul>

<h2>Preventing Forged Pointers</h2>

<p>
The first attempt at closing security gaps in pointer passing was to
prevent pointer values from being forged.  This was accomplished by
having the sender attach a subtype to each pointer using
[sqlite3_result_subtype()] and having the receiver verify that subtype
using [sqlite3_value_subtype()] and reject pointers that had an incorrect
subtype.  Since there is no way to attach a subtype to a result using
pure SQL, this prevents pointers from being forged using SQL.  The only
way to send a pointer is to use C code.  If an attacker can set a subtype,
then he is also able to forge pointer without the help of SQLite.

<p>
Using subtypes to identify valid pointers prevented the WebSQL exploit.
But it turned out to be an incomplete solution.

<tcl>hd_fragment ptrleak {pointer leak} {pointer leaks}</tcl>
<h2>Pointer Leaks</h2>

<p>
The use of subtypes on pointers prevented pointer forgery using
pure SQL.  But subtypes do nothing to prevent an attacker from reading
the values of pointers.  In other words, subtypes on pointer values
prevents attacks using SQL statements like this:

<codeblock>
SELECT snippet(x'6092310100000000') FROM t1 WHERE cx MATCH $pattern;
</codeblock>

<p>
The BLOB argument to snippet() does not have the correct subtype, so the
snippet function ignores it, makes no changes to any data structures,
and harmlessly returns NULL.

<p>
But the use of subtypes does nothing to prevent the value of a
pointer from being read using SQL code like this:

<codeblock>
SELECT hex(t1) FROM t1 WHERE cx MATCH $pattern;
</codeblock>

<p>
What harm could come of that, you ask?  The SQLite developers (including
this author) wondered the same thing.  But then security researchers
pointed out that knowledge of pointers can help attackers to circumvent
address-space randomization defenses.  This is called a "pointer leak".
A pointer leak is not itself a vulnerability, but it can aid an attacker
in effectively exploiting other vulnerabilities.

<h1>The New Pointer-Passing Interfaces</h1>

<p>
Allowing extension components to pass private information to one another
securely and without introducing pointer leaks requires new interfaces:

<ul>
<li><b>[sqlite3_bind_pointer](S,I,P,T)</b> &rarr;
Bind pointer P of type T to the I-th parameter of prepared statement S.
<li><b>[sqlite3_result_pointer](C,P,T)</b> &rarr;
Return pointer P of type T as the argument of function C.
<li><b>[sqlite3_value_pointer](V,T)</b> &rarr;
Return the pointer of type T associated with value V, or if V has no
associated pointer, or if the pointer on V is of a type different from
T, then return NULL.
</ul>

<p>
To SQL, the values created by [sqlite3_bind_pointer()] and 
[sqlite3_result_pointer()] are indistinguishable from NULL.  An
SQL statement that tries to use the [hex()] function to read the
value of a pointer will get an SQL NULL answer.  The only way to
discover whether or not a value has an associated pointer is to
use the [sqlite3_value_pointer()] interface with the appropriate
type string T. 

<p>
Pointer values generated by [sqlite3_bind_pointer()] and
[sqlite3_result_pointer()] cannot be generated by pure SQL, only
by C-code extensions.  And so it is not possible for SQL to forge
a pointers.

<p>
Pointer values generated [sqlite3_bind_pointer()] and
[sqlite3_result_pointer()] cannot be read by pure SQL, only
by C-code extensions.  And so it is not possible for SQL to leak
the value of pointers.

<p>
In this way the new pointer-passing interface seems to solve all of the
security problems associated with passing pointer values from one
extension to another in SQLite.

<h2>Pointer Types</h2>

<p>
The "pointer type" which is the last parameter to [sqlite3_bind_pointer()],
[sqlite3_result_pointer()], and [sqlite3_value_pointer()] is used to prevent
pointers intended for one extension from being redirected to a different
extension.  For example, without the use of pointer types, an attacker 
could still get access to pointer information in a system that included 
by the [FTS3] and the [carray|CARRAY extension] using SQL like this:

<codeblock>
SELECT ca.value FROM t1, carray(t1,10) AS ca WHERE cx MATCH $pattern
</codeblock>

<p>
In the statement above, the FTS3 cursor pointer generated by the
MATCH operator is send into the carray() table-valued function instead
of its intended recipient snippet().  The carray() function treats the
pointer as a pointer to an array of integers and returns each integer
one by one, thus leaking the content of the FTS3 cursor object.  Since
the FTS3 cursor object contains pointers to other objects, the statement
above would be a pointer leak.

<p>
Except, the statement above does not work, thanks to pointer types.
The pointer generated by the MATCH operator has a type of "fts3cursor"
but the carray() function expects to receives a pointer of type "carray".
Because the pointer type on the [sqlite3_result_pointer()] does not match
the pointer type on the [sqlite3_value_pointer()] call, 
[sqlite3_value_pointer()] returns NULL in carray() and thus signals
the CARRAY extension that it has been passed and invalid pointer.

<p>
Pointer types are static strings, which ideally should be string literals
embedded directly in the SQLite API call, not parameters passed in from
other functions.  Consideration was giving to using integer values as
the pointer type, but static strings provides a much larger name space
which reduces the chance of accidental type-name collisions between
unrelated extensions.

<p>
Because pointer types must be static strings, and because string values
in SQLite are dynamic strings, that means that SQL values cannot be used
as a pointer type.  This prevents misguided developers from creating a
new SQL function that can manufacture pointer values directly from SQL.  
Such a function, if possible to write, would undermine the security of
the pointer-passing APIs.  Thus, the requirement that pointer types be 
static strings is an effective deterrent against writing such 
security-defeating extension functions.

<h1>Restrictions On The Use of Pointer Values</h1>

<p>
The pointers that piggy-back on SQL NULL values using the
[sqlite3_bind_pointer()], [sqlite3_result_pointer()], and
[sqlite3_value_pointer()] interface are transient and ephemeral.
The pointers are never written into the database.  The pointers
will not survive sorting.  The latter fact is why there is no
sqlite3_column_pointer() interface, since it is impossible to
predict whether or not the query planner will insert a sort operating
prior to returning a value from a query, so it would be impossible to
know if a pointer value insert into a query by
[sqlite3_bind_pointer()] or [sqlite3_result_pointer()] would survive
through to the result set.

<p>
Pointer values must flow directly from their producer into their
consumer, with no intermediate operators or functions.  Any transformation
of a pointer value destroys the pointer and transforms the value into
an ordinary SQL NULL.

<h1>Summary</h1>

<p>Key take-aways from this essay:

<ol>
<li><p>
The internet is an increasingly hostile place.  One should now assume that
attackers will find a way to execute arbitrary SQL in an application.
Applications should be designed to prevent the execution of arbitrary
SQL from escalating into a more severe exploit.

<li><p>
A few SQLite extensions benefit from passing pointers:
<ul>
<li>The [FTS3] MATCH operator passes pointers into [snippet()],
    [offsets()], and [matchinfo()].
<li>The [carray table-valued function] needs to accept a pointer to
    an array of C-language values from the application.
<li>The [https://sqlite.org/src/file/ext/misc/remember.c|remember() extension]
    needs a pointer to a C-language integer variable in which to remember
    the value it passes.
<li>Applications need to receive a pointer to the "fts5_api" object in order
    to add extensions, such as custom tokenizers, to the [FTS5] extension.
</ul>

<li><p>
Pointers should <u>never</u> be exchanged by encoding them as some other
SQL datatype, such as integers or BLOBs.  Instead, use the interfaces
designed to facility secure pointer passing:
[sqlite3_bind_pointer()], [sqlite3_result_pointer()], and
[sqlite3_value_pointer()].

<li><p>
The use of pointer-passing is an advanced technique that should be used
infrequently and cautiously.  Pointer-passing should not be
used haphazardly or carelessly.  Pointer-passing is a sharp tool 
that leave deep scars if misused.

<li><p>
The "pointer type" string which is the last parameter to each of the
pointer-passing interfaces should be a distinct, application-specific
string literal that appears directly in the API call.  The pointer type
should not be a parameter passed in from a higher-level function.
</ol>

Changes to pages/changes.in.

20
21
22
23
24
25
26
27
28
29
30
31

32
33
34
35
36
37
38
  set xrefChng($date) $nChng
  incr nChng
}

chng {2017-08-01 (3.20.0)} {
<li> Update the text of error messages returned by [sqlite3_errmsg()] for some
     error codes.
<li> Add new interfaces [sqlite3_bind_pointer()], [sqlite3_result_pointer()], and
     [sqlite3_value_pointer()].
<li> Backwards-incompatible changes to some extensions in order to take 
     advantage of the improved security offered by the new [sqlite3_bind_pointer()]
     interface:

     <ul>
     <li> [Extending FTS5] &rarr; requires [sqlite3_bind_pointer()] to find
          the fts5_api pointer.
     <li> [carray(PTR,N)] &rarr; requires [sqlite3_bind_pointer()] to set the PTR parameter.
     <li> [https://www.sqlite.org/src/file/ext/misc/remember.c|remember(V,PTR)]
          &rarr; requires [sqlite3_bind_pointer()] to set the PTR parameter.
     </ul>







|
<

|
<
>







20
21
22
23
24
25
26
27

28
29

30
31
32
33
34
35
36
37
  set xrefChng($date) $nChng
  incr nChng
}

chng {2017-08-01 (3.20.0)} {
<li> Update the text of error messages returned by [sqlite3_errmsg()] for some
     error codes.
<li> Add new interfaces [pointer passing interfaces].

<li> Backwards-incompatible changes to some extensions in order to take 
     advantage of the improved security offered by the new 

     [pointer passing interfaces]:
     <ul>
     <li> [Extending FTS5] &rarr; requires [sqlite3_bind_pointer()] to find
          the fts5_api pointer.
     <li> [carray(PTR,N)] &rarr; requires [sqlite3_bind_pointer()] to set the PTR parameter.
     <li> [https://www.sqlite.org/src/file/ext/misc/remember.c|remember(V,PTR)]
          &rarr; requires [sqlite3_bind_pointer()] to set the PTR parameter.
     </ul>