SQLite Android Bindings

Check-in [7a62c59e53]
Login

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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 7a62c59e53946e0b0e75aec24919edd2d3792786
User & Date: dan 2017-05-03 19:59:54.034
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
Unified Diff Ignore Whitespace Patch
Changes to www/see.wiki.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

<h1>Using The SQLite Encryption Extension</h1>

<p>
  The <a href=http://www.sqlite.org/see/doc/trunk/www/readme.wiki>The SQLite Encryption Extension</a> 
  provides an easy way to create, read and write encrypted database files.
  It may be used with the SQLite Android bindings to add encrypted database
  capability to any application.

<h2> Building a SEE Enabled Version </h2>
<p>
Unless you are using a [./install.wiki#prebuilt | pre-built aar file] to use
the SEE extension with the SQLite Android bindings you will need to build
a custom version, either as a [./install.wiki#customaar | custom aar file]
or by [./install.wiki#directint|directly integrating] the code with the application.
<p>
To do this, follow the instructions linked above. Except, before running









|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

<h1>Using The SQLite Encryption Extension</h1>

<p>
  The <a href=http://www.sqlite.org/see/doc/trunk/www/readme.wiki>The SQLite Encryption Extension</a> 
  provides an easy way to create, read and write encrypted database files.
  It may be used with the SQLite Android bindings to add encrypted database
  capability to any application.

<h2>1. Building a SEE Enabled Version </h2>
<p>
Unless you are using a [./install.wiki#prebuilt | pre-built aar file] to use
the SEE extension with the SQLite Android bindings you will need to build
a custom version, either as a [./install.wiki#customaar | custom aar file]
or by [./install.wiki#directint|directly integrating] the code with the application.
<p>
To do this, follow the instructions linked above. Except, before running
27
28
29
30
31
32
33
34
35


36














37
38
39

40
41
42
43
44
45
46

<verbatim>
  # If using SEE, uncomment the following:
  # LOCAL_CFLAGS += -DSQLITE_HAS_CODEC
</verbatim>
</ol>

<h2> Application Code Notes </h2>



<p>














  After opening or creating an encrypted database, the application must
  immediately execute a PRAGMA to configure the encryption key. This must
  be done before any other database methods are called. For example:


<verbatim>
  import org.sqlite.database.sqlite.SQLiteDatabase;

    ...

  SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase("my.db", null);







|

>
>

>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
|
|
>







27
28
29
30
31
32
33
34
35
36
37
38
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

<verbatim>
  # If using SEE, uncomment the following:
  # LOCAL_CFLAGS += -DSQLITE_HAS_CODEC
</verbatim>
</ol>

<h2> 2. Application Code Notes </h2>

<h3> 2.1. Opening an Encrypted Database </h3>

<p>
The best way to open an existing encrypted database, or to create a new one, 
is to specify an encryption key as part of an 
<a href=http://sqlite.org/uri.html>SQLite URI database identifier</a>. For
example, instead of "DatabaseName.db", one of:

<verbatim>
  file:DatabaseName.db?key=secret
  file:DatabaseName.db?hexkey=0123ABCD
</verbatim>

<p>
The first form above, specifying a text key, requires SQLite version 3.19.0.

<p>
  Alternatively, after opening or creating an encrypted database, the
  application may immediately execute a PRAGMA to configure the encryption
  key. This must be done before any other database methods are called. For
  example:

<verbatim>
  import org.sqlite.database.sqlite.SQLiteDatabase;

    ...

  SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase("my.db", null);
63
64
65
66
67
68
69
70
71









72
73
























74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93





94
    ...
    void onConfigure(SQLiteDatabase db){
      db.execSQL("PRAGMA key = 'secretkey'");
    }
    ...
  }
</verbatim>

<p>









  Refer to the <a href=http://www.sqlite.org/see/doc/trunk/www/readme.wiki>
  SEE documentation</a> for further details regarding encryption keys.

























<p>Aside from supporting encrypted databases, SEE-enabled builds behave
differently in two more respects:

<ol>
  <li> <p>The SQLiteDatabase.<a href="http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#enableWriteAheadLogging()">enableWriteAheadLogging()</a> method does not enable
       connection pooling. It is not possible for connection pooling to be
       used with a SEE-enabled build (even if the database is unencrypted).

  <li> <p>In Android, if database corruption is encountered, or if an attempt is
       made to open a file that is not an SQLite database, the default
       behaviour is to delete the file and create an empty database file in 
       its place. In a SEE-enabled build, the default behaviour is to throw
       an exception.<br><br>
       The reason for this is that supplying an incorrect encryption key
       is indistinguishable from opening a file that is not a database file.
       And it seems too dangerous to simply delete the file in this case.
       <br><br>
       The default behaviour can be overriden using the
       <a href="http://developer.android.com/reference/android/database/DatabaseErrorHandler.html">DatabaseErrorHandler</a> interface.





</ol>









>
>
>
>
>
>
>
>
>


>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>





<
<
<












>
>
>
>
>

80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128



129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
    ...
    void onConfigure(SQLiteDatabase db){
      db.execSQL("PRAGMA key = 'secretkey'");
    }
    ...
  }
</verbatim>

<p>
Note that <b>using the PRAGMA to specify the encryption key as described above
is incompatible with WAL mode</b>. In Android, enabling WAL mode also enables
connection pooling under the hood. This increase concurrency for multi-threaded
applications, but also makes configuring the encryption key with SQLite
directly using the PRAGMA unsafe (as Android may create and use new SQLite
connections that have not been configured at any time).


<p>
  Refer to the <a href=http://www.sqlite.org/see/doc/trunk/www/readme.wiki>
  SEE documentation</a> for further details regarding encryption keys.

<h3> 2.2. Encrypting an Existing Database or Changing the Encryption Key</h3>

<p>
An unencrypted database may be encrypted, or the encryption key of an
existing database changed using the "PRAGMA rekey" or "PRAGMA rehexkey" 
commands as described under "Using the "key" PRAGMA" in the 
<a href=http://www.sqlite.org/see/doc/trunk/www/readme.wiki>
SEE documentation</a>. 

<p>
If using WAL mode, this suffers from the same problem as "PRAGMA key" - 
after (re-)encrypting the database it only modifies the key used internally
by one connection within the connection pool. Meaning that when Android
attempts to use a different connection to access the database it throws a
"file is encrypted or not a database" exception (SQLITE_NOTADB). Applications
that need to modify the encryption key of a WAL mode database should therefore
create a new SQLiteDatabase (or SQLiteOpenHelper) object to access the 
database, specifying the new key as part of the new URI identifier, 
immediately after running "PRAGMA rekey".



<h3> 2.3. Other Differences From Non-SEE Builds </h3> 

<p>Aside from supporting encrypted databases, SEE-enabled builds behave
differently in two more respects:

<ol>




  <li> <p>In Android, if database corruption is encountered, or if an attempt is
       made to open a file that is not an SQLite database, the default
       behaviour is to delete the file and create an empty database file in 
       its place. In a SEE-enabled build, the default behaviour is to throw
       an exception.<br><br>
       The reason for this is that supplying an incorrect encryption key
       is indistinguishable from opening a file that is not a database file.
       And it seems too dangerous to simply delete the file in this case.
       <br><br>
       The default behaviour can be overriden using the
       <a href="http://developer.android.com/reference/android/database/DatabaseErrorHandler.html">DatabaseErrorHandler</a> interface.

  <li> <p>Earlier versions of this module disabled WAL mode connection pooling
       altogether for SEE-enabled builds. This 
       <a href=http://www.sqlite.org/android/info/e8a9b149f74f517b>changed 
       here</a> as part of the 3.19.0 development cycle.
</ol>