PowerBuilder

Duplicate SQL Server ENCRYPTBYPASSPHRASE

See more Encryption Examples

Demonstrates how to duplicate SQL Server's ENCRYPTBYPASSPHRASE. PowerBuilder

integer li_rc
string ls_Password
string ls_EncryptedHex_v1
string ls_EncryptedHex_v2
oleobject loo_SbEncHex
oleobject loo_Crypt
integer li_V1
integer li_IvLen
string ls_HashAlg
string ls_IvHex
oleobject loo_SbPassword
string ls_Pwd_hash
oleobject loo_SbKey
oleobject loo_Bd
string ls_PlainText
oleobject loo_Encryptor
oleobject loo_Prng
integer li_PlainTextLen
oleobject loo_BdData
oleobject loo_SbEnc

// 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.

ls_Password = "tEst1234"
ls_EncryptedHex_v1 = "0x010000001E8E7DCDBD4061B951999E25D18445D2305474D2D71EEE98A241C755246F58AB"

// Here's an encrypted string using AES256/SHA256
ls_EncryptedHex_v2 = "0x02000000FFE880C0354780481E64EF25B6197A02E2A854A4BA9D8D9BDDFDAB27EB56537ABDA0B1D9C4D1050C91B313550DECF429"

loo_SbEncHex = create oleobject
li_rc = loo_SbEncHex.ConnectToNewObject("Chilkat.StringBuilder")
if li_rc < 0 then
    destroy loo_SbEncHex
    MessageBox("Error","Connecting to COM object failed")
    return
end if
loo_SbEncHex.Append(ls_EncryptedHex_v1)

// If present, we don't want the leading "0x"
if loo_SbEncHex.StartsWith("0x",0) = 1 then
    loo_SbEncHex.RemoveCharsAt(0,2)
end if

loo_Crypt = create oleobject
li_rc = loo_Crypt.ConnectToNewObject("Chilkat.Crypt2")

loo_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.
li_V1 = loo_SbEncHex.StartsWith("01",0)

li_IvLen = 0

if li_V1 = 1 then
    loo_Crypt.CryptAlgorithm = "3des"
    loo_Crypt.CipherMode = "cbc"
    loo_Crypt.KeyLength = 168
    li_IvLen = 8
    ls_HashAlg = "sha1"
else
    loo_Crypt.CryptAlgorithm = "aes"
    loo_Crypt.CipherMode = "cbc"
    loo_Crypt.KeyLength = 256
    li_IvLen = 16
    ls_HashAlg = "sha256"
end if

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

// Get the IV part of the sbEncHex, and also remove it from the StringBuilder.
ls_IvHex = loo_SbEncHex.GetRange(0,li_IvLen * 2,1)
Write-Debug "IV = " + ls_IvHex
loo_Crypt.SetEncodedIV(ls_IvHex,"hex")

loo_SbPassword = create oleobject
li_rc = loo_SbPassword.ConnectToNewObject("Chilkat.StringBuilder")

loo_SbPassword.Append(ls_Password)
ls_Pwd_hash = loo_SbPassword.GetHash(ls_HashAlg,"hex","utf-16")
loo_SbKey = create oleobject
li_rc = loo_SbKey.ConnectToNewObject("Chilkat.StringBuilder")

loo_SbKey.Append(ls_Pwd_hash)
if li_V1 = 1 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)
    loo_SbKey.Shorten(8)
end if

Write-Debug "crypt key: " + loo_SbKey.GetAsString()

loo_Crypt.SetEncodedKey(loo_SbKey.GetAsString(),"hex")

// Decrypt
loo_Bd = create oleobject
li_rc = loo_Bd.ConnectToNewObject("Chilkat.BinData")

loo_Bd.AppendEncoded(loo_SbEncHex.GetAsString(),"hex")
loo_Crypt.DecryptBd(loo_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...
loo_Bd.RemoveChunk(0,8)
ls_PlainText = loo_Bd.GetString("utf-8")
Write-Debug "decrypted plain text: " + ls_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

loo_Encryptor = create oleobject
li_rc = loo_Encryptor.ConnectToNewObject("Chilkat.Crypt2")

loo_Encryptor.EncodingMode = "hex"

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

// Generate a random 8-byte IV
loo_Prng = create oleobject
li_rc = loo_Prng.ConnectToNewObject("Chilkat.Prng")

ls_IvHex = loo_Prng.GenRandom(8,"hex")
loo_Encryptor.SetEncodedIV(ls_IvHex,"hex")

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

li_PlainTextLen = 8
ls_PlainText = "ABCD1234"

// Encrypt the header + the plain-text.
loo_BdData = create oleobject
li_rc = loo_BdData.ConnectToNewObject("Chilkat.BinData")

loo_BdData.AppendEncoded("0DF0ADBA","hex")
loo_BdData.AppendEncoded("0000","hex")
loo_BdData.AppendInt2(li_PlainTextLen,1)
Write-Debug "header: " + loo_BdData.GetEncoded("hex")
loo_BdData.AppendString(ls_PlainText,"utf-8")
loo_Encryptor.EncryptBd(loo_BdData)

// Compose the result..
loo_SbEnc = create oleobject
li_rc = loo_SbEnc.ConnectToNewObject("Chilkat.StringBuilder")

loo_SbEnc.Append("0x01000000")
loo_SbEnc.Append(ls_IvHex)
loo_SbEnc.Append(loo_BdData.GetEncoded("hex"))

Write-Debug "result: " + loo_SbEnc.GetAsString()


destroy loo_SbEncHex
destroy loo_Crypt
destroy loo_SbPassword
destroy loo_SbKey
destroy loo_Bd
destroy loo_Encryptor
destroy loo_Prng
destroy loo_BdData
destroy loo_SbEnc