Adding upstream version 3.40.1.upstream/3.40.1 upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
author: Daniel Baumann <daniel.baumann@progress-linux.org> 2024-05-05 17:28:19 +0000
committer: Daniel Baumann <daniel.baumann@progress-linux.org> 2024-05-05 17:28:19 +0000
commit: 18657a960e125336f704ea058e25c27bd3900dcb (patch)
tree: 17b438b680ed45a996d7b59951e6aa34023783f2 /www/howtocorrupt.html
parent: Initial commit. (diff)
download: sqlite3-18657a960e125336f704ea058e25c27bd3900dcb.tar.xz
sqlite3-18657a960e125336f704ea058e25c27bd3900dcb.zip
1 files changed, 813 insertions, 0 deletions
diff --git a/www/howtocorrupt.html b/www/howtocorrupt.html
new file mode 100644
index 0000000..56d9a29
--- /dev/null
+++ b/www/howtocorrupt.html
@@ -0,0 +1,813 @@
+<!DOCTYPE html>
+<html><head>
+<meta name="viewport" content="width=device-width, initial-scale=1.0">
+<meta http-equiv="content-type" content="text/html; charset=UTF-8">
+<link href="sqlite.css" rel="stylesheet">
+<title>How To Corrupt An SQLite Database File</title>
+<!-- path= -->
+</head>
+<body>
+<div class=nosearch>
+<a href="index.html">
+<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
+</a>
+<div><!-- IE hack to prevent disappearing logo --></div>
+<div class="tagline desktoponly">
+Small. Fast. Reliable.<br>Choose any three.
+</div>
+<div class="menu mainmenu">
+<ul>
+<li><a href="index.html">Home</a>
+<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
+<li class='wideonly'><a href='about.html'>About</a>
+<li class='desktoponly'><a href="docs.html">Documentation</a>
+<li class='desktoponly'><a href="download.html">Download</a>
+<li class='wideonly'><a href='copyright.html'>License</a>
+<li class='desktoponly'><a href="support.html">Support</a>
+<li class='desktoponly'><a href="prosupport.html">Purchase</a>
+<li class='search' id='search_menubutton'>
+<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
+</ul>
+</div>
+<div class="menu submenu" id="submenu">
+<ul>
+<li><a href='about.html'>About</a>
+<li><a href='docs.html'>Documentation</a>
+<li><a href='download.html'>Download</a>
+<li><a href='support.html'>Support</a>
+<li><a href='prosupport.html'>Purchase</a>
+</ul>
+</div>
+<div class="searchmenu" id="searchmenu">
+<form method="GET" action="search">
+<select name="s" id="searchtype">
+<option value="d">Search Documentation</option>
+<option value="c">Search Changelog</option>
+</select>
+<input type="text" name="q" id="searchbox" value="">
+<input type="submit" value="Go">
+</form>
+</div>
+</div>
+<script>
+function toggle_div(nm) {
+var w = document.getElementById(nm);
+if( w.style.display=="block" ){
+w.style.display = "none";
+}else{
+w.style.display = "block";
+}
+}
+function toggle_search() {
+var w = document.getElementById("searchmenu");
+if( w.style.display=="block" ){
+w.style.display = "none";
+} else {
+w.style.display = "block";
+setTimeout(function(){
+document.getElementById("searchbox").focus()
+}, 30);
+}
+}
+function div_off(nm){document.getElementById(nm).style.display="none";}
+window.onbeforeunload = function(e){div_off("submenu");}
+/* Disable the Search feature if we are not operating from CGI, since */
+/* Search is accomplished using CGI and will not work without it. */
+if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
+document.getElementById("search_menubutton").style.display = "none";
+}
+/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
+function hideorshow(btn,obj){
+var x = document.getElementById(obj);
+var b = document.getElementById(btn);
+if( x.style.display!='none' ){
+x.style.display = 'none';
+b.innerHTML='show';
+}else{
+x.style.display = '';
+b.innerHTML='hide';
+}
+return false;
+}
+var antiRobot = 0;
+function antiRobotGo(){
+if( antiRobot!=3 ) return;
+antiRobot = 7;
+var j = document.getElementById("mtimelink");
+if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
+}
+function antiRobotDefense(){
+document.body.onmousedown=function(){
+antiRobot |= 2;
+antiRobotGo();
+document.body.onmousedown=null;
+}
+document.body.onmousemove=function(){
+antiRobot |= 2;
+antiRobotGo();
+document.body.onmousemove=null;
+}
+setTimeout(function(){
+antiRobot |= 1;
+antiRobotGo();
+}, 100)
+antiRobotGo();
+}
+antiRobotDefense();
+</script>
+<div class=fancy>
+<div class=nosearch>
+<div class="fancy_title">
+How To Corrupt An SQLite Database File
+</div>
+<div class="fancy_toc">
+<a onclick="toggle_toc()">
+<span class="fancy_toc_mark" id="toc_mk">&#x25ba;</span>
+Table Of Contents
+</a>
+<div id="toc_sub"><div class="fancy-toc1"><a href="#_file_overwrite_by_a_rogue_thread_or_process">1.  File overwrite by a rogue thread or process</a></div>
+<div class="fancy-toc2"><a href="#_continuing_to_use_a_file_descriptor_after_it_has_been_closed">1.1.  Continuing to use a file descriptor after it has been closed</a></div>
+<div class="fancy-toc2"><a href="#_backup_or_restore_while_a_transaction_is_active">1.2.  Backup or restore while a transaction is active</a></div>
+<div class="fancy-toc2"><a href="#_deleting_a_hot_journal">1.3.  Deleting a hot journal</a></div>
+<div class="fancy-toc2"><a href="#_mispairing_database_files_and_hot_journals_">1.4.  Mispairing database files and hot journals </a></div>
+<div class="fancy-toc1"><a href="#_file_locking_problems">2.  File locking problems</a></div>
+<div class="fancy-toc2"><a href="#_filesystems_with_broken_or_missing_lock_implementations">2.1.  Filesystems with broken or missing lock implementations</a></div>
+<div class="fancy-toc2"><a href="#_posix_advisory_locks_canceled_by_a_separate_thread_doing_close_">2.2.  Posix advisory locks canceled by a separate thread doing close()</a></div>
+<div class="fancy-toc3"><a href="#multiple_copies_of_sqlite_linked_into_the_same_application">2.2.1. Multiple copies of SQLite linked into the same application</a></div>
+<div class="fancy-toc2"><a href="#_two_processes_using_different_locking_protocols">2.3.  Two processes using different locking protocols</a></div>
+<div class="fancy-toc2"><a href="#_unlinking_or_renaming_a_database_file_while_in_use">2.4.  Unlinking or renaming a database file while in use</a></div>
+<div class="fancy-toc2"><a href="#_multiple_links_to_the_same_file">2.5.  Multiple links to the same file</a></div>
+<div class="fancy-toc2"><a href="#_carrying_an_open_database_connection_across_a_fork_">2.6.  Carrying an open database connection across a fork() </a></div>
+<div class="fancy-toc1"><a href="#_failure_to_sync">3.  Failure to sync</a></div>
+<div class="fancy-toc2"><a href="#_disk_drives_that_do_not_honor_sync_requests">3.1.  Disk drives that do not honor sync requests</a></div>
+<div class="fancy-toc2"><a href="#_disabling_sync_using_pragmas">3.2.  Disabling sync using PRAGMAs</a></div>
+<div class="fancy-toc1"><a href="#_disk_drive_and_flash_memory_failures">4.  Disk Drive and Flash Memory Failures</a></div>
+<div class="fancy-toc2"><a href="#_non_powersafe_flash_memory_controllers">4.1.  Non-powersafe flash memory controllers</a></div>
+<div class="fancy-toc2"><a href="#_fake_capacity_usb_sticks">4.2.  Fake capacity USB sticks</a></div>
+<div class="fancy-toc1"><a href="#_memory_corruption">5.  Memory corruption</a></div>
+<div class="fancy-toc1"><a href="#_other_operating_system_problems">6.  Other operating system problems</a></div>
+<div class="fancy-toc2"><a href="#_linux_threads">6.1.  Linux Threads</a></div>
+<div class="fancy-toc2"><a href="#_failures_of_mmap_on_qnx">6.2.  Failures of mmap() on QNX</a></div>
+<div class="fancy-toc2"><a href="#_filesystem_corruption">6.3.  Filesystem Corruption</a></div>
+<div class="fancy-toc1"><a href="#sqlite_configuration_errors">7. SQLite Configuration Errors</a></div>
+<div class="fancy-toc1"><a href="#_bugs_in_sqlite">8.  Bugs in SQLite</a></div>
+<div class="fancy-toc2"><a href="#_false_corruption_reports_due_to_database_shrinkage">8.1.  False corruption reports due to database shrinkage</a></div>
+<div class="fancy-toc2"><a href="#_corruption_following_switches_between_rollback_and_wal_modes">8.2.  Corruption following switches between rollback and WAL modes</a></div>
+<div class="fancy-toc2"><a href="#_i_o_error_while_obtaining_a_lock_leads_to_corruption">8.3.  I/O error while obtaining a lock leads to corruption</a></div>
+<div class="fancy-toc2"><a href="#_database_pages_leak_from_the_free_page_list">8.4.  Database pages leak from the free page list</a></div>
+<div class="fancy-toc2"><a href="#_corruption_following_alternating_writes_from_3_6_and_3_7_">8.5.  Corruption following alternating writes from 3.6 and 3.7.</a></div>
+<div class="fancy-toc2"><a href="#_race_condition_in_recovery_on_windows_system_">8.6.  Race condition in recovery on windows system.</a></div>
+<div class="fancy-toc2"><a href="#_boundary_value_error_in_the_secondary_journals_used_by_nested_transactions_">8.7.  Boundary value error in the secondary journals used by nested transactions.</a></div>
+</div>
+</div>
+<script>
+function toggle_toc(){
+var sub = document.getElementById("toc_sub")
+var mk = document.getElementById("toc_mk")
+if( sub.style.display!="block" ){
+sub.style.display = "block";
+mk.innerHTML = "&#x25bc;";
+} else {
+sub.style.display = "none";
+mk.innerHTML = "&#x25ba;";
+}
+}
+</script>
+</div>
+
+
+
+
+<h2 style="margin-left:1.0em" notoc="1" id="overview"> Overview</h2> 
+
+<p>An SQLite database is highly resistant to corruption.
+If an application crash, or an operating-system crash, or even
+a power failure occurs in the middle of a transaction, the partially
+written transaction should be automatically rolled back the next time
+the database file is accessed.  The recovery process is fully
+automatic and does not require any action on the part of the user
+or the application.
+</p>
+
+<p>Though SQLite is resistant to database corruption, it is not immune.
+This document describes the various ways that an SQLite database might
+go corrupt.</p>
+
+<h1 id="_file_overwrite_by_a_rogue_thread_or_process"><span>1. </span> File overwrite by a rogue thread or process</h1>
+
+<p>SQLite database files are ordinary disk files.
+That means that any process can open the file and 
+overwrite it with garbage.  There is nothing that the SQLite
+library can do to defend against this.</p>
+
+<a name="stalefd"></a>
+
+<h2 id="_continuing_to_use_a_file_descriptor_after_it_has_been_closed"><span>1.1. </span> Continuing to use a file descriptor after it has been closed</h2>
+
+<p>We have seen multiple cases where a file descriptor was open on a file,
+then that file descriptor was closed and reopened on an SQLite database.
+Later, some other thread continued to write into the
+old file descriptor, not realizing that the original file had been closed
+already.  But because the file descriptor had been reopened by SQLite,
+the information that was intended to go into the original file ended up
+overwriting parts of the SQLite database, leading to corruption of the
+database.</p>
+
+<p>One example of this occurred circa 2013-08-30 on the canonical repository
+for the <a href="http://www.fossil-scm.org/">Fossil DVCS</a>.  In that event,
+file descriptor 2 (standard error) was being erroneously closed (by
+<a href="http://www.stunnel.org/">stunnel</a>, we suspect) prior to 
+<a href="c3ref/open.html">sqlite3_open_v2()</a> so that the file descriptor used for the
+repository database file was 2.  Later, an application 
+bug caused an assert() statement to emit
+an error message by invoking write(2,...).  But since file descriptor 2 was 
+now connected to a database file, the error message
+overwrote part of the database.  To guard against this kind of problem,
+SQLite <a href="releaselog/3_8_1.html">version 3.8.1</a> (2013-10-17)
+and later refuse to use low-numbered file descriptors
+for database files. 
+(See <a href="compile.html#minimum_file_descriptor">SQLITE_MINIMUM_FILE_DESCRIPTOR</a> for additional information.)</p>
+
+<p>Another example of corruption caused by using a closed file
+descriptor was 
+<a href="https://code.facebook.com/posts/313033472212144/debugging-file-corruption-on-ios/">reported by facebook engineers</a> in a blog post on 2014-08-12.</p>
+
+<p>Another example of this error was reported against
+<a href="https://fossil-scm.org/">Fossil</a> on 2019-07-11.  A file descriptor would
+be opened for debugging output, but then closed and reopened by SQLite.
+But the debugging logic continued to write into the original file
+descriptor. See the
+<a href="https://fossil-scm.org/forum/forumpost/c51b9a1169">forum discussion</a>
+for the bug report and a link to the fix.
+
+</p><h2 id="_backup_or_restore_while_a_transaction_is_active"><span>1.2. </span> Backup or restore while a transaction is active</h2>
+
+<p>Systems that run automatic backups in the background might try to
+make a backup copy of an SQLite database file while it is in the middle
+of a transaction.  The backup copy then might contain some old and some
+new content, and thus be corrupt.</p>
+
+<p>The best approach to make reliable backup copies of an SQLite database
+is to make use of the <a href="backup.html">backup API</a> that is part of the SQLite library.
+Failing that, it is safe to make a copy of an SQLite database file as long
+as there are no transactions in progress by any process.  If the previous
+transaction failed, then it is important that any rollback journal
+(the <tt>*-journal</tt> file) or write-ahead log (the <tt>*-wal</tt> file)
+be copied together with the database file itself.</p>
+
+<a name="delhotjrnl"></a>
+
+<h2 id="_deleting_a_hot_journal"><span>1.3. </span> Deleting a hot journal</h2>
+
+<p>SQLite normally stores all content in a single disk file.  However,
+while performing a transaction, information necessary to recover the
+database following a crash or power failure is stored in auxiliary
+journal files.  Such journal files are described as <a href="fileformat2.html#hotjrnl">"hot"</a>.
+The journal files have the same name as the
+original database file with the addition
+of <tt>-journal</tt> or <tt>-wal</tt> suffix.</p>
+
+<p>SQLite must see the journal files in order to recover from a crash
+or power failure.  If the <a href="fileformat2.html#hotjrnl">hot journal files</a> are moved, deleted, or renamed
+after a crash or power failure, then automatic recovery will not work
+and the database may go corrupt.</p>
+
+<p>Another manifestation of this problem is
+<a href="shortnames.html#db83corrupt">database corruption caused by inconsistent use of 8+3 filenames</a>.</p>
+
+<a name="roguejrnl"></a>
+
+<h2 id="_mispairing_database_files_and_hot_journals_"><span>1.4. </span> Mispairing database files and hot journals </h2>
+
+<p> The previous example is a specific case of a more general problem:
+The state of an SQLite database is controlled by both the
+database file and the journal file.  In a quiescent state, the journal
+file does not exist and only the database file matters.
+But if the journal file does exist, it must be
+kept together with the database to avoid corruption.  The following
+actions are all likely to lead to corruption:
+</p><ul>
+<li> Swapping journal files between two different databases.
+</li><li> Overwritting a journal file with a different journal file.
+</li><li> Moving a journal file from one database to another.
+</li><li> Copying a database file without also copying its journal.
+</li><li> Overwriting a database file with another without also
+     deleting any hot journal associated with the original database.
+</li></ul>
+
+<h1 id="_file_locking_problems"><span>2. </span> File locking problems</h1>
+
+<p>SQLite uses file locks on the database file, and on the 
+<a href="wal.html">write-ahead log</a> or <a href="wal.html">WAL</a> file, to coordinate access between concurrent
+processes.  Without coordination, two threads or processes might try
+to make incompatible changes to a database file at the same time,
+resulting in database corruption.</p>
+
+<h2 id="_filesystems_with_broken_or_missing_lock_implementations"><span>2.1. </span> Filesystems with broken or missing lock implementations</h2>
+
+<p>SQLite depends on the underlying filesystem to do locking as the
+documentation says it will.  But some filesystems contain bugs in their
+locking logic such that the locks do not always behave as advertised.
+This is especially true of network filesystems and NFS in particular.
+If SQLite is used on a filesystem where the locking primitives contain
+bugs, and if two or more threads or processes try to access the same
+database at the same time, then database corruption might result.</p>
+
+<a name="posix_close_bug"></a>
+
+<h2 id="_posix_advisory_locks_canceled_by_a_separate_thread_doing_close_"><span>2.2. </span> Posix advisory locks canceled by a separate thread doing close()</h2>
+
+<p>The default locking mechanism used by SQLite on unix platforms is
+POSIX advisory locking.  Unfortunately, POSIX advisory locking has design
+quirks that make it prone to misuse and failure. In particular, any
+thread in the same process with a file descriptor that is holding a POSIX
+advisory lock can override that lock using a different file descriptor.
+One particularly pernicious problem is that the <tt>close()</tt> system
+call will cancel all POSIX advisory locks on the same file for all
+threads and all file descriptors in the process.</p>
+
+<p>So, for example, suppose a multi-thread process has
+two or more threads with separate SQLite database connections to the
+same database file.  Then a third thread comes along and wants to read
+something out of that same database file on its own, without using the 
+SQLite library.
+The third thread does an <tt>open()</tt>, a <tt>read()</tt> and then
+a <tt>close()</tt>.
+One would think this would be harmless.
+But the <tt>close()</tt> system call caused the
+locks held on the database by all the other threads to be dropped.  Those
+other threads have no way of knowing that their locks have just been
+trashed (POSIX does not provide any mechanism to determine this) and so
+they keep on running under the assumption that their locks are still valid.
+This can lead to two or more threads or processes trying to write to the
+database at the same time, resulting in database corruption.</p>
+
+<p>Note that it is perfectly safe for two or more threads to access the
+same SQLite database file using the SQLite library.  The unix drivers for
+SQLite know about the POSIX advisory locking quirks and work around them.
+This problem only arises when a thread tries to bypass the SQLite library
+and read the database file directly.</p>
+
+<h3 id="multiple_copies_of_sqlite_linked_into_the_same_application"><span>2.2.1. </span>Multiple copies of SQLite linked into the same application</h3>
+
+<p>As pointed out in the previous paragraph, SQLite takes steps to work
+around the quirks of POSIX advisory locking.  Part of that work-around involves
+keeping a global list (mutex protected) of open SQLite database files.
+But, if multiple copies of SQLite are linked into the same application,
+then there will be multiple instances of this global list.
+Database connections opened using one copy of the SQLite library
+will be unaware of database connections opened using the other copy,
+and will be unable to work around the POSIX advisory locking quirks.
+A <tt>close()</tt> operation on one connection might unknowingly 
+clear the locks on a different database connection, leading to database
+corruption.</p>
+
+<p>The scenario above sounds far-fetched.
+But the SQLite developers are aware of at 
+least one commercial product that was released
+with exactly this bug.  The vendor came to the SQLite developers seeking
+help in tracking down some infrequent database corruption issues they were
+seeing on Linux and Mac.  The problem was eventually traced to the
+fact that the application was linking against two separate copies of SQLite.
+The solution was to change the application build procedures to link against
+just one copy of SQLite instead of two.</p>
+
+<h2 id="_two_processes_using_different_locking_protocols"><span>2.3. </span> Two processes using different locking protocols</h2>
+
+<p>The default locking mechanism used by SQLite on unix platforms is
+POSIX advisory locking, but there are other options.  By selecting an
+alternative <a href="c3ref/vfs.html">sqlite3_vfs</a> using the <a href="c3ref/open.html">sqlite3_open_v2()</a> interface, an
+application can make use of other locking protocols that might be more
+appropriate to certain filesystems.  For example, dot-file locking might
+be select for use in an application that has to run on an NFS filesystem
+that does not support POSIX advisory locking.</p>
+
+<p>It is important that all connections to the same database file use 
+the same locking protocol.
+If one application is using POSIX advisory locks and another application
+is using dot-file locking, then the two applications will not see each
+other's locks and will not be able to coordinate database access, possibly
+leading to database corruption.</p>
+
+<a name="unlink"></a>
+
+<h2 id="_unlinking_or_renaming_a_database_file_while_in_use"><span>2.4. </span> Unlinking or renaming a database file while in use</h2>
+
+<p>If two processes have open connections to the same database file and
+one process closes its connection, unlinks the file, then creates a new
+database file in its place with the same name and reopens the new file,
+then the two processes will be talking to different database files with 
+the same name.  (Note that this is only possible on Posix and Posix-like
+systems that permit a file to be unlinked while it is still open for
+reading and writing.  Windows does not allow this to occur.)
+Since rollback journals and WAL files are based on the name of the database
+file, the two different database files will share the same rollback
+journal or WAL file.  A rollback or recovery for one of the databases
+might use content from the other database, resulting in corruption.
+A similar problem occurs if a database file is renamed while it is
+opened and a new file is created with the old name.</p>
+
+<p>In other words, unlinking or renaming an open database file 
+results in behavior that is undefined and probably undesirable.</p>
+
+<p>Beginning with SQLite <a href="releaselog/3_7_17.html">version 3.7.17</a> (2013-05-20),
+the unix OS interface will
+send SQLITE_WARNING messages to the <a href="errlog.html">error log</a> if a database file is unlinked
+while it is still in use.</p>
+
+<a name="alias"></a>
+
+<h2 id="_multiple_links_to_the_same_file"><span>2.5. </span> Multiple links to the same file</h2>
+
+<p>If a single database file has multiple links (either hard or soft links)
+then that is just another way of saying that the file has multiple names.
+If two or more processes open the database using different names, then
+they will use different rollback journals and WAL files.  That means that
+if one process crashes, the other process will be unable to recover the
+transaction in progress because it will be looking in the wrong place
+for the appropriate journal.</p>
+
+<p>In other words, opening and using a database file that has two or
+more names results in behavior that is undefined and probably undesirable.</p>
+
+<p>Beginning with SQLite <a href="releaselog/3_7_17.html">version 3.7.17</a> (2013-05-20),
+the unix OS interface will
+send SQLITE_WARNING messages to the <a href="errlog.html">error log</a> if a database file has 
+multiple hard links.</p>
+
+<p>Beginning with SQLite <a href="releaselog/3_10_0.html">version 3.10.0</a> (2016-01-06), 
+the unix OS interface will
+attempt to resolve symbolic links and open the database file by its
+canonical name.  Prior to version 3.10.0, opening a database file 
+through a symbolic link was similar to opening a database file
+that had multiple hard links and resulted in undefined behavior.</p>
+
+<a name="fork"></a>
+
+<h2 id="_carrying_an_open_database_connection_across_a_fork_"><span>2.6. </span> Carrying an open database connection across a fork() </h2>
+
+<p> Do not open an SQLite database connection, then fork(), then try
+to use that database connection in the child process.  All kinds of
+locking problems will result and you can easily end up with a corrupt
+database.  SQLite is not designed to support that kind of behavior.
+Any database connection that is used in a child process must be opened
+in the child process, not inherited from the parent.
+
+</p><p> Do not even call <a href="c3ref/close.html">sqlite3_close()</a> on a database connection from a
+child process if the connection was opened in the parent.  It is safe
+to close the underlying file descriptor, but the <a href="c3ref/close.html">sqlite3_close()</a>
+interface might invoke cleanup activities that will delete content out
+from under the parent, leading to errors and perhaps even database
+corruption.
+
+</p><h1 id="_failure_to_sync"><span>3. </span> Failure to sync</h1>
+
+<p>In order to guarantee that database files are always consistent, SQLite
+will occasionally ask the operating system to flush all pending writes to
+persistent storage then wait for that flush to complete.  This is 
+accomplished using the <tt>fsync()</tt> system call under unix and
+<tt>FlushFileBuffers()</tt> under Windows.  We call this flush of
+pending writes a "sync".</p>
+
+<p>Actually, if one is only concerned with atomic and consistent writes and
+is willing to forego durable writes, the sync operation does not need
+to wait until the content is completely stored on persistent media.  Instead,
+the sync operation can be thought of as an I/O barrier.  As long as all
+writes that occur before the sync are completed before any write that happens
+after the sync, no database corruption will occur.  If sync is operating as
+an I/O barrier and not as a true sync, then a power failure or system crash
+might cause one or more previously committed transactions to roll back
+(in violation of the "durable" property of "ACID") but the database will at
+least continue to be consistent, and that is what most people care about.</p>
+
+<h2 id="_disk_drives_that_do_not_honor_sync_requests"><span>3.1. </span> Disk drives that do not honor sync requests</h2>
+
+<p>Unfortunately, most consumer-grade mass storage devices lie about
+syncing.  Disk drives will report that content is safely on persistent
+media as soon as it reaches the track buffer and before actually being
+written to oxide.  This makes the disk drives seem to operate faster
+(which is vitally important to the manufacturer so that they can show
+good benchmark numbers in trade magazines).  And in fairness, the lie
+normally causes no harm, as long as there is no power loss or hard reset
+prior to the track buffer actually being written to oxide.  But if a
+power loss or hard reset does occur, and if that results in content that
+was written after a sync reaching oxide while content written before
+the sync is still in a track buffer, then database corruption can occur.</p>
+
+<p>USB flash memory sticks seem to be especially pernicious liars 
+regarding sync requests.  One can easily see this by committing a large
+transaction to an SQLite database on a USB memory stick.  The COMMIT
+command will return relatively quickly, indicating that the memory stick
+has told the operating system and the operating system has told SQLite that
+all content is safely in persistent storage, and yet the LED on the end
+of the memory stick will continue flashing for several more seconds. 
+Pulling out the memory stick while the LED is still flashing will frequently
+result in database corruption.</p>
+
+<p>Note that SQLite must believe whatever the operating system and hardware
+tell it about the status of sync requests.  There is no way for SQLite to
+detect that either is lying and that writes might be occurring out-of-order.
+However, SQLite in <a href="wal.html">WAL mode</a> is far more forgiving of
+out-of-order writes than in the default rollback journal modes.  In WAL
+mode, the only time that a failed sync operation can cause database corruption
+is during a <a href="wal.html#ckpt">checkpoint</a> operation.  A sync failure during a COMMIT might
+result in loss of durability but not in a corrupt database file.  Hence,
+one line of defense against database corruption due to failed sync operations
+is to use SQLite in WAL mode and to checkpoint as infrequently as possible.</p>
+
+<h2 id="_disabling_sync_using_pragmas"><span>3.2. </span> Disabling sync using PRAGMAs</h2>
+
+<p>The sync operations that SQLite performs to help ensure integrity
+can be disabled at run-time using the <a href="pragma.html#pragma_synchronous">synchronous pragma</a>.  By setting
+PRAGMA synchronous=OFF, all sync operations are omitted.  This makes
+SQLite seem to run faster, but it also allows the operating system to freely
+reorder writes, which could result in database corruption if a power failure
+or hard reset occurs prior to all content reaching persistent storage.</p>
+
+<p>For maximum reliability and for robustness against database corruption,
+SQLite should always be run with its default synchronous setting of FULL.</p>
+
+<a name="hardwarefault"></a>
+
+<h1 id="_disk_drive_and_flash_memory_failures"><span>4. </span> Disk Drive and Flash Memory Failures</h1>
+
+<p>An SQLite database can become corrupt if the file content changes 
+due to a disk drive or flash memory failure.  It is very rare, but disks 
+will occasionally flip a bit in the middle of a sector.</p>
+
+<h2 id="_non_powersafe_flash_memory_controllers"><span>4.1. </span> Non-powersafe flash memory controllers</h2>
+
+<p>We are told that in some flash memory controllers the wear-leveling logic
+can cause random filesystem damage if power is interrupted during a write.
+This can manifest, for example, as random changes in the middle of a file
+that was not even open at the time of the power loss.  So, for example,
+a device would be writing content into an MP3 file in flash memory when a
+power loss occurs, and that could result in an SQLite database being
+corrupted even though the database was not even in use at the time of the
+power loss.</p>
+
+<a name="fakeusb"></a>
+
+<h2 id="_fake_capacity_usb_sticks"><span>4.2. </span> Fake capacity USB sticks</h2>
+
+<p>There are many fraudulent USB sticks in circulation that report to have
+a high capacity (ex: 8GB) but are really only capable of storing a much
+smaller amount (ex: 1GB).   Attempts to write on these devices will
+often result in unrelated files being overwritten.  Any use of a fraudulent
+flash memory device can easily lead to database corruption, therefore.
+Internet searches such as "fake capacity usb" will turn up lots of
+disturbing information about this problem.
+
+</p><h1 id="_memory_corruption"><span>5. </span> Memory corruption</h1>
+
+<p>SQLite is a C-library that runs in the same address space as the 
+application that it serves.  That means that stray pointers, buffer
+overruns, heap corruption, or other malfunctions in the application can
+corrupt internal SQLite data structure and ultimately result in a
+corrupt database file.  Normally these kinds of problems manifest themselves
+as segfaults prior to any database corruption occurring, but there have
+been instances where application code errors have caused SQLite to
+malfunction subtly so as to corrupt the database file rather than
+panicking.</p>
+
+<p>The memory corruption problem becomes more acute when
+using <a href="mmap.html">memory-mapped I/O</a>.
+When all or part of the database file is mapped into the application's
+address space, then a stray pointer that overwrites any part of that
+mapped space will immediately corrupt the database file, without
+requiring the application to do a subsequent write() system call.</p>
+
+<h1 id="_other_operating_system_problems"><span>6. </span> Other operating system problems</h1>
+
+<p>Sometimes operating systems will exhibit non-standard behavior which
+can lead to problems.  Sometimes this non-standard behavior is deliberate,
+and sometimes it is a mistake in the implementation.  But in any event,
+if the operating performs differently from they way SQLite expects it to
+perform, the possibility of database corruption exists.</p>
+
+<h2 id="_linux_threads"><span>6.1. </span> Linux Threads</h2>
+
+<p>Some older versions of Linux used the LinuxThreads library for thread
+support.  LinuxThreads is similar to Pthreads, but is subtly different
+with respect to handling of POSIX advisory locks.  SQLite versions
+2.2.3 through 3.6.23 recognized that LinuxThreads were being used at
+runtime and took appropriate action to work around the non-standard
+behavior of LinuxThreads.  But most modern Linux implementations make
+use of the newer, and correct, NPTL implementation of Pthreads.  Beginning
+with SQLite <a href="releaselog/3_7_0.html">version 3.7.0</a> (2010-07-21), the use of NPTL is assumed.
+No checks are 
+made.  Hence, recent versions of SQLite will subtly malfunction and may 
+corrupt database files if used in multi-threaded application that run
+on older linux systems that make use of LinuxThreads.</p>
+
+<h2 id="_failures_of_mmap_on_qnx"><span>6.2. </span> Failures of mmap() on QNX</h2>
+
+<p>There exists some subtle problem with mmap() on QNX such that making
+a second mmap() call against a single file descriptor can cause
+the memory obtained from the first mmap() call to be zeroed.  SQLite on
+unix uses mmap() to create a shared memory region for transaction 
+coordination in <a href="wal.html">WAL mode</a>, and it will call mmap() multiple times
+for large transactions.  The QNX mmap() has been demonstrated to corrupt
+database file under that scenario.  QNX engineers are aware of this problem
+and are working on a solution; the problem may have already been fixed by
+the time you read this.</p>
+
+<p>When running on QNX, it is recommended that <a href="mmap.html">memory-mapped I/O</a> never
+be used.  Furthermore, to use <a href="wal.html">WAL mode</a>, it is recommended that applications
+employ the <a href="pragma.html#pragma_locking_mode">exclusive locking mode</a> in order to 
+use <a href="wal.html#noshm">WAL without shared memory</a>.
+
+
+<a name="fscorruption"></a>
+
+</p><h2 id="_filesystem_corruption"><span>6.3. </span> Filesystem Corruption</h2>
+
+<p>Since SQLite databases are ordinary disk files, any malfunction in the
+filesystem can corrupt the database.  Filesystems in modern operating systems
+are very reliable, but errors do still occur.  For example, on 2013-10-01
+the SQLite database that holds the
+<a href="http://wiki.tcl-lang.org/">Wiki for Tcl/Tk</a> went corrupt a few days
+after the host computer was moved to a dodgy build of the (linux) kernel
+that had issues in the filesystem layer.  In that event, the filesystem
+eventually became so badly corrupted that the machine was unusable, but
+the earliest symptom of trouble was the corrupted SQLite database.</p>
+
+<a name="cfgerr"></a>
+
+<h1 id="sqlite_configuration_errors"><span>7. </span>SQLite Configuration Errors</h1>
+
+<p>SQLite has many built-in protections against database corruption.
+But many of these protections can be disabled by configuration options.
+If protections are disabled, database corruption may occur.
+
+</p><p>The following are examples of disabling the built-in protection
+mechanisms of SQLite:
+
+</p><ul>
+<li><p>Setting <a href="pragma.html#pragma_synchronous">PRAGMA synchronous=OFF</a> can cause the database to
+go corrupt if there is an operating-system crash or power failure,
+though this setting is safe from damage due to application crashes.
+
+</p></li><li><p>Changing the <a href="pragma.html#pragma_schema_version">PRAGMA schema_version</a> while other database
+connections are open.
+
+</p></li><li><p>Using <a href="pragma.html#pragma_journal_mode">PRAGMA journal_mode=OFF</a> or <a href="pragma.html#pragma_journal_mode">PRAGMA journal_mode=MEMORY</a>
+and taking an application crash in the middle of a write transaction.
+
+</p></li><li><p>Setting <a href="pragma.html#pragma_writable_schema">PRAGMA writable_schema=ON</a> and then changing the
+database schema using DML statements can render the database completely
+unreadable, if not done carefully.
+</p></li></ul>
+
+<h1 id="_bugs_in_sqlite"><span>8. </span> Bugs in SQLite</h1>
+
+<p>SQLite is <a href="testing.html">very carefully tested</a> to help ensure that it is
+as bug-free as possible.  Among the many tests that are carried out for
+every SQLite version are tests that simulate power failures, I/O errors,
+and out-of-memory (OOM) errors and verify that no database corruption occurs
+during any of these events.  SQLite is also field-proven with approximately
+two billion active deployments with no serious problems.</p>
+
+<p>Nevertheless, no software is 100% perfect.  There have been a few
+historical bugs in SQLite (now fixed) that could cause database corruption.
+And there may be yet a few more that remain undiscovered.  Because of the
+extensive testing and widespread use of SQLite, bugs that result in
+database corruption tend to be very obscure.  The likelihood
+of an application encountering an SQLite bug is small.  To illustrate this,
+an account is given below 
+of all database-corruption bugs found in SQLite during the
+four-year period from 2009-04-01 to 2013-04-15.
+This account should give the reader an intuitive sense of the
+kinds of bugs in SQLite that manage to slip through testing procedures
+and make it into a release.</p>
+
+
+<h2 id="_false_corruption_reports_due_to_database_shrinkage"><span>8.1. </span> False corruption reports due to database shrinkage</h2>
+
+<p>If a database is written by SQLite version 3.7.0 or later and then
+written again by SQLite version 3.6.23 or earlier in such a way as to
+make the size of the database file decrease, then the next time that
+SQLite version 3.7.0 access the database file, it might report that the
+database file is corrupt.  The database file is not really corrupt, however.
+Version 3.7.0 was simply being overly zealous in its corruption detection.</p>
+
+<p>The problem was fixed on 2011-02-20.  The fix first appears in
+SQLite <a href="releaselog/3_7_6.html">version 3.7.6</a> (2011-04-12).</p>
+
+<h2 id="_corruption_following_switches_between_rollback_and_wal_modes"><span>8.2. </span> Corruption following switches between rollback and WAL modes</h2>
+
+<p>Repeatedly switching an SQLite database in and out of <a href="wal.html">WAL mode</a>
+and running the <a href="lang_vacuum.html">VACUUM</a> command in between switches, in one process or
+thread, can cause another process or thread that has the database file
+open to miss the fact that the database has changed.  That second process
+or thread might then try to modify the database using a stale cache and
+cause database corruption.</p>
+
+<p>This problem was discovered during internal testing and has never been
+observed in the wild.  The problem was fixed on 2011-01-27 and in version
+3.7.5.</p>
+
+<h2 id="_i_o_error_while_obtaining_a_lock_leads_to_corruption"><span>8.3. </span> I/O error while obtaining a lock leads to corruption</h2>
+
+<p>If the operating system returns an I/O error while attempting to obtain
+a certain lock on shared memory in <a href="wal.html">WAL mode</a> then SQLite might fail 
+to reset its cache,
+which could lead to database corruption if subsequent writes are attempted.</p>
+
+<p>Note that this problem only occurs if the attempt to acquire the lock
+resulted in an I/O error.  If the lock is simply not granted (because some
+other thread or process is already holding a conflicting lock) then no
+corruption will ever occur.  We are not aware of any operating systems that
+will fail with an I/O error while attempting to get a file lock on shared
+memory.  So this is a theoretical problem rather than a real problem.
+Needless to say, this problem has never been observed in the wild.  The
+problem was discovered while doing stress testing of SQLite in a test
+harness that simulates I/O errors.</p>
+
+<p>This problem was fixed on 2010-09-20 for SQLite version 3.7.3.</p>
+
+<h2 id="_database_pages_leak_from_the_free_page_list"><span>8.4. </span> Database pages leak from the free page list</h2>
+
+<p>When content is deleted from an SQLite database, pages that are no
+longer used are added to a free list and are reused to hold content
+added by subsequent inserts.  A bug in SQLite that was present in
+version 3.6.16 through 3.7.2 might cause pages to go missing out of
+the free list when <a href="pragma.html#pragma_incremental_vacuum">incremental_vacuum</a> was used.  This would not cause
+data loss.  But it would result in the database file being larger than
+necessary.  And it would cause the <a href="pragma.html#pragma_integrity_check">integrity_check pragma</a> to report
+pages missing from the free list.</p>
+
+<p>This problem was fixed on 2010-08-23 for SQLite version 3.7.2.</p>
+
+<h2 id="_corruption_following_alternating_writes_from_3_6_and_3_7_"><span>8.5. </span> Corruption following alternating writes from 3.6 and 3.7.</h2>
+
+<p>SQLite version 3.7.0 introduced a number of new enhancements to
+the SQLite database file format (such as but not limited to <a href="wal.html">WAL</a>).
+The 3.7.0 release was a shake-out release for these new features. 
+We expected to find problems and were not disappointed.</p>
+
+<p>If a database were originally created using SQLite version 3.7.0,
+then written by SQLite version 3.6.23.1 such that the size of the database
+file increased, then written again by SQLite version 3.7.0, the database
+file could go corrupt.</p>
+
+<p>This problem was fixed on 2010-08-04 for SQLite version 3.7.1.</p>
+
+<h2 id="_race_condition_in_recovery_on_windows_system_"><span>8.6. </span> Race condition in recovery on windows system.</h2>
+
+<p>SQLite version 3.7.16.2 fixes a subtle race condition in the locking
+logic on Windows systems.  When a database file is in need
+of recovery because the previous process writing to it crashed in the
+middle of a transaction and two or more processes try to open the 
+that database at the same time, then the race condition might cause
+one of those processes to get a false indication that the recovery 
+has already completed, allowing that process to continue using the
+database file without running recovery first.  If that process writes
+to the file, then the file might go corrupt.  This race condition
+had apparently existed in all prior versions of SQLite for Windows going
+back to 2004.  But the race was very tight.  Practically speaking, you
+need a fast multi-core machine in which you launch two processes to run
+recovery at the same moment on two separate cores.  This defect was
+on Windows systems only and did not affect the posix OS interface.</p>
+
+<a name="svptbug"></a>
+
+<h2 id="_boundary_value_error_in_the_secondary_journals_used_by_nested_transactions_"><span>8.7. </span> Boundary value error in the secondary journals used by nested transactions.</h2>
+
+<p>When a nested transaction is started using <a href="lang_savepoint.html">SAVEPOINT</a>, SQLite uses
+a secondary rollback journal to track the changes for the nested
+transaction, in case the inner transaction needs to be rolled back.  Secondary
+journals are not involved in protecting the database from corruption due
+to program crashes or power outages.  The secondary journals only come into
+play when rolling back an inner transaction of a nested transaction.
+
+</p><p>These secondary journals can be held either in memory or as temporary
+files on disk.  The default behavior is to store them on disk.  But that 
+can be changed using the <a href="compile.html#temp_store">-DSQLITE_TEMP_STORE</a> compile-time option,
+or at run-time using the <a href="pragma.html#pragma_temp_store">PRAGMA temp_store</a> statement.  The bug
+only arises when secondary journals are held in memory.
+
+</p><p>In SQLite <a href="releaselog/3_35_0.html">version 3.35.0</a> (2021-03-12), a new optimization was
+added so that when SQLite is holding secondary journals in memory,
+less memory will be used. Unfortunately, an boundary check in
+the new logic was coded incorrectly.
+What should have been a "&lt;" operator was coded as "&lt;=".  This
+error might cause the secondary journal to enter an inconsistent state
+if it is ever rolled back.  If additional changes are made and the
+outer transaction eventually commits, the database might be left in
+an inconsistent state.
+
+</p><p>This problem was discovered by an 
+<a href="https://sqlite.org/forum/forumpost/b03d86f9516cb3a2">independent researcher</a>
+who was attempting to find bugs in SQLite using a fuzzer.  The fuzzer found a
+failure in an <a href="assert.html">assert() statement</a> that is used
+to help verify the internal state of the secondary journal.  The bug was a
+sufficiently obscure corner-case that it might have gone unnoticed for many
+years, had it not been for the intensive use of assert() statements in SQLite,
+the persistence and tenacity of the security researchers, and their
+customized state-of-the-art fuzzer.
+
+</p><p>This problem was <a href="https://www.sqlite.org/src/info/73c2b50211d3ae26">fixed</a>
+in <a href="releaselog/3_37_2.html">version 3.37.2</a> (2022-01-06).
+</p><p align="center"><small><i>This page last modified on  <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink"  data-href="https://sqlite.org/docsrc/finfo/pages/howtocorrupt.in?m=015e9d7d1a173fcf1">2022-02-12 22:18:19</a> UTC </small></i></p>
+
author	Daniel Baumann <daniel.baumann@progress-linux.org>	2024-05-05 17:28:19 +0000
committer	Daniel Baumann <daniel.baumann@progress-linux.org>	2024-05-05 17:28:19 +0000
commit	18657a960e125336f704ea058e25c27bd3900dcb (patch)
tree	17b438b680ed45a996d7b59951e6aa34023783f2 /www/howtocorrupt.html
parent	Initial commit. (diff)
download	sqlite3-18657a960e125336f704ea058e25c27bd3900dcb.tar.xz sqlite3-18657a960e125336f704ea058e25c27bd3900dcb.zip