Documentation Source Text

Check-in [b41d1c753f]
Login

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

Overview
Comment:Additional suggestions for defense against dark arts.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: b41d1c753fc69056862c43380b514dddb8874edee17f1084a71d5bc0e48d9c17
User & Date: drh 2019-04-13 15:33:55.119
Context
2019-04-13
15:45
Wording changes in defense-against-dark-arts. (check-in: adb3c10d3e user: drh tags: trunk)
15:33
Additional suggestions for defense against dark arts. (check-in: b41d1c753f user: drh tags: trunk)
2019-04-08
18:06
Fix a minor error in the datatype documentation. (check-in: a5590f5f81 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/security.in.
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
Applications that accept untrusted SQL inputs should take the following
precautions:

<ol>
<li><p>
Set the [SQLITE_DBCONFIG_DEFENSIVE] flag.
This prevents ordinary SQL statements from corrupting the database




file.


























<li><p>
Consider using the [sqlite3_set_authorizer()] interface to limit
the scope of SQL that will be processed.






























</ol>

<h2>Untrusted SQLite Database Files</h2>

<p>Applications that accept untrusted database files should do the following:

<ol>
<li value="3"><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>

<h1>Summary</h1>

<p>
The precautions above are not required in order to use SQLite safely
with potentially hostile inputs.







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

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


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







|











>
>
>
>
>
>
>
>
>







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
Applications that accept untrusted SQL inputs should take the following
precautions:

<ol>
<li><p>
Set the [SQLITE_DBCONFIG_DEFENSIVE] flag.
This prevents ordinary SQL statements from corrupting the database
file.  SQLite should be proof against attacks that involve both
malicious SQL inputs and a maliciously corrupted database file at the
same time.  Nevertheless, preventing the attacker from corrupting
the database file provides an extra layer of defense.

<a name="limits"></a>
<li><p>
Reduce the [limits] that SQLite imposes on inputs, to help prevent
denial of service attacks and other kinds of mischief that can occur
as a result of unusually large inputs.  You can do this either at compile-time
using -DSQLITE_MAX_... options, or at run-time using the
[sqlite3_limit()] interface.  Most applications can reduce limits
dramatically without impacting functionality.  The table below
provides some suggestions, though exact values will vary depending
on the application:

<table border="1" cellspacing="0">
<tr><th>Limit Setting<th>Default Value<th>High-security Value
<tr><td>LIMIT_LENGTH<td align="right">1,000,000,000<td align="right">1,000,000
<tr><td>LIMIT_SQL_LENGTH<td align="right">1,000,000,000<td align="right">100,000
<tr><td>LIMIT_COLUMN<td align="right">2,000<td align="right">100
<tr><td>LIMIT_EXPR_DEPTH<td align="right">1,000<td align="right">10
<tr><td>LIMIT_COMPOUND_SELECT<td align="right">500<td align="right">3
<tr><td>LIMIT_VDBE_OP<td align="right">250,000,000<td align="right">25,000
<tr><td>LIMIT_FUNCTION_ARG<td align="right">127<td align="right">8
<tr><td>LIMIT_ATTACH<td align="right">10<td align="right">0
<tr><td>LIMIT_LIKE_PATTERN_LENGTH<td align="right">50,000<td align="right">50
<tr><td>LIMIT_VARIABLE_NUMBER<td align="right">999<td align="right">10
<tr><td>LIMIT_TRIGGER_DEPTH<td align="right">1,000<td align="right">10
</table>

<li><p>
Consider using the [sqlite3_set_authorizer()] interface to limit
the scope of SQL that will be processed.  For example, an application
that does not need to change the database schema might add an
sqlite3_set_authorizer() callback that causes any CREATE or DROP
statement to fail.

<a name="cpulimit"></a>
<li><p>
The SQL language is very powerful, and so it is always possible for
malicious SQL inputs (or erroneous SQL inputs caused by an application
bug) to submit SQL that runs for a very long time.  To prevent this
from becoming a denial-of-service attack, consider using the
[sqlite3_progress_handler()] interface to invoke a callback periodically
as each SQL statement runs, and have that callback return non-zero to
abort the statement if the statement runs for too long.  Alternatively,
set a timer in a separate thread and invoke [sqlite3_interrupt()] when
the timer goes off to prevent the SQL statement from running forever.

<a name="memlimit"></a>
<li><p>
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="6"><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].

<li><p>
A corrupt database can cause excess resource usage for what
would otherwise be benign SQL statements.  So, if [PRAGMA quick_check]
has not been run on the database, one might want to reduce the 
[limits] and provide run-time and memory usage limiters as described
in items <a href="#limits">(2)</a>, 
<a href="#cpulimit">(4)</a>, and
<a href="#memlimit">(5)</a> above.
</ol>

<h1>Summary</h1>

<p>
The precautions above are not required in order to use SQLite safely
with potentially hostile inputs.