SQLite in Android adding quotes to start and end of datelike string

Question

Interesting issue while using SQLite in Android. I am seeing an inconsistency in the string length and quoting of a string between what is stored in the database and the materialized value seen in Java.

We are using an ORM called SugarORM to query the DB, but I've traced the offending code to the internal android.database.sqlite.SQLiteCursor class used within SugarORM, specifically the cursor.getString(columnIndex) method.

I have a string in the database that is an ISO data string 2019-03-25T19:19:39.664Z and is stored in a VARCHAR column . I have confirmed using DB Browser for SQLite that the length of the string as its stored in the database is indeed 24 characters. SELECT LENGTH(MyStringColumn) FROM MyTable WHERE ...

When I get the value of this string via cursor.getString(columnIndex), it is returning the string "2019-03-25T19:19:39.664Z". Notice the leading and trailing quotes. Java reports to me that the string is 26 characters long.

Any value that I store in this column that is not an ISO data does not have this behavior. I tried tracing the SQLiteCursor source back, but ultimately it ends up being a Native method and that's where my skill set stops.

Can anyone explain what might be going on here? I am probably just going to write a wrapper around my queries to get rid of the quotes, but its all very perplexing. The date string is being fed to a JavaScript interpreter and causing it to fail when creating a JavaScript Date object.

If it helps, I have replicated the behavior on both my S7 physical device and a Pixel 6 emulator.

Answer 1

As a quick get around you could use :-

SELECT length(replace(mystringcolumn,'"','')) FROM mytable;

or before using the original SELECT use :-

UPDATE mytable SET mystringcolumn = replace(mystringcolumn,'"','');

• If this doesn't fix the issue, then for some reason it is the code that retrieves the data that is at fault.

e.g. consider :-

DROP TABLE IF EXISTS mytable;
CREATE TABLE IF NOT EXISTS mytable (mystringcolumn VARCHAR);
INSERT INTO mytable VALUES('2019-03-25T19:19:39.664Z'),('"2019-03-25T19:19:39.664Z"');
SELECT length(mystringcolumn), length(replace(mystringcolumn,'"','')) FROM mytable;

which results in :-

i.e. The 2nd row, 2nd column retrieves the appropriate value by using the replace function to strip of the quotes, if they exist.

As to why the quotes exist could depend upon either the way that the data is inserted (perhaps you have inadvertenly coded the quotes but the db being looked at isn't the actual database as copied from the App) or the way in which the data is being retrieved that for some reason adds them.

I don't believe it likely that the Cursor getString method has a bug in which the quotes are added, otherwise such an issue would likely be a recurring issue.