(Xojo Plugin) Duplicate SQL Server ENCRYPTBYPASSPHRASE

See more Encryption Examples

Demonstrates how to duplicate SQL Server's ENCRYPTBYPASSPHRASE.

Chilkat Xojo Plugin Download Xojo Plugin for Windows, Linux, Mac OS X, and ARM, ARM64

// This example requires the Chilkat API to have been previously unlocked.
// See Global Unlock Sample for sample code.

// For SQL Server 2008 - SQL Server 2016 we must use TripleDES with SHA1
// For SQL Server 2017 and later, use AES256 / SHA256.

Dim password As String
password = "tEst1234"
Dim encryptedHex_v1 As String
encryptedHex_v1 = "0x010000001E8E7DCDBD4061B951999E25D18445D2305474D2D71EEE98A241C755246F58AB"

// Here's an encrypted string using AES256/SHA256
Dim encryptedHex_v2 As String
encryptedHex_v2 = "0x02000000FFE880C0354780481E64EF25B6197A02E2A854A4BA9D8D9BDDFDAB27EB56537ABDA0B1D9C4D1050C91B313550DECF429"

Dim sbEncHex As New Chilkat.StringBuilder
Dim success As Boolean
success = sbEncHex.Append(encryptedHex_v1)

// If present, we don't want the leading "0x"
If (sbEncHex.StartsWith("0x",False) = True) Then
    success = sbEncHex.RemoveCharsAt(0,2)
End If

Dim crypt As New Chilkat.Crypt2
crypt.EncodingMode = "hex"

// The encrypted hex string will begin with either 01000000 or 02000000
// version 1 is produced by SQL Server 2008 to SQL Server 2016, and we must use TripleDES with SHA1
// version 2 is for SQL Server 2017 and later, and uses AES256 / SHA256.
Dim v1 As Boolean
v1 = sbEncHex.StartsWith("01",False)

Dim ivLen As Int32
ivLen = 0
Dim hashAlg As String

If (v1 = True) Then
    crypt.CryptAlgorithm = "3des"
    crypt.CipherMode = "cbc"
    crypt.KeyLength = 168
    ivLen = 8
    hashAlg = "sha1"
Else
    crypt.CryptAlgorithm = "aes"
    crypt.CipherMode = "cbc"
    crypt.KeyLength = 256
    ivLen = 16
    hashAlg = "sha256"
End If

// Remove the SQL Server version info (i.e. the "01000000")
success = sbEncHex.RemoveCharsAt(0,8)

// Get the IV part of the sbEncHex, and also remove it from the StringBuilder.
Dim ivHex As String
ivHex = sbEncHex.GetRange(0,ivLen * 2,True)
System.DebugLog("IV = " + ivHex)
crypt.SetEncodedIV ivHex,"hex"

Dim sbPassword As New Chilkat.StringBuilder
success = sbPassword.Append(password)
Dim pwd_hash As String
pwd_hash = sbPassword.GetHash(hashAlg,"hex","utf-16")
Dim sbKey As New Chilkat.StringBuilder
success = sbKey.Append(pwd_hash)
If (v1 = True) Then
    // For v1, we only want the 1st 16 bytes of the 20 byte hash.
    // (remember, the hex encoding uses 2 chars per byte, so we remove the last 8 chars)
    success = sbKey.Shorten(8)
End If

System.DebugLog("crypt key: " + sbKey.GetAsString())

crypt.SetEncodedKey sbKey.GetAsString(),"hex"

// Decrypt
Dim bd As New Chilkat.BinData
success = bd.AppendEncoded(sbEncHex.GetAsString(),"hex")
success = crypt.DecryptBd(bd)

// The result is composed of a header of 8 bytes which we can discard.
// The remainder is the decrypted text.

// The header we are discarding is composed of:
// Bytes 0-3: Magic number equal to 0DF0ADBA
// Bytes 4-5: Number of integrity bytes, which is 0 unless an authenticator is used. We're assuming no authenticator is used.
// Bytes 6-7: Number of plain-text bytes. We really don't need this because the CBC padding takes care of it.

// Therefore, just return the data after the 1st 8 bytes.
// Assuming the encrypted string was utf-8 text...
success = bd.RemoveChunk(0,8)
Dim plainText As String
plainText = bd.GetString("utf-8")
System.DebugLog("decrypted plain text: " + plainText)

// The output:

// IV = 1E8E7DCDBD4061B9
// crypt key: 710B9C2E61ACCC9570D4112203BD9738
// decrypted plain text: Hello world.

// ------------------------------------------------------------------------------------------
// To encrypt, do the reverse...

// Let's do v1 with TripleDES with SHA1

Dim encryptor As New Chilkat.Crypt2
encryptor.EncodingMode = "hex"

encryptor.CryptAlgorithm = "3des"
encryptor.CipherMode = "cbc"
encryptor.KeyLength = 168

// Generate a random 8-byte IV
Dim prng As New Chilkat.Prng
ivHex = prng.GenRandom(8,"hex")
encryptor.SetEncodedIV ivHex,"hex"

// The binary password is generated the same as above.
// We'll use the same password (and same binary password)
encryptor.SetEncodedKey sbKey.GetAsString(),"hex"

Dim plainTextLen As Int32
plainTextLen = 8
plainText = "ABCD1234"

// Encrypt the header + the plain-text.
Dim bdData As New Chilkat.BinData
success = bdData.AppendEncoded("0DF0ADBA","hex")
success = bdData.AppendEncoded("0000","hex")
success = bdData.AppendInt2(plainTextLen,True)
System.DebugLog("header: " + bdData.GetEncoded("hex"))
success = bdData.AppendString(plainText,"utf-8")
success = encryptor.EncryptBd(bdData)

// Compose the result..
Dim sbEnc As New Chilkat.StringBuilder
success = sbEnc.Append("0x01000000")
success = sbEnc.Append(ivHex)
success = sbEnc.Append(bdData.GetEncoded("hex"))

System.DebugLog("result: " + sbEnc.GetAsString())