(SQL Server) Encrypting/decrypting a data stream.

This example demonstrates how to encrypt (using a symmetric encryption algorithm such as AES, ChaCha20, Blowfish, RC2, 3DES, etc) a large amount of data. The data is passed in chunks to one of the encrypt methods: EncryptBytes, EncryptString, EncryptBytesENC, or EncryptStringENC, etc. The FirstChunk and LastChunk properties are used to indicate whether a chunk is the first, middle, or last in a stream to be encrypted. By default, both FirstChunk and LastChunk equal 1 -- meaning that the data passed is the entire amount.

Note: You may feed the data in chunks that are smaller than the block size of the encryption algorithm (AES and Twofish have a block size of 16 bytes. DES, 3DES, Blowfish, and RC2 have a block size of 8 bytes. ARC4 and ChaCha20 are streaming encryption algorithms and don't have a block size, but technically you can think of it as having a block size of 1 byte.)

Chilkat will buffer the data as required. If an amount of data smaller than the block size is passed such that the entire amount is buffered, then an empty string (or 0 bytes) is returned. The buffered data will be added to the data passed in subsequent calls. When the final data chunk is passed, it is padded to the encryption algorithm's block size (according to the PaddingScheme property) and the encryption is complete.

Decrypting in chunks is identical to encrypting in chunks.

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
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @crypt int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Crypt2', @crypt OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @crypt, 'CryptAlgorithm', 'aes'
    EXEC sp_OASetProperty @crypt, 'CipherMode', 'cbc'
    EXEC sp_OASetProperty @crypt, 'KeyLength', 128

    EXEC sp_OAMethod @crypt, 'SetEncodedKey', NULL, '000102030405060708090A0B0C0D0E0F', 'hex'
    EXEC sp_OAMethod @crypt, 'SetEncodedIV', NULL, '000102030405060708090A0B0C0D0E0F', 'hex'

    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'hex'
    DECLARE @txt1 nvarchar(4000)
    SELECT @txt1 = 'The quick brown fox jumped over the lazy dog.' + CHAR(13) + CHAR(10)
    DECLARE @txt2 nvarchar(4000)
    SELECT @txt2 = '-' + CHAR(13) + CHAR(10)
    DECLARE @txt3 nvarchar(4000)
    SELECT @txt3 = 'Done.' + CHAR(13) + CHAR(10)

    DECLARE @sbEncrypted int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbEncrypted OUT

    -- Encrypt the 1st chunk:
    -- (don't worry about feeding the data to the encryptor in 
    -- exact multiples of the encryption algorithm's block size.
    -- Chilkat will buffer the data.)
    EXEC sp_OASetProperty @crypt, 'FirstChunk', 1
    EXEC sp_OASetProperty @crypt, 'LastChunk', 0
    DECLARE @success int
    EXEC sp_OAMethod @crypt, 'EncryptStringENC', @sTmp0 OUT, @txt1
    EXEC sp_OAMethod @sbEncrypted, 'Append', @success OUT, @sTmp0

    -- Encrypt the 2nd chunk
    EXEC sp_OASetProperty @crypt, 'FirstChunk', 0
    EXEC sp_OASetProperty @crypt, 'LastChunk', 0
    EXEC sp_OAMethod @crypt, 'EncryptStringENC', @sTmp0 OUT, @txt2
    EXEC sp_OAMethod @sbEncrypted, 'Append', @success OUT, @sTmp0

    -- Now encrypt N more chunks...
    -- Remember -- we're doing this in CBC mode, so each call
    -- to the encrypt method depends on the state from previous
    -- calls...
    EXEC sp_OASetProperty @crypt, 'FirstChunk', 0
    EXEC sp_OASetProperty @crypt, 'LastChunk', 0
    DECLARE @i int

    SELECT @i = 0
    WHILE @i <= 4
      BEGIN
        EXEC sp_OAMethod @crypt, 'EncryptStringENC', @sTmp0 OUT, @txt1
        EXEC sp_OAMethod @sbEncrypted, 'Append', @success OUT, @sTmp0
        EXEC sp_OAMethod @crypt, 'EncryptStringENC', @sTmp0 OUT, @txt2
        EXEC sp_OAMethod @sbEncrypted, 'Append', @success OUT, @sTmp0
        SELECT @i = @i + 1
      END

    -- Now encrypt the last chunk:
    EXEC sp_OASetProperty @crypt, 'FirstChunk', 0
    EXEC sp_OASetProperty @crypt, 'LastChunk', 1
    EXEC sp_OAMethod @crypt, 'EncryptStringENC', @sTmp0 OUT, @txt3
    EXEC sp_OAMethod @sbEncrypted, 'Append', @success OUT, @sTmp0

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

    -- Now decrypt in one call.
    -- (The data we're decrypting is both the first AND last chunk.)  
    EXEC sp_OASetProperty @crypt, 'FirstChunk', 1
    EXEC sp_OASetProperty @crypt, 'LastChunk', 1
    DECLARE @decryptedText nvarchar(4000)
    EXEC sp_OAMethod @sbEncrypted, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @crypt, 'DecryptStringENC', @decryptedText OUT, @sTmp0


    PRINT @decryptedText

    -- Note: You may decrypt in N chunks by setting the FirstChunk
    -- and LastChunk properties prior to calling the Decrypt* methods

    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @sbEncrypted


END
GO