SQL Server

Streaming Encryption by Encrypting in Chunks

See more Encryption Examples

Encrypt data in chunks. 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)
    -- 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.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.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