Documentation Source Text

Check-in [a9b40186dd]
Login

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

Overview
Comment:Update the limits documentation with additional description and hyperlinks to the sqlite3_limit() interface.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: a9b40186dd0f6571ba79fb6664f040b40efc7289
User & Date: drh 2009-02-11 15:05:44
Context
2009-02-13
15:09
Corrections to the bubble diagram for ANALYZE. check-in: f12500891b user: drh tags: trunk
2009-02-11
15:05
Update the limits documentation with additional description and hyperlinks to the sqlite3_limit() interface. check-in: a9b40186dd user: drh tags: trunk
2009-02-10
13:40
Minor edits to the backup application note. Integrate the same into the other documents. check-in: d2614c5467 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/limits.in.

39
40
41
42
43
44
45

















46
47
48
49
50
51
52
..
81
82
83
84
85
86
87





88
89
90
91
92
93
94
...
115
116
117
118
119
120
121
122







123
124
125
126
127
128
129
...
145
146
147
148
149
150
151





152
153
154
155
156
157
158
159








160
161
162
163
164
165
166
...
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
...
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
...
272
273
274
275
276
277
278
279

280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
or there, but we expect such needs to be rare.  More commonly,
an application might want to recompile SQLite with much lower
limits to avoid excess resource utilization in the event of
bug in higher-level SQL statement generators or to help thwart 
attackers who inject malicious SQL statements.
</p>


















<tcl>
proc limititem {title keywords text} {
  if {[llength $keywords]} {
    set frag [string tolower [lindex $keywords 0]]
    regsub {sqlite_} $frag {} frag
    hd_fragment $frag
    foreach kw $keywords {hd_keywords $kw}
................................................................................

<p>
During part of SQLite's INSERT and SELECT processing, the complete
content of each row in the database is encoded as a single BLOB.
So the SQLITE_MAX_LENGTH parameter also determines the maximum
number of bytes in a row.
</p>





}

limititem {Maximum Number Of Columns} SQLITE_MAX_COLUMN {
<p>
The SQLITE_MAX_COLUMN compile-time parameter is used to set an upper
bound on:
</p>
................................................................................
<p>
In most applications, the number of columns is small - a few dozen.
There are places in the SQLite code generator that use algorithms
that are O(N&sup2;) where N is the number of columns.  
So if you redefine SQLITE_MAX_COLUMN to be a
really huge number and you generate SQL that uses a large number of
columns, you may find that [sqlite3_prepare_v2()]
runs slowly.







}

limititem {Maximum Length Of An SQL Statement} SQLITE_MAX_SQL_LENGTH {
<p>
The maximum number of bytes in the text of an SQL statement is 
limited to SQLITE_MAX_SQL_LENGTH which defaults to 1000000.  You
can redefine this limit to be as large as the smaller of SQLITE_MAX_LENGTH
................................................................................
<p>
Then use the [sqlite3_bind_blob|sqlite3_bind_XXXX()] functions
to bind your large string values to the SQL statement.  The use of binding
obviates the need to escape quote characters in the string, reducing the
risk of SQL injection attacks.  It is also runs faster since the large
string does not need to be parsed or copied as much.
</p>





}

limititem {Maximum Number Of Tables In A Join} {} {
<p>
SQLite does not support joins containing more than 64 tables.
This limit arises from the fact that the SQLite code generator
uses bitmaps with one bit per join-table in the query optimizer.
</p>








}

limititem {Maximum Depth Of An Expression Tree} SQLITE_MAX_EXPR_DEPTH {
<p>
SQLite parses expressions into a tree for processing.  During
code generation, SQLite walks this tree recursively.  The depth
of expression trees is therefore limited in order to avoid
................................................................................
</p>

<p>
The SQLITE_MAX_EXPR_DEPTH parameter determines the maximum expression
tree depth.  If the value is 0, then no limit is enforced.  The
current implementation has a default value of 1000.
</p>












}

limititem {Maximum Number Of Arguments On A Function} SQLITE_MAX_FUNCTION_ARG {
<p>
The SQLITE_MAX_FUNCTION_ARG parameter determines the maximum number
of parameters that can be passed to an SQL function.  The default value
of this limit is 100.  SQLite should work with functions that have 
thousands of parameters.  However, we suspect that anybody who tries
to invoke a function with more than a few parameters is really
trying to find security exploits in systems that use SQLite, 
not do useful work, 
and so for that reason we have set this parameter relatively low.









}

limititem {Maximum Number Of Terms In A Compound SELECT Statement} \
          SQLITE_MAX_COMPOUND_SELECT {
<p>
A compound SELECT statement is two or more SELECT statements connected
by operators UNION, UNION ALL, EXCEPT, or INTERSECT.  We call each
individual SELECT statement within a compound SELECT a "term".
</p>

<p>
The code generator in SQLite processes compound SELECT statements using
a recursive algorithm.  In order to limit the size of the stack, we
therefore limit the number of terms in a compound SELECT.  The maximum
number of terms is SQLITE_MAX_COMPOUND_SELECT which defaults to 500.
We think this is a generous allotment since in practice we almost
never see the number of terms in a compound select exceed single digits.
</p>







}

limititem {Maximum Length Of A LIKE Or GLOB Pattern} \
          SQLITE_MAX_LIKE_PATTERN_LENGTH {
<p>
The pattern matching algorithm used in the default LIKE and GLOB
implementation of SQLite can exhibit O(N&sup2) performance (where
N is the number of characters in the pattern) for certain pathological
cases.  To avoid denial-of-service attacks from miscreants who are able
to specify their own LIKE or GLOB patterns, the length of the LIKE
or GLOB pattern is limited to SQLITE_MAX_LIKE_PATTERN_LENGTH bytes.
The default value of this limit is 50000.  A modern workstation can
evaluate even a pathological LIKE or GLOB pattern of 50000 bytes
................................................................................
relatively quickly.  The denial of service problem only comes into
play when the pattern length gets into millions of bytes.  Nevertheless,
since most useful LIKE or GLOB patterns are at most a few dozen bytes
in length, paranoid application developers may want to reduce this
parameter to something in the range of a few hundred if they know that
external users are able to generate arbitrary patterns.
</p>





}

limititem {Maximum Number Of Host Parameters In A Single SQL Statement} \
          SQLITE_MAX_VARIABLE_NUMBER {
<p>
A host parameter is a place-holder in an SQL statement that is filled
in using one of the
................................................................................
Each host parameter in an SQLite statement is assigned a number.  The
numbers normally begin with 1 and increase by one with each new
parameter.  However, when the "?123" form is used, the host parameter
number is the number that follows the question mark.
</p>

<p>





The maximum value of a host parameter number is SQLITE_MAX_VARIABLE_NUMBER.
This setting defaults to 999.





</p>
}

limititem {Maximum Number Of Attached Databases} SQLITE_MAX_ATTACHED {
<p>
The <a href="lang_attach.html">ATTACH</a> statement is an SQLite extension
that allows two or more databases to be associated to the same database
connection and to operate as if they were a single database.  The number
of simulataneously attached databases is limited to SQLITE_MAX_ATTACHED
which is set to 10 by default.
The code generator in SQLite uses bitmaps
to keep track of attached databases.  That means that the number of
attached databases cannot be increased above 30 on a 32-bit machine
or 62 on a 64-bit machine.





}

limititem {Maximum Database Page Size} SQLITE_MAX_PAGE_SIZE {
<p>
An SQLite database file is organized as pages.  The size of each
page is a power of 2 between 512 and SQLITE_MAX_PAGE_SIZE.
The default value for SQLITE_MAX_PAGE_SIZE is 32768.  The current
................................................................................
<p>
It used to be the case that SQLite would allocate some stack
structures whose size was proportional to the maximum page size.
For this reason, SQLite would sometimes be compiled with a smaller
maximum page size on embedded devices with limited stack memory.  But
more recent versions of SQLite put these large structures on the
heap, not on the stack, so reducing the maximum page size is no
longer necessary on embedded devices.

</p>
}

limititem {Maximum Number Of Pages In A Database File} \
          SQLITE_MAX_PAGE_COUNT {
<p>
SQLite is able to limit the size of a database file to prevent
the database file from growing too large and consuming too much
disk or flash space.
The SQLITE_MAX_PAGE_COUNT parameter, which is normally set to
1073741823, is the maximum number of pages allowed in a single
database file.  An attempt to insert new data that would cause
the database file to grow larger than this will return
SQLITE_FULL.
</p>








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







 







>
>
>
>
>







 







|
>
>
>
>
>
>
>







 







>
>
>
>
>








>
>
>
>
>
>
>
>







 







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











|
>
>
>
>
>
>
>
>
>





|












>
>
>
>
>
>
>





|







 







>
>
>
>
>







 







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












|
|
>
>
>
>
>







 







|
>








|







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
..
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
...
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
...
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
...
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
...
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
...
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
or there, but we expect such needs to be rare.  More commonly,
an application might want to recompile SQLite with much lower
limits to avoid excess resource utilization in the event of
bug in higher-level SQL statement generators or to help thwart 
attackers who inject malicious SQL statements.
</p>

<p>
Some limits can be changed at run-time on a per-connection basis
using the [sqlite3_limit()] interface with on of the
[SQLITE_LIMIT_LENGTH | limit categories] defined for that interface.
Run-time limits are designed for applications that have multiple
databases, some of which are for internal use only and others which
can be influenced or controlled by potentially hostile external agents.
For example, a web browser application might use an internal database
to track historical page views but have one or more separate databases
that are created and controlled by javascript applications that are
downloaded from the internet.
The [sqlite3_limit()] interface allows internal databases managed by
trusted code to be unconstrained while simultaneously  placing tight
limitations on databases created or controlled by untrusted external
code in order to help prevent a denial of service attack.
</p>

<tcl>
proc limititem {title keywords text} {
  if {[llength $keywords]} {
    set frag [string tolower [lindex $keywords 0]]
    regsub {sqlite_} $frag {} frag
    hd_fragment $frag
    foreach kw $keywords {hd_keywords $kw}
................................................................................

<p>
During part of SQLite's INSERT and SELECT processing, the complete
content of each row in the database is encoded as a single BLOB.
So the SQLITE_MAX_LENGTH parameter also determines the maximum
number of bytes in a row.
</p>

<p>
The maximum string or BLOB length can be lowered at run-time using
the [sqlite3_limit](db,[SQLITE_LIMIT_LENGTH],size) interface.
</p>
}

limititem {Maximum Number Of Columns} SQLITE_MAX_COLUMN {
<p>
The SQLITE_MAX_COLUMN compile-time parameter is used to set an upper
bound on:
</p>
................................................................................
<p>
In most applications, the number of columns is small - a few dozen.
There are places in the SQLite code generator that use algorithms
that are O(N&sup2;) where N is the number of columns.  
So if you redefine SQLITE_MAX_COLUMN to be a
really huge number and you generate SQL that uses a large number of
columns, you may find that [sqlite3_prepare_v2()]
runs slowly.</p>


<p>
The maximum number of columns can be lowered at run-time using
the [sqlite3_limit](db,[SQLITE_LIMIT_COLUMN],size) interface.
</p>

}

limititem {Maximum Length Of An SQL Statement} SQLITE_MAX_SQL_LENGTH {
<p>
The maximum number of bytes in the text of an SQL statement is 
limited to SQLITE_MAX_SQL_LENGTH which defaults to 1000000.  You
can redefine this limit to be as large as the smaller of SQLITE_MAX_LENGTH
................................................................................
<p>
Then use the [sqlite3_bind_blob|sqlite3_bind_XXXX()] functions
to bind your large string values to the SQL statement.  The use of binding
obviates the need to escape quote characters in the string, reducing the
risk of SQL injection attacks.  It is also runs faster since the large
string does not need to be parsed or copied as much.
</p>

<p>
The maximum length of an SQL statement can be lowered at run-time using
the [sqlite3_limit](db,[SQLITE_LIMIT_SQL_LENGTH],size) interface.
</p>
}

limititem {Maximum Number Of Tables In A Join} {} {
<p>
SQLite does not support joins containing more than 64 tables.
This limit arises from the fact that the SQLite code generator
uses bitmaps with one bit per join-table in the query optimizer.
</p>

<p>
SQLite uses a very efficient O(N&sup2;) greedy algorithm for determining
the order of tables in a join and so a large join can be
[sqlite3_prepare() | prepared] quickly.
Hence, there is no mechanism to raise or lower the limit on the
number of tables in a join.
</p>
}

limititem {Maximum Depth Of An Expression Tree} SQLITE_MAX_EXPR_DEPTH {
<p>
SQLite parses expressions into a tree for processing.  During
code generation, SQLite walks this tree recursively.  The depth
of expression trees is therefore limited in order to avoid
................................................................................
</p>

<p>
The SQLITE_MAX_EXPR_DEPTH parameter determines the maximum expression
tree depth.  If the value is 0, then no limit is enforced.  The
current implementation has a default value of 1000.
</p>

<p>
The maximum depth of an expression tree can be lowered at run-time using
the [sqlite3_limit](db,[SQLITE_LIMIT_EXPR_DEPTH],size) interface if the
SQLITE_MAX_EXPR_DEPTH is initially positive.  In other words, the maximum
expression depth can be lowered at run-time if there is already a 
compile-time limit on the expression depth.  If SQLITE_MAX_EXPR_DEPTH is
set to 0 at compile time (if the depth of expressions is unlimited) then
the [sqlite3_limit](db,[SQLITE_LIMIT_EXPR_DEPTH],size) is a no-op.
</p>


}

limititem {Maximum Number Of Arguments On A Function} SQLITE_MAX_FUNCTION_ARG {
<p>
The SQLITE_MAX_FUNCTION_ARG parameter determines the maximum number
of parameters that can be passed to an SQL function.  The default value
of this limit is 100.  SQLite should work with functions that have 
thousands of parameters.  However, we suspect that anybody who tries
to invoke a function with more than a few parameters is really
trying to find security exploits in systems that use SQLite, 
not do useful work, 
and so for that reason we have set this parameter relatively low.</p>

<p>The number of arguments to a function is sometimes stored in a signed
character.  So there is a hard upper bound on SQLITE_MAX_FUNCTION_ARG
of 127.</p>

<p>
The maximum number of arguments in a function can be lowered at run-time using
the [sqlite3_limit](db,[SQLITE_LIMIT_FUNCTION_ARG],size) interface.
</p>
}

limititem {Maximum Number Of Terms In A Compound SELECT Statement} \
          SQLITE_MAX_COMPOUND_SELECT {
<p>
A compound [SELECT] statement is two or more SELECT statements connected
by operators UNION, UNION ALL, EXCEPT, or INTERSECT.  We call each
individual SELECT statement within a compound SELECT a "term".
</p>

<p>
The code generator in SQLite processes compound SELECT statements using
a recursive algorithm.  In order to limit the size of the stack, we
therefore limit the number of terms in a compound SELECT.  The maximum
number of terms is SQLITE_MAX_COMPOUND_SELECT which defaults to 500.
We think this is a generous allotment since in practice we almost
never see the number of terms in a compound select exceed single digits.
</p>

<p>
The maximum number of compound SELECT terms can be lowered at run-time using
the [sqlite3_limit](db,[SQLITE_LIMIT_COMPOUND_SELECT],size) interface.
</p>


}

limititem {Maximum Length Of A LIKE Or GLOB Pattern} \
          SQLITE_MAX_LIKE_PATTERN_LENGTH {
<p>
The pattern matching algorithm used in the default [LIKE] and [GLOB]
implementation of SQLite can exhibit O(N&sup2) performance (where
N is the number of characters in the pattern) for certain pathological
cases.  To avoid denial-of-service attacks from miscreants who are able
to specify their own LIKE or GLOB patterns, the length of the LIKE
or GLOB pattern is limited to SQLITE_MAX_LIKE_PATTERN_LENGTH bytes.
The default value of this limit is 50000.  A modern workstation can
evaluate even a pathological LIKE or GLOB pattern of 50000 bytes
................................................................................
relatively quickly.  The denial of service problem only comes into
play when the pattern length gets into millions of bytes.  Nevertheless,
since most useful LIKE or GLOB patterns are at most a few dozen bytes
in length, paranoid application developers may want to reduce this
parameter to something in the range of a few hundred if they know that
external users are able to generate arbitrary patterns.
</p>

<p>
The maximum length of a LIKE or GLOB pattern can be lowered at run-time using
the [sqlite3_limit](db,[SQLITE_LIMIT_LIKE_PATTERN_LENGTH],size) interface.
</p>
}

limititem {Maximum Number Of Host Parameters In A Single SQL Statement} \
          SQLITE_MAX_VARIABLE_NUMBER {
<p>
A host parameter is a place-holder in an SQL statement that is filled
in using one of the
................................................................................
Each host parameter in an SQLite statement is assigned a number.  The
numbers normally begin with 1 and increase by one with each new
parameter.  However, when the "?123" form is used, the host parameter
number is the number that follows the question mark.
</p>

<p>
SQLite allocates space to hold all host parameters between 1 and the
largest host parameter number used.  Hence, an SQL statement that contains
a host parameter like ?1000000000 would require gigabytes of storage.
This could easily overwhelm the resources of the host machine.
To prevent excessive memory allocations, 
the maximum value of a host parameter number is SQLITE_MAX_VARIABLE_NUMBER,
which defaults to 999.
</p>

<p>
The maximum host parameter number can be lowered at run-time using
the [sqlite3_limit](db,[SQLITE_LIMIT_VARIABLE_NUMBER],size) interface.
</p>
}

limititem {Maximum Number Of Attached Databases} SQLITE_MAX_ATTACHED {
<p>
The <a href="lang_attach.html">ATTACH</a> statement is an SQLite extension
that allows two or more databases to be associated to the same database
connection and to operate as if they were a single database.  The number
of simulataneously attached databases is limited to SQLITE_MAX_ATTACHED
which is set to 10 by default.
The code generator in SQLite uses bitmaps
to keep track of attached databases.  That means that the number of
attached databases cannot be increased above 30 on a machines with
a 32-bit integer.</p>

<p>
The maximum number of attached databases can be lowered at run-time using
the [sqlite3_limit](db,[SQLITE_LIMIT_ATTACHED],size) interface.
</p>
}

limititem {Maximum Database Page Size} SQLITE_MAX_PAGE_SIZE {
<p>
An SQLite database file is organized as pages.  The size of each
page is a power of 2 between 512 and SQLITE_MAX_PAGE_SIZE.
The default value for SQLITE_MAX_PAGE_SIZE is 32768.  The current
................................................................................
<p>
It used to be the case that SQLite would allocate some stack
structures whose size was proportional to the maximum page size.
For this reason, SQLite would sometimes be compiled with a smaller
maximum page size on embedded devices with limited stack memory.  But
more recent versions of SQLite put these large structures on the
heap, not on the stack, so reducing the maximum page size is no
longer necessary on embedded devices.  There is no longer any
real reason to lower the maximum page size.
</p>
}

limititem {Maximum Number Of Pages In A Database File} \
          SQLITE_MAX_PAGE_COUNT {
<p>
SQLite is able to limit the size of a database file to prevent
the database file from growing too large and consuming too much
disk space.
The SQLITE_MAX_PAGE_COUNT parameter, which is normally set to
1073741823, is the maximum number of pages allowed in a single
database file.  An attempt to insert new data that would cause
the database file to grow larger than this will return
SQLITE_FULL.
</p>