(Visual FoxPro) Duplicate SQL Server ENCRYPTBYPASSPHRASE

See more Encryption Examples

Demonstrates how to duplicate SQL Server's ENCRYPTBYPASSPHRASE.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

LOCAL lcPassword
LOCAL lcEncryptedHex_v1
LOCAL lcEncryptedHex_v2
LOCAL loSbEncHex
LOCAL loCrypt
LOCAL lnV1
LOCAL lnIvLen
LOCAL lcHashAlg
LOCAL lcIvHex
LOCAL loSbPassword
LOCAL lcPwd_hash
LOCAL loSbKey
LOCAL loBd
LOCAL lcPlainText
LOCAL loEncryptor
LOCAL loPrng
LOCAL lnPlainTextLen
LOCAL loBdData
LOCAL loSbEnc

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

lcPassword = "tEst1234"
lcEncryptedHex_v1 = "0x010000001E8E7DCDBD4061B951999E25D18445D2305474D2D71EEE98A241C755246F58AB"

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

* For versions of Chilkat < 10.0.0, use CreateObject('Chilkat_9_5_0.StringBuilder')
loSbEncHex = CreateObject('Chilkat.StringBuilder')
loSbEncHex.Append(lcEncryptedHex_v1)

* If present, we don't want the leading "0x"
IF (loSbEncHex.StartsWith("0x",0) = 1) THEN
    loSbEncHex.RemoveCharsAt(0,2)
ENDIF

* For versions of Chilkat < 10.0.0, use CreateObject('Chilkat_9_5_0.Crypt2')
loCrypt = CreateObject('Chilkat.Crypt2')
loCrypt.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.
lnV1 = loSbEncHex.StartsWith("01",0)

lnIvLen = 0

IF (lnV1 = 1) THEN
    loCrypt.CryptAlgorithm = "3des"
    loCrypt.CipherMode = "cbc"
    loCrypt.KeyLength = 168
    lnIvLen = 8
    lcHashAlg = "sha1"
ELSE
    loCrypt.CryptAlgorithm = "aes"
    loCrypt.CipherMode = "cbc"
    loCrypt.KeyLength = 256
    lnIvLen = 16
    lcHashAlg = "sha256"
ENDIF

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

* Get the IV part of the sbEncHex, and also remove it from the StringBuilder.
lcIvHex = loSbEncHex.GetRange(0,lnIvLen * 2,1)
? "IV = " + lcIvHex
loCrypt.SetEncodedIV(lcIvHex,"hex")

* For versions of Chilkat < 10.0.0, use CreateObject('Chilkat_9_5_0.StringBuilder')
loSbPassword = CreateObject('Chilkat.StringBuilder')
loSbPassword.Append(lcPassword)
lcPwd_hash = loSbPassword.GetHash(lcHashAlg,"hex","utf-16")
* For versions of Chilkat < 10.0.0, use CreateObject('Chilkat_9_5_0.StringBuilder')
loSbKey = CreateObject('Chilkat.StringBuilder')
loSbKey.Append(lcPwd_hash)
IF (lnV1 = 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)
    loSbKey.Shorten(8)
ENDIF

? "crypt key: " + loSbKey.GetAsString()

loCrypt.SetEncodedKey(loSbKey.GetAsString(),"hex")

* Decrypt
* For versions of Chilkat < 10.0.0, use CreateObject('Chilkat_9_5_0.BinData')
loBd = CreateObject('Chilkat.BinData')
loBd.AppendEncoded(loSbEncHex.GetAsString(),"hex")
loCrypt.DecryptBd(loBd)

* 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...
loBd.RemoveChunk(0,8)
lcPlainText = loBd.GetString("utf-8")
? "decrypted plain text: " + lcPlainText

* 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

* For versions of Chilkat < 10.0.0, use CreateObject('Chilkat_9_5_0.Crypt2')
loEncryptor = CreateObject('Chilkat.Crypt2')
loEncryptor.EncodingMode = "hex"

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

* Generate a random 8-byte IV
* For versions of Chilkat < 10.0.0, use CreateObject('Chilkat_9_5_0.Prng')
loPrng = CreateObject('Chilkat.Prng')
lcIvHex = loPrng.GenRandom(8,"hex")
loEncryptor.SetEncodedIV(lcIvHex,"hex")

* The binary password is generated the same as above.
* We'll use the same password (and same binary password)
loEncryptor.SetEncodedKey(loSbKey.GetAsString(),"hex")

lnPlainTextLen = 8
lcPlainText = "ABCD1234"

* Encrypt the header + the plain-text.
* For versions of Chilkat < 10.0.0, use CreateObject('Chilkat_9_5_0.BinData')
loBdData = CreateObject('Chilkat.BinData')
loBdData.AppendEncoded("0DF0ADBA","hex")
loBdData.AppendEncoded("0000","hex")
loBdData.AppendInt2(lnPlainTextLen,1)
? "header: " + loBdData.GetEncoded("hex")
loBdData.AppendString(lcPlainText,"utf-8")
loEncryptor.EncryptBd(loBdData)

* Compose the result..
* For versions of Chilkat < 10.0.0, use CreateObject('Chilkat_9_5_0.StringBuilder')
loSbEnc = CreateObject('Chilkat.StringBuilder')
loSbEnc.Append("0x01000000")
loSbEnc.Append(lcIvHex)
loSbEnc.Append(loBdData.GetEncoded("hex"))

? "result: " + loSbEnc.GetAsString()

RELEASE loSbEncHex
RELEASE loCrypt
RELEASE loSbPassword
RELEASE loSbKey
RELEASE loBd
RELEASE loEncryptor
RELEASE loPrng
RELEASE loBdData
RELEASE loSbEnc