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
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
|
<!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>Pointer Passing Interfaces</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">
Pointer Passing Interfaces
</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="#overview">1. Overview</a></div>
<div class="fancy-toc1"><a href="#a_brief_history_of_pointer_passing_in_sqlite">2. A Brief History Of Pointer Passing In SQLite</a></div>
<div class="fancy-toc2"><a href="#upping_the_threat_level">2.1. Upping The Threat Level</a></div>
<div class="fancy-toc2"><a href="#preventing_forged_pointers">2.2. Preventing Forged Pointers</a></div>
<div class="fancy-toc2"><a href="#pointer_leaks">2.3. Pointer Leaks</a></div>
<div class="fancy-toc1"><a href="#the_new_pointer_passing_interfaces">3. The New Pointer-Passing Interfaces</a></div>
<div class="fancy-toc2"><a href="#pointer_types">3.1. Pointer Types</a></div>
<div class="fancy-toc3"><a href="#pointer_types_are_static_strings">3.1.1. Pointer types are static strings</a></div>
<div class="fancy-toc2"><a href="#destructor_functions">3.2. Destructor Functions</a></div>
<div class="fancy-toc1"><a href="#restrictions_on_the_use_of_pointer_values">4. Restrictions On The Use of Pointer Values</a></div>
<div class="fancy-toc1"><a href="#summary">5. Summary</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="overview"><span>1. </span>Overview</h1>
<p>
Three new "_pointer()" interfaces were added to SQLite 3.20.0 (2017-08-01):
</p><ul>
<li> <a href="c3ref/bind_blob.html">sqlite3_bind_pointer()</a>,
</li><li> <a href="c3ref/result_blob.html">sqlite3_result_pointer()</a>, and
</li><li> <a href="c3ref/value_blob.html">sqlite3_value_pointer()</a>.
</li></ul>
<p>Questions and confusion quickly arose
on the <a href="support.html#mailinglists">mailing lists</a> 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.
</p><h1 id="a_brief_history_of_pointer_passing_in_sqlite"><span>2. </span>A Brief History Of Pointer Passing In SQLite</h1>
<p>
It is sometimes convenient for SQLite extensions to
communicate non-SQL values between subcomponents or between the extension
and the application. Some examples:
</p><ul>
<li><p>
In the <a href="fts3.html">FTS3</a> extension, the
<a href="fts3.html#full_text_index_queries">MATCH operator</a> (which does the full-text search)
needs to communicate details of matching entries
to the <a href="fts3.html#snippet">snippet()</a>, <a href="fts3.html#offsets">offsets()</a>,
and <a href="fts3.html#matchinfo">matchinfo()</a> functions so that those functions can convert the details
of the match into useful output.
</p></li><li><p>
In order for an application to
<a href="fts5.html#extending_fts5">add new extensions to FTS5</a>, such as new tokenizers,
the application needs a pointer to the "fts5_api" object.
</p></li><li><p>
In the <a href="carray.html">CARRAY extension</a>, 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.
</p></li></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
<a href="c3ref/bind_blob.html">sqlite3_bind_blob()</a>, <a href="c3ref/result_blob.html">sqlite3_result_blob()</a>, <a href="c3ref/value_blob.html">sqlite3_value_blob()</a> or
the integer equivalents.
</p><h2 id="upping_the_threat_level"><span>2.1. </span>Upping The Threat 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><p>
For example, the first argument to the <a href="fts3.html#snippet">snippet()</a> function is supposed 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:
</p><div class="codeblock"><pre>SELECT snippet(t1) FROM t1 WHERE cx MATCH $pattern;
</pre></div>
<p>
But if a hacker is able to run arbitrary SQL, he might run a slightly
different query, like this:
</p><div class="codeblock"><pre>SELECT hex(t1) FROM t1 WHERE cx MATCH $pattern;
</pre></div>
<p>
Because the pointer is passed in the t1 column of the t1
table 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 supposed to be operating on:
</p><div class="codeblock"><pre>SELECT snippet(x'6092310100000000') FROM t1 WHERE cx MATCH $pattern;
</pre></div>
<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 does not give the agent
any new capability.
</p><p>
For most cases, it is true that potential attackers have no way of injecting
arbitrary SQL, and so most uses of SQLite are immune to the attack above.
But there are some notable exceptions. To wit:
</p><ul>
<li><p>
The <a href="https://en.wikipedia.org/wiki/Web_SQL_Database">WebSQL</a> interface
to webkit allowed any webpage to run arbitrary SQL in the browser
for Chrome and Safari. That arbitrary SQL was supposed to be run inside
a sandbox where it could do no 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.
</p></li><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.
</p></li><li><p>
The <a href="https://www.fossil-scm.org/">Fossil</a> 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
<a href="c3ref/set_authorizer.html">sqlite3_set_authorizer()</a> 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.
</p></li></ul>
<h2 id="preventing_forged_pointers"><span>2.2. </span>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
<a href="c3ref/result_subtype.html">sqlite3_result_subtype()</a> and having the receiver verify that subtype
using <a href="c3ref/value_subtype.html">sqlite3_value_subtype()</a> 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 a pointer without the help of SQLite.
</p><p>
Using subtypes to identify valid pointers prevented the WebSQL exploit.
But it turned out to be an incomplete solution.
<a name="ptrleak"></a>
</p><h2 id="pointer_leaks"><span>2.3. </span>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
prevent attacks using SQL statements like this:
</p><div class="codeblock"><pre>SELECT snippet(x'6092310100000000') FROM t1 WHERE cx MATCH $pattern;
</pre></div>
<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><p>
But the use of subtypes does nothing to prevent the value of a
pointer from being read using SQL code like this:
</p><div class="codeblock"><pre>SELECT hex(t1) FROM t1 WHERE cx MATCH $pattern;
</pre></div>
<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.
</p><h1 id="the_new_pointer_passing_interfaces"><span>3. </span>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:
</p><ul>
<li><b><a href="c3ref/bind_blob.html">sqlite3_bind_pointer</a>(S,I,P,T,D)</b> →
Bind pointer P of type T to the I-th parameter of prepared statement S.
D is an optional destructor function for P.
</li><li><b><a href="c3ref/result_blob.html">sqlite3_result_pointer</a>(C,P,T,D)</b> →
Return pointer P of type T as the argument of function C.
D is an optional destructor function for P.
</li><li><b><a href="c3ref/value_blob.html">sqlite3_value_pointer</a>(V,T)</b> →
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.
</li></ul>
<p>
To SQL, the values created by <a href="c3ref/bind_blob.html">sqlite3_bind_pointer()</a> and
<a href="c3ref/result_blob.html">sqlite3_result_pointer()</a> are indistinguishable from NULL. An
SQL statement that tries to use the <a href="lang_corefunc.html#hex">hex()</a> 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 <a href="c3ref/value_blob.html">sqlite3_value_pointer()</a> interface with the appropriate
type string T.
</p><p>
Pointer values read by <a href="c3ref/value_blob.html">sqlite3_value_pointer()</a>
cannot be generated by pure SQL. Hence, it is not possible for SQL to
forge pointers.
</p><p>
Pointer values generated by <a href="c3ref/bind_blob.html">sqlite3_bind_pointer()</a> and
<a href="c3ref/result_blob.html">sqlite3_result_pointer()</a> cannot be read by pure SQL.
Hence, it is not possible for SQL to leak the value of pointers.
</p><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.
<a name="ptrtyp"></a>
</p><h2 id="pointer_types"><span>3.1. </span>Pointer Types</h2>
<p>
The "pointer type" in the last parameter to <a href="c3ref/bind_blob.html">sqlite3_bind_pointer()</a>,
<a href="c3ref/result_blob.html">sqlite3_result_pointer()</a>, and <a href="c3ref/value_blob.html">sqlite3_value_pointer()</a> 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
both the <a href="fts3.html">FTS3</a> and the <a href="carray.html">CARRAY extension</a> using SQL like this:
</p><div class="codeblock"><pre>SELECT ca.value FROM t1, carray(t1,10) AS ca WHERE cx MATCH $pattern
</pre></div>
<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><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 <a href="c3ref/result_blob.html">sqlite3_result_pointer()</a> does not match
the pointer type on the <a href="c3ref/value_blob.html">sqlite3_value_pointer()</a> call,
<a href="c3ref/value_blob.html">sqlite3_value_pointer()</a> returns NULL in carray() and thus signals
the CARRAY extension that it has been passed an invalid pointer.
</p><h3 id="pointer_types_are_static_strings"><span>3.1.1. </span>Pointer types are static strings</h3>
<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 given 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><p>
By "static string", we mean a zero-terminated array of bytes that is
fixed and unchanging for the life of the program. In other words, the
pointer type string should be a string constant.
In contrast, a "dynamic string" is a zero-terminated array of bytes
that is held in memory allocated
from the heap, and which must be freed to avoid a memory leak.
Do not use dynamic strings as the pointer type string.
</p><p>
Multiple commentators have expressed a desire to use dynamic strings
for the pointer type, and to have SQLite take ownership of the type strings
and to automatically free the type string
when it has finished using it. That design is rejected for the
following reasons:
</p><ol>
<li><p>
The pointer type is not intended to be flexible and dynamic. The
pointer type is intended to be a design-time constant. Applications
should not synthesize pointer type strings at run-time. Providing
support for dynamic pointer type strings would lead developers
to misuse the pointer-passing interfaces by creating run-time
synthesized pointer type strings. Requiring the pointer type strings
to be static encourages developers to do the right thing by choosing
fixed pointer type names at design-time and encoding those names
as constant strings.
</p></li><li><p>
All string values at the SQL level in SQLite are dynamic strings.
Requiring type strings to be static makes it difficult to
create an application-defined SQL function that
can synthesize a pointer of an arbitrary type. We do not want users
to create such SQL functions, since such functions would compromise the
security of the system. Thus, the requirement to use static strings
helps to defend that the integrity of the pointer-passing interfaces against
ill-designed SQL functions. The static string requirement is not
a perfect defense, since a sophisticated programmer can code around
it, and a novice program can simply take the memory leak. But by
stating that the pointer type string must be static, we hope to encourage
developers who might otherwise use a dynamic string for the pointer type
to think more carefully about the problem and avoid introducing
security issues.
</p></li><li><p>
Having SQLite take ownership of the type strings would impose a performance
cost on all applications, even applications that do not use the
pointer-passing interfaces. SQLite passes values around as instances
of the <a href="c3ref/value.html">sqlite3_value</a> object. That object has a destructor, which because
of the fact that sqlite3_value objects are used for nearly everything, is
invoked frequently. If the destructor needs to check to see if there is
a pointer type string that needs to be freed, that is a few extra CPU
cycles that need to be burned on each call to the destructor. Those
cycles add up. We would be willing to bear the cost of the extra CPU
cycles if pointer-passing was a commonly used programming paradigm, but
pointer-passing is rare, and so it seems unwise to impose a run-time cost
on billions and billions of applications that do not use pointer passing
just for convenience of a few applications that do.
</p></li></ol>
<p>
If you feel that you need dynamic pointer type strings in your application,
that is a strong indicator that you are misusing the pointer-passing interface.
Your intended use may be unsafe.
Please rethink your design. Determine if you really need to be passing
pointers through SQL in the first place. Or perhaps find a different
mechanism other than the pointer-passing interfaces described by this
article.
</p><h2 id="destructor_functions"><span>3.2. </span>Destructor Functions</h2>
<p>
The last parameter to the <a href="c3ref/bind_blob.html">sqlite3_bind_pointer()</a> and
<a href="c3ref/result_blob.html">sqlite3_result_pointer()</a> routines is a pointer to a procedure
used to dispose of the P pointer once SQLite has finished with it.
This pointer can be NULL, in which case no destructor is called.
</p><p>
When the D parameter is not NULL, that means that ownership of the
pointer is being transferred to SQLite. SQLite will take responsibility
for freeing resources associated with the pointer when it has finished
using the pointer. If the D parameter is NULL, that means that ownership
of the pointer remains with the caller and the caller is responsible for
disposing of the pointer.
</p><p>
Note that the destructor function D is for the pointer value P, not for
the type string T. The type string T should be a static string with an
infinite lifetime.
</p><p>
If ownership of the pointer is passed into SQLite by providing a
non-NULL D parameter to <a href="c3ref/bind_blob.html">sqlite3_bind_pointer()</a> or <a href="c3ref/result_blob.html">sqlite3_result_pointer()</a>
then the ownership remains with SQLite until the object is destroyed.
There is no way to transfer ownership out of SQLite and back into the
application again.
</p><h1 id="restrictions_on_the_use_of_pointer_values"><span>4. </span>Restrictions On The Use of Pointer Values</h1>
<p>
The pointers that piggy-back on SQL NULL values using the
<a href="c3ref/bind_blob.html">sqlite3_bind_pointer()</a>, <a href="c3ref/result_blob.html">sqlite3_result_pointer()</a>, and
<a href="c3ref/value_blob.html">sqlite3_value_pointer()</a> 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 operation
prior to returning a value from a query, so it would be impossible to
know if a pointer value inserted into a query by
<a href="c3ref/bind_blob.html">sqlite3_bind_pointer()</a> or <a href="c3ref/result_blob.html">sqlite3_result_pointer()</a> would survive
through to the result set.
</p><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.
</p><h1 id="summary"><span>5. </span>Summary</h1>
<p>Key take-aways from this essay:
</p><ol>
<li><p>
The internet is an increasingly hostile place. These day, developers
should 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.
</p></li><li><p>
A few SQLite extensions benefit from passing pointers:
</p><ul>
<li>The <a href="fts3.html">FTS3</a> MATCH operator passes pointers into <a href="fts3.html#snippet">snippet()</a>,
<a href="fts3.html#offsets">offsets()</a>, and <a href="fts3.html#matchinfo">matchinfo()</a>.
</li><li>The <a href="carray.html">carray table-valued function</a> needs to accept a pointer to
an array of C-language values from the application.
</li><li>The <a href="https://sqlite.org/src/file/ext/misc/remember.c">remember() extension</a>
needs a pointer to a C-language integer variable in which to remember
the value it passes.
</li><li>Applications need to receive a pointer to the "fts5_api" object in order
to add extensions, such as custom tokenizers, to the <a href="fts5.html">FTS5</a> extension.
</li></ul>
</li><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 facilitate secure pointer passing:
<a href="c3ref/bind_blob.html">sqlite3_bind_pointer()</a>, <a href="c3ref/result_blob.html">sqlite3_result_pointer()</a>, and
<a href="c3ref/value_blob.html">sqlite3_value_pointer()</a>.
</p></li><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 can leave deep scars if misused.
</p></li><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.
</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/bindptr.in?m=0313c341708d7be8d">2022-10-07 10:23:26</a> UTC </small></i></p>
|
