System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: 3567020edf12d438cb7cf757b774ff3a04dc381e
Title: Can't properly read UTF string with \0 symbol from a database.
Status: Closed Type: Code_Defect
Severity: Important Priority: NextRelease
Subsystem: Native_Assembly Resolution: Fixed
Last Modified: 2013-03-07 21:14:01
Version Found In: 1.0.84
User Comments:
anonymous added on 2013-03-06 10:07:42:
I have an UTF string with \0 character (string s = "beforeNull\0afterNull";) and text field in a sqlite table.
When I tried to insert the string into table text field and then read it from the database I noticed that resulting string value was truncated after \0 character ("beforeNull"). Database contains whole string - (before \0 and after \0 parts) - it is been detected by opening database in text editor so problem is no read side.

OBSERVED: First UTF symbol \0 is not allowed to be used in UTF texts that are stored in sqlite tables. 
EXPECTED: Strings that are being read from sqlite table can contain any UTF8(16) symbols including \0 symbol. Read operation doesn't truncate information from the database.

I tried to debug the issue and investigated that SQLite3.GetText() function is being used to grab text value.  Inside there is a code to determine length of a resulting string

//////////////////////////
int len.
UnsafeNativeMethods.sqlite3_column_text_interop(stmt._sqlite_stmt, index, out len);
//////////////////////////
Unfortunately len return value is a string length till first \0 character.

Just for experiment I invoked another code to get length of the string 
//////////////////////////
len = UnsafeNativeMethods.sqlite3_column_bytes(stmt._sqlite_stmt, index);
//////////////////////////
that returns original whole string length, and resulting UTF8ToString(ptr, len) contains all string sybmols with \0 in the middle. 
( I can't say it working solution - that is just to illustrate problem)
 


See code snippet to reproduce the issue below.
///////////////////////////////////
  public static void IssueWith0Character()
        {
            const string sql = "DROP TABLE IF EXISTS SomeTable;" +
                               "CREATE TABLE SomeTable (SomeField TEXT not null);"
                               + "INSERT INTO SomeTable (SomeField) Values ( :value )";

            var csb = new SQLiteConnectionStringBuilder
                          {DataSource = "stringWithNull.db", Version = 3};

            // string with '0' character
            const string stringWithNull = "beforeNull\0afterNull";

            using (var c = new SQLiteConnection(csb.ConnectionString))
            {
                c.Open();

                using (var cmd = c.CreateCommand())
                {
                    var p = new SQLiteParameter(":value", DbType.String) {Value = stringWithNull};
                    cmd.CommandText = sql;
                    cmd.Parameters.Add(p);
                    cmd.ExecuteNonQuery();
                }

                using (var cmd = c.CreateCommand())
                {
                    cmd.CommandText = "SELECT SomeField FROM SomeTable;";
                    var restoredValue = (string) cmd.ExecuteScalar();
                   
                  
                    Debug.Assert(stringWithNull == restoredValue);
                }
            }
        }    
///////////////////////////////////

mistachkin added on 2013-03-06 21:20:10:
The root cause appears to be improper use of strlen()/wcslen() by the
sqlite3_column_text_interop() and sqlite3_column_text16_interop()
functions present in the interop assembly.

mistachkin added on 2013-03-06 22:02:47:
The same issue exists in the sqlite3_value_text_interop() and
sqlite3_value_text16_interop() functions in the interop assembly.  The
methods SQLite3.GetText, SQLite3.GetDateTime, SQLite3.GetParamValueText,
SQLite3_UTF16.GetText, and SQLite3_UTF16.GetParamValueText also have
similar issues.

mistachkin added on 2013-03-06 23:06:25:
Fixed on trunk via check-in [c77dd425d4].