SQLite Android Bindings
Check-in [7a62c59e53]
Not logged in

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

Overview
Comment:Update see.wiki to advise use of a URI parameter instead of "PRAGMA key = ?".
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 7a62c59e53946e0b0e75aec24919edd2d3792786
User & Date: dan 2017-05-03 19:59:54
Context
2017-05-22
15:32
Update this project to SQLite version 3.19.0. check-in: 2238cdeb55 user: dan tags: trunk
2017-05-03
19:59
Update see.wiki to advise use of a URI parameter instead of "PRAGMA key = ?". check-in: 7a62c59e53 user: dan tags: trunk
18:18
Restore standard behaviours of (a) activating a connection pool in wal mode and (b) switching into wal mode automatically if the flag is set even if SQLITE_HAS_CODEC is defined (they were previously disabled in this case). Strip any URI parameters from the database name before it is included in any log messages. Always build with SQLITE_USE_URI=1 defined. check-in: e8a9b149f7 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to www/see.wiki.

     3      3   
     4      4   <p>
     5      5     The <a href=http://www.sqlite.org/see/doc/trunk/www/readme.wiki>The SQLite Encryption Extension</a> 
     6      6     provides an easy way to create, read and write encrypted database files.
     7      7     It may be used with the SQLite Android bindings to add encrypted database
     8      8     capability to any application.
     9      9   
    10         -<h2> Building a SEE Enabled Version </h2>
           10  +<h2>1. Building a SEE Enabled Version </h2>
    11     11   <p>
    12     12   Unless you are using a [./install.wiki#prebuilt | pre-built aar file] to use
    13     13   the SEE extension with the SQLite Android bindings you will need to build
    14     14   a custom version, either as a [./install.wiki#customaar | custom aar file]
    15     15   or by [./install.wiki#directint|directly integrating] the code with the application.
    16     16   <p>
    17     17   To do this, follow the instructions linked above. Except, before running
................................................................................
    27     27   
    28     28   <verbatim>
    29     29     # If using SEE, uncomment the following:
    30     30     # LOCAL_CFLAGS += -DSQLITE_HAS_CODEC
    31     31   </verbatim>
    32     32   </ol>
    33     33   
    34         -<h2> Application Code Notes </h2>
           34  +<h2> 2. Application Code Notes </h2>
           35  +
           36  +<h3> 2.1. Opening an Encrypted Database </h3>
           37  +
           38  +<p>
           39  +The best way to open an existing encrypted database, or to create a new one, 
           40  +is to specify an encryption key as part of an 
           41  +<a href=http://sqlite.org/uri.html>SQLite URI database identifier</a>. For
           42  +example, instead of "DatabaseName.db", one of:
           43  +
           44  +<verbatim>
           45  +  file:DatabaseName.db?key=secret
           46  +  file:DatabaseName.db?hexkey=0123ABCD
           47  +</verbatim>
           48  +
           49  +<p>
           50  +The first form above, specifying a text key, requires SQLite version 3.19.0.
    35     51   
    36     52   <p>
    37         -  After opening or creating an encrypted database, the application must
    38         -  immediately execute a PRAGMA to configure the encryption key. This must
    39         -  be done before any other database methods are called. For example:
           53  +  Alternatively, after opening or creating an encrypted database, the
           54  +  application may immediately execute a PRAGMA to configure the encryption
           55  +  key. This must be done before any other database methods are called. For
           56  +  example:
    40     57   
    41     58   <verbatim>
    42     59     import org.sqlite.database.sqlite.SQLiteDatabase;
    43     60   
    44     61       ...
    45     62   
    46     63     SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase("my.db", null);
................................................................................
    63     80       ...
    64     81       void onConfigure(SQLiteDatabase db){
    65     82         db.execSQL("PRAGMA key = 'secretkey'");
    66     83       }
    67     84       ...
    68     85     }
    69     86   </verbatim>
           87  +
           88  +<p>
           89  +Note that <b>using the PRAGMA to specify the encryption key as described above
           90  +is incompatible with WAL mode</b>. In Android, enabling WAL mode also enables
           91  +connection pooling under the hood. This increase concurrency for multi-threaded
           92  +applications, but also makes configuring the encryption key with SQLite
           93  +directly using the PRAGMA unsafe (as Android may create and use new SQLite
           94  +connections that have not been configured at any time).
           95  +
    70     96   
    71     97   <p>
    72     98     Refer to the <a href=http://www.sqlite.org/see/doc/trunk/www/readme.wiki>
    73     99     SEE documentation</a> for further details regarding encryption keys.
          100  +
          101  +<h3> 2.2. Encrypting an Existing Database or Changing the Encryption Key</h3>
          102  +
          103  +<p>
          104  +An unencrypted database may be encrypted, or the encryption key of an
          105  +existing database changed using the "PRAGMA rekey" or "PRAGMA rehexkey" 
          106  +commands as described under "Using the "key" PRAGMA" in the 
          107  +<a href=http://www.sqlite.org/see/doc/trunk/www/readme.wiki>
          108  +SEE documentation</a>. 
          109  +
          110  +<p>
          111  +If using WAL mode, this suffers from the same problem as "PRAGMA key" - 
          112  +after (re-)encrypting the database it only modifies the key used internally
          113  +by one connection within the connection pool. Meaning that when Android
          114  +attempts to use a different connection to access the database it throws a
          115  +"file is encrypted or not a database" exception (SQLITE_NOTADB). Applications
          116  +that need to modify the encryption key of a WAL mode database should therefore
          117  +create a new SQLiteDatabase (or SQLiteOpenHelper) object to access the 
          118  +database, specifying the new key as part of the new URI identifier, 
          119  +immediately after running "PRAGMA rekey".
          120  +
          121  +
          122  +
          123  +<h3> 2.3. Other Differences From Non-SEE Builds </h3> 
    74    124   
    75    125   <p>Aside from supporting encrypted databases, SEE-enabled builds behave
    76    126   differently in two more respects:
    77    127   
    78    128   <ol>
    79         -  <li> <p>The SQLiteDatabase.<a href="http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#enableWriteAheadLogging()">enableWriteAheadLogging()</a> method does not enable
    80         -       connection pooling. It is not possible for connection pooling to be
    81         -       used with a SEE-enabled build (even if the database is unencrypted).
    82    129   
    83    130     <li> <p>In Android, if database corruption is encountered, or if an attempt is
    84    131          made to open a file that is not an SQLite database, the default
    85    132          behaviour is to delete the file and create an empty database file in 
    86    133          its place. In a SEE-enabled build, the default behaviour is to throw
    87    134          an exception.<br><br>
    88    135          The reason for this is that supplying an incorrect encryption key
    89    136          is indistinguishable from opening a file that is not a database file.
    90    137          And it seems too dangerous to simply delete the file in this case.
    91    138          <br><br>
    92    139          The default behaviour can be overriden using the
    93    140          <a href="http://developer.android.com/reference/android/database/DatabaseErrorHandler.html">DatabaseErrorHandler</a> interface.
          141  +
          142  +  <li> <p>Earlier versions of this module disabled WAL mode connection pooling
          143  +       altogether for SEE-enabled builds. This 
          144  +       <a href=http://www.sqlite.org/android/info/e8a9b149f74f517b>changed 
          145  +       here</a> as part of the 3.19.0 development cycle.
    94    146   </ol>