[MOD-2259] iOS: Encrypted Database Adapter error on module upgrade

Description

Issue Description

Steps to reproduce

Stack Trace

Jan 21 11:06:55 iPad SpringBoard[48] <Error>: SecTrustEvaluate [leaf IssuerCommonName SubjectCommonName] 
Jan 21 11:06:55 iPad SpringBoard[48] <Notice>: MIS: Using empty blacklist. 
Jan 21 11:06:55 iPad SpringBoard[48] <Error>: SecTrustEvaluate [leaf IssuerCommonName SubjectCommonName] 
Jan 21 11:06:55 iPad SpringBoard[48] <Notice>: MIS: Using empty blacklist. 
Jan 21 11:06:55 iPad securityd[81] <Error>: secTaskDiagnoseEntitlements MISSING keychain entitlements: no stored taskRef found 
Jan 21 11:06:55 iPad securityd[81] <Error>: secTaskDiagnoseEntitlements MISSING keychain entitlements: no stored taskRef found 
Jan 21 11:06:55 iPad amfid[655] <Error>: SecTrustEvaluate [leaf IssuerCommonName SubjectCommonName] 
Jan 21 11:06:55 iPad amfid[655] <Notice>: MIS: Using empty blacklist. 
Jan 21 11:06:55 iPad securityd[81] <Error>: SecOCSPSingleResponseCalculateValidity OCSPSingleResponse: nextUpdate 0.82 days ago 
Jan 21 11:06:55 iPad securityd[81] <Error>: SecOCSPResponseCreateWithID OCSPResponse: decode failure at top level 3C68746D6C3E0A3C686561643E0A3C6D65746120687474702D65717569763D27726566726573682720636F6E74656E743D27313B2075726C3D687474703A2F2F6F6373702E6170706C652E636F6D2F6367692D62696E2F6C6F67696E3F636D643D72656469726563742661727562616C703D62636436636161622D343339302D343635322D386236322D35653363373763393035272F3E0A3C2F686561643E0A3C2F68746D6C3E0A
Jan 21 11:06:55 iPad securityd[81] <Error>: SecOCSPResponseCreateWithID OCSPResponse: no responseStatus 3C68746D6C3E0A3C686561643E0A3C6D65746120687474702D65717569763D27726566726573682720636F6E74656E743D27313B2075726C3D687474703A2F2F6F6373702E6170706C652E636F6D2F6367692D62696E2F6C6F67696E3F636D643D72656469726563742661727562616C703D62636436636161622D343339302D343635322D386236322D35653363373763393035272F3E0A3C2F686561643E0A3C2F68746D6C3E0A
Jan 21 11:06:56 iPad kernel[0] <Notice>: xpcproxy[932] Container: /private/var/mobile/Containers/Data/Application/EEC10B5D-9ADE-44DC-AB96-83C10AF75F4B (sandbox) 
Jan 21 11:06:56 iPad Test EncryptedDB[932] <Warning>: [ERROR] A SQLite database error occurred on database '/var/mobile/Containers/Data/Application/EEC10B5D-9ADE-44DC-AB96-83C10AF75F4B/Library/Private Documents/todo.enc.db.sql': Error Domain=com.plausiblelabs.EncPLDatabase Code=3 "An error occured parsing the provided SQL statement." UserInfo={com.plausiblelabs.EncPLDatabase.error.vendor.code=26, com.plausiblelabs.EncPLDatabase.error.vendor.string=file is encrypted or is not a database, NSLocalizedDescription=An error occured parsing the provided SQL statement., com.plausiblelabs.EncPLDatabase.error.query.string=CREATE TABLE IF NOT EXISTS migrations (latest TEXT, model TEXT);} (SQLite #26: file is encrypted or is not a database) (query: 'CREATE TABLE IF NOT EXISTS migrations (latest TEXT, model TEXT);') 
Jan 21 11:06:56 iPad Test EncryptedDB[932] <Warning>: [ERROR] Application received error: invalid SQL statement at enc.db.js (line 1) 
Jan 21 11:06:56 iPad Test EncryptedDB[932] <Warning>: [ERROR] Application received error: Module "alloy/models/Todo" failed to leave a valid exports object

Attachments

Comments

1. Eduardo Gomez 2016-02-01 The migrations cannot be done due this file (migrations table): * app/assets/alloy/sync/enc.db.js

      // Gets the current saved migration
      function GetMigrationFor(dbname, table) {
          var mid = null;
          var db = _database.open(dbname);
          db.execute('DROP TABLE IF EXISTS migrations'); 
          db.execute('CREATE TABLE IF NOT EXISTS migrations (latest TEXT, model TEXT);');
          var rs = db.execute('SELECT latest FROM migrations where model = ?;', table);
          if (rs.isValidRow()) {
              mid = rs.field(0) + '';
          }
          rs.close();
          db.close();
          return mid;
      }
      

2. Chee Kiat Ng 2016-05-12 Basically, the difference between appcelerator.encrypteddatabase 1.0.0 and 1.3.0 is that they contain different default settings, and thus existing databases need to be migrated or upgraded from older settings to a new version. I'm still working on a way to do a in-place conversion of a existing database to a new one (which will take time), but at the mean time, a simple way to enable the usage of 1.3.0 module is, in your titanium app, in every

   var db = database.open

   being called, you just need to add this in the following line:

   db.execute('PRAGMA kdf_iter = 4000;'); 

   So in the attached sample app, you are adding this line in /app/assets/alloy/sync/enc.db.js after

   _database.open(..);

   This basically tells the new module to read the table with the old setting. (the new one is kdf_iter = 64000)This really doesn't have any impact on the overall usage of the database. And it's a good solution for now if you really need this out asap. Eventually we will need a solution like this (pseudocode):

      if (olderDatabaseExists && !hasMigrated) {
          db.open();
          db.migrate();
          hasMigrated = true;
      }
      

   The eventual solution *will still work* in a later release if you choose to the above fix first in an interim version. [~shossain] ^
3. Chee Kiat Ng 2016-05-12

   Steps to test if above fix works

   1. Starting from scratch (no pre-existing app on the simulator) 2. Get sample app attached and install it (contains both module versions already) https://www.dropbox.com/s/kppn75te6xaarft/Test_EncryptedDB.zip?dl=0 3) Run Encrypted DB module 1.0.0 on sim/device: *appc run -p ios -C * only ipad2 works now because the module 1.0.0 is 32bit. 4) DB operations work as expected 5) Clean Build and upgrade module version to 1.3.0 6) replace /app/assets/alloy/sync/enc.db.js with the one attached in ticket 7) Run app again and it will still work
4. Ingo Muschenetz 2016-05-20 To clarify, what Kiat has proposed is a workaround for now. The 32-bit to 64-bit SDK change forced a change in the connection parameters. However, it currently fails during the migration. Thus, what Kiat is suggesting is to read the tables with the old default: https://www.zetetic.net/sqlcipher/sqlcipher-api/#kdf_iter https://www.zetetic.net/blog/2013/9/3/sqlcipher-300-beta-release.html (it went from 4000 in 1.0.0 to 64000 in 1.3.0, thus the pragma definition he adds of setting it back to 4000). This change is not something we can control if we wish to take advantage of 64-bit support.
5. Ingo Muschenetz 2016-05-20 Note also the description of "cypher_migrate" in https://www.zetetic.net/blog/2013/9/3/sqlcipher-300-beta-release.html, so perhaps ACE could explore that pragma as a workaround.
6. Chee Kiat Ng 2016-05-25 PR here: https://github.com/appcelerator-modules/appcelerator.encrypteddatabase/pull/12
7. Chee Kiat Ng 2016-05-26 PR updated. release updated. Tested on new project as well as attached project.

   Steps to test

   1. Starting from scratch (no pre-existing app on the simulator) 2. Get sample app attached and install it (contains both module versions already) https://www.dropbox.com/s/kppn75te6xaarft/Test_EncryptedDB.zip?dl=0 3) Run Encrypted DB module 1.0.0 on sim/device: *appc run -p ios -C * only ipad2 works now because the module 1.0.0 is 32bit. 4) DB operations work as expected 5) Clean Build and upgrade module version to 1.3.1 7) Run app again and it will still work
8. Hans Knöchel 2016-05-26 CR approved. Waiting for the customer to confirm the supplied solution before merging.
9. Ingo Muschenetz 2016-06-10 [~arohini] did you have an updated test case?
10. Ajith Rohini 2016-06-22 [~cng], Can we please give this a high priority? I had an email from Richard who is the Dir.Technical Architecture and IT Strategy @ ACE mentioning that they are getting pretty upset since this has been going on since Jan. Any help would be much appreciated
11. Chee Kiat Ng 2016-06-22 [~sliang] Use this version please. https://github.com/appcelerator-modules/appcelerator.encrypteddatabase/releases/tag/1.3.1 and try it against your reproducible case. it's a new build.
12. Shuo Liang 2016-06-23 [~cng] I think I did use this one, I downloaded and tested it yesterday. Did you build a new today?
13. Shuo Liang 2016-06-28 [~cng], I tried your latest build for v1.3.1 servial time. And the problem still there. Here is the step: 1. Delete the old app from simulator. (important) 2. Go with the reproduce steps in my comment. (v1.0.0 to v1.3.1) This error will pop out.

        [INFO] :   Opening DB...
        [ERROR] :  Error occurred calling next on a EncPLSqliteResultSet. SQLite error: 'attempt to write a readonly database' for 'INSERT OR IGNORE INTO testtable(id) VALUES (3);'
        [ERROR] :  Script Error {
        [ERROR] :      column = 21;
        [ERROR] :      line = 19;
        [ERROR] :      message = "Error occurred calling next on a EncPLSqliteResultSet. SQLite error: 'attempt to write a readonly database' for 'INSERT OR IGNORE INTO testtable(id) VALUES (3);'";
        [ERROR] :      sourceURL = "file:///Users/shuoliang/Library/Developer/CoreSimulator/Devices/44C74F69-DC96-461F-9FE1-93256A9F224D/data/Containers/Bundle/Application/03127931-BB65-413C-8CEF-FDEFFCF82117/AlloyTest6.app/app.js";
        [ERROR] :      stack = "[native code]\ninsert@file:///Users/shuoliang/Library/Developer/CoreSimulator/Devices/44C74F69-DC96-461F-9FE1-93256A9F224D/data/Containers/Bundle/Application/03127931-BB65-413C-8CEF-FDEFFCF82117/AlloyTest6.app/app.js:19:21\nglobal code@file:///Users/shuoliang/Library/Developer/CoreSimulator/Devices/44C74F69-DC96-461F-9FE1-93256A9F224D/data/Containers/Bundle/Application/03127931-BB65-413C-8CEF-FDEFFCF82117/AlloyTest6.app/app.js:41:7";
        [ERROR] :  }
        

    Note: If I run the app again with v1.3.1 right after this error (not delete app, not clean the project), the error will gone.
14. Scott Davenport 2016-06-29 [~cbowley] can you work with Ajith and make sure this one gets over the line? Thanks...Ajith just FYI Chris works very closely with the ACE/Chubb team as they have paid for 1 year of a Solutions Architect 6 hours a week. He knows the ins and outs of the account from an app perspective.
15. Chee Kiat Ng 2016-06-30 [~sliang] I'll try your test case again and see what I can do. Meanwhile, instead of your test case, can the customer provide a 100% reproducible one?
16. Eric Merriman 2018-08-06 Cleaning up older fixed issues. If this issue should not have been closed as fixed, please reopen.

JSON Source