How to get string back in c# after converting to binary in SQL Server

Question

I have string value key:

TxtProductKey.Text has value "key"

Encode this value

 byte[] Key = Encoding.ASCII.GetBytes(TxtProductKey.Text.Trim());

Save it in database table in a column of datatype binary(50) null.

The value in the table looks like this:

0x6B00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

Now I am trying to get it back in string value that I entered is Key

byte [] TempKey = (byte[])LicenseInfo.Tables[0].Rows[0]["ProductKey"];
var newText = Encoding.ASCII.GetString(TempKey);

but the result I am getting in newText is:

k\0\0\0\0\0\00\

Where am I doing something wrong? I hope for your suggestions

C# code for saving value in database:

Sqlconnection.Open();

SqlCommand Cmd = new SqlCommand("SpAddAttempts", Sqlconnection);
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.Add("@Attempts", SqlDbType.Int).Value = Attempt;
Cmd.Parameters.Add("@Activate", SqlDbType.Bit).Value = Activate;
Cmd.Parameters.Add("@Key", SqlDbType.Binary).Value = Key;

Cmd.ExecuteNonQuery();
CloseConnection();

Stored procedure is:

ALTER PROCEDURE [dbo].[SpAddAttempts]
    @Attempts INT,
    @Activate BIT,
    @Key BINARY
AS
BEGIN
    UPDATE License 
    SET Attempts = @Attempts,
        Activate = @Activate,
        ProductKey = @Key 
    WHERE LicenseId = '1'
END

You are not saving it right. The data has only one non-zero byte, it should have three bytes (i.e. 0x6B657900...) — Sergey Kalinichenko
– Sergey Kalinichenko, Commented Nov 25, 2017 at 10:32
You can do that, can you see in debug what is the value of the Key byte array ? Before it goes inside the DB. The example shows only one character in the db binary data — farbiondriven
– farbiondriven, Commented Nov 25, 2017 at 10:32
@dasblinkenlight Can you show me mistake ? my saving code is there in my post. — john
– john, Commented Nov 25, 2017 at 10:53
@john The edit to the answer of Caius Jard (passing length) should fix this problem. — Sergey Kalinichenko
– Sergey Kalinichenko, Commented Nov 25, 2017 at 10:58

Caius Jard · Accepted Answer · 2017-11-26 03:04:09Z

2

I don't think you can: you only stored a single byte (the equivalent of char k) in your db table.. 0x68 is k, the next byte is 0x00 - ascii null - string terminator - whatever your language of choice will refer to it as, it's definitely not e

So, because only the first byte has been preserved and the rest is ascii NUL 000000000000000000000000....), there's no way to know whether the rest of the value was ey, eyboard, oolaid etc.. The fault is during the storing of the value in the table (you didn't show code for that part) - it's only stored the first byte

I recommend you store this text as text, not binary; it seems like an unnecessary world of pain to store it as binary when it starts life as text and you clearly want it back out as text.

EDIT: Now you've posted the code that does the saving, I can see youve declared the parameter as type Binary, but you havent specified a length, so the length is defaulting to 1 byte. This is why only one of your bytes is getting saved. You need to declare the parameter more like this:

cmd.Parameters.Add("@Key", SqlDbType.Binary, binaryData.Length); //dont pass an array longer than what the table can store, or you'll get a "data would be truncated" error

edited Nov 26, 2017 at 3:04

answered Nov 25, 2017 at 10:30

Caius Jard

75k6 gold badges61 silver badges95 bronze badges

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

5 Comments

john Over a year ago

i enter "key" for saving and reason for encoding is to secure it otherwise it is easy to save text directly. May be i am doing any mistake so that it is saving only "k" value out of "key".

Caius Jard Over a year ago

Oh yeah, converting it to binary makes it really secure when it's a couple of extra key strokes to paste it into branah.com/ascii-converter and get the original ascii back. ROT13'd text would be harder to crack (read: take a few seconds longer to realise that was the security mechanism). Either implement a proper security solution, or store it as text and review your "who has the sql server password" policy :)

john Over a year ago

bro you seems to be more professional i am quit new so dont have much information but thanks for giving me more information :) i edited post now you can find code for storing data.

Caius Jard Over a year ago

If you're looking to store this data securely, you need to look at making it a lot harder to casually observe - encrypt it using a proper algorithm

john Over a year ago

i mention length of binary column @Key binary(50) in stored procedure help me out.

Collectives™ on Stack Overflow

How to get string back in c# after converting to binary in SQL Server

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related