[MOD-2598] Android: Encrypted DB wrongly stores booleans/numbers as strings via query parameters

GitHub Issue	n/a
Type	Bug
Priority	Low
Status	Open
Resolution	Unresolved
Affected Version/s	n/a
Fix Version/s	n/a
Components	Encrypted SQLite DB
Labels	android, database, module, parameter, parity, query, type
Reporter	Joshua Quick
Assignee	Joshua Quick
Created	2020-04-14T02:33:31.000+0000
Updated	2020-04-17T18:19:17.000+0000

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:*

Build and run the below code on Android.

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);

Comments

No comments

JSON Source