PureBasic
PureBasic
Duplicate SQL Server ENCRYPTBYPASSPHRASE
See more Encryption Examples
Demonstrates how to duplicate SQL Server's ENCRYPTBYPASSPHRASE.Chilkat PureBasic Downloads
IncludeFile "CkBinData.pb"
IncludeFile "CkStringBuilder.pb"
IncludeFile "CkPrng.pb"
IncludeFile "CkCrypt2.pb"
Procedure ChilkatExample()
; 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.
password.s = "tEst1234"
encryptedHex_v1.s = "0x010000001E8E7DCDBD4061B951999E25D18445D2305474D2D71EEE98A241C755246F58AB"
; Here's an encrypted string using AES256/SHA256
encryptedHex_v2.s = "0x02000000FFE880C0354780481E64EF25B6197A02E2A854A4BA9D8D9BDDFDAB27EB56537ABDA0B1D9C4D1050C91B313550DECF429"
sbEncHex.i = CkStringBuilder::ckCreate()
If sbEncHex.i = 0
Debug "Failed to create object."
ProcedureReturn
EndIf
CkStringBuilder::ckAppend(sbEncHex,encryptedHex_v1)
; If present, we don't want the leading "0x"
If CkStringBuilder::ckStartsWith(sbEncHex,"0x",0) = 1
CkStringBuilder::ckRemoveCharsAt(sbEncHex,0,2)
EndIf
crypt.i = CkCrypt2::ckCreate()
If crypt.i = 0
Debug "Failed to create object."
ProcedureReturn
EndIf
CkCrypt2::setCkEncodingMode(crypt, "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.
v1.i = CkStringBuilder::ckStartsWith(sbEncHex,"01",0)
ivLen.i = 0
hashAlg.s
If v1 = 1
CkCrypt2::setCkCryptAlgorithm(crypt, "3des")
CkCrypt2::setCkCipherMode(crypt, "cbc")
CkCrypt2::setCkKeyLength(crypt, 168)
ivLen = 8
hashAlg = "sha1"
Else
CkCrypt2::setCkCryptAlgorithm(crypt, "aes")
CkCrypt2::setCkCipherMode(crypt, "cbc")
CkCrypt2::setCkKeyLength(crypt, 256)
ivLen = 16
hashAlg = "sha256"
EndIf
; Remove the SQL Server version info (i.e. the "01000000")
CkStringBuilder::ckRemoveCharsAt(sbEncHex,0,8)
; Get the IV part of the sbEncHex, and also remove it from the StringBuilder.
ivHex.s = CkStringBuilder::ckGetRange(sbEncHex,0,ivLen * 2,1)
Debug "IV = " + ivHex
CkCrypt2::ckSetEncodedIV(crypt,ivHex,"hex")
sbPassword.i = CkStringBuilder::ckCreate()
If sbPassword.i = 0
Debug "Failed to create object."
ProcedureReturn
EndIf
CkStringBuilder::ckAppend(sbPassword,password)
pwd_hash.s = CkStringBuilder::ckGetHash(sbPassword,hashAlg,"hex","utf-16")
sbKey.i = CkStringBuilder::ckCreate()
If sbKey.i = 0
Debug "Failed to create object."
ProcedureReturn
EndIf
CkStringBuilder::ckAppend(sbKey,pwd_hash)
If v1 = 1
; 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)
CkStringBuilder::ckShorten(sbKey,8)
EndIf
Debug "crypt key: " + CkStringBuilder::ckGetAsString(sbKey)
CkCrypt2::ckSetEncodedKey(crypt,CkStringBuilder::ckGetAsString(sbKey),"hex")
; Decrypt
bd.i = CkBinData::ckCreate()
If bd.i = 0
Debug "Failed to create object."
ProcedureReturn
EndIf
CkBinData::ckAppendEncoded(bd,CkStringBuilder::ckGetAsString(sbEncHex),"hex")
CkCrypt2::ckDecryptBd(crypt,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...
CkBinData::ckRemoveChunk(bd,0,8)
plainText.s = CkBinData::ckGetString(bd,"utf-8")
Debug "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
encryptor.i = CkCrypt2::ckCreate()
If encryptor.i = 0
Debug "Failed to create object."
ProcedureReturn
EndIf
CkCrypt2::setCkEncodingMode(encryptor, "hex")
CkCrypt2::setCkCryptAlgorithm(encryptor, "3des")
CkCrypt2::setCkCipherMode(encryptor, "cbc")
CkCrypt2::setCkKeyLength(encryptor, 168)
; Generate a random 8-byte IV
prng.i = CkPrng::ckCreate()
If prng.i = 0
Debug "Failed to create object."
ProcedureReturn
EndIf
ivHex = CkPrng::ckGenRandom(prng,8,"hex")
CkCrypt2::ckSetEncodedIV(encryptor,ivHex,"hex")
; The binary password is generated the same as above.
; We'll use the same password (and same binary password)
CkCrypt2::ckSetEncodedKey(encryptor,CkStringBuilder::ckGetAsString(sbKey),"hex")
plainTextLen.i = 8
plainText = "ABCD1234"
; Encrypt the header + the plain-text.
bdData.i = CkBinData::ckCreate()
If bdData.i = 0
Debug "Failed to create object."
ProcedureReturn
EndIf
CkBinData::ckAppendEncoded(bdData,"0DF0ADBA","hex")
CkBinData::ckAppendEncoded(bdData,"0000","hex")
CkBinData::ckAppendInt2(bdData,plainTextLen,1)
Debug "header: " + CkBinData::ckGetEncoded(bdData,"hex")
CkBinData::ckAppendString(bdData,plainText,"utf-8")
CkCrypt2::ckEncryptBd(encryptor,bdData)
; Compose the result..
sbEnc.i = CkStringBuilder::ckCreate()
If sbEnc.i = 0
Debug "Failed to create object."
ProcedureReturn
EndIf
CkStringBuilder::ckAppend(sbEnc,"0x01000000")
CkStringBuilder::ckAppend(sbEnc,ivHex)
CkStringBuilder::ckAppend(sbEnc,CkBinData::ckGetEncoded(bdData,"hex"))
Debug "result: " + CkStringBuilder::ckGetAsString(sbEnc)
CkStringBuilder::ckDispose(sbEncHex)
CkCrypt2::ckDispose(crypt)
CkStringBuilder::ckDispose(sbPassword)
CkStringBuilder::ckDispose(sbKey)
CkBinData::ckDispose(bd)
CkCrypt2::ckDispose(encryptor)
CkPrng::ckDispose(prng)
CkBinData::ckDispose(bdData)
CkStringBuilder::ckDispose(sbEnc)
ProcedureReturn
EndProcedure