~ubuntu-branches/ubuntu/wily/sqlite3/wily

onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">

112

113

</form>

114

</div>

115

</table>

116

</div></div></div></div>

117

</td></tr></table>

118

119

120

121

122

123

<h2>Limits In SQLite</h2>

124

125

<p>

126

"Limits" in the context of this article means sizes or

127

quantities that can not be exceeded. We are concerned

128

with things like the maximum number of bytes in a

129

BLOB or the maximum number of columns in a table.

130

</p>

131

132

<p>

133

SQLite was originally designed with a policy of avoiding

134

arbitrary limits.

135

Of course, every program that runs on a machine with finite

136

memory and disk space has limits of some kind. But in SQLite,

137

those limits

138

were not well defined. The policy was that if it would fit

139

in memory and you could count it with a 32-bit integer, then

140

it should work.

141

</p>

142

143

<p>

144

Unfortunately, the no-limits policy has been shown to create

145

problems. Because the upper bounds were not well

146

defined, they were not tested, and bugs (including possible

147

security exploits) were often found when pushing SQLite to

148

extremes. For this reason, newer versions of SQLite have

149

well-defined limits and those limits are tested as part of

150

the <a href="testing.html">test suite</a>.

151

</p>

152

153

<p>

154

This article defines what the limits of SQLite are and how they

155

can be customized for specific applications. The default settings

156

for limits are normally quite large and adequate for almost every

157

application. Some applications may want to increase a limit here

158

or there, but we expect such needs to be rare. More commonly,

159

an application might want to recompile SQLite with much lower

160

limits to avoid excess resource utilization in the event of

161

bug in higher-level SQL statement generators or to help thwart

162

attackers who inject malicious SQL statements.

163

</p>

164

165

<p>

166

Some limits can be changed at run-time on a per-connection basis

167

using the <a href="c3ref/limit.html">sqlite3_limit()</a> interface with one of the

168

<a href="c3ref/c_limit_attached.html#sqlitelimitlength">limit categories</a> defined for that interface.

169

Run-time limits are designed for applications that have multiple

170

databases, some of which are for internal use only and others which

171

can be influenced or controlled by potentially hostile external agents.

172

For example, a web browser application might use an internal database

173

to track historical page views but have one or more separate databases

174

that are created and controlled by javascript applications that are

175

downloaded from the internet.

176

The <a href="c3ref/limit.html">sqlite3_limit()</a> interface allows internal databases managed by

177

trusted code to be unconstrained while simultaneously placing tight

178

limitations on databases created or controlled by untrusted external

179

code in order to help prevent a denial of service attack.

180

</p>

181

182

183

<ol>

184

185

<li><p><b>Maximum length of a string or BLOB</b></p>

186

187

<p>

188

The maximum number of bytes in a string or BLOB in SQLite is defined

189

by the preprocessor macro SQLITE_MAX_LENGTH. The default value

190

of this macro is 1 billion (1 thousand million or 1,000,000,000).

191

You can raise or lower this value at compile-time using a command-line

192

option like this:

193

</p>

194

195

<blockquote>-DSQLITE_MAX_LENGTH=123456789</blockquote>

196

197

<p>

198

The current implementation will only support a string or BLOB

199

length up to 2<small><sup>31</sup></small>-1 or 2147483647. And

200

some built-in functions such as hex() might fail well before that

201

point. In security-sensitive applications it is best not to

202

try to increase the maximum string and blob length. In fact,

203

you might do well to lower the maximum string and blob length

204

to something more in the range of a few million if that is

205

possible.

206

</p>

207

208

<p>

209

During part of SQLite's INSERT and SELECT processing, the complete

210

content of each row in the database is encoded as a single BLOB.

211

So the SQLITE_MAX_LENGTH parameter also determines the maximum

212

number of bytes in a row.

213

</p>

214

215

<p>

216

The maximum string or BLOB length can be lowered at run-time using

217

the <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitlength">SQLITE_LIMIT_LENGTH</a>,size) interface.

218

</p>

219

</li><a name="max_column"></a>

220

<li><p><b>Maximum Number Of Columns</b></p>

221

222

<p>

223

The SQLITE_MAX_COLUMN compile-time parameter is used to set an upper

224

bound on:

225

</p>

226

227

<ul>

228

<li>The number of columns in a table</li>

229

<li>The number of columns in an index</li>

230

<li>The number of columns in a view</li>

231

<li>The number of terms in the SET clause of an UPDATE statement</li>

232

<li>The number of columns in the result set of a SELECT statement</li>

233

<li>The number of terms in a GROUP BY or ORDER BY clause</li>

234

<li>The number of values in an INSERT statement</li>

235

</ul>

236

237

<p>

238

The default setting for SQLITE_MAX_COLUMN is 2000. You can change it

239

at compile time to values as large as 32767. On the other hand, many

240

experienced database designers will argue that a well-normalized database

241

will never need more than 100 columns in a table.

242

</p>

243

244

<p>

245

In most applications, the number of columns is small - a few dozen.

246

There are places in the SQLite code generator that use algorithms

247

that are O(N²) where N is the number of columns.

248

So if you redefine SQLITE_MAX_COLUMN to be a

249

really huge number and you generate SQL that uses a large number of

250

columns, you may find that <a href="c3ref/prepare.html">sqlite3_prepare_v2()</a>

251

runs slowly.</p>

252

253

254

<p>

255

The maximum number of columns can be lowered at run-time using

256

the <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitcolumn">SQLITE_LIMIT_COLUMN</a>,size) interface.

257

</p>

258

259

</li><a name="max_sql_length"></a>

260

<li><p><b>Maximum Length Of An SQL Statement</b></p>

261

262

<p>

263

The maximum number of bytes in the text of an SQL statement is

264

limited to SQLITE_MAX_SQL_LENGTH which defaults to 1000000. You

265

can redefine this limit to be as large as the smaller of SQLITE_MAX_LENGTH

266

and 1073741824.

267

</p>

268

269

<p>

270

If an SQL statement is limited to be a million bytes in length, then

271

obviously you will not be able to insert multi-million byte strings

272

by embedding them as literals inside of INSERT statements. But

273

you should not do that anyway. Use host <a href="lang_expr.html#varparam">parameters</a>

274

for your data. Prepare short SQL statements like this:

275

</p>

276

277

278

INSERT INTO tab1 VALUES(?,?,?);

279

</blockquote>

280

281

<p>

282

Then use the <a href="c3ref/bind_blob.html">sqlite3_bind_XXXX()</a> functions

283

to bind your large string values to the SQL statement. The use of binding

284

obviates the need to escape quote characters in the string, reducing the

285

risk of SQL injection attacks. It is also runs faster since the large

286

string does not need to be parsed or copied as much.

287

</p>

288

289

<p>

290

The maximum length of an SQL statement can be lowered at run-time using

291

the <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitsqllength">SQLITE_LIMIT_SQL_LENGTH</a>,size) interface.

292

</p>

293

</li><li><p><b>Maximum Number Of Tables In A Join</b></p>

294

295

<p>

296

SQLite does not support joins containing more than 64 tables.

297

This limit arises from the fact that the SQLite code generator

298

uses bitmaps with one bit per join-table in the query optimizer.

299

</p>

300

301

<p>

302

SQLite uses a very efficient O(N²) greedy algorithm for determining

303

the order of tables in a join and so a large join can be

304

<a href="c3ref/prepare.html">prepared</a> quickly.

305

Hence, there is no mechanism to raise or lower the limit on the

306

number of tables in a join.

307

</p>

308

</li><a name="max_expr_depth"></a>

309

<li><p><b>Maximum Depth Of An Expression Tree</b></p>

310

311

<p>

312

SQLite parses expressions into a tree for processing. During

313

code generation, SQLite walks this tree recursively. The depth

314

of expression trees is therefore limited in order to avoid

315

using too much stack space.

316

</p>

317

318

<p>

319

The SQLITE_MAX_EXPR_DEPTH parameter determines the maximum expression

320

tree depth. If the value is 0, then no limit is enforced. The

321

current implementation has a default value of 1000.

322

</p>

323

324

<p>

325

The maximum depth of an expression tree can be lowered at run-time using

326

the <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitexprdepth">SQLITE_LIMIT_EXPR_DEPTH</a>,size) interface if the

327

SQLITE_MAX_EXPR_DEPTH is initially positive. In other words, the maximum

328

expression depth can be lowered at run-time if there is already a

329

compile-time limit on the expression depth. If SQLITE_MAX_EXPR_DEPTH is

330

set to 0 at compile time (if the depth of expressions is unlimited) then

331

the <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitexprdepth">SQLITE_LIMIT_EXPR_DEPTH</a>,size) is a no-op.

332

</p>

333

334

335

</li><a name="max_function_arg"></a>

336

<li><p><b>Maximum Number Of Arguments On A Function</b></p>

337

338

<p>

339

The SQLITE_MAX_FUNCTION_ARG parameter determines the maximum number

340

of parameters that can be passed to an SQL function. The default value

341

of this limit is 100. SQLite should work with functions that have

342

thousands of parameters. However, we suspect that anybody who tries

343

to invoke a function with more than a few parameters is really

344

trying to find security exploits in systems that use SQLite,

345

not do useful work,

346

and so for that reason we have set this parameter relatively low.</p>

347

348

<p>The number of arguments to a function is sometimes stored in a signed

349

character. So there is a hard upper bound on SQLITE_MAX_FUNCTION_ARG

350

of 127.</p>

351

352

<p>

353

The maximum number of arguments in a function can be lowered at run-time using

354

the <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitfunctionarg">SQLITE_LIMIT_FUNCTION_ARG</a>,size) interface.

355

</p>

356

</li><a name="max_compound_select"></a>

357

<li><p><b>Maximum Number Of Terms In A Compound SELECT Statement</b></p>

358

359

<p>

360

A compound <a href="lang_select.html">SELECT</a> statement is two or more SELECT statements connected

361

by operators UNION, UNION ALL, EXCEPT, or INTERSECT. We call each

362

individual SELECT statement within a compound SELECT a "term".

363

</p>

364

365

<p>

366

The code generator in SQLite processes compound SELECT statements using

367

a recursive algorithm. In order to limit the size of the stack, we

368

therefore limit the number of terms in a compound SELECT. The maximum

369

number of terms is SQLITE_MAX_COMPOUND_SELECT which defaults to 500.

370

We think this is a generous allotment since in practice we almost

371

never see the number of terms in a compound select exceed single digits.

372

</p>

373

374

<p>

375

The maximum number of compound SELECT terms can be lowered at run-time using

376

the <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitcompoundselect">SQLITE_LIMIT_COMPOUND_SELECT</a>,size) interface.

377

</p>

378

379

380

</li><a name="max_like_pattern_length"></a>

381

<li><p><b>Maximum Length Of A LIKE Or GLOB Pattern</b></p>

382

383

<p>

384

The pattern matching algorithm used in the default <a href="lang_expr.html#like">LIKE</a> and <a href="lang_expr.html#glob">GLOB</a>

385

implementation of SQLite can exhibit O(N&sup2) performance (where

386

N is the number of characters in the pattern) for certain pathological

387

cases. To avoid denial-of-service attacks from miscreants who are able

388

to specify their own LIKE or GLOB patterns, the length of the LIKE

389

or GLOB pattern is limited to SQLITE_MAX_LIKE_PATTERN_LENGTH bytes.

390

The default value of this limit is 50000. A modern workstation can

391

evaluate even a pathological LIKE or GLOB pattern of 50000 bytes

392

relatively quickly. The denial of service problem only comes into

393

play when the pattern length gets into millions of bytes. Nevertheless,

394

since most useful LIKE or GLOB patterns are at most a few dozen bytes

395

in length, paranoid application developers may want to reduce this

396

parameter to something in the range of a few hundred if they know that

397

external users are able to generate arbitrary patterns.

398

</p>

399

400

<p>

401

The maximum length of a LIKE or GLOB pattern can be lowered at run-time using

402

the <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitlikepatternlength">SQLITE_LIMIT_LIKE_PATTERN_LENGTH</a>,size) interface.

403

</p>

404

</li><a name="max_variable_number"></a>

405

<li><p><b>Maximum Number Of Host Parameters In A Single SQL Statement</b></p>

406

407

<p>

408

A host <a href="lang_expr.html#varparam">parameter</a> is a place-holder in an SQL statement that is filled

409

in using one of the

410

<a href="c3ref/bind_blob.html">sqlite3_bind_XXXX()</a> interfaces.

411

Many SQL programmers are familiar with using a question mark ("?") as a

412

host parameter. SQLite also supports named host parameters prefaced

413

by ":", "$", or "@" and numbered host parameters of the form "?123".

414

</p>

415

416

<p>

417

Each host parameter in an SQLite statement is assigned a number. The

418

numbers normally begin with 1 and increase by one with each new

419

parameter. However, when the "?123" form is used, the host parameter

420

number is the number that follows the question mark.

421

</p>

422

423

<p>

424

SQLite allocates space to hold all host parameters between 1 and the

425

largest host parameter number used. Hence, an SQL statement that contains

426

a host parameter like ?1000000000 would require gigabytes of storage.

427

This could easily overwhelm the resources of the host machine.

428

To prevent excessive memory allocations,

429

the maximum value of a host parameter number is SQLITE_MAX_VARIABLE_NUMBER,

430

which defaults to 999.

431

</p>

432

433

<p>

434

The maximum host parameter number can be lowered at run-time using

435

the <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitvariablenumber">SQLITE_LIMIT_VARIABLE_NUMBER</a>,size) interface.

436

</p>

437

</li><a name="max_trigger_depth"></a>

438

<li><p><b>Maximum Depth Of Trigger Recursion</b></p>

439

440

<p>

441

SQLite limits the depth of recursion of triggers in order to prevent

442

a statement involving recursive triggers from using an unbounded amount

443

of memory.

444

</p>

445

446

<p>Prior to SQLite version 3.6.18, triggers were not recursive and so

447

this limit was meaningless. Beginning with version 3.6.18, recursive triggers

448

were supported but had to be explicitly enabled using the

449

<a href="pragma.html#pragma_recursive_triggers">PRAGMA recursive_triggers</a> statement. Beginning with version 3.7.0,

450

recursive triggers are enabled by default but can be manually disabled

451

using <a href="pragma.html#pragma_recursive_triggers">PRAGMA recursive_triggers</a>. The SQLITE_MAX_TRIGGER_DEPTH is

452

only meaningful if recursive triggers are enabled.</p>

453

454

<p>The default maximum trigger recursion depth is 1000.</p>

455

</li><a name="max_attached"></a>

456

<li><p><b>Maximum Number Of Attached Databases</b></p>

457

458

<p>

459

The <a href="lang_attach.html">ATTACH</a> statement is an SQLite extension

460

that allows two or more databases to be associated to the same database

461

connection and to operate as if they were a single database. The number

462

of simultaneously attached databases is limited to SQLITE_MAX_ATTACHED

463

which is set to 10 by default.

464

The code generator in SQLite uses bitmaps

465

to keep track of attached databases. That means that the number of

466

attached databases cannot be increased above 62.</p>

467

468

<p>

469

The maximum number of attached databases can be lowered at run-time using

470

the <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitattached">SQLITE_LIMIT_ATTACHED</a>,size) interface.

471

</p>

472

</li><a name="max_page_count"></a>

473

<li><p><b>Maximum Number Of Pages In A Database File</b></p>

474

475

<p>

476

SQLite is able to limit the size of a database file to prevent

477

the database file from growing too large and consuming too much

478

disk space.

479

The SQLITE_MAX_PAGE_COUNT parameter, which is normally set to

480

1073741823, is the maximum number of pages allowed in a single

481

database file. An attempt to insert new data that would cause

482

the database file to grow larger than this will return

483

SQLITE_FULL.

484

</p>

485

486

<p>

487

The largest possible setting for SQLITE_MAX_PAGE_COUNT is 2147483646.

488

When used with the maximum page size of 65536, this gives a maximum

489

SQLite database size of about 140 terabytes.</p>

490

491

<p>

492

The <a href="pragma.html#pragma_max_page_count">

493

max_page_count PRAGMA</a> can be used to raise or lower this

494

limit at run-time.

495

</p>

496

</li><li><p><b>Maximum Number Of Rows In A Table</b></p>

497

498

<p>

499

The theoretical maximum number of rows in a table is

500

2<sup><small>64</small></sup> (18446744073709551616 or about 1.8e+19).

501

This limit is unreachable since the maximum database size of 14 terabytes

502

will be reached first. A 14 terabytes database can hold no more than

503

approximately 1e+13 rows, and then only if there are no indices and if

504

each row contains very little data.

505

</li>

506

</ol>

507