How to decrypt a string in PL/SQL that is encrypted using PHP?

Question

I am new to encryption and decryption techniques. I have a requirement to decrypt the response in PL/SQL received from the PHP server. The encryption method is AES128 CBC.

PHP Code

$clear_text = "Secret Message";
$str        = "0962774221568619";
$key        = "0962774221568619";

$iv = str_pad($iv, 16, "\0");
$encrypt_text = openssl_encrypt($clear_text, "AES-128-CBC", $key, OPENSSL_RAW_DATA, $iv);
$data = base64_encode($encrypt_text);

echo $data;

The encrypted string from the above code is: 87UMyOAog3rlmzorneakjA==

Now I want to decrypt this string in PL/SQL. Before decrypting, first I tried to encrypt the same message by replicating PHP code in PL/SQL as below.

Encryption using PL/SQL

DECLARE
   input_string       VARCHAR2 (200) :=  'Secret Message';
   output_string      VARCHAR2 (200);
   encrypted_raw      RAW (2000);             -- stores encrypted binary text
   decrypted_raw      RAW (2000);             -- stores decrypted binary text
   encrypted_string   VARCHAR2(1000);        
   num_key_bytes      NUMBER := 128/8;        -- key length 256 bits (32 bytes)
   key_bytes_raw      RAW (32);               -- stores 256-bit encryption key
   encryption_type    PLS_INTEGER :=          -- total encryption type
                            DBMS_CRYPTO.ENCRYPT_AES128
                          + DBMS_CRYPTO.CHAIN_CBC
                          + DBMS_CRYPTO.PAD_PKCS5;

   iv_raw             RAW (16);

BEGIN
   key_bytes_raw := UTL_I18N.STRING_TO_RAW ('0962774221568619',  'UTF8');
   iv_raw        := UTL_I18N.STRING_TO_RAW ('0962774221568619',  'UTF8');
   
   encrypted_raw := DBMS_CRYPTO.ENCRYPT
      (
         src => UTL_I18N.STRING_TO_RAW (input_string,  'UTF8'),
         typ => encryption_type,
         key => key_bytes_raw,
         iv  => iv_raw
      );

    DBMS_OUTPUT.PUT_LINE ('encrypted_raw: ' || encrypted_raw);    
    
    encrypted_string:= utl_raw.cast_to_varchar2(UTL_ENCODE.BASE64_ENCODE(encrypted_raw));
    
    DBMS_OUTPUT.PUT_LINE ('encrypted_string: ' || encrypted_string);
END;

Output

encrypted_raw: 0C36016A23FE45D8C62C50615336E5C6

encrypted_string: DDYBaiP+RdjGLFBhUzblxg=

Here I got different encrypted strings from PHP and PL/SQL. I am not sure but seems like the PHP code replication in PL/SQL is not correct to me.

Using the below code, I am able to decrypt the string encrypted from PL/SQL.

DECLARE
   output_string      VARCHAR2 (200);
   encrypted_raw      RAW (2000);             -- stores encrypted binary text
   decrypted_raw      RAW (2000);             -- stores decrypted binary text
   num_key_bytes      NUMBER := 128/8;        -- key length 256 bits (32 bytes)
   key_bytes_raw      RAW (32);               -- stores 256-bit encryption key
   encryption_type    PLS_INTEGER :=          -- total encryption type
                            DBMS_CRYPTO.ENCRYPT_AES128
                          + DBMS_CRYPTO.CHAIN_CBC
                          + DBMS_CRYPTO.PAD_PKCS5;

   iv_raw             RAW (16);

BEGIN
   encrypted_raw := '0C36016A23FE45D8C62C50615336E5C6'; 
   key_bytes_raw := UTL_I18N.STRING_TO_RAW ('0962774221568619',  'UTF8');
   iv_raw        := UTL_I18N.STRING_TO_RAW ('0962774221568619',  'UTF8');
   
     decrypted_raw := DBMS_CRYPTO.DECRYPT
      (
         src => encrypted_raw,
         typ => encryption_type,
         key => key_bytes_raw,
         iv  => iv_raw
      );

   output_string := UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL32UTF8');

   DBMS_OUTPUT.PUT_LINE ('Decrypted string: ' || output_string);
END;

Output: Secret Message

This is fine but I am facing challenges while decrypting the string encrypted from PHP.

Update

I forgot to assign a value of IV in the PHP code that I used to encrypt the string. Thanks to @topaco and @BartoszOlchowik for pointing out this mistake.

Updated code

PHP

$clear_text = "Secret Message";
$iv         = "0962774221568619";
$key        = "0962774221568619";

$iv = str_pad($iv, 16, "\0");
$encrypt_text = openssl_encrypt($clear_text, "AES-128-CBC", $key, OPENSSL_RAW_DATA, $iv);
$data = base64_encode($encrypt_text);

echo $data;

Encrypted String: DDYBaiP+RdjGLFBhUzblxg==

PL/SQL

DECLARE
   encrypted_string   VARCHAR2(1000):= 'DDYBaiP+RdjGLFBhUzblxg=='; -- Encrypted string from php
   output_string      VARCHAR2 (200);
   encrypted_raw      RAW (2000);             -- stores encrypted binary text
   decrypted_raw      RAW (2000);             -- stores decrypted binary text
          
   num_key_bytes      NUMBER := 128/8;        -- key length 256 bits (32 bytes)
   key_bytes_raw      RAW (32);               -- stores 256-bit encryption key
   encryption_type    PLS_INTEGER :=          -- total encryption type
                            DBMS_CRYPTO.ENCRYPT_AES128
                          + DBMS_CRYPTO.CHAIN_CBC
                          + DBMS_CRYPTO.PAD_PKCS5;

   iv_raw             RAW (16);

BEGIN
   key_bytes_raw := UTL_I18N.STRING_TO_RAW ('0962774221568619',  'UTF8');
   iv_raw        := UTL_I18N.STRING_TO_RAW ('0962774221568619',  'UTF8');
    
   --convert base64 encrypted string from php to raw
   encrypted_raw:= UTL_ENCODE.BASE64_DECODE(utl_raw.cast_to_raw(encrypted_string));
   
   decrypted_raw := DBMS_CRYPTO.DECRYPT
                    ( src => encrypted_raw,
                      typ => encryption_type,
                      key => key_bytes_raw,
                      iv  => iv_raw );
                      
   output_string := UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL32UTF8');
   DBMS_OUTPUT.PUT_LINE ('Decrypted string: ' || output_string);    
END;

Output: Decrypted string: Secret Message

what value is in $iv ? I got undeclared an undeclared error. — Bartosz
– Bartosz, Commented Apr 26, 2022 at 6:56
Both codes use different IVs and therefore produce different ciphertexts: The PHP code uses a zero IV (16 times 0x00), the PL/SQL code uses the key as IV. Both are insecure btw (the secure way is to use a random IV for each encryption, passed together with the ciphertext, usually concatenated). — Topaco
– Topaco, Commented Apr 26, 2022 at 6:56
Yeah I copied the PHP code provided by the client and in that, I missed to assign value for $IV and because of that, it is giving different cyphertexts. For testing purposes, I am using the same key and IV value but in prod, we have a different value for both. Thank you for pointing out this mistake. — Rutvik Prajapati
– Rutvik Prajapati, Commented Apr 26, 2022 at 10:26
New PHP code is: $clear_text = "Secret Message"; $iv = "0962774221568619"; $key = "0962774221568619"; $iv = str_pad($iv, 16, "\0"); $encrypt_text = openssl_encrypt($clear_text, "AES-128-CBC", $key, OPENSSL_RAW_DATA, $iv); $data = base64_encode($encrypt_text); echo $data; — Rutvik Prajapati
– Rutvik Prajapati, Commented Apr 26, 2022 at 10:27

Bartosz · Accepted Answer · 2022-04-27 13:12:31Z

0

You have to declare same cryptographic data to be able to decrypt it correctly.

In your PLSQL code IV variable has different value from IV declared in PHP, so you wont be able to decrypt data in PLSQL, which you encrypted with different IV in PHP.

Using the same IV value in PLSQL as in PHP will solve the problem.

answered Apr 27, 2022 at 13:12

Bartosz

1,89617 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to decrypt a string in PL/SQL that is encrypted using PHP?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related