How to save an encrypted string to the database?

Question

I have this function in VB.net "ENCRYPT" (see below)

Private key() As Byte = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24} Private iv() As Byte = {65, 110, 68, 26, 69, 178, 200, 219}

Public Function Encrypt(ByVal plainText As String) As Byte()

    ' Declare a UTF8Encoding object so we may use the GetByte 
    ' method to transform the plainText into a Byte array. 
    Dim utf8encoder As UTF8Encoding = New UTF8Encoding()
    Dim inputInBytes() As Byte = utf8encoder.GetBytes(plainText)

    ' Create a new TripleDES service provider 
    Dim tdesProvider As TripleDESCryptoServiceProvider = New TripleDESCryptoServiceProvider()

    ' The ICryptTransform interface uses the TripleDES 
    ' crypt provider along with encryption key and init vector 
    ' information 
    Dim cryptoTransform As ICryptoTransform = tdesProvider.CreateEncryptor(Me.key, Me.iv)

    ' All cryptographic functions need a stream to output the 
    ' encrypted information. Here we declare a memory stream 
    ' for this purpose. 
    Dim encryptedStream As MemoryStream = New MemoryStream()
    Dim cryptStream As CryptoStream = New CryptoStream(encryptedStream, cryptoTransform, CryptoStreamMode.Write)

    ' Write the encrypted information to the stream. Flush the information 
    ' when done to ensure everything is out of the buffer. 
    cryptStream.Write(inputInBytes, 0, inputInBytes.Length)
    cryptStream.FlushFinalBlock()
    encryptedStream.Position = 0

    ' Read the stream back into a Byte array and return it to the calling 
    ' method. 
    Dim result(encryptedStream.Length - 1) As Byte
    encryptedStream.Read(result, 0, encryptedStream.Length)
    cryptStream.Close()
    Return result
End Function

I want to save the encrypted string in the SQL database. How do I do it?

Answer 1

Encode the array of byte into a string. 0x00 can be "00" and 0xFF can be "FF." Or you can take at look at Base64.

Answer 2

An encripted string should be no different to any binary data.

If you know the results are going to be small you could uuencode it and save it in a text field.

Answer 3

Simply store in a binary column. (Mostly done from memory, corrections welcome!)

CREATE TABLE [Test]
(
    [Id] NOT NULL IDENTITY(1,1) PRIMARY KEY,
    [Username] NOT NULL VARCHAR(500),
    [Password] NOT NULL VARBINARY(500)
)

Then insert such:

Dim conn As SqlConnection

Try
    conn = New SqlConnection("<connectionstring>")
    Dim command As New SqlCommand("INSERT INTO [Test] ([Username], [Password]) VALUES (@Username, @Password)", conn)

    Dim usernameParameter = New SqlParameter("@Username", SqlDbType.VarChar)
    usernameParameter.Value = username
    command.Parameters.Add(usernameParameter)

    Dim passwordParameter = New SqlParameter("@Password", SqlDbType.VarBinary)
    passwordParameter.Value = password
    command.Parameters.Add(passwordParameter)

    command.ExecuteNonQuery()

Finally
    If (Not (conn Is Nothing)) Then
        conn.Close()
    End If
End Try