Uploaded image for project: 'Appcelerator Modules'
  1. Appcelerator Modules
  2. MOD-2598

Android: Encrypted DB wrongly stores booleans/numbers as strings via query parameters

    Details

      Description

      Summary:
      The "appcelerator.encrypteddatabase" module's DB.execute() method wrongly stores values of type boolean and number as type string when passed via query parameters.

      // Will store boolean false as string "false". Should be stored as number 0.
      db.execute("INSERT INTO data(value) VALUES (?)", false);
       
      // Will store number as string "123.456". Should be stored as number.
      db.execute("INSERT INTO data(value) VALUES (?)", 123.456);
      

      Note:
      This is not an issue on iOS. Values of type boolean and number are stored as numbers. Also note that SQLite does not natively support boolean types and will be stored as integers instead.

      Steps to reproduce:

      1. Build and run the below code on Android.
      2. Look in the log.

      var database = require("appcelerator.encrypteddatabase");
      database.password = "password";
      var dbConnection = database.open("test_encrypted.db");
      dbConnection.execute("CREATE TABLE IF NOT EXISTS properties(name PRIMARY KEY, value);");
      var sqlInsertStatement = "INSERT OR REPLACE INTO properties(name, value) VALUES (?, ?);";
      dbConnection.execute(sqlInsertStatement, "null", null);
      dbConnection.execute(sqlInsertStatement, "boolean-false", false); // uh-oh!
      dbConnection.execute(sqlInsertStatement, "boolean-true", true);   // uh-oh!
      dbConnection.execute(sqlInsertStatement, "integer-0", 0);         // uh-oh!
      dbConnection.execute(sqlInsertStatement, "integer-2", 2);         // uh-oh!
      dbConnection.execute(sqlInsertStatement, "float", 123.456);       // uh-oh!
      dbConnection.execute(sqlInsertStatement, "string-empty", "");
      dbConnection.execute(sqlInsertStatement, "string-not-empty", "Hello World");
      var resultSet = dbConnection.execute("SELECT name, value FROM properties;");
      while (resultSet.isValidRow()) {
      	var name = resultSet.field(0);
      	var value = resultSet.field(1);
      	Ti.API.info(`@@@ db entry "${name}": ${value} (type ${typeof value})`);
      	resultSet.next();
      }
      dbConnection.close();
      

      Results from Android:
      Notice entries of type boolean, integer, and float are of type string. They should all be of type number.

      [INFO]  @@@ db entry "null": null (type object)
      [INFO]  @@@ db entry "boolean-false": false (type string)
      [INFO]  @@@ db entry "boolean-true": true (type string)
      [INFO]  @@@ db entry "integer-0": 0 (type string)
      [INFO]  @@@ db entry "integer-2": 2 (type string)
      [INFO]  @@@ db entry "float": 123.456 (type string)
      [INFO]  @@@ db entry "string-empty":  (type string)
      [INFO]  @@@ db entry "string-not-empty": Hello World (type string)
      

      Results from iOS:

      [INFO]  @@@ db entry "null": null (type object)
      [INFO]  @@@ db entry "boolean-false": 0 (type number)
      [INFO]  @@@ db entry "boolean-true": 1 (type number)
      [INFO]  @@@ db entry "integer-0": 0 (type number)
      [INFO]  @@@ db entry "integer-2": 2 (type number)
      [INFO]  @@@ db entry "float": 123.456 (type number)
      [INFO]  @@@ db entry "string-empty":  (type string)
      [INFO]  @@@ db entry "string-not-empty": Hello World (type string)
      

      Work-Around:
      When creating a table, assign the column a numeric affinity such as NUMERIC, INTEGER, or REAL. SQLite will automatically convert parameter values to that numeric affinity if possible. You can still store strings to a numeric column. However, a boolean will still be stored as a string.

      CREATE TABLE IF NOT EXISTS properties(name PRIMARY KEY, value NUMERIC);
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                jquick Joshua Quick
                Reporter:
                jquick Joshua Quick
              • Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:

                  Git Integration