sqlite icon indicating copy to clipboard operation
sqlite copied to clipboard

Fix SQL parsing for RETURNING + Correct value handling in executeSet (Issues #670 and #671)

Open xEverth opened this issue 1 month ago • 0 comments

This Draft Pull Request proposes fixes for multiple issues in the plugin’s SQL parsing and statement preparation logic in the Java/Android implementation. The goal is to align behavior with SQLite grammar, ensure correctness when handling RETURNING clauses, and eliminate string-based parameter substitution that causes data corruption in multi-row inserts.

This PR is not ready for merging — it is intended to gather feedback and align on the expected behavior before finalizing the implementation. The code has been linted, formatted and verified (verified:android) using the npm scripts in the package.json. I would be glad to implement the same changes with a parallel PR on the repo https://github.com/jepiqueau/jeep-sqlite/tree/master which shares the same API, but I am currently unable to test these changes on iOS.

Fixes Included

  1. Improved handling of RETURNING clauses
  • The logic for separating the returning clause has been unified for INSERT/UPDATE/DELETE statements.
  1. Uses parameter binding for multipleRowsStatement
  • Preserves text content byte-for-byte while still preventing SQL injections
  1. Adds support for common table expressions in UPDATE/DELETE clauses and in the RETURNING statement
  • Added utility classes in UtilsSQLStatement for parsing (simple) CTEs
  1. Fixes the logic for retrieving inserted values
  • The library now selects the ROWID for the specific table that is being interested by the current insert statement, instead of requesting LAST_INSERT_ROWID() from the database. This is relevant in case the previous insert was on a different table on the same database.
  • It attempts to handle more cases gracefully by returning "-1" instead of breaking. In case of an UPSERT statement for example, the ROWID of the last insert may be lower than the last insert rowid before the insert, and that would create a query with BETWEEN a AND b with b<a, resulting in an error.
  • Uses the last insert rowid returned by the insert statement instead of requesting it in a separate statement.
  1. Adds the ability to get returned values from UPDATE or DELETE queries containing CTEs
  • The extractWhereClause has been updated to support additional grammar. It currently assumes that there is no RETURNING clause, but the code may be updated to remove this assumption so it does not depend on the order of calling the isReturning method.

Rationale

SQLite’s grammar guarantees that RETURNING appears last, so parsing should only modify that clause. Manual string substitution cannot safely reproduce SQLite’s parameter binding behavior, especially with complex string data. Aligning the behavior across Android and Web improves consistency and reduces surprising behavior for end users. Fixes #670 and fixes #671 on Android (API 26 and up). May also resolve #605 but can't say for sure without additional information from the submitter.

xEverth avatar Nov 29 '25 19:11 xEverth