dbms_crypto and a Powershell

Question

Can someone help me with code to encrypt a string in Oracle, and decrypt using powershell? AES, DES, either one will do. I feel like I'm close but am missing something...

See also this post: Convert encryption / decryption function in PowerShell to PHP (openssl_)

Using that Powershell encryption I can turn "Its a secret" into "AEe2LSdmwi79UYduDZS7Mg=". The below decrypts that back. But how to do this encrypt in Oracle?

function DecryptDES
    {
    Param(
        [String] $encrypted,
        [byte[]] $Key,
        [byte[]] $Iv
    )
        [byte[]]$NewStr = [System.Convert]::FromBase64String($encrypted)
        $tdsAlg = New-Object System.Security.Cryptography.DESCryptoServiceProvider
        $tdsAlg.Key = $Key
        $tdsAlg.IV = $Iv
        $encrypt = $tdsAlg.CreateDecryptor($tdsAlg.Key, $tdsAlg.IV)
        $msEncrypt = New-Object System.IO.MemoryStream @(,$NewStr)
        $csEncrypt = New-Object System.Security.Cryptography.CryptoStream $msEncrypt, $encrypt, "Read"
        $swEncrypt = New-Object System.IO.StreamReader $csEncrypt
        [String]$result = $swEncrypt.ReadToEnd()
        $swEncrypt.Close()
        $csEncrypt.Close()
        $msEncrypt.Close()
        $encrypt.Clear()
    
        return $result;     
    }
    $enc = [system.Text.Encoding]::UTF8
    $string1 = "PeShVmYq" 
    $data1 = $enc.GetBytes($string1) 
    
    $dec = DecryptDES -encrypted 'AEe2LSdmwi79UYduDZS7Mg==' -Key $data1 -Iv $data1
    Write-Host $dec

So far I have this Oracle SQL:

select           
       dbms_crypto.Encrypt(
       src => UTL_RAW.CAST_TO_RAW('Its a secret'),
       typ => 4353,
       key => UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW('PeShVmYq')),
       iv => UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW('PeShVmYq')))
from dual

Which outputs:

F480D03A9564CAAD0CD815EA1524B6B7

Answer 1

You need to keep the key and IV as RAW, not base64-encode those; then base64 encode the result of the encrypt call; and finally convert that RAW back to a string:

select
       utl_raw.cast_to_varchar2(
         utl_encode.base64_encode(
           dbms_crypto.encrypt(
             src => utl_raw.cast_to_raw('Its a secret'),
             typ => 4353,
             key => utl_raw.cast_to_raw('PeShVmYq'),
             iv => utl_raw.cast_to_raw('PeShVmYq')
           )
         )
       ) as encrypted
from dual;

ENCRYPTED                                                       
----------------------------------------------------------------
AEe2LSdmwi79UYduDZS7Mg==

As you've specified UTF-8 in your PowerShell script, and just generally really, it would probably be better to do RAW/string conversion with a specific character set too:

select
       utl_i18n.raw_to_char(
         utl_encode.base64_encode(
           dbms_crypto.encrypt(
             src => utl_i18n.string_to_raw(data => 'Its a secret', dst_charset => 'AL32UTF8'),
             typ => 4353,
             key => utl_i18n.string_to_raw(data => 'PeShVmYq', dst_charset => 'AL32UTF8'),
             iv => utl_i18n.string_to_raw(data => 'PeShVmYq', dst_charset => 'AL32UTF8')
           )
         ),
         src_charset => 'AL32UTF8'
       ) as encrypted
from dual;

which gets the same result.

---

Just for reference, the typ value 4353 (0x1101) comes from:

dbms_crypto.encrypt_des + dbms_crypto.chain_cbc + dbms_crypto.pad_pkcs5

The AES256 equivalent would be 4360; but you'd also need a longer key.