returning is treated as a keyword in a string object

[mla03]

Hi,

I'm currently working on an Ionic app that we recently updated to use Capacitor 5, TypeORM, and capacitor-community/sqlite for data storage. Previously, we were using an older Cordova setup.

We're encountering an issue when saving entities: if a string object contains the 'returning' keyword, we encounter an error.
e.g. "This is a string including returning that we want to save" the generated query logged by TypeORM:

query: INSERT OR REPLACE INTO job ("guid","unitGuid","deleted","baseUnitGuid","modified","retrieved","jobNo","name","text","latestJobHistoryRemark","nextDate","componentGuid","componentNo" ) VALUES ("94787267-f411-4132-a79e-3b479b494236","d06b3fe3-c5e1-4a0b-bb92-bfcb37dbe582",null,null,null,null,"test","test","This is a string including returning that we want to save",null,"2024-01-15T00:00:00","3c48cf1f-9b3e-43dd-92a0-dcc0e326b94d","Comp2")

The error generated:
error: Error: Run: unrecognized token: ""This is a string including;": , while compiling: INSERT OR REPLACE INTO job ("guid","unitGuid","deleted","baseUnitGuid","modified","retrieved","jobNo","name","text","latestJobHistoryRemark","nextDate","componentGuid","componentNo") VALUES ("94787267-f411-4132-a79e-3b479b494236","d06b3fe3-c5e1-4a0b-bb92-bfcb37dbe582",null,null,null,null,"test","test","This is a string including;

I'm uncertain whether this issue is related to TypeORM or SQLite, but based on the console log, the query generated by TypeORM appears to be correct. Therefore, I suspect it might be a bug or issue within capacitor-community/sqlite.

Could someone help me figure out what's going on and possibly provide information on how to resolve the issue? This is a critical issue as our app is actively used by customers, and currently, they are unable to synchronize their work if any of their strings contain the 'returning' keyword.

Thank you!

Update just tried downgrading to 5.0.4, and it works as we expect. So my guess is that the error has something todo with the 5.0.5 release "add insert/delete/update with returning" 293f032

[jepiqueau]

@mla03 on which platform

[mla03]

@jepiqueau the above error msg is from Android.
iOS returns:
Failed in runSQL : Error: prepareSQL prepare failed rc: 1 message unrecognized token: ""This is a string including ;"

Did you see my update regarding 5.0.5 release I think that is the problem, since it's working in 5.0.4

Update Running it on my Pixel 7 (Android 14) the error msg is:
Run: Not implemented for above TIRAMISU xxxxxxxxxxxxxxxxxxx (replace the x with random numbers/leters).

[jepiqueau]

@mla03 for the run command put the values in a separate are and run(sql, values). Il will have a look for the reason why it was working on 5.0.4 and not in 5.0.5. I am currently working on updating another plugin and this take all my time

[ygarg465]

Hey, @jepiqueau and @mla03 I looked into version 5.0.5.

See this line it checks for "RETURNING" string in SQL statement from where this error arises from, which was not present in version 5.0.4 here

It would be best to always pass values as a second parameter to the function. :)

[mla03]

Now I'm not an expert in how this fits together with TypeORM, but we're using this plugin together with TypeORM. And the SQL generated is posted to our console and looks like this:
INSERT OR REPLACE INTO job ("guid","unitGuid","deleted","baseUnitGuid","modified","retrieved","jobNo","name","text","latestJobHistoryRemark","nextDate","componentGuid","componentNo" ) VALUES ("94787267-f411-4132-a79e-3b479b494236","d06b3fe3-c5e1-4a0b-bb92-bfcb37dbe582",null,null,null,null,"test","test","This is a string including returning that we want to save",null,"2024-01-15T00:00:00","3c48cf1f-9b3e-43dd-92a0-dcc0e326b94d","Comp2")

So according to the code you linked @ygarg465 will this whole query be threated as a "statement" in the prepareSQL? and thats why it finds "returning" in the middle of the values and threat it like a keyword, and breaks?
Can we change this behavior when using TypeORM?

For me it looks like a bug, if the statement/query includes VALUES as in our example, these values should not be "searched" for any keywords.

[ygarg465]

I haven't used TypeORM but it looks like it sanitizes the user input and creates a SQL query with values in the statement itself, which is then passed to the datasource in this case CapacitorSQLite Plugin. But the best practice is to have parameterized queries and that design pattern is followed by this plugin as well.

Maybe a solution would be to also check for single quotes around the "RETURNING" keyword instead of just checking it using the contains method.

So, I think @jepiqueau would be the best to answer this further.

[jepiqueau]

@mla03 @ygarg465 i am working on it should be fixed in next release

[ygarg465]

@jepiqueau Perfect 👍🏼

[mla03]

@jepiqueau That sounds great.

[jepiqueau]

@mla03 @ygarg465 fix i 5.5.1