(PureBasic) Duplicate SQL Server ENCRYPTBYPASSPHRASE

See more Encryption Examples

Demonstrates how to duplicate SQL Server's ENCRYPTBYPASSPHRASE.

Chilkat PureBasic Module Download Chilkat PureBasic Module

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