Match MySQL AES_ENCRYPT Function

Demonstrates how to use the MySqlAesEncrypt and MySqlAesDecrypt methods to match MySQL's AES_ENCRYPT and AES_DECRYPT functions.

Here are MySQL AES_ENCRYPT test vectors:

HEX(AES_ENCRYPT('The quick brown fox jumps over the lazy dog','password'))
Output:
CC5FDDF621AE2F48241BB80EDF2422949526FBAAA74885ACB020A74CAAB98BCFA1DD12E5D4C7922A2F9205D367921D9B

HEX(AES_ENCRYPT('The quick brown fox jumps over the lazy dog','a'))
Output:
47A7E5FB591AD818BA3C9025D040514696BF50C2EFB0A453730E23E824F5F0357F3F673FCF0A7BD16465E82F937ED365

HEX(AES_ENCRYPT('The quick brown fox jumps over the lazy dog','1234567890123456'))
Output:
D70F1A158553B721C5DB9220A627E7B17BFF78FF7A72C4AB565AC903A94DF76DB75A30583D4ED01E265147D5A9B04EC3

HEX(AES_ENCRYPT('The quick brown fox jumps over the lazy dog','abcdefghijklmnopqrstuvwxyz'))
Output:
425F259AD4C3DD0B37831C8F24507D7E1F056437C0D50D3C18FB6F600FC941A8D871E7DD7BCBEE4CA4698F074C135275

Download Chilkat Crypt ActiveX

CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @crypt int
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int

    EXEC sp_OAMethod @crypt, 'UnlockComponent', @success OUT, 'Anything for 30-day trial'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        RETURN
      END

    DECLARE @clearText nvarchar(4000)

    SELECT @clearText = 'The quick brown fox jumps over the lazy dog'
    DECLARE @password nvarchar(4000)

    DECLARE @hexEncryptedStr nvarchar(4000)

    DECLARE @decryptedStr nvarchar(4000)

    SELECT @password = 'password'
    EXEC sp_OAMethod @crypt, 'MySqlAesEncrypt', @hexEncryptedStr OUT, @clearText, @password

    PRINT @hexEncryptedStr
    EXEC sp_OAMethod @crypt, 'MySqlAesDecrypt', @decryptedStr OUT, @hexEncryptedStr, @password

    PRINT @decryptedStr

    SELECT @password = 'a'
    EXEC sp_OAMethod @crypt, 'MySqlAesEncrypt', @hexEncryptedStr OUT, @clearText, @password

    PRINT @hexEncryptedStr
    EXEC sp_OAMethod @crypt, 'MySqlAesDecrypt', @decryptedStr OUT, @hexEncryptedStr, @password

    PRINT @decryptedStr

    SELECT @password = '1234567890123456'
    EXEC sp_OAMethod @crypt, 'MySqlAesEncrypt', @hexEncryptedStr OUT, @clearText, @password

    PRINT @hexEncryptedStr
    EXEC sp_OAMethod @crypt, 'MySqlAesDecrypt', @decryptedStr OUT, @hexEncryptedStr, @password

    PRINT @decryptedStr

    SELECT @password = 'abcdefghijklmnopqrstuvwxyz'
    EXEC sp_OAMethod @crypt, 'MySqlAesEncrypt', @hexEncryptedStr OUT, @clearText, @password

    PRINT @hexEncryptedStr
    EXEC sp_OAMethod @crypt, 'MySqlAesDecrypt', @decryptedStr OUT, @hexEncryptedStr, @password

    PRINT @decryptedStr

END
GO