Various restrictions in SQLite

English original: Limits in SQLite

This article defines the limitations of SQLite and how to customize specific applications for these restrictions. The default throttling settings are usually appropriate and are suitable for almost every application. Some applications may need to add a setting here or there, but we estimate it to be very rare. More generally, an application may need to recompile sqlite and lower the limit to avoid excessive resource utilization, and an error occurs on the advanced SQL statement Generator to help prevent an attacker from injecting a malicious SQL statement. On the limit categories using the Sqlite3_limit () interface, some restrictions are defined for the interface that can be changed at run time for each underlying connection. The runtime of an application design restricts multiple databases, and some internal-only restrictions can affect or control potentially hostile external agents. For example, a Web browser application might use an internal database to track historical page views, but it has one or more detached databases that are created and controlled through JavaScript applications that are downloaded from the Internet. Then the Sqlite3_limit () interface is allowed to manage the built-in database of constraints through trusted code, with strict restrictions on database creation or control, which rejects untrusted external code attack services.

No if Translated 7 days ago 0 Person Top Top translation of good Oh!

1, the maximum length of a string or blob The maximum number of bytes in a string or blob in SQLite is defined by the preprocessor macro sqlite_max_length. The default value for this macro is 1 billion, and you can adjust the default value at compile time using a command line parameter like this: -dsqlite_max_length=123456789 Only string or blob lengths are supported to be raised to the maximum 231-1 or 2147483647 in the current implementation. And this time some of the built-in functions such as Hex () will be called failed. It is best not to attempt to increase the maximum length of string and blob in security-sensitive applications. In fact, if you can, you can reduce the maximum length of a string and blob by a certain range (hundreds of-square bytes). In SQLite insert and select processing, all the contents of each row in the database are encoded into a single blob. So sqlite_max_length This parameter also defines the maximum number of bytes in a row. The maximum length of a string or blob can be lowered at run time by the Sqlite3_limit (Db,sqlite_limit_length,size) method. 2, the maximum number of columns Sqlite_max_column is used to set an upper limit at compile time: The default setting Sqlite_max_column value is 2000. You can adjust it to a maximum of 32767 at compile time. On the other hand, many experienced database designers would argue that a well-designed database would never require more than 100 columns in a table. The number of columns in most applications is very small, about dozens of. The algorithm used in the SQLite code generator is O (N²), and this N is the number of columns. So if you redefine sqlite_max_column as a huge number, use this large number of columns when generating SQL and you will find that SQLITE3_PREPARE_V2 () is running slowly. The maximum number of columns can be lowered at run time using the Sqlite3_limit (db,sqlite_limit_column,size) method. Number of columns in the table Number of columns in the index Number of columns in the view The number of items in the SET clause in the UPDATE statement Number of columns in the result set of the SELECT statement Number of items in the GROUP by or ORDER by clause Number of value items in INSERT statement

Temperament Dance Wangnikulas Translated 5 days ago 0 Person Top Top translation of good Oh!

Maximum length of SQL statement The maximum number of bytes in a SQL statement text, limited by sqlite_max_sql_length, which defaults to 1000000. You can re-define this limit to a lesser value of sqlite_max_length and 10,737,418,242. If an SQL statement is limited to 1 million bytes in length, it is clear that you will not be able to embed millions of bytes in the INSERT statement as a string literal. But you're not supposed to do that. At this point, you can use the placeholder parameters to prepare a short SQL statement as follows: INSERT into Tab1 VALUES (?,?,?); Then use Sqlite3 's _bind_xxxx () function to bind a large string value to this SQL statement. The use of bindings avoids the need to escape quotation marks in a string, while reducing the risk of being subjected to SQL injection attacks. It also runs faster because large strings do not have to be done in other ways for multiple conversions and copy operations. The maximum length of an SQL statement can be reduced at run time using the Sqlite3 _limit (db,sqlite_limit_sql_length,size) interface. Maximum number of tables in a single join operation SQLite does not support connection operations for more than 64 tables. This limitation stems from the fact that in the query optimizer, the SQLite code generator uses a bit bitmap for each connection table. SQLite uses an efficient query planner algorithm, so even a large-scale connection operation can be quickly preprocessed. So there is no mechanism to increase or decrease the number of tables in a single join operation. Maximum depth of an expression tree structure SQLite parses the expression into a number structure for processing. During code generation, SQLite iterates through the tree structure in a recursive manner. The depth of the expression tree structure is therefore added to the limit to avoid using too much stack space. The sqlite_max_expr_depth parameter determines the maximum depth of the expression tree structure. If its value is 0, it means no limit. The default value used by the current implementation is 1000. If Sqlite_max_expr_depth is initially positive, the maximum depth of the expression tree structure can be reduced at run time using the Sqlite3 _limit (db,sqlite_limit_expr_depth,size) interface. In other words, if the expression depth is already constrained at compile time, the maximum value of the depth of the expression tree structure can be lowered at run-time. If the value of if Sqlite_max_expr_depth is set to 0 at compile time (that is, the depth of the expression is unrestricted), then Sqlite3 's _limit (db,sqlite_limit_expr_depth,size) interface is an invalid operation.

Leoxu Translated 4 days ago 0 Person Top Top translation of good Oh!

maximum number of parameters in a function The Sqlite_max_function_arg parameter determines the maximum number of parameters that can be passed into an SQL function. The default value for this limit is 100. SQLite should be able to function with thousands of parameters. However, we are skeptical of those who use too many parameters, because they may be trying to find a security hole in a system that uses sqlite, rather than doing something useful, because we have already set a relatively low value for this parameter. The number of arguments passed into a function is sometimes stored in a signed character, so Sqlite_max_function_arg has a limit of 127 dead. The maximum number of parameters in a function can be reduced at run time using the Sqlite3 _limit (db,sqlite_limit_function_arg,size) interface. maximum number of paragraphs in a composite SELECT statement A composite SELECT statement is one of two or more SQL statements that are joined by the Operator Union, union All, EXCEPT, or INTERSECT. We refer to each individual SELECT statement in a composite select as a "paragraph". The code generator in SQLite uses a recursive algorithm to process composite SELECT statements. There is a need to limit the size of the stack, and we will limit the number of paragraphs in the composite select for this reason. The maximum limit parameter for a paragraph is sqlite_max_compound_select, and its default value is 500. We think this is a fairly generous allocation, because in practice it is rare to encounter more than one-digit number of paragraphs in a composite select. The maximum number of composite SELECT paragraphs can be reduced at run time using the Sqlite3 _limit (db,sqlite_limit_compound_select,size) interface. the maximum number of like or GLOB modes In certain extreme scenarios, the pattern matching algorithm used in the default like and Glob implementations of SQLite shows the performance consumption of O (N²), where N refers to the number of characters in the pattern. To avoid denial-of-service attacks from those who are able to specify like or GLOB patterns, the length of the like or GLOB pattern is limited by the number of bits specified by the sqlite_max_like_pattern_length. The default value for this is 50000. Modern workstations can calculate a like or GLOB mode with a 50,000-bit extreme length at relatively fast speeds. The issue of denial of service attacks can only be affected if the pattern length reaches a maximum of million byte bits. But since most of the practical like or glob pattern lengths are mostly only dozens of bytes, paranoid developers may want to limit this parameter to hundreds of if they know that external users have the ability to generate arbitrary patterns. The maximum length of the like or GLOB mode can be reduced at run time using the Sqlite3 _limit (db,sqlite_limit_like_pattern_length,size) interface.

Leoxu Translated 4 days ago 0 Person Top Top translation of good Oh!

Maximum number of preset parameters in a single SQL statement The preset parameter is a placeholder in the SQL statement that will be populated in the future using one of the _bind_xxxx () interfaces of Sqlite3. Many SQL programmers are familiar with using question marks ("?") as preset parameters. SQLite also supports a pre-set ":", "$", or "@" named as a preset parameter, and with "? 123 "The preset parameters are numbered in this form. Each preset parameter in the SQLite statement is assigned a number. The number is usually 1, and then the number of each new parameter plus one. However, when "? 123 "When this form is used, the number of the preset parameter will be the number followed by the question mark. SQLite allocates space between the preset parameters from 1 to the maximum number of preset parameters. Therefore, a SQL that contains a preset parameter like 1000000000 will require gigabytes of storage. This makes it easy to exceed the host's resource supply capabilities. To prevent this excessive memory allocation, the maximum number of preset parameters will be limited to a fixed value of sqlite_max_variable_number, which defaults to 999. The maximum number of preset parameters can be reduced at run time using the Sqlite3 _limit (db,sqlite_limit_variable_number,size) interface. Maximum depth of trigger recursion SQLite limits the recursive depth of a trigger, once to prevent a statement that involves a recursive trigger from using memory without a limit. SQLite before version 3.6.18, triggers are not recursive, so this limitation is meaningless. Starting with version 3.6.18, recursive triggers are supported, but you must use the PRAGMA recursive_triggers statement to explicitly declare that it is enabled. Starting with version 3.7.0, recursive triggers are enabled by default, but can be disabled manually using the PRAGMA recursive_triggers. The sqlite_max_trigger_depth is only valid when it is enabled. The default maximum depth of trigger recursion is 1000.

Leoxu Translated 4 days ago 0 Person Top Top translation of good Oh!

Maximum number of

dependent databases The attach  statement is a sqlite extension that enables two or more databases to be combined with the same database connection. Act as if they were a database. The number of databases that can be federated is limited to a constant of  sqlite_max_attached, with a default value of 10. The maximum number of federated databases cannot exceed 125. The maximum number of databases that the is federated with can be reduced at run time using the  sqlite3_limit (db,sqlite_limit_attached,size) interface. Maximum number of pages in a database file SQLite can limit the size of a database file to prevent the database file from becoming too large and consuming too much disk space. The Sqlite_max_page_count parameter, which is typically set to 1073741823, is the maximum number of pages in a database file. An operation that inserts new data that causes the database file to grow beyond this value will return  sqlite_full. The maximum possible setting for the  sqlite_max_page_count is 214748364. When the maximum page size is 65536, the largest SQLite database size is approximately 140 megabytes. The maximum number of pages pragma  can be used to increase or decrease this limit at run time. Maximum number of data rows in the table The maximum number of data rows in the table can theoretically go to 264  (18446744073709551616 or about 1.8e+19). This restriction is not available because the database will first reach the size limit of 140 kilobytes. A 140-byte-sized database can hold more data than would be near  1e+13 rows, and to accommodate so many rows of data, only when no exponential data exists, and each row contains only a very small amount of data.

leoxu translated 4 days ago 0 human top   top   translation is good!

Maximum database size Each database contains one or more pages. In a single database, each page is the same size, but different databases have different page sizes, between 512 bytes (inclusive) and 65536 bytes (inclusive). A database file consists of up to 2147483646 pages. Each page has a maximum of 65536 bytes, and the maximum database size is approximately 1.4e+14 bytes (1.4 billion megabytes or 128 kilobytes or 140 billion megabytes or 128000 gigabytes). This limit is not tested because the developer does not have the hardware conditions to meet this limitation. However, the test shows that when the database reaches the maximum file size of the underlying file system (which is usually much smaller than the theoretical maximum database size) and is unable to scale due to disk space exhaustion, SQLite still runs correctly and robustly. Maximum number of tables in a pattern At least one page is required for each table and index in the database file. The index can be created explicitly using the CREATE INDEX statement, or it can be implicitly created with the unique and primary key constraints. Because the maximum number of pages for a database file is 2147483646 (more than 2 billion), this is the upper limit of the number of tables and indexes in the schema. As soon as the database is opened, the entire pattern is scanned and parsed, and the parse tree of the schema is stored in memory. This means that the startup time and initial memory usage of the database are proportional to the pattern size.

Http://www.oschina.net/translate/limits-in-sqlite-new

Various restrictions in SQLite