storesafe/cordova-sqlite-storage

Unicode issues (Android)

mirko77 opened this issue · 4 comments

mirko77 commented

Hi there,

I had the app failing to load a string from the database due to Unicode chars, like:

\u2019
\u2013

Is this still related to this Android bug?

I confirm it is failing on Nougat and Oreo, DevTools report:
VM288:1Uncaught SyntaxError: Invalid or unexpected token

brodycj commented

Hi @mirko77,

The error message indicates to me that you are using Unicode characters in the SQL query string, NOT in a parameter argument value. I highly recommend that you use parameter argument values together with ? placeholders, as already documented, for reasons including maximum flexibility and reduced vulnerability to SQL injection issues.

A related issue reported in #641 is if you would use CJK or certain other Unicode characters in a column name. (I think this case is NOT covered in the existing test suite.)

Using certain Unicode characters in either SQL statements or parameter argument values is known to be broken on Android in case of default Android database implementation, already reported in #564 in case of emojis and other 4-byte UTF-8 characters. This issue is already documented but I think not clearly enough. The workaround is to use the androidDatabaseImplementation: 2 setting when opening the database, also documented.

I hope to resolve this issue in the near future on the evcore versions and eventually on this plugin version.

mirko77 commented

Thanks, @brodybits .
We are actually using parameterized queries already, still the issue is present.

I have not looked into it thoroughly honestly, as we have the chance to sanitise the source data before saving to the DB, and that fixes the issue. In our case, we have to filter out Unicode characters coming from copying and pasting from Word documents into our web forms.

We are already not allowing emoji.

Thanks again for the clarification

brodycj commented

Hi @mirko77 I tried both string manipulation and storage tests in 69ce810 with \u2013 and \u2029, both inline and as parameters, on Android 4.4.2 / 5.1.1 / 6.0 / 7.1.1 devices in addition to Android 7.1.1 (API 25) x86_64 and Android API 207 (Android 8.1.x) x86 emulator, could not see any issue. Also passes on iOS and macOS ("osx").

It took me almost half a workday to look at this issue, with customers waiting on some other items. I also spent considerable effort testing the most important kinds of characters including special symbols, 2-byte UTF-8 characters, 3-byte UTF-8 characters, Unicode u2028 line separator and u2029 paragraph separator, NULL (u0000 aka \0), and 4-byte UTF-8 emoji characters in the past. More detailed pointers would be really helpful otherwise I would like to close this one.

mirko77 commented