Lua

Duplicate SQL Server ENCRYPTBYPASSPHRASE

See more Encryption Examples

Demonstrates how to duplicate SQL Server's ENCRYPTBYPASSPHRASE. Lua

local chilkat = require("chilkat")

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

local password = "tEst1234"
local encryptedHex_v1 = "0x010000001E8E7DCDBD4061B951999E25D18445D2305474D2D71EEE98A241C755246F58AB"

-- Here's an encrypted string using AES256/SHA256
local encryptedHex_v2 = "0x02000000FFE880C0354780481E64EF25B6197A02E2A854A4BA9D8D9BDDFDAB27EB56537ABDA0B1D9C4D1050C91B313550DECF429"

local sbEncHex = chilkat.StringBuilder.new()
sbEncHex:append(encryptedHex_v1)

-- If present, we don't want the leading "0x"
if sbEncHex:starts_with("0x", false) then
    sbEncHex:remove_chars_at(0, 2)
end

local crypt = chilkat.Crypt2.new()
crypt.encoding_mode = "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.
local v1 = sbEncHex:starts_with("01", false)

local ivLen = 0
local hashAlg = ""

if v1 then
    crypt.crypt_algorithm = "3des"
    crypt.cipher_mode = "cbc"
    crypt.key_length = 168
    ivLen = 8
    hashAlg = "sha1"
else
    crypt.crypt_algorithm = "aes"
    crypt.cipher_mode = "cbc"
    crypt.key_length = 256
    ivLen = 16
    hashAlg = "sha256"
end

-- Remove the SQL Server version info (i.e. the "01000000")
sbEncHex:remove_chars_at(0, 8)

-- Get the IV part of the sbEncHex, and also remove it from the StringBuilder.
local ivHex = sbEncHex:get_range(0, ivLen * 2, true)
print("IV = " .. ivHex)
crypt:set_encoded_iv(ivHex, "hex")

local sbPassword = chilkat.StringBuilder.new()
sbPassword:append(password)
local pwd_hash = sbPassword:get_hash(hashAlg, "hex", "utf-16")
local sbKey = chilkat.StringBuilder.new()
sbKey:append(pwd_hash)
if v1 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)
    sbKey:shorten(8)
end

print("crypt key: " .. sbKey:get_as_string())

crypt:set_encoded_key(sbKey:get_as_string(), "hex")

-- Decrypt
local bd = chilkat.BinData.new()
bd:append_encoded(sbEncHex:get_as_string(), "hex")
crypt:decrypt_bd(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...
bd:remove_chunk(0, 8)
local plainText = bd:get_string("utf-8")
print("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

local encryptor = chilkat.Crypt2.new()
encryptor.encoding_mode = "hex"

encryptor.crypt_algorithm = "3des"
encryptor.cipher_mode = "cbc"
encryptor.key_length = 168

-- Generate a random 8-byte IV
local prng = chilkat.Prng.new()
ivHex = prng:gen_random(8, "hex")
encryptor:set_encoded_iv(ivHex, "hex")

-- The binary password is generated the same as above.
-- We'll use the same password (and same binary password)
encryptor:set_encoded_key(sbKey:get_as_string(), "hex")

local plainTextLen = 8
plainText = "ABCD1234"

-- Encrypt the header + the plain-text.
local bdData = chilkat.BinData.new()
bdData:append_encoded("0DF0ADBA", "hex")
bdData:append_encoded("0000", "hex")
bdData:append_int2(plainTextLen, true)
print("header: " .. bdData:get_encoded("hex"))
bdData:append_string(plainText, "utf-8")
encryptor:encrypt_bd(bdData)

-- Compose the result..
local sbEnc = chilkat.StringBuilder.new()
sbEnc:append("0x01000000")
sbEnc:append(ivHex)
sbEnc:append(bdData:get_encoded("hex"))

print("result: " .. sbEnc:get_as_string())