0

After referring to the question Base64 encoding in SQL Server 2005 T-SQL, I tried to get the base64 values for some data from sql table but it's not giving proper values when compared to direct text values.

Using Direct text:

SELECT CAST('?' as varbinary) FOR XML PATH(''), BINARY BASE64

gives value as Pw== which is correct and it decodes to ?

Using Database entry:

SELECT CAST([Col] as varbinary) from tblTable FOR XML PATH(''), BINARY BASE64

with [Col] value = ?, gives output as PwA= which when decoded gives ? and an extra non-printable character.

Screenshot when checked using len function.

enter image description here

The reason for this is that I want to convert data for few columns from plain text to base64 using update statement, this is just sample value and actual values are bigger text which is also having this problem but with every character.

Edit: This when decoded from ASP.Net, if it's on label then it displays properly but when used in textbox shows extra junk characters.

Community
  • 1
  • 1
Techie
  • 1,491
  • 3
  • 18
  • 24
  • Is `[tblTable].[Col]` type `nvarchar`? – Liesel Mar 20 '16 at 10:12
  • @LesH It's nvarchar(250), Collation - SQL_Latin1_General_CP1_CI_AS – Techie Mar 20 '16 at 13:25
  • Ok, you're not comparing like with like in your example. Your Direct code is using a char, your table is nvarchar. Try `SELECT CAST(N'?' as varbinary) FOR XML PATH(''), BINARY BASE64` for your direct example and you'll get the same answer for direct and from your table. – Liesel Mar 21 '16 at 01:41
  • @LesH Okay, got your point about the difference. But why is it adding non-printable character's? and what's the solution to avoid it? – Techie Mar 21 '16 at 06:48
  • 1
    It's not. You're converting an `NVARCHAR` unicode two-byte character from your table into Base64, then converting those two encoded bytes into a `VARCHAR` giving two characters. Decode it with `CAST(CAST( 'PwA=' as XML ).value('.','varbinary(max)') AS NVARCHAR(250) )` and you'll get a single, two-byte `NVARCHAR` character. – Liesel Mar 21 '16 at 08:11
  • @LesH Thanks for clarification on 2byte character. Converted the field data type from nvarchar to varchar and all problems resolved. – Techie Mar 21 '16 at 09:46

1 Answers1

2

Two things:

First, the "Direct Text" example:

SELECT CAST('?' as varbinary) FOR XML PATH(''), BINARY BASE64

----
Pw==

Is encoding the single byte (VARCHAR) character to base 64.

For an NVARCHAR, a 2 bytes per character type, it should be this:

SELECT CAST(N'?' as varbinary) FOR XML PATH(''), BINARY BASE64

----
PwA=

In the sceond part of your question, asking why there is an extra character produced during decoding of your previously encoded NVARCHAR type column. You're actually taking the 2 bytes encoded as base 64 and converting them to 2 single byte (VARCHAR) characters.

In order to decode to NVARCHAR you need to do this:

SELECT CAST(CAST( 'PwA=' as XML ).value('.','varbinary(max)') AS NVARCHAR(250) )

---
?
Liesel
  • 2,929
  • 2
  • 12
  • 18