DataFlex
DataFlex
Duplicate SQL Server ENCRYPTBYPASSPHRASE
See more Encryption Examples
Demonstrates how to duplicate SQL Server's ENCRYPTBYPASSPHRASE.Chilkat DataFlex Downloads
Use ChilkatAx-win32.pkg
Procedure Test
String sPassword
String sEncryptedHex_v1
String sEncryptedHex_v2
Handle hoSbEncHex
Boolean iSuccess
Handle hoCrypt
Boolean iV1
Integer iIvLen
String sHashAlg
String sIvHex
Handle hoSbPassword
String sPwd_hash
Handle hoSbKey
Variant vBd
Handle hoBd
String sPlainText
Handle hoEncryptor
Handle hoPrng
Integer iPlainTextLen
Variant vBd
Data Handle hoBdData
Handle hoSbEnc
String sTemp1
Boolean bTemp1
// 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.
Move "tEst1234" To sPassword
Move "0x010000001E8E7DCDBD4061B951999E25D18445D2305474D2D71EEE98A241C755246F58AB" To sEncryptedHex_v1
// Here's an encrypted string using AES256/SHA256
Move "0x02000000FFE880C0354780481E64EF25B6197A02E2A854A4BA9D8D9BDDFDAB27EB56537ABDA0B1D9C4D1050C91B313550DECF429" To sEncryptedHex_v2
Get Create (RefClass(cComChilkatStringBuilder)) To hoSbEncHex
If (Not(IsComObjectCreated(hoSbEncHex))) Begin
Send CreateComObject of hoSbEncHex
End
Get ComAppend Of hoSbEncHex sEncryptedHex_v1 To iSuccess
// If present, we don't want the leading "0x"
Get ComStartsWith Of hoSbEncHex "0x" False To bTemp1
If (bTemp1 = True) Begin
Get ComRemoveCharsAt Of hoSbEncHex 0 2 To iSuccess
End
Get Create (RefClass(cComChilkatCrypt2)) To hoCrypt
If (Not(IsComObjectCreated(hoCrypt))) Begin
Send CreateComObject of hoCrypt
End
Set ComEncodingMode Of hoCrypt To "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.
Get ComStartsWith Of hoSbEncHex "01" False To iV1
Move 0 To iIvLen
If (iV1 = True) Begin
Set ComCryptAlgorithm Of hoCrypt To "3des"
Set ComCipherMode Of hoCrypt To "cbc"
Set ComKeyLength Of hoCrypt To 168
Move 8 To iIvLen
Move "sha1" To sHashAlg
End
Else Begin
Set ComCryptAlgorithm Of hoCrypt To "aes"
Set ComCipherMode Of hoCrypt To "cbc"
Set ComKeyLength Of hoCrypt To 256
Move 16 To iIvLen
Move "sha256" To sHashAlg
End
// Remove the SQL Server version info (i.e. the "01000000")
Get ComRemoveCharsAt Of hoSbEncHex 0 8 To iSuccess
// Get the IV part of the sbEncHex, and also remove it from the StringBuilder.
Get ComGetRange Of hoSbEncHex 0 (iIvLen * 2) True To sIvHex
Showln "IV = " sIvHex
Send ComSetEncodedIV To hoCrypt sIvHex "hex"
Get Create (RefClass(cComChilkatStringBuilder)) To hoSbPassword
If (Not(IsComObjectCreated(hoSbPassword))) Begin
Send CreateComObject of hoSbPassword
End
Get ComAppend Of hoSbPassword sPassword To iSuccess
Get ComGetHash Of hoSbPassword sHashAlg "hex" "utf-16" To sPwd_hash
Get Create (RefClass(cComChilkatStringBuilder)) To hoSbKey
If (Not(IsComObjectCreated(hoSbKey))) Begin
Send CreateComObject of hoSbKey
End
Get ComAppend Of hoSbKey sPwd_hash To iSuccess
If (iV1 = True) Begin
// 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)
Get ComShorten Of hoSbKey 8 To iSuccess
End
Get ComGetAsString Of hoSbKey To sTemp1
Showln "crypt key: " sTemp1
Get ComGetAsString Of hoSbKey To sTemp1
Send ComSetEncodedKey To hoCrypt sTemp1 "hex"
// Decrypt
Get Create (RefClass(cComChilkatBinData)) To hoBd
If (Not(IsComObjectCreated(hoBd))) Begin
Send CreateComObject of hoBd
End
Get ComGetAsString Of hoSbEncHex To sTemp1
Get ComAppendEncoded Of hoBd sTemp1 "hex" To iSuccess
Get pvComObject of hoBd to vBd
Get ComDecryptBd Of hoCrypt vBd To iSuccess
// 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...
Get ComRemoveChunk Of hoBd 0 8 To iSuccess
Get ComGetString Of hoBd "utf-8" To sPlainText
Showln "decrypted plain text: " sPlainText
// 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
Get Create (RefClass(cComChilkatCrypt2)) To hoEncryptor
If (Not(IsComObjectCreated(hoEncryptor))) Begin
Send CreateComObject of hoEncryptor
End
Set ComEncodingMode Of hoEncryptor To "hex"
Set ComCryptAlgorithm Of hoEncryptor To "3des"
Set ComCipherMode Of hoEncryptor To "cbc"
Set ComKeyLength Of hoEncryptor To 168
// Generate a random 8-byte IV
Get Create (RefClass(cComChilkatPrng)) To hoPrng
If (Not(IsComObjectCreated(hoPrng))) Begin
Send CreateComObject of hoPrng
End
Get ComGenRandom Of hoPrng 8 "hex" To sIvHex
Send ComSetEncodedIV To hoEncryptor sIvHex "hex"
// The binary password is generated the same as above.
// We'll use the same password (and same binary password)
Get ComGetAsString Of hoSbKey To sTemp1
Send ComSetEncodedKey To hoEncryptor sTemp1 "hex"
Move 8 To iPlainTextLen
Move "ABCD1234" To sPlainText
// Encrypt the header + the plain-text.
Get Create (RefClass(cComChilkatBinData)) To hoBdData
If (Not(IsComObjectCreated(hoBdData))) Begin
Send CreateComObject of hoBdData
End
Get ComAppendEncoded Of hoBdData "0DF0ADBA" "hex" To iSuccess
Get ComAppendEncoded Of hoBdData "0000" "hex" To iSuccess
Get ComAppendInt2 Of hoBdData iPlainTextLen True To iSuccess
Get ComGetEncoded Of hoBdData "hex" To sTemp1
Showln "header: " sTemp1
Get ComAppendString Of hoBdData sPlainText "utf-8" To iSuccess
Get pvComObject of hoBdData to vBdData
Get ComEncryptBd Of hoEncryptor vBdData To iSuccess
// Compose the result..
Get Create (RefClass(cComChilkatStringBuilder)) To hoSbEnc
If (Not(IsComObjectCreated(hoSbEnc))) Begin
Send CreateComObject of hoSbEnc
End
Get ComAppend Of hoSbEnc "0x01000000" To iSuccess
Get ComAppend Of hoSbEnc sIvHex To iSuccess
Get ComGetEncoded Of hoBdData "hex" To sTemp1
Get ComAppend Of hoSbEnc sTemp1 To iSuccess
Get ComGetAsString Of hoSbEnc To sTemp1
Showln "result: " sTemp1
End_Procedure