(SQL Server) AES and CHACHA20 Encrypt/Decrypt Text

Demonstrates the use of the new EncryptSb and DecryptSb methods introduced in Chilkat v9.5.0.67 to encrypt/decrypt the contents of StringBuilder objects.

Note: This example requires Chilkat v9.5.0.67 or greater.

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

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @success int

    -- First build a string to be encrypted
    DECLARE @sb int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sb OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @i int
    SELECT @i = 1
    WHILE @i < 10
      BEGIN
        EXEC sp_OAMethod @sb, 'AppendInt', @success OUT, @i
        EXEC sp_OAMethod @sb, 'Append', @success OUT, ' the quick brown fox jumped over the lazy dog.' + CHAR(13) + CHAR(10)
        SELECT @i = @i + 1
      END

    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    -- The string to be encrypted looks like this:

    -- 1 the quick brown fox jumped over the lazy dog.
    -- 2 the quick brown fox jumped over the lazy dog.
    -- 3 the quick brown fox jumped over the lazy dog.
    -- 4 the quick brown fox jumped over the lazy dog.
    -- 5 the quick brown fox jumped over the lazy dog.
    -- 6 the quick brown fox jumped over the lazy dog.
    -- ...

    DECLARE @crypt int
    -- Use "Chilkat_9_5_0.Crypt2" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT

    -- Specify the encryption to be used.
    -- First we'll do AES-128 CBC
    EXEC sp_OASetProperty @crypt, 'CryptAlgorithm', 'aes'
    EXEC sp_OASetProperty @crypt, 'CipherMode', 'cbc'
    EXEC sp_OASetProperty @crypt, 'KeyLength', 128

    DECLARE @ivHex nvarchar(4000)
    SELECT @ivHex = '000102030405060708090A0B0C0D0E0F'
    EXEC sp_OAMethod @crypt, 'SetEncodedIV', NULL, @ivHex, 'hex'

    DECLARE @keyHex nvarchar(4000)
    SELECT @keyHex = '000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F'
    EXEC sp_OAMethod @crypt, 'SetEncodedKey', NULL, @keyHex, 'hex'

    -- When EncryptSb is called, the contents of the source (sb)
    -- remains unmodified.  The encrypted bytes are written into bdEncrypted.
    -- If bdEncrypted already contained data, it is replaced with the encrypted bytes.
    DECLARE @bdEncrypted int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdEncrypted OUT

    EXEC sp_OAMethod @crypt, 'EncryptSb', @success OUT, @sb, @bdEncrypted
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sb
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @bdEncrypted
        RETURN
      END

    -- Examine the encrypted bytes:
    EXEC sp_OAMethod @bdEncrypted, 'GetEncoded', @sTmp0 OUT, 'base64_mime'
    PRINT @sTmp0

    -- Sample encrypted data:

    -- 0DdNZ22ckMMJBiaKhAu3wUEfh16XW356NIUsDmNs/xwbHe/p1201OmpfzwXwKktkAefu2pckrBgC
    -- df+1w8lRo+KAy5n5wlAgMGM/UrsVJsp0BmDPk1vaxKrmrGpSXOVCQs1n2+0atIs5YLiOG+Va3+Mi
    -- EQNb4YK7bNMmvt0++irBxTiGnkx/RncfKwgkbBUpl2x7yV13MW6lapDT6Md0DKAMsTXFJYGeIdEf
    -- g2uxDDQzI5gUOUHTMrXQ8paD/K76KKB9Jpp/kAM9z8g/d8KUmuphA7KI64d38xsgOmcITlbhlCQ2
    -- PDkcU6RRzX0FUTUSMgQukhy0jkLZEjHX9poKJD+iJTOkcQUC3OqR9hKhSrvIgJN4lxdR71MheOoQ
    -- 2wmvRdq+agTWWh333Vmb6J6yDV79aSpnqEDrA8Ks7Xzciol0gve91+JtVJlJKjWwEzWEU8GxF7Q8
    -- eaWI70lsC5nTLGcbqgKu6gzkzHlHyHaE2FAQA/d5I2dvfsAYUQCza0Zdyw8mmTtHhlP2Tfxj1uPv
    -- H4Q7BGuKnx3SWT2CnpbX4091w7KzLAztrbFBo/Tf9w8ZpgTK9k1ryfW9/xnk6rW6iQ==

    -- Decrypt to restore back to the original:
    DECLARE @sbOriginal int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbOriginal OUT

    EXEC sp_OAMethod @crypt, 'DecryptSb', @success OUT, @bdEncrypted, @sbOriginal
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sb
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @bdEncrypted
        EXEC @hr = sp_OADestroy @sbOriginal
        RETURN
      END

    -- Examine the original data:
    EXEC sp_OAMethod @sbOriginal, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    -- ----------------------------------------------------------------------------------
    -- To do chacha20 encryption, just change the settings:

    EXEC sp_OASetProperty @crypt, 'CryptAlgorithm', 'chacha20'
    EXEC sp_OASetProperty @crypt, 'KeyLength', 256
    -- The initial count is the initial block counter for the chacha20 algorithm.
    -- It can be any integer, but must be set to the same when decrypting.
    EXEC sp_OASetProperty @crypt, 'InitialCount', 22

    -- EncryptSb completely replaces the contents of bdEncrypted.
    EXEC sp_OAMethod @crypt, 'EncryptSb', @success OUT, @sb, @bdEncrypted

    -- However.. DecryptSb appends the decrypted text to whatever may
    -- have already existed within sbOriginal.
    EXEC sp_OAMethod @crypt, 'DecryptSb', @success OUT, @bdEncrypted, @sbOriginal


    PRINT '----'

    PRINT 'The original data should be here twice.  Once from the AES decrypt, and again from the chacha20 decrypt.'
    EXEC sp_OAMethod @sbOriginal, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    PRINT 'Success.'

    EXEC @hr = sp_OADestroy @sb
    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @bdEncrypted
    EXEC @hr = sp_OADestroy @sbOriginal


END
GO