CODEWITHSUNDEEP
sqlvb.netsql-server-2008-r2
Lefteris Gkinis
Lefteris Gkinis

Reputation: 1259

Write data in a binary field in SQL Server 2008 R2

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?

Upvotes: 2

Views: 1554

Answers (3)

user1608817
user1608817

Reputation: 491

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

Upvotes: 0

Cristian Lupascu
Cristian Lupascu

Reputation: 40526

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/

Upvotes: 2

user1608817
user1608817

Reputation: 491

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,).

Upvotes: 1

Related Questions