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.
– Alex K., Commented Mar 15, 2017 at 11:39
no, and it works fine but the problem is on sql side since it doesn't decode the encoded value — Covert
– Covert, Commented Mar 15, 2017 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.
– Alex K., Commented Mar 15, 2017 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
– Jon Skeet, Commented Mar 15, 2017 at 11:56
Possible duplicate of Base64 encoding in SQL Server 2005 T-SQL — Ed Harper
– Ed Harper, Commented Mar 15, 2017 at 11:57

Ed Harper · Accepted Answer · 2017-03-15 11:49:41Z

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, 2017 at 11:49

Ed Harper

21.6k5 gold badges60 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why BASE64 string is not getting decoded in sql 2012?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related