UTF-8 strings get encoded incorrectly

[Aurora12]

I'm building a universal store project for Windows 8.1 and Windows Phone 8.1.

• SQLite for Windows Runtime (Windows 8.1) v. 3.8.8.3
• SQLite.Net.Async PCL v. 2.5.1
• SQLite.Net PCL - WinRT Platform v. 2.5.1

I insert data into tables both through ORM objects and through direct requests.
This works fine through ORM or via requests with @params in query, but writing a direct request breaks text encoding.

Is this an expected behaviour? Do I need to encode text somehow?

[Table("Tests")]
public class Test { public string a { get; set; } }

Connection = new SQLiteConnectionWithLock(
  new SQLitePlatformWinRT(),
  new SQLiteConnectionString(
    Path.Combine(ApplicationData.Current.LocalFolder.Path, "test123.db"),
    true
  )
);

Database = new SQLiteAsyncConnection(() => Connection);
await Database.ExecuteAsync("PRAGMA encoding='UTF-8'");

await Database.DropTableAsync<Test>();
await Database.CreateTableAsync<Test>();

// 1: works fine
await Database.InsertOrReplaceAsync(new Test() {
  a = "Through ORM: ЩЫИЯ"
});

// 2: breaks encoding
await Database.ExecuteAsync("INSERT OR REPLACE INTO Tests(a) VALUES ('Via direct request: ЩЫИЯ')");

// 3: works fine
await Database.ExecuteAsync(
  "INSERT OR REPLACE INTO Tests(a) VALUES (@param)", 
  "Via direct request w/param: ЩЫИЯ"
);

List<Test> list = await Database.Table<Test>().ToListAsync();

Debug.WriteLine(list[0].a); // Through ORM: ЩЫИЯ
Debug.WriteLine(list[1].a); // Via direct request: ����
Debug.WriteLine(list[2].a); // Via direct request w/param: ЩЫИЯ

[prasannavl]

I'd think that this is expected behavior, since in case 2, you're explicitly passing a string (which is UTF16), and in other cases, you're letting the library take care of the encoding.

Unfortunately, strings are always represented as UTF16 internally. So there's no way to work around it, unless you're playing with bytes.

[Amenti]

I think praeclarum did solve this already with this declaration of Prepare2.

[DllImport ("sqlite3", EntryPoint = "sqlite3_prepare_v2", CallingConvention = CallingConvention.Cdecl)]
public static extern Result Prepare2 (IntPtr db, byte[] queryBytes, int numBytes, out IntPtr stmt, IntPtr pzTail);

And then encode the strings.

public static IntPtr Prepare2 (IntPtr db, string query)
{
            IntPtr stmt;
            byte[] queryBytes = System.Text.UTF8Encoding.UTF8.GetBytes (query);
            var r = Prepare2 (db, queryBytes, queryBytes.Length, out stmt, IntPtr.Zero);
[...]
}

This seems to work just fine.

[michael-bg]

In Prepare2, why is there this differentiation between NETFX_CORE and non-NETFX_CORE? I think the NETFX_CORE version should always be used as strings are always UTF-16 encoded.

public static IntPtr Prepare2 (IntPtr db, string query)
{
    IntPtr stmt;
#if NETFX_CORE
    byte[] queryBytes = System.Text.UTF8Encoding.UTF8.GetBytes (query);
    var r = Prepare2 (db, queryBytes, queryBytes.Length, out stmt, IntPtr.Zero);
#else
    var r = Prepare2 (db, query, System.Text.UTF8Encoding.UTF8.GetByteCount (query), out stmt, IntPtr.Zero);
#endif
    if (r != Result.OK) {
        throw SQLiteException.New (r, GetErrmsg (db));
    }
    return stmt;
}