capacitor-community / capacitor-community/sqlite

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

Open
#671 0 comments 0 reactions 0 assignees View on GitHub
bug/fix needs: triage
Dominant language
Swift
Stars
661
Forks
158
PR merge metrics
No merged PRs in 30d

Description

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

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.