Documentation Source Text

Check-in [0d0e8a024d]
Login

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

Overview
Comment:Enhanced documentation for the TCL interface, and especially warnings about the importance of using the -directonly option for application-defined functions.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 0d0e8a024df7bdd5baa4f5b40f7e00bc811d80733c55f2e8c88607dd7cdf294a
User & Date: drh 2020-01-18 22:24:50
Context
2020-01-21
20:14
Update the speed-and-size spreadsheet. check-in: 6639a1d518 user: drh tags: trunk
2020-01-18
22:24
Enhanced documentation for the TCL interface, and especially warnings about the importance of using the -directonly option for application-defined functions. check-in: 0d0e8a024d user: drh tags: trunk
00:56
Documentation of two-size lookaside. check-in: 668d3ca782 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/tclsqlite.in.

465
466
467
468
469
470
471
472
473
474
475
476
477
478
479









480


481
482
483
484
485
486
487
...
580
581
582
583
584
585
586





587
588
589
590
591
592
593
...
622
623
624
625
626
627
628






629
630
631
632
633
634
635
...
647
648
649
650
651
652
653
654































655
656
657
658
659
660
661
662
663
664
665
666
667
668

669



670
671
672
673
674
675
676
<blockquote>
<i>dbcmd</i>&nbsp;&nbsp;<b>config</b>
</blockquote>

<p>The above will return something like this:

<blockquote>
enable_fkey 0 enable_trigger 1 enable_view 1 fts3_tokenizer 0 load_extension 0 no_ckpt_on_close 0 enable_qpsg 0 trigger_eqp 0 reset_database 0 defensive 0 writable_schema 0 legacy_alter_table 0 dqs_dml 1 dqs_ddl 1
</blockquote>

<p>Add the name of an individual configuration setting to query the current
value of that setting.  Optionally add a boolean value to change a setting.
For example, turn off support for the [double-quoted string literal]
misfeature like this:










<blockquote><pre>


db config dqs_dml 0
db config dqs_ddl 0
</pre></blockquote>
}

##############################################################################
METHOD copy {
................................................................................
[load_extension()] SQL function) is turned off by default.  This is
a security precaution.  If an application wants to make use of the
[load_extension()] function it must first turn the capability on using
this method.</p>

<p>This method takes a single boolean argument which will turn the
extension loading functionality on or off.</p>






<p>This method maps to the [sqlite3_enable_load_extension()] C/C++
interface.</p>
}

##############################################################################
METHOD exists {
................................................................................
METHOD function {

<p>The "function" method registers new SQL functions with the SQLite engine.
The arguments are the name of the new SQL function and a TCL command that
implements that function.  Arguments to the function are appended to the
TCL command before it is invoked.</p>







<p>
The following example creates a new SQL function named "hex" that converts
its numeric argument in to a hexadecimal encoded string:
</p>

<blockquote><b>
db function hex {format 0x%X}
................................................................................
<dt><b>-deterministic</b>
<dd><p>
This option indicates that the function will always return the same
answer given the same argument values.  The SQLite query optimizer
uses this information to cache answers from function calls with 
constant inputs and reuse the result rather than invoke the function
repeatedly.
</dl>
































<dt><b>-returntype integer|real|text|blob|any</b>
<dd><p>
This option is used to configure the type of the result returned by the
function.  If this option is set to "any" (the default), SQLite attempts to
determine the type of each value returned by the function implementation based
on the Tcl value's internal type. Or, if it is set to "text" or "blob", the
returned value is always a text or blob value, respectively. If this option is
set to "integer", SQLite attempts to coerce the value returned by the function
to an integer. If this is not possible without data loss, it attempts to coerce
it to a real value, and finally falls back to text. If this option is set to
"real", an attempt is made to return a real value, falling back to text if this
is not possible.


</dl>



</blockquote>
}

##############################################################################
METHOD nullvalue {

<p>







|




<
<

>
>
>
>
>
>
>
>
>

>
>







 







>
>
>
>
>







 







>
>
>
>
>
>







 







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













<
>

>
>
>







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
...
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
...
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
...
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718

719
720
721
722
723
724
725
726
727
728
729
730
<blockquote>
<i>dbcmd</i>&nbsp;&nbsp;<b>config</b>
</blockquote>

<p>The above will return something like this:

<blockquote>
defensive 0 dqs_ddl 1 dqs_dml 1 enable_fkey 0 enable_qpsg 0 enable_trigger 1 enable_view 1 fts3_tokenizer 1 legacy_alter_table 0 legacy_file_format 0 load_extension 0 no_ckpt_on_close 0 reset_database 0 trigger_eqp 0 trusted_schema 1 writable_schema 0
</blockquote>

<p>Add the name of an individual configuration setting to query the current
value of that setting.  Optionally add a boolean value to change a setting.



<p>
The following four configuration changes are recommended for maximum
application security.  Turning off the trust_schema setting prevents
virtual tables and dodgy SQL functions from being used inside of triggers,
views, CHECK constraints, generated columns, and expression indexes.
Turning off the dqs_dml and dqs_ddl settings previews the use of 
double-quoted strings.  Turning on defensive prevents direct writes
to shadow tables.

<blockquote><pre>
db config trusted_schema 0
db config defensive 1
db config dqs_dml 0
db config dqs_ddl 0
</pre></blockquote>
}

##############################################################################
METHOD copy {
................................................................................
[load_extension()] SQL function) is turned off by default.  This is
a security precaution.  If an application wants to make use of the
[load_extension()] function it must first turn the capability on using
this method.</p>

<p>This method takes a single boolean argument which will turn the
extension loading functionality on or off.</p>

<p>For best security, do not use this method unless truly needed, 
and run [PRAGMA trusted_schema=OFF] or the
"<a href="#config">db config trusted_schema 0</a>" method <u>before</u>
invoking this method.

<p>This method maps to the [sqlite3_enable_load_extension()] C/C++
interface.</p>
}

##############################################################################
METHOD exists {
................................................................................
METHOD function {

<p>The "function" method registers new SQL functions with the SQLite engine.
The arguments are the name of the new SQL function and a TCL command that
implements that function.  Arguments to the function are appended to the
TCL command before it is invoked.</p>

<p>
For security reasons, it is recommended that applications first set
[PRAGMA trusted_schema=OFF] or run the 
"<a href="#config">db config trusted_schema 0</a>"
method before using this method.

<p>
The following example creates a new SQL function named "hex" that converts
its numeric argument in to a hexadecimal encoded string:
</p>

<blockquote><b>
db function hex {format 0x%X}
................................................................................
<dt><b>-deterministic</b>
<dd><p>
This option indicates that the function will always return the same
answer given the same argument values.  The SQLite query optimizer
uses this information to cache answers from function calls with 
constant inputs and reuse the result rather than invoke the function
repeatedly.
</dd>

<dt><b>-directonly</b>
<dd><p>
This option restricts the function to only be usable by direct
top-level SQL statement.  The function will not be accessible to
triggers, views, CHECK constraints, generated columns, or index
expressions.  This option is recommended for all application-defined
SQL functions, and is <u>highly recommended</u> for any SQL
function that has side effects or that reveals internal state of
the application.

<p style="background-color:#ffff90;"><b>Security Warning:</b>
Without this switch, an attacker might be able to change the
schema of a database file to include the new function inside a trigger
or view or CHECK constraint and thereby trick the application into
running the function with parameters of the attacker's choosing.
Hence, if the new function has side effects or reveals internal
state about the application and the -directonly option is not
used, that is a potential security vulnerability.</p>
</dd>

<dt><b>-innocuous</b>
<dd><p>
This option indicates that the function has no side effects and
does not leak any information that cannot be computed directly
from its input parameters.  When this option is specified, the
function may be used in triggers, views, CHECK constraints,
generated columns, and/or index expressions even if
[PRAGMA trusted_schema=OFF].  The use of this option is discouraged
unless it is truly needed.
</dd>

<dt><b>-returntype integer|real|text|blob|any</b>
<dd><p>
This option is used to configure the type of the result returned by the
function.  If this option is set to "any" (the default), SQLite attempts to
determine the type of each value returned by the function implementation based
on the Tcl value's internal type. Or, if it is set to "text" or "blob", the
returned value is always a text or blob value, respectively. If this option is
set to "integer", SQLite attempts to coerce the value returned by the function
to an integer. If this is not possible without data loss, it attempts to coerce
it to a real value, and finally falls back to text. If this option is set to
"real", an attempt is made to return a real value, falling back to text if this
is not possible.

</dd>
</dl>



</blockquote>
}

##############################################################################
METHOD nullvalue {

<p>