(SQL Server) Duplicate PHP's openssl_encrypt and openssl_random_pseudo_bytes

Demonstrates how to duplicate PHP's openssl_encrypt function. (https://www.php.net/manual/en/function.openssl-encrypt.php)

For more information, see https://www.php.net/manual/en/function.openssl-encrypt.php

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.

    -- Duplicates thw following PHP script:

    -- $text = "This is a test";
    -- $passphrase = "my password";
    -- $iv = openssl_random_pseudo_bytes(openssl_cipher_iv_length("AES-256-CBC"));
    -- $crypted = base64_encode($iv.openssl_encrypt($text, "AES-256-CBC", $passphrase, OPENSSL_RAW_DATA, $iv));
    -- echo $crypted;

    DECLARE @success int

    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

    DECLARE @text nvarchar(4000)
    SELECT @text = 'This is a test'
    DECLARE @passphrase nvarchar(4000)
    SELECT @passphrase = 'my password'

    -- AES is a block cipher.  The IV size for any block cipher is the size of the block, which is defined by the encryption algorithm. 
    -- For AES, the block size is always 16 bytes, regardless of key size (i.e. 128-bits, 192-bits, or 256-bits).
    -- Therefore, generate 16 random bytes for the IV.
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64'
    DECLARE @ivBase64 nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'GenRandomBytesENC', @ivBase64 OUT, 16


    PRINT 'Generated IV = ' + @ivBase64

    -- Because we're doing AES-256-CBC, the key length must be 256-bits (i.e. 32 bytes).
    -- Given that our passphrase is a us-ascii string that can be shorter or longer than 32-bytes, we need to 
    -- somehow transform the passphrase to a 32-byte secret key.  We need to know what openssl_encrypt does.
    -- Here's the answer from the openssl_encrypt documentation:
    -- 
    -- "If the passphrase is shorter than expected, it is silently padded with NUL characters; 
    -- if the passphrase is longer than expected, it is silently truncated."

    -- OK.... so let's pad or shorten to get a 32-byte key.
    DECLARE @bdKey int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.BinData', @bdKey OUT

    EXEC sp_OAMethod @bdKey, 'AppendString', @success OUT, @passphrase, 'utf-8'

    DECLARE @sz int
    EXEC sp_OAGetProperty @bdKey, 'NumBytes', @sz OUT
    IF @sz > 32
      BEGIN
        EXEC sp_OAMethod @bdKey, 'RemoveChunk', @success OUT, 32, @sz - 32
      END
    ELSE
      BEGIN
        EXEC sp_OAMethod @bdKey, 'Clear', @success OUT
        EXEC sp_OAMethod @bdKey, 'AppendPadded', @success OUT, @passphrase, 'utf-8', 0, 32
      END

    -- Setup for encryption.
    EXEC sp_OASetProperty @crypt, 'CryptAlgorithm', 'aes'
    EXEC sp_OASetProperty @crypt, 'KeyLength', 256
    EXEC sp_OAMethod @crypt, 'SetEncodedIV', NULL, @ivBase64, 'base64'
    EXEC sp_OAMethod @bdKey, 'GetEncoded', @sTmp0 OUT, 'base64'
    EXEC sp_OAMethod @crypt, 'SetEncodedKey', NULL, @sTmp0, 'base64'

    -- Encrypt and base64 encode.
    DECLARE @cipherText64 nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'EncryptStringENC', @cipherText64 OUT, @text

    -- The PHP code fragment above returns the base64 encoded bytes of the IV and the encrypted text.
    -- So let's do that..
    DECLARE @bd int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.BinData', @bd OUT

    EXEC sp_OAMethod @bd, 'AppendEncoded', @success OUT, @ivBase64, 'base64'
    EXEC sp_OAMethod @bd, 'AppendEncoded', @success OUT, @cipherText64, 'base64'
    DECLARE @result nvarchar(4000)
    EXEC sp_OAMethod @bd, 'GetEncoded', @result OUT, 'base64'


    PRINT 'result = ' + @result

    -- Sample output:
    -- dN0vS1O0cWi5BbLAAY+NTf7bs3S27xzPf11RkG47sjs=

    -- Now let's decrypt from the output...

    -- Setup for decryption.
    EXEC sp_OASetProperty @crypt, 'CryptAlgorithm', 'aes'
    EXEC sp_OASetProperty @crypt, 'KeyLength', 256
    EXEC sp_OAMethod @bdKey, 'GetEncoded', @sTmp0 OUT, 'base64'
    EXEC sp_OAMethod @crypt, 'SetEncodedKey', NULL, @sTmp0, 'base64'

    DECLARE @bdResult int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.BinData', @bdResult OUT

    EXEC sp_OAMethod @bdResult, 'AppendEncoded', @success OUT, @result, 'base64'
    EXEC sp_OAMethod @bdResult, 'GetEncodedChunk', @sTmp0 OUT, 0, 16, 'base64'
    EXEC sp_OAMethod @crypt, 'SetEncodedIV', NULL, @sTmp0, 'base64'

    -- Remove the IV (first 16 bytes) from the result.
    EXEC sp_OAMethod @bdResult, 'RemoveChunk', @success OUT, 0, 16
    EXEC sp_OAMethod @crypt, 'DecryptBd', @success OUT, @bdResult
    DECLARE @originalText nvarchar(4000)
    EXEC sp_OAMethod @bdResult, 'GetString', @originalText OUT, 'utf-8'


    PRINT 'original text = ' + @originalText

    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @bdKey
    EXEC @hr = sp_OADestroy @bd
    EXEC @hr = sp_OADestroy @bdResult


END
GO