B4X

Duplicate SQL Server ENCRYPTBYPASSPHRASE

See more Encryption Examples

Demonstrates how to duplicate SQL Server's ENCRYPTBYPASSPHRASE. B4X

'  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 = "tEst1234"
Dim encryptedHex_v1 As String = "0x010000001E8E7DCDBD4061B951999E25D18445D2305474D2D71EEE98A241C755246F58AB"


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

Dim sbEncHex As ChilkatStringBuilder
sbEncHex.Initialize
sbEncHex.Append(encryptedHex_v1)

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


Dim crypt As ChilkatCrypt2
crypt.Initialize("crypt")
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 = sbEncHex.StartsWith("01", False)

Dim ivLen As Int = 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")
sbEncHex.RemoveCharsAt(0, 8)

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

Dim sbPassword As ChilkatStringBuilder
sbPassword.Initialize
sbPassword.Append(password)
Dim pwd_hash As String = sbPassword.GetHash(hashAlg, "hex", "utf-16")
Dim sbKey As ChilkatStringBuilder
sbKey.Initialize
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)
    sbKey.Shorten(8)
End If


Log("crypt key: " & sbKey.GetAsString)

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


'  Decrypt
Dim bd As ChilkatBinData
bd.Initialize
bd.AppendEncoded(sbEncHex.GetAsString, "hex")
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...
bd.RemoveChunk(0, 8)
Dim plainText As String = bd.GetString("utf-8")
Log("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 ChilkatCrypt2
encryptor.Initialize("encryptor")
encryptor.EncodingMode = "hex"

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

'  Generate a random 8-byte IV
Dim prng As ChilkatPrng
prng.Initialize
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 Int = 8
plainText = "ABCD1234"

'  Encrypt the header + the plain-text.
Dim bdData As ChilkatBinData
bdData.Initialize
bdData.AppendEncoded("0DF0ADBA", "hex")
bdData.AppendEncoded("0000", "hex")
bdData.AppendInt2(plainTextLen, True)
Log("header: " & bdData.GetEncoded("hex"))
bdData.AppendString(plainText, "utf-8")
encryptor.EncryptBd(bdData)

'  Compose the result..
Dim sbEnc As ChilkatStringBuilder
sbEnc.Initialize
sbEnc.Append("0x01000000")
sbEnc.Append(ivHex)
sbEnc.Append(bdData.GetEncoded("hex"))

Log("result: " & sbEnc.GetAsString)