bug: Incorrect value substitution in executeSet causes text corruption (manual escaping instead of parameter binding)

[xEverth]

Plugin version:
7.0.2

Platform(s):
Android, Web

Current behavior:
Currently, the values sent in an executeSet statement are replaced by the library after manually escaping the values using DatabaseUtils.sqlEscapeString. This makes it so the inserted string is not the same as the one retrieved, potentially damaging data.

Expected behavior:
Inserted strings are byte-by-byte identical when inserted in the SQLite database.

Steps to reproduce:
Use the executeSet method with query and values like the following:

const statement = "INSERT INTO calendars (year, content) VALUES (?, ?);";

const values = [
  [2020, "BEGIN:VCALENDAR\r\nVERSION:2.0...END:VCALENDAR\r\n"],
  [2021, "BEGIN:VCALENDAR\r\nVERSION:2.0...END:VCALENDAR\r\n"]
];

const set = [{ statement, values }];

const res = await this.sqlitePlugin!.executeSet({
  database: dbName,
  set,
  returnMode,
  transaction: true,
  readonly: false
});

The statement will be executed without hinting at any issues, but the values written and returned (either from this query when using returnMode = "all" and adding "RETURNING *" to the statement, or by a select at a later moment) will lack the carriage returns, making the calendar text out of spec and impossible to parse and recover.

Related code:
The method "multipleRowsStatement" performs the substitution: android\src\main\java\com\getcapacitor\community\database\sqlite\SQLite\Database.java

public JSObject multipleRowsStatement(String statement, JSONArray valuesJson, String returnMode) throws Exception {
        StringBuilder sqlBuilder = new StringBuilder();
        try {
            for (int j = 0; j < valuesJson.length(); j++) {
                JSONArray innerArray = valuesJson.getJSONArray(j);
                StringBuilder innerSqlBuilder = new StringBuilder();
                for (int k = 0; k < innerArray.length(); k++) {
                    Object innerElement = innerArray.get(k);
                    String elementValue = "";

                    if (innerElement instanceof String) {
                        elementValue = DatabaseUtils.sqlEscapeString((String) innerElement);
                    } else {
                        elementValue = String.valueOf(innerElement);
                    }
                    innerSqlBuilder.append(elementValue);

                    if (k < innerArray.length() - 1) {
                        innerSqlBuilder.append(",");
                    }
                }

                sqlBuilder.append("(").append(innerSqlBuilder.toString()).append(")");

                if (j < valuesJson.length() - 1) {
                    sqlBuilder.append(",");
                }
            }
            String finalSql = replacePlaceholders(statement, sqlBuilder.toString());

            JSObject respSet = prepareSQL(finalSql, new ArrayList<>(), false, returnMode);
            return respSet;
        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
    }

Other information:
I have reproduced and debugged the issue in Android Studio.
A proposed fix will be provided in an associated Draft PR.
The recommended approach is to pass the parameter values as an ArrayList to prepareSQL() and let SQLite handle binding, rather than manually constructing SQL strings.

Capacitor doctor:

   Capacitor Doctor   

Latest Dependencies:

  @capacitor/cli: 7.4.4
  @capacitor/core: 7.4.4
  @capacitor/android: 7.4.4
  @capacitor/ios: 7.4.4

Installed Dependencies:

  @capacitor/core: 7.4.3
  @capacitor/android: 7.4.3
  @capacitor/ios: 7.4.3
  @capacitor/cli: 7.4.3

[success] Android looking great! 👌
[error] Xcode is not installed