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. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
a9b40186dd0f6571ba79fb6664f040b4 |
User & Date: | drh 2009-02-11 15:05:44.000 |
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
Changes to pages/limits.in.
︙ | ︙ | |||
39 40 41 42 43 44 45 46 47 48 49 50 51 52 | 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} | > > > > > > > > > > > > > > > > > | 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 | 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} |
︙ | ︙ | |||
81 82 83 84 85 86 87 88 89 90 91 92 93 94 | <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> | > > > > > | 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 | <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> |
︙ | ︙ | |||
115 116 117 118 119 120 121 | <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²) 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()] | | > > > > > > > | 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 | <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²) 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 |
︙ | ︙ | |||
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 | <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 using too much stack space. </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, | > > > > > > > > > > > > > > > > > > > > > > > > > | > > > > > > > > > | > > > > > > > | > > > > > | 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 | <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²) 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 using too much stack space. </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²) 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 |
︙ | ︙ | |||
239 240 241 242 243 244 245 | 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> | > > > > > | | > > > > > | > | > > > > | > | | 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 | 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 implementation will not support a larger value. </p> <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> |
︙ | ︙ |