(SQL Server) AES Encrypt String (utf-8 byte representation) and return Base64

Demonstrates how to AES encrypt the utf-8 byte representation of a string and return in base64 format.

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
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @crypt int
    -- Use "Chilkat_9_5_0.Crypt2" for versions of Chilkat < 10.0.0
    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', 256
    EXEC sp_OASetProperty @crypt, 'Charset', 'utf-8'
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64'

    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'

    DECLARE @encryptedStr nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'EncryptStringENC', @encryptedStr OUT, 'This is the original string'

    PRINT @encryptedStr

    -- Let's say we want to URL encode the base64 string..
    EXEC sp_OASetProperty @crypt, 'CryptAlgorithm', 'none'
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'url'
    DECLARE @urlEncoded nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'EncryptStringENC', @urlEncoded OUT, @encryptedStr

    PRINT @urlEncoded

    -- Sample output:

    --  q3fmgEqjqa9o//ZS6aPuh4Wtbrrxx/WOIQSKeVnesZg=
    --  q3fmgEqjqa9o%2F%2FZS6aPuh4Wtbrrxx%2FWOIQSKeVnesZg%3D

    -- -------------------------------------------------------------------
    -- Now let's do the reverse...
    -- 

    -- We start with a string that is the binary encrypted data, base64 encoded, and then URL encoded.
    DECLARE @encBase64Url nvarchar(4000)
    SELECT @encBase64Url = 'q3fmgEqjqa9o%2F%2FZS6aPuh4Wtbrrxx%2FWOIQSKeVnesZg%3D'

    -- We'll use the same crypt object.  If using a new instance of the crypt object,
    -- make sure *all* settings are identical:  the algorithm, cipher mode, key length, charset, encoding mode, IV, and secret key.

    -- Decode from URL...
    EXEC sp_OASetProperty @crypt, 'CryptAlgorithm', 'none'
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'url'
    DECLARE @encBase64 nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'DecryptStringENC', @encBase64 OUT, @urlEncoded

    PRINT @encBase64

    -- Now decrypt...
    EXEC sp_OASetProperty @crypt, 'CryptAlgorithm', 'aes'
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64'
    DECLARE @originalStr nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'DecryptStringENC', @originalStr OUT, @encBase64

    PRINT @originalStr

    EXEC @hr = sp_OADestroy @crypt


END
GO