Why BASE64 string is not getting decoded in sql 2012?

Question

I have Encoded my string to Base64 representation and storing it in VARCHAR(MAX) column in sql 2012's db.

Now I am trying to decode the value using this:

Declare @FMoney varchar(max)

Set @FMoney= (Select FinancialMoney from  AppliedWorks aw
where AppliedWorkID= 20082)

SELECT CAST( CAST( @FMoney as XML ).value('.','varbinary(max)') AS varchar(max) )

But it doesn't give me the original value and gives a long another base64 string.

Note: The value stored in db is encoded via C#.net and sent to db (anhhZWRzaGFzYjJuenVka2J1aWRqcjM1fDE1LjAw) but decoding should be performed in sql.

C# Encoding Code:

public static String Encrypt(string strData)
    {
        if (strData != "")
        {
            strData = string.Format("{0}|{1}", HttpContext.Current.Session.SessionID, strData);
            SHA1Managed shaM = new SHA1Managed();
            Convert.ToBase64String(shaM.ComputeHash(Encoding.ASCII.GetBytes(strData)));
            Byte[] encByteData;
            encByteData = ASCIIEncoding.ASCII.GetBytes(strData);
            String encStrData = Convert.ToBase64String(encByteData);
            return encStrData;
        }
        else
        {
            return "";
        }

    }

Update: I have added c#.net's decoding procedure also.

 public static String Decrypt(string strData)
    {
        if (string.IsNullOrEmpty(strData) == false)
        {
            Byte[] decByteData;
            decByteData = Convert.FromBase64String(strData);
            String decStrData = ASCIIEncoding.ASCII.GetString(decByteData);

            String[] SplitValue = decStrData.Split('|');

            String ReturnValue = SplitValue[1];
            return ReturnValue;
        }
        else
        {
            return "";
        }

    }

The c# side works fine for ENCODING and DECODING but what I am trying to do it sot encode in c# and decode in sql.

You don't assign the result of `Convert.ToBase64String(shaM.ComputeHash(Encoding.ASCII.GetBytes(strData)));` to anything? UTF8 may be wiser than ASCII as an encoder. — Alex K., Mar 15 '17 at 11:39
no, and it works fine but the problem is on sql side since it doesn't decode the encoded value — Covert, Mar 15 '17 at 11:43
Change it to use UTF8.GetBytes/String - this will fix things like a `£` symbol breaking, if that does not work can you share sample input/output at all stages. — Alex K., Mar 15 '17 at 11:48
Your Encrypt method really isn't encrypting. Even if you were using the result of calling `ComputeHash`, that would be hashing rather than encrypting. Why are you even bothering to encode in base64 when you start with text and end with text? — Jon Skeet, Mar 15 '17 at 11:56
Possible duplicate of [Base64 encoding in SQL Server 2005 T-SQL](http://stackoverflow.com/questions/5082345/base64-encoding-in-sql-server-2005-t-sql) — Ed Harper, Mar 15 '17 at 11:57

score 1 · Accepted Answer · answered Mar 15 '17 at 11:49

1

You are missing the xs:base64Binary function from the SELECT statement

SELECT CAST( CAST( @FMoney as XML ).value('xs:base64Binary(.)','varbinary(max)') AS varchar(max) )

returns

jxaedshasb2nzudkbuidjr35|15.00

answered Mar 15 '17 at 11:49

Ed Harper

21,127
4
54
80

Why BASE64 string is not getting decoded in sql 2012?

1 Answers1