SQL Server

Example for both AES-128 and ChaCha20 to Encrypt Binary Data

See more Encryption Examples

Demonstrates the use of the new EncryptBd and DecryptBd methods introduced in Chilkat v9.5.0.67 to encrypt/decrypt binary bytes.

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

SQL Server

-- 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)
    DECLARE @success int
    SELECT @success = 0

    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- Load a small JPG file to be encrypted/decrypted.
    DECLARE @jpgBytes int
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @jpgBytes OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @jpgBytes, 'LoadFile', @success OUT, 'qa_data/jpg/starfish.jpg'
    IF @success <> 1
      BEGIN

        PRINT 'Failed to load JPG file.'
        EXEC @hr = sp_OADestroy @jpgBytes
        RETURN
      END

    -- Show the unencrypted JPG bytes in Base64 format.
    -- (The "base64_mime" encoding was added in Chilkat v9.5.0.67.
    -- The "base64" encoding emits a single line of base64, whereas
    -- "base64_mime" will emit multi-line base64 as it would appear
    -- in MIME.)
    EXEC sp_OAMethod @jpgBytes, 'GetEncoded', @sTmp0 OUT, 'base64_mime'
    PRINT @sTmp0

    -- Sample base64_mime JPG data:

    -- 	/9j/4AAQSkZJRgABAgEASABIAAD/7Q18UGhvdG9zaG9wIDMuMAA4QklNA+0AAAAAABAASAAAAAEA
    -- 	AQBIAAAAAQABOEJJTQPzAAAAAAAIAAAAAAAAAAE4QklNBAoAAAAAAAEAADhCSU0nEAAAAAAACgAB
    -- 	AAAAAAAAAAI4QklNA/UAAAAAAEgAL2ZmAAEAbGZmAAYAAAAAAAEAL2ZmAAEAoZmaAAYAAAAAAAEA
    -- 	MgAAAAEAWgAAAAYAAAAAAAEANQAAAAEALQAAAAYAAAAAAAE4QklNBBQAAAAAAAQAAAABOEJJTQQM
    -- 	...

    DECLARE @crypt int
    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'

    -- Do the in-place 128-bit AES CBC encryption.
    -- The contents of jpgBytes are replaced with the encrypted bytes.
    EXEC sp_OAMethod @crypt, 'EncryptBd', @success OUT, @jpgBytes
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @jpgBytes
        EXEC @hr = sp_OADestroy @crypt
        RETURN
      END

    -- Examine the JPG bytes again.  The bytes should be different because they are encrypted:
    EXEC sp_OAMethod @jpgBytes, 'GetEncoded', @sTmp0 OUT, 'base64_mime'
    PRINT @sTmp0

    -- Sample base64_mime encrypted JPG data:

    -- 	sbz0babt1WCkQf5xKMdg/baZAcUBO5GVUUDF2BjVqmd+HrqKN+t6hAcqakL/bdo0q9hYmow0Tp1e
    -- 	AQ9V9DOiifQUZqWVkR+kL/c45bq8JGFDvgNl0djPt+yYhV789IB/fPH0upx+/ad++WNOlv1IxGMr
    -- 	Y1x1oERU/IsiEzafUJdI4kZ6FQo2IPGMF/Rm1h79I7hP1yYUFxvJyz+PzaySAUH1nLsNHyDVY5VY
    -- 	O90aH3steRSYbz8C8UF9wQ3qqEIXQNnnixvoNDnmHyY39VoVBI5F6rnPwYDfAk2t8tmuryFqvwAu
    -- 	...

    -- Decrypt to restore back to the original:
    EXEC sp_OAMethod @crypt, 'DecryptBd', @success OUT, @jpgBytes
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @jpgBytes
        EXEC @hr = sp_OADestroy @crypt
        RETURN
      END

    EXEC sp_OAMethod @jpgBytes, 'GetEncoded', @sTmp0 OUT, 'base64_mime'
    PRINT @sTmp0

    -- 	/9j/4AAQSkZJRgABAgEASABIAAD/7Q18UGhvdG9zaG9wIDMuMAA4QklNA+0AAAAAABAASAAAAAEA
    -- 	AQBIAAAAAQABOEJJTQPzAAAAAAAIAAAAAAAAAAE4QklNBAoAAAAAAAEAADhCSU0nEAAAAAAACgAB
    -- 	AAAAAAAAAAI4QklNA/UAAAAAAEgAL2ZmAAEAbGZmAAYAAAAAAAEAL2ZmAAEAoZmaAAYAAAAAAAEA
    -- 	MgAAAAEAWgAAAAYAAAAAAAEANQAAAAEALQAAAAYAAAAAAAE4QklNBBQAAAAAAAQAAAABOEJJTQQM
    -- 	...

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

    EXEC sp_OAMethod @crypt, 'EncryptBd', @success OUT, @jpgBytes
    -- jpgBytes now contains chacha20 encrypted bytes.

    EXEC sp_OAMethod @crypt, 'DecryptBd', @success OUT, @jpgBytes
    -- jpgBytes is now restored back to the original unencrypted by bytes.

    -- Save the bytes to a file..
    EXEC sp_OAMethod @jpgBytes, 'WriteFile', @success OUT, 'qa_output/starfish.jpg'


    PRINT 'Success.'

    EXEC @hr = sp_OADestroy @jpgBytes
    EXEC @hr = sp_OADestroy @crypt


END
GO