Line separator extended character causes query to silently fail and prevents further queries on iOS

[subblue]

I'm using a prepopulated sqlite database in our iOS app exported from a separate system.
I've found that for some queries the database would never return a result or even error and leave the app in a state where it couldn't recover to make any further queries.

Digging into the entries that were failing I found that the content (which was a block of JSON stored as a string in a TEXT field) contained extended characters such as the unicode line separator U+2028.
I'm guessing that at the point where the content from the database is converted to a form to send to the embedded browser it is tripping up on this. It would be good if some sort of check could be added in the plugin to warn of potential issues extracting the data.

Anyway, in case others have similar weird issues this is worth checking for. I've now added an extra stage in the sqlite.db creation script to remove these extended characters.

[brodybits]

This issue definitely needs investigation (on all three platforms Android/iOS/WP(8)), and hopefully I will reproduce this (or a similar) issue with the \u2028 character in a unit test.

[brodybits]

This scenario is now reproduced for the iOS version. The test causes a silent failure (no success or error callback) for WP(8) version. This is not an issue for the Android version.

[brodybits]

From some further testing I now suspect the Objective-C JSON encoding of the query results.

[brodybits]

I just discovered this article: http://timelessrepo.com/json-isnt-a-javascript-subset that helps explain the problem with the UNICODE line separator character. Suspect this bug is with cordova-ios.

[brodybits]

Also need to test UNICODE \u2029 - paragraph separator

UPDATE: When testing the following code against cordova-ext-echo:

        var Echo = cordova.require('org.apache.cordova.plugins.echo.Echo');
        Echo.echo('first\u2027second', function(v) {
          console.log('cb 1: ' + v);
        });
        Echo.echo('third\u2028fourth', function(v) {
          console.log('cb 2: ' + v);
        });
        Echo.echo('fifth\u2029six', function(v) {
          console.log('cb 3: ' + v);
        });

only shows the following output:

cb 1: first‧second

Definitely a bug in cordova-ios.

[brodybits]

In addition, this can be an issue when using lz-string to compress data, as described here: https://groups.google.com/d/msgid/Cordova-SQLitePlugin/5a31f7d7-143f-45fe-979f-e2ec5a02f44d%40googlegroups.com

Original description:

I found a weird issue reading a string encoded in UTF16.

I'm using lzstring to compress big XML blocks as json elements and store it
in the db. The insert or update works file, but when I retrieve the record,
the plugin crash immediatly after the cordova.js call

return iOSExec.nativeFetchMessages();

returning a

SyntaxError: Unexpected EOF.

This is caused by the unicode char E2 80 A9.

The following workaround was given:

I fixed the problem by replacing all occurrences of both characters after compression and before storage, then after retrieval and before decompression. Something like this...
before storage:

var dbvalue = LZString.compressToUTF16(value);
dbvalue = dbvalue.replace(/\u2028/g, '\\u2028').replace(/\u2029/g, '\\u2029');

after retrieval

var utfdata = dbvalue.replace(/\\u2028/g,'\u2028').replace(/\\u2029/g,'\u2029');
var value = LZString.decompressFromUTF16(utfdata);

But it has two problems:

• It could be more efficient (see (#2890) - websql: avoid hex() for binaries pouchdb/pouchdb#2900 to see how they deal with a similar problem with \0 characters in a "binary" string)
• In case the characters \ u 2 0 2 8 or \ u 2 0 2 9 would ever show up in the output of lz-string, they would be replaced after retrieval and then LZString.decompressFromUTF16(utfdata) would not give you the original string value.

Here is a corrected version which should work properly (if I am not mistaken):

before storage:

var dbvalue = LZString.compressToUTF16(value);
dbvalue = dbvalue
  .replace(/\u007f/g, '\u007f\u007f')
  .replace(/\u2028/g, '\u007f\u0028').replace(/\u2029/g, '\u007f\u0029');

after retrieval

var utfdata = dbvalue
  .replace(/\u007f\u0028/g,'\u2028').replace(/\u007f\u0029/g,'\u2029')
  .replace(/\u007f\u007f/g,'\u007f');
var value = LZString.decompressFromUTF16(utfdata);

(It should be possible to use any other "Basic Latin" (Unicode block) character in the range of \u0001 ... \u007e with the exception of \u0028 and \u0029 in place of \u007f, if necessary for some reason.)

Another alternative from pouchdb/pouchdb#2900 (comment):

COBS would be more efficient for preventing embedded nulls. Use a 16-bit version of it, if a null is a 0000.

Some references:

• https://github.com/nolanlawson/state-of-binary-data-in-the-browser#readme
• http://pouchdb.com/2014/10/26/10-things-i-learned-from-reading-and-writing-the-pouchdb-source.html
• (#2890) - websql: avoid hex() for binaries pouchdb/pouchdb#2900
• http://www.embeddedrelated.com/showarticle/113.php - nice use case and description about how COBS would be more efficient for preventing embedded nulls
• http://en.wikipedia.org/wiki/Consistent_Overhead_Byte_Stuffing (COBS)

[brodybits]

I just filed Apache Cordova CB-9435 to report the problem with U+2028 and U+2029 on cordova-ios.

[assyme]

Hi,

I am currently struggling with the same problem and unfortunately, cordova has asked to find a work around in CB-9435. I tried replacing the U+0028 & U+0029 characters from my json string before inserting in the database, but the problem still persists. I am not too comfortable with Objective C and unsure what i need to do in the plugin side to escape these characters. Will highly appreciate if you could guide me on this one.

[brodybits]

This is solved in litehelpers / Cordova-sqlite-enterprise-free, available under a different licensing scheme.

[CookieCookson]

@brodybits There are other characters which also make this plugin fail on open. I have had issues with the following character:

http://www.fileformat.info/info/unicode/char/2019/index.htm

[brodybits]

Thanks @CookieCookson! I will document this and add it to the test for the other platforms when I get a chance.

[brodybits]

@brodybits There are other characters which also make this plugin fail on open. I have had issues with the following character:

http://www.fileformat.info/info/unicode/char/2019/index.htm

I raised #459 with this since it seems to be quite different from the OP.

FYI a normal string test with the \u2019 (as well as \u2018) works OK on Android.

[brodybits]

FYI this is also an issue with cordova-android 6.0.0 which is currently installed by the latest Cordova CLI (6.4.0), raised in apache/cordova-discuss#57 for discussion.

[GeeKanJi]

Hi,

Is there a workaround?

[GeeKanJi]

workaround:
WP-API/WP-API#2923