SQLite on Android

1. SQLite on Android David Allen Quantum Mobile Solutions dallen@qmobilesolutions.com

2. Introduction to SQLite • Simple, small (~350KB), light weight RDMS implementation with simple API • Each database is stored as a single file containing both Pragma & Data • Writes cause file locking and are always sequential and blocking • Reads can be multi-tasked • Designed for local use • NOT Client-Server, is an integral part of the client application • Ideal for embedded and mobile devices • Open Source • ACID Compliant (Atomicity, Consistency, Isolation, Durability) • Uses dynamic, weakly types data types and syntax • Uses SQL query language – Implements most of SQL-92 • SQL is mostly portable between major database technologies • Does not fully implement ALTER TABLE (can’t modify/delete columns) • Partial support for TRIGGERS (does not support ‘For Each Statement’)

3. Introduction to SQLite • SQLite is reasonably performant • Typically comparable to MySQL • Most reported issues related to single file database and incorrect pragma settings • Performance tweaks to suite specific use • Available on multiple OS’s • Ships natively on all mobile platforms as standard except Windows Phone (libraries available for .Net) • Version included with Android varies with OS version (& even manufacturer): • ≤ 2.1 : SQLite 3.5.9 • 2.2 - 2.3.3 : SQlite 3.6.22 • 3.0 – 4.0.3 : SQLite 3.7.4 • 4.1 – 4.2.x : SQLite 3.7.11 • Latest SQLite Version: 3.7.17 • SDK automatically links in the version of the lowest supported OS of your project (usually 3.5.9 or 3.6.22)

4. Architectural Tips • Assess when to use a local database and when to make data available via web services • Be mindful of processing power, battery usage, bandwidth • What is/are the producer and consumer of the data? • When to use local database (such as SQLite) • Persistent Storage • Structured Data • Data Manipulation • Relatively Complex Data • Data Relationships • When Not to use local database (such as SQLite) • Highly Complex Relationships and queries • Very Large tables • Rapidly changing data • Lots of ‘Data processing’

5. Architectural Tips – Database Design • Use meaningful database, table, field and field names • Avoid Domain pollution (domain as in data modeling, NOT networking) • Tables should only store intrinsically related fields • Where possible Tables should be self contained • Reference data in other databases if not in tables domain • Normalize the database • Make database orthogonal • Do not duplicate core data across tables (unless • Be mindful of processing power, battery usage, bandwidth

6. Performance Tips • Use multiple databases to avoid locking • Use multiple databases to allow multi-threaded access • Avoid multiple open and close statements • Synchronous access takes time – only use on tables when needed • Use Indexes • Use transactions for larger groups of data manipulation • Use Indexes when creating table (PRIMARY and UNIQUE) or by CREATE INDEX • Split tables • Compact database - Use VACUUM – manual and auto (or on demand based on pagecount and freelist count) – do in thread, may take time do after big delete • For complex nested queries, break up and use temporary tables to stire results • Store Compressed data • Use LIMIT=1 when checking for something existing • Avoid GLOB and LIKE where possible. Avoid LENGTH if long strings • Use IN instead of OR when comparing single variable to multiple values - can sue index • Use SQL statements rather then code • Pragma settings • Main.page_size = 4096; • Main.cache_size=5000; • Main.locking+_mode=EXCLUSIVE; - not supported use call • Main.synchronous=OFF (or NORMAL); • Main,journal_mode=WAL; - may not be supported • main.temp_store=MEMORY; - • Count_Changes=OFF; - or sqlite_changes() api • Normalize the database • Make database orthogonal • Do not duplicate core data across tables (unless • Be mindful of processing power, battery usage, bandwidth

7. PERFORMS (cont.) • sqliTEdATABASE.SETlOCKIGNeNABLES(FALSE); • SqliteDatabase.execSQL(“PRAGMA read_uncommited=true;”); • - for each connection • SQLiteDatabase.execSQL(“begin immediate tranbsaction);

8. CERTIFICATES • CERTIFICATE AUTHORITIES (CAs) • Trusted organizations that issues and manages certificates • Issues public Root Certificates that validate its own identity • Issues private user certificates that validate the identity of the party the certificate was issued to • Acts as a trusted party that vouches for your the identity • Can be used for public communications • SELF SIGNED CERTIFICATES • Anyone can generate their own Self signed Certificates • They are self validated so require implicit trust • Are not managed or validated by third parties so their exchange must be managed by the developers • Only really suitable for private communication

9. TYPES OF CERTIFICATES ADVANATGES / DISADVANTAGES • CERTIFICATE AUTHORITY (CA) ISSUED • Can be accessed by anyone you provide it to • Can be dynamically distributed from your server • Is validated by the CA • Does not have to be distributed with your application • Widely used • Most CAs supported by most browsers • Built in support in Android architecture • Must be purchased • Restricted Certificate properties • Not all authorities are equal (handle certificate chains differently) • Some root certificates have been compromised • Security increasingly becoming a issue

10. TYPES OF CERTIFICATES ADVANATGES / DISADVANTAGES • SELF SIGNED CERTIFICATES • Free • You have complete control • Complete customization of certificate properties • More secure, if you store them securely (essentially certificate pining) • Ideal if your server application will only communicate with your own client applications • Should be pre-distributed so must normally included with you application • Are not managed or validated by third parties so their exchange must be managed by the developers • Only really suitable for private communication • Android architecture presents challenges

11. PROBLEMS WITH ANDROID • USES A PRE-LOADED SET OF ROOT CERTIFICATES • System Trust Store • Pre 4.0 SYSTEM TRUST STORE IS READ ONLY • Pre 4.0 CAN NOT DIRECTLY SUPPORT SELF SIGNED CERTIFICATES • CERTIFICATES NOT STANDARDIZED BY GOOGLE • Manufacturers load root certificates as they see fit • Differences between manufacturers • Differences between Android versions

12. SOLUTIONS • BY-PASS CHECKS • Very Insecure • ACCEPT ALL CERTIFICATES • Allows Encryption but does not validate the server or Data Integrity • Vulnerable to ‘Man in the Middle’ attacks • USE CUSTOM TRUST STORE TO VALIDATE SERVER CERTIFICATE • Allows Encryption, validates Data Integrity and Certificate • Server can still be spoofed • USE CUSTOM TRUST STORE AND CUSTOM HOST VERIFIER • Allows Encryption, validates Data Integrity, Certificate and Server • ALSO USE CLIENT CERTIFICATE • All above plus validates the client application identity

13. CREATE A CUSTOM KEY STORE FOR SELF SIGNED CERTIFICATE • DISTRIBUTE SELF SIGNED CERIFICATE WITH APPLICATION • Create an encrypted KeyStore containing the Certificate • Store as Resource in the Raw or Assets directory • Include Certificate filename, password and encoding in application • ON INITIALIZATION, LOAD CERTIFICATE INTO KEYSTORE • WRITE KEYSTORE TO A LOCAL FILE • Now available in app • READ IN KEYSTORE FROM FILE TO CREATE TRUST MANAGER ETC.

14. CODE: LOADING KEYSTORE FROM RESOURCE protectedstatic KeyStore loadKeyStore(Context ctx, String keyStorePassword, Integer sourceresourceReference, KeyStoreFormat storeEncoding) { KeyStore destKeyStore = null; try { destKeyStore = KeyStore.getInstance(encoding.toString()); InputStream in = ctx.getResources().openRawResource(sourceKeyStoreResourceReference); try { destKeyStore.load(in, keyStorePassword.toCharArray()); } finally { in.close(); } } catch (Exception e) { thrownew RuntimeException(e); } return destKeyStore; }

15. CODE: WRITE KEYSTORE TO LOCAL FILE protectedstaticvoid saveKeyStore(KeyStore localKeyStore, String localKeyStoreFilePath, String password) throws IOException, GeneralSecurityException { File localKeyStoreFile = new File (localKeyStoreFilePath); FileOutputStream out = new FileOutputStream(localKeyStoreFile); localKeyStore.store(out, password.toCharArray()); out.close(); }

16. CODE: CREATE CUSTOM TRUST MANAGER publicclass CustomTrustManager implements X509TrustManager { privatestaticfinal String TAG = CustomTrustManager.class.getSimpleName(); private X509TrustManager _defaultTrustManager; private X509TrustManager _localTrustManager; private TrustCertificateMode _mode; private X509Certificate[] _acceptedIssuers; publicenum TrustCertificateMode { CA_ONLY, // Server Certificate verification, HostName Verification, Client Certificate SELF_SIGNED_ONLY, // Server Certificate verification, HostName Verification BOTH// No TSL/SSL }; publicenum TrustStoreFormat { BKS, // Bouncy Castle JKS, PKCS12, JCEKS, UBER } public CustomTrustManager(KeyStore localTrustStore, TrustCertificateMode mode) { createCustomTrustManager(localTrustStore, mode); }

17. CODE: CREATE CUSTOM TRUST MANAGER privatevoid createCustomTrustManager(KeyStore localTrustStore) { this._mode = mode; List<X509Certificate> allIssuers = new ArrayList<X509Certificate>(); try { this._defaultTrustManager = null; if (localTrustStore == null){ this._localTrustManager = (X509TrustManager)getUniveralTrustManager(); } else { this._localTrustManager = new LocalStoreX509TrustManager(localTrustStore); } for (X509Certificate cert : _localTrustManager.getAcceptedIssuers()) { allIssuers.add(cert); } _acceptedIssuers = allIssuers.toArray(new X509Certificate[allIssuers.size()]); } catch (GeneralSecurityException e) { thrownew RuntimeException(e); } }

18. CODE: CREATE CUSTOM HOST VERIFIER publicstatic X509HostnameVerifier getHostNameVerifier(CommsSecuriyMode mode){ boolean verifyHost = false; if ( (mode == CommsSecurityMode.FULL_SECURE) || (mode == CommsSecurityMode.SERVER_CERT_AND_HOST) ){ verifyHost = true; } if (verifyHost){ return ( new BrowserCompatHostnameVerifier() ); } else { return (new X509HostnameVerifier() { publicboolean verify(String hostname, SSLSession session) { returntrue; } publicvoid verify(String host, SSLSocket ssl)throws IOException { } publicvoid verify(String host, X509Certificate cert) throws SSLException { } publicvoid verify(String host, String[] cns, String[] subjectAlts) throws SSLException { } } ); } }

19. CODE: TRUSTED HTTP CONNECTION publicstatic HttpsURLConnection openSecureURLConnection(KeyStore trustStore, String CommsSecurityMode secMode, TrustCertificateMode mode, String urlString){ HttpsURLConnection urlConnection = null; try { CustomTrustManager trustManager = new CustomTrustManager(trustStore, mode); SSLContext sslCtx = SSLContext.getInstance("TLS"); sslCtx.init(null, CustomTrustManager.getUniveralTrustManagers(), null); //set the hostname verifier based on the security mode HostnameVerifier hostNameVerifier = CustomHostNameVerifier.getHostNameVerifier(secMode); HttpsURLConnection.setDefaultHostnameVerifier(hostNameVerifier); URL url = new URL(urlString); urlConnection = (HttpsURLConnection) url.openConnection(); urlConnection.setSSLSocketFactory(sslCtx.getSocketFactory()); } catch (NoSuchAlgorithmException e) { } catch (KeyStoreException e) { } catch (UnrecoverableKeyException e) { } catch ( KeyManagementException e){ } catch ( MalformedURLException e){ } catch ( IOException e){ } return urlConnection; }

20. CODE: TRUSTED HTTP CLIENT publicstatic HttpClient createTrustedHttpClient(CommsSecurityMode secMode, KeyStore trustStore, TrustCertificateMode certificateMode, Integer timeout) { SchemeRegistry schemeRegistry = new SchemeRegistry(); schemeRegistry.register(new Scheme("http", PlainSocketFactory.getSocketFactory(), 80)); CustomSSLSocketFactory sslSocketFactory; try { CustomTrustManager trustManager = new CustomTrustManager(trustStore, certificateMode); SSLContext sslCtx = SSLContext.getInstance("TLS"); sslCtx.init(null, CustomTrustManager.getUniveralTrustManagers(), null); HostnameVerifier hostnameVerifier = CustomHostNameVerifier.getHostNameVerifier(secMode); sslSocketFactory = new CustomSSLSocketFactory(sslCtx, (X509HostnameVerifier) hostnameVerifier); schemeRegistry.register(new Scheme("https", sslSocketFactory, 443)); } catch (KeyManagementException e) { } catch (NoSuchAlgorithmException e) { } catch (KeyStoreException e) { } catch (UnrecoverableKeyException e) { } // set up the client timeout parameters HttpParams params = new BasicHttpParams(); HttpConnectionParams.setConnectionTimeout(params, timeout); HttpConnectionParams.setSoTimeout(params, timeout); ClientConnectionManager cm = new ThreadSafeClientConnManager(params, schemeRegistry); return new DefaultHttpClient(cm, params); }

21. CODE: Custom SSL Socket Factory publicclass CustomSSLSocketFactory implements LayeredSocketFactory { private SSLContext sslContext; private SSLSocketFactory socketFactory; private X509HostnameVerifier hostnameVerifier; public CustomSSLSocketFactory(SSLContext sslContext, X509HostnameVerifier hostnameVerifier) { this.sslContext = sslContext; this.socketFactory = sslContext.getSocketFactory(); this.hostnameVerifier = hostnameVerifier; } public Socket createSocket() throws IOException { return socketFactory.createSocket(); } public Socket createSocket(Socket socket, String host, int port, boolean autoClose) throws IOException, UnknownHostException { SSLSocket sslSocket = (SSLSocket) socketFactory.createSocket(socket,host, port, autoClose); hostnameVerifier.verify(host, sslSocket); return sslSocket; }

22. CODE: Custom SSL Socket Factory (cont.) public Socket connectSocket(Socket sock, String host, int port, InetAddress localAddress, int localPort, HttpParams params) throws IOException, UnknownHostException, ConnectTimeoutException { if ( (host == null) || (params == null) ) { thrownew IllegalArgumentException("Parameters or Host may not be null."); } SSLSocket sslsock = (SSLSocket) ((sock != null) ? sock : createSocket()); if ((localAddress != null) || (localPort > 0)) { if (localPort < 0){ localPort = 0; } InetSocketAddress isa = new InetSocketAddress(localAddress, localPort); sslsock.bind(isa); } int connTimeout = HttpConnectionParams.getConnectionTimeout(params); int soTimeout = HttpConnectionParams.getSoTimeout(params); InetSocketAddress remoteAddress = new InetSocketAddress(host, port); sslsock.connect(remoteAddress, connTimeout); sslsock.setSoTimeout(soTimeout); try { hostnameVerifier.verify(host, sslsock); } catch (IOException iox) { try { sslsock.close(); } catch (Exception x) { } throw iox; } return sslsock; }

23. CODE: HTTPS GET publicstatic String httpGet(Context ctx, String urlString, String host, String port, CommsSecurityMode secMode, CustomTrustManager.TrustCertificateMode certMode) throws IOException { HttpURLConnection conn = TrustedURLConnection.openSecureURLConnection(ctx, urlString, host, port, secMode, certMode); StringBuilder sb = new StringBuilder(); if (conn != null){ // connect to the server (avoids potential issues with order of connections) conn.connect(); // Check for successful response code or throw error int responseCode = conn.getResponseCode(); if (responseCode != 200) { conn.disconnect(); thrownew IOException(conn.getResponseMessage()); } // Buffer the result into a string BufferedReader buffrd = new BufferedReader(new InputStreamReader(conn.getInputStream()) ); sb = new StringBuilder(); String line; while ((line = buffrd.readLine()) != null) { sb.append(line); } buffrd.close(); conn.disconnect(); } return sb.toString(); }

24. CODE: HTTPS POST publicstatic String httpPostRequest (Context ctx, String content, int timeout, String uri, CommsSecurityMode securityMode, TrustCertificateMode certificateMode, String contentType, String responseType, String trustStoreLocation, String trustStoreFilename, String trustStorePassword, KeyStoreFormat trustStoreFormat, String keyStoreLocation, String keyStoreFilename, String keyStorePassword, KeyStoreFormat keyStoreFormat) { String resp = null; try { HttpParams httpParams = new BasicHttpParams(); HttpConnectionParams.setConnectionTimeout(httpParams, timeout); HttpConnectionParams.setSoTimeout(httpParams, timeout); TrustedHttpClient client =TrustedHttpClient.createTrustedHttpClient(ctx, securityMode, certificateMode, timeout, trustStoreLocation, trustStoreFilename, trustStorePassword, trustStoreFormat, keyStoreLocation, keyStoreFilename, keyStorePassword, keyStoreFormat); if (securityMode != CommsSecurityMode.INSECURE){ uri = ensureHttpsPreamble(uri); } HttpPost request = new HttpPost(uri); StringEntity se = new StringEntity(content); se.setContentEncoding(new BasicHeader(HTTP.CONTENT_TYPE, contentType)); request.setEntity(se); request.setHeader(RESPONSE_TYPE, responseType); request.setHeader(CONTENT_TYPE, contentType); HttpResponse response = client.execute(request);

25. CODE: HTTPS POST (cont.) if (response != null){ HttpEntity entity = response.getEntity(); if (entity != null){ InputStream in = entity.getContent(); // Buffer the result into a string BufferedReader buffrd = new BufferedReader(new InputStreamReader(in)); StringBuilder sb = new StringBuilder(); String line; while ((line = buffrd.readLine()) != null) { sb.append(line); } buffrd.close(); in.close(); resp = sb.toString(); } } } catch (Exception ex){} return resp; }