C# and SQL Server disagree about whether a string is valid Base64 - which is correct?

Question

We have the following table on a SQL Server 2008 R2 (SP1) database:

-- irrelevant columns omitted
create table Person
           ( PersonID int,
             Portrait varchar(max) )

The Person.Portrait column contains Base64 strings encoded from JPG images - this is populated by a third-party system we have no control over. I need to convert this data back to raw bytes so I can display it in a report:

select isnull(cast(N'' as xml).value('xs:base64Binary(sql:column("psn.Portrait"))', 'varbinary(max)'), 0xdeadbeef) as [Portrait]
  from Person psn with (nolock)
 where psn.PersonID = <n>

For some rows this is returning valid varbinary data, for others it's returning OxDEADBEEF (in other words, the result of the XML expression is returning null).

However, if I run the following C# code against the rows in the Person table that are returning null in SQL Server, I get valid JPG images output:

var portraitBytes = Convert.FromBase64String(Sql.SelectSingleString(
@"select psn.Portrait
    from Person psn with (nolock)
   where psn.PersonID = <n>"));

using (var writer = new FileStream(@"C:\portrait.jpg", FileMode.CreateNew))
{
  writer.Write(portraitBytes, 0, portraitBytes.Length);
}

The only differences I can see between the values that are seen as "valid" by SQL Server, versus those that are "invalid", is that the "invalid" ones end with the character Z. If I replace this character with = then SQL Server is happy.

If I run the above C# code on a Base64 string ending in Z, but with that char replaced by =, the code runs fine and the resulting outputted image is 1 byte smaller but (apparently) renders identically.

So my questions:

1. Is it valid to end a Base64 string with Z?
2. Which one is misbehaving: C# by accepting an invalid Base64 string, or SQL Server by rejecting a valid one?
3. Is it safe to replace Z with = to allow SQL Server to not puke? In other words, is there a possibility that the resulting bytes will not be valid JPG data?

Answer 1

1. Yes.
2. Neither, see below.
3. No longer relevant, see below.

After much spelunking I figured that the data in psn.Portrait contains a trailing NUL (\0) character, which is not displayed when running a select in SQL Server Management Studio. So every time I copied data from SSMS to test with as a literal, it wouldn't have a trailing NUL and hence would decode correctly. Of course, when referencing the actual value in the column, the NUL came along for the ride and caused MSSQL to correctly reject the string.

On the C# side, turns out that Sql.SelectSingleString helper method has a line in it that says result.Trim('\0'). Why it's there I can't fathom, but that explains why C# didn't choke on the NUL... it wasn't there to choke on.