vbEncrypt/Decrypt bug with MS SQL Server: strongm et al. 2

[OrthoDocSoft]

Strongm and others,

I have been using vbEncrypt/vbDecrypt that you, strongm, provided to me several years ago (and for which I'm most grateful). This has worked well as I can encrypt and store the encrypted bit in a:

file, and
MS Access DB

retrieve it, decrypt it and the data all comes back just fine.

But when I store an encrypted string into

MS SQL Server

The data gets corrupted, such that when I retrieve it, then decrypt it, it doesn't decrypt; I get some gibberish.

I used the EXACT code to write and then retrive from an MS Access DB (only changing the connection strings) and it works fine. I have used trim() to no avail. I have changed the data type in cell in SQL Server DB to many different types, and that doesn't seem to work.

Any suggestions?

Thanks,

Ortho

"you cain't fix 'stupid'...

 

[HarleyQuinn]

Could you show use the exact code you're using?

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[strongm]

Just what I was about to ask

 

[OrthoDocSoft]

Thanks guys,

This is the little "test sub" hooked to a command button that I wrote to investigate the problem. It just encrypts a small string, stores it, retrieves it and decrypts it.

Private Sub Command1_Click()

Dim adoRSTest As ADODB.Recordset

Dim strSQL As String
Dim strTestString As String
Dim strDecryptedString As String
Dim strDecryptedFromDB As String

Dim varTestString As Variant
Dim varEncryptedGoingToDB As Variant
Dim varEncryptedFromDB As Variant

Set adoRSTest = New ADODB.Recordset

varTestString = "Test string"

varEncryptedGoingToDB = vbEncrypt(varTestString, "LeadMine82")

MsgBox varEncryptedGoingToDB

strSQL = "UPDATE admin SET TestCell = '" & varEncryptedGoingToDB & "'"

adoConnection.Execute (strSQL)

MsgBox "Stop to check DB's"

strSQL = "SELECT * FROM admin"

MsgBox "adoConnection = " & adoConnection

adoRSTest.Open (strSQL), adoConnection, adOpenKeyset, adLockOptimistic

adoRSTest.MoveFirst

varEncryptedFromDB = Trim(adoRSTest.Fields("TestCell"))

strDecryptedFromDB = vbDecrypt(varEncryptedFromDB, "LeadMine82")

MsgBox "Decrypted from DB: " & strDecryptedFromDB

strDecryptedString = vbDecrypt(varEncryptedGoingToDB, "LeadMine82")

MsgBox "Decrypted before storing: " & strDecryptedString

adoRSTest.Close

Set adoRSTest = Nothing

End Sub

Here is vbEncrypt

Public Function vbEncrypt(varText As Variant, strPassword As String) _
As Byte()

    vbEncrypt = CoreCrypto(varText, strPassword, encrypt)
    
End Function

Here is vbDecrypt

Public Function vbDecrypt(varText As Variant, strPassword As String) _
As Byte()
    
    vbDecrypt = CoreCrypto(varText, strPassword, decrypt)
    
End Function

Here's CoreCrypto

Public Function CoreCrypto(strText As Variant, strPassword As String, _
Mode As EncryptionMode) As Byte()
    Dim hProv As Long
    Dim ByteBuffer() As Byte
    Dim strprovider As String
    Dim hHash As Long
    Dim hKey As Long
    Dim datalen As Long
    
    
    ByteBuffer = strText
    
    ' Grab an RSA-based cryptoapi context using Microsoft's base provider
    strprovider = MS_DEFAULT_PROVIDER & vbNullChar
    Call CryptAcquireContext(hProv, vbNullString, strprovider, PROV_RSA_FULL, CRYPT_VERIFYCONTEXT) ' final param could be  0&
    
    ' Generate a hash of the password
    Call CryptCreateHash(hProv, CALG_MD5, 0, 0, hHash)
    Call CryptHashData(hHash, strPassword, Len(strPassword), 0)
    
    ' Derive a key symmetric key based on hashed password
    Call CryptDeriveKey(hProv, CALG_RC4, hHash, 0&, hKey)
    
    ' Apply decryption or encryption using derived key
    datalen = UBound(ByteBuffer)
    Select Case Mode
        Case encrypt
            Call CryptEncrypt(hKey, 0, 1, 0, ByteBuffer(0), datalen, UBound(ByteBuffer))
        Case decrypt
            Call CryptDecrypt(hKey, 0, 1, 0, ByteBuffer(0), UBound(ByteBuffer))
    End Select
    
    CoreCrypto = ByteBuffer
    
    ' Clean up
    CryptDestroyKey hKey
    CryptReleaseContext hProv, 0&

End Function

"you cain't fix 'stupid'...

 

[gmmastros]

I suggest you use (if not already) a unicode data type, like nvarchar. Then, you'll also need to change your VB code, too.

For example, set the data type for TestCell to nvarchar(max) if you are using SQL 2005+ or nvarchar(1000) if you are using SQL2000.

Then, to use unicode literals, you need to preface the delimiter with N, like this.

strSQL = "UPDATE admin SET TestCell = [!]N[/!]'" & varEncryptedGoingToDB & "'"

Basically, you are getting a unicode to ascii conversion when you don't use the N. For example, in a query window....

Select 'u?op ?p?sdn'

Select N'u?op ?p?sdn'

Output is...

[tt][blue]
u?op ?pisdn
u?op ?p?sdn
[/blue][/tt]

Without the N, the data gets mangled.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[strongm]

Yep - as George says - the data type of TestCell is quite important here

 

[OrthoDocSoft]

Worked like a friggin' charm! Star for George.

Hey, while I've got your attention, could you comment on one thing:

vbEncrypt and vbDecrypt are functions of type "Byte" Does that mean that I can only encrypt/decrypt a shorter (one byte) bit of data?

Thanks guys, as always,

Ortho

"you cain't fix 'stupid'...

 

[gmmastros]

vbEncrypt and vbDecrypt are functions of type "Byte"

look again...

[tt]As Byte[!]()[/!][/tt]

That's actually a byte array so it can handle (significantly) more than one byte.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[OrthoDocSoft]

AHAAAAAAAAA! Another star.....

Ortho

"you cain't fix 'stupid'...