How to store a BLOB containing a null character with Microsoft.Data.SQLite?

Question

I'm trying to store a BLOB in an SQLite database with Microsoft.Data.SQLite 2.2.6.

Here is how I'm doing it:

<Project Sdk="Microsoft.NET.Sdk">

    <PropertyGroup>
        <OutputType>Exe</OutputType>
        <TargetFramework>netcoreapp2.2</TargetFramework>
    </PropertyGroup>

    <ItemGroup>
      <PackageReference Include="Microsoft.Data.SQLite" Version="2.2.6" />
    </ItemGroup>

</Project>

using Microsoft.Data.Sqlite;

namespace InsertBlobContainingNullCharacter
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var connection = new SqliteConnection("Data Source=Test.sqlite3"))
            {
                connection.Open();

                var create = connection.CreateCommand();
                create.CommandText = "CREATE TABLE IF NOT EXISTS test(file1 BLOB, file2 BLOB)";
                create.ExecuteNonQuery();

                var delete = connection.CreateCommand();
                delete.CommandText = "DELETE FROM test";
                delete.ExecuteNonQuery();

                var insert = connection.CreateCommand();
                insert.CommandText = "INSERT INTO test (file1, file2) VALUES (@file1, @file2)";
                insert.Parameters.AddWithValue("file1", new byte[] {0x31, 0x32, 0x33, 0x00, 0x34, 0x35, 0x36}).SqliteType = SqliteType.Blob;
                insert.Parameters.AddWithValue("file2", new byte[] {0x31, 0x32, 0x33, 0x34, 0x35, 0x36}).SqliteType = SqliteType.Blob;
                insert.ExecuteNonQuery();

                connection.Close();
            }
        }
    }
}

I'm storing a byte[] containing a null character (0x00) in the file1 column and it gets truncated. When I try to read the value with the sqlite3 tool, I see that it is truncated exactly at the null character. As expected, the file2 column is not truncated:

$ sqlite3 Test.sqlite3 "SELECT file1 FROM test" | xxd
00000000: 3132 330a                                123.
$ sqlite3 Test.sqlite3 "SELECT file2 FROM test" | xxd
00000000: 3132 3334 3536 0a                        123456.

What I am doing wrong? How am I supposed to store a byte[] as BLOB without truncation?

Answer 1

It turns out that my assumption about the data being truncated is wrong. The data is fully stored but the sqlite3 command truncates it when displaying the result.

Using the HEX function demonstrates that everything is fine:

$ sqlite3 Test.sqlite3 "SELECT HEX(file1) FROM test"
31323300343536
$ sqlite3 Test.sqlite3 "SELECT HEX(file2) FROM test"
313233343536