Write data in a binary field in SQL Server 2008 R2

Question

I have already convert the "Password" input with this code

Dim password As String = (MyPass + MyUName)
    Dim mhash As HashAlgorithm = New SHA1CryptoServiceProvider
    Dim bytValue() As Byte = System.Text.Encoding.UTF8.GetBytes(password)
    Dim bytHash() As Byte = mhash.ComputeHash(bytValue)
    mhash.Clear()
    ENPass = Convert.ToBase64String(bytHash)

And with this code I'm taking back a byte array same like the bytHash

Dim Text As String = Convert.ToString(OfficeCommsRecs(5))
    Dim oReturn As New StringBuilder
    Dim Separator As String = ("")
    For Each Character As Byte In ASCIIEncoding.ASCII.GetBytes(Text)
        oReturn.Append(Convert.ToString(Character, 2).PadLeft(8, "1"))
        oReturn.Append(Separator)
    Next
    Dim myPass As Object = oReturn

What I have to do to take back the password?

can you post your VB code that does that? I suspect you are trying to save a string representation of the binary data when you really should be using byte[] instead — Cristian Lupascu
– Cristian Lupascu, Commented Apr 7, 2013 at 17:04
wOlf yes tha is what i do and i see that is not the wright one. please can you give a guide line what to do about? — Lefteris Gkinis
– Lefteris Gkinis, Commented Apr 7, 2013 at 17:11
Sorry I just see that it works the same without turn it to string — Lefteris Gkinis
– Lefteris Gkinis, Commented Apr 7, 2013 at 17:13
My method might not be what you are looking for, but I usually create a new field with the desired data type (here binary) and use an UPDATE to insert the values in the new binary field, after which I delete the no-more-needed varchar field. I do this when I cannot — Jerry
– Jerry, Commented Apr 7, 2013 at 17:20

Cristian Lupascu · Accepted Answer · 2013-04-08 11:59:18Z

2

ASCIIEncoding.ASCII.GetBytes(Text) gives you back a byte array. Use that directly in your data access code, as the value of an SqlParameter.

You don't need the rest of the code that you posted in the question: it only creates a human-readable representation of the byte array, but the database does not understand that. It expects a Byte(), not a String.

Update

I just realized I was a fool - I kept thinking about the technical problem (save/load binary data) and missed the big picture: you are not supposed to read the password back. So, hashing is the right way to go, but please check out this great article while you're at it: http://codahale.com/how-to-safely-store-a-password/

edited Apr 8, 2013 at 11:59

answered Apr 7, 2013 at 19:22

Cristian Lupascu

40.8k18 gold badges107 silver badges139 bronze badges

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

6 Comments

Lefteris Gkinis Over a year ago

OK right now I'm in position to have (back) the bytes like the bytHas in my encoding code above. What I have to do next to take back the password?

Cristian Lupascu Over a year ago

@LefterisGkinis jsut retrieve the data normally from the DB (using DataAdapter or DataReader). Your Password column will now contain a byte() value. Transfer that byte() back to string, using ASCII.GetString(passwordBytes)

Lefteris Gkinis Over a year ago

@wOlf I did that and i didn't get what i want. that GetString returns me the record itself which is finally converted to Convert.ToBase64String

Lefteris Gkinis Over a year ago

I have put the code which helps me to convert the password end put it to the SQL record. Now I'm taking this encoded record and I need to Decode in order to take back the password.

ErikE Over a year ago

If you're converting to base 64, why does it need to be stored in the database as binary?

|

user1608817 · Accepted Answer · 2013-04-07 19:07:35Z

1

If you are using the store procedure then pass the string and use the convert function of sql before store the data like Convert(binary,).

answered Apr 7, 2013 at 19:07

user1608817

4913 silver badges4 bronze badges

Comments

user1608817 · Accepted Answer · 2013-04-08 17:43:28Z

0

Find the below example for insert string in binary format:

---Create Table

Create Table Test ( Password binary(1000) )

---Create Proc for insert value

Create Proc InsertValue @password nvarchar(200) as Insert into Test(Password) Values (Convert(binary,@password))

--Insert value

Exec InsertValue 'NewPasword'

-----Fetch the binary data and converted data

Select Password , Convert(nvarchar(200), Password) Password From Test

edited Apr 8, 2013 at 17:43

answered Apr 8, 2013 at 17:38

user1608817

4913 silver badges4 bronze badges

1 Comment

Lefteris Gkinis Over a year ago

I user this for writing images I believe it's the same.

Collectives™ on Stack Overflow

Write data in a binary field in SQL Server 2008 R2

3 Answers 3

Update

6 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Update

6 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related