Parsing Binary(64) SQL Server Data To Plain Text In C#

Question

I have a SQL Server database with a binary(64) column housing a password. I am using a C# ASP.NET API to query the data and return the data as a string back to my C# code.

In SQL Server the password is

0x507572706C6500000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

In my C# the password as a string is

UHVycGxlAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA==

And in plain text the password is: Purple

My question at hand is how can I use C# to "parse" the returned string and convert it to readable format?

I tried the below syntax, but I get an error of... What would be the proper way to "parse" the hashed password into plain text?

Could not find any recognizable digits

public class SQLData
{
    public string pass { get; set; }
}

public static string BinaryToString(string data)
{
    List<Byte> byteList = new List<Byte>();

    for (int i = 0; i < data.Length; i += 8)
    {
        byteList.Add(Convert.ToByte(data.Substring(i, 8), 2));
    }
    return Encoding.ASCII.GetString(byteList.ToArray());
}
 private void btnPullData_Click(object sender, EventArgs e)
 {
    string URI = "http://192.168.5.200:8888/api/Xamarin?email=jose%40gmail.com;

    using (var webClient = new System.Net.WebClient())
    {
        var json = webClient.DownloadString(URI);

        var message = JsonConvert.DeserializeObject<SQLData>(json);

        string clearpass = BinaryToString(message.pass);
        MessageBox.Show(clearpass);
    }

}

Are you asking how to decode the SQL server string, or the c# string? The c# string looks like Base64 so see [How do I decode a base64 encoded string?](https://stackoverflow.com/q/7134837/3744182)... yes, it's base64. Go to https://codebeautify.org/base64-decode and decode and you will get "Purple..." with a bunch of null characters appended. — dbc, Nov 20 '18 at 01:15
@dbc - that is exactly what I needed!!! Thank you so much for that! It's all about knowing what to search for! — user2932408, Nov 20 '18 at 01:22

score 0 · Answer 1 · answered Nov 20 '18 at 01:18

0

First off, it's a terrible idea to store passwords in plain-text in the database. That said, the binary column of data from the database is in hex (base 16).

In your example, to convert

0x507572706C6500000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

to Purple in C#, you could write:

Encoding.UTF8.GetString(yourByteArrayVariable).TrimEnd(\'0')

The TrimEnd is necessary because your column is a fixed width binary and not varbinary. As a result, it is right padded with the null character (0) and should be removed from the resulting string.

answered Nov 20 '18 at 01:18

Connor

807
1
10
20

I get a compile error of can not convert from string to byte[]. – user2932408 Nov 20 '18 at 01:21
The datatype you have coming back from your database is probably not a `byte[]` then and is probably a string. Can you edit your post to include your code showing how you're getting the column from your database? – Connor Nov 20 '18 at 01:23
if you look at my class SQLData I am converting it to String data type in C# – user2932408 Nov 20 '18 at 01:24

Parsing Binary(64) SQL Server Data To Plain Text In C#

1 Answers1