Xrhrft

I am trying to replicate encryption/decryption method present in java in Oracle DB, so that data encrypted in java can be decrypted through Oracle Function.

Below is java code:

package com.encr;

import java.security.NoSuchAlgorithmException;

import javax.crypto.Cipher;

import javax.crypto.NoSuchPaddingException;

import javax.crypto.spec.IvParameterSpec;

import javax.crypto.spec.SecretKeySpec;

import sun.misc.BASE64Decoder;

import sun.misc.BASE64Encoder;

public class EncrUtil 

{

private static Cipher cipher = null;



public static void aesInit(String inKey) throws Exception 

{

    String methodName = "aesInit";

    try

    {



        byte inkeyBytes = inKey.getBytes("utf-8");

        System.out.println("inkeyBytes="+inkeyBytes.toString());

        final SecretKeySpec clientkey = new SecretKeySpec(inkeyBytes, "AES");

        cipher = Cipher.getInstance("AES/CBC/PKCS5Padding");

        final IvParameterSpec iv = new IvParameterSpec(new byte[32]);

        cipher.init(Cipher.ENCRYPT_MODE,clientkey,iv);

    }

    catch (NoSuchAlgorithmException e) {

        throw new Exception("NoSuchAlgorithmException", e);

    } catch (NoSuchPaddingException e) {



        throw new Exception("NoSuchPaddingException", e);



    }

}



public static String encrypt(String inData,String inKey)

{

    String methodName = "encrypt";

    String strCipherText = null;

    try

    {

        if ( null != inData && null != inKey)

        {

            if(EncrUtil.cipher == null){

                EncrUtil.aesInit(inKey);

            }

            byte byteDataToEncrypt = inData.getBytes("utf-8");

            System.out.println(byteDataToEncrypt.toString());

            byte byteCipherText = cipher.doFinal(byteDataToEncrypt); 

            System.out.println(byteCipherText.toString());

            strCipherText = new BASE64Encoder().encode(byteCipherText);

        }



    }

    catch (Exception e) {

        String sErrMsg = "Text to be encrypted: " + inData;

        sErrMsg = new StringBuffer(methodName).append( sErrMsg) .append( "  Message: ") .append(e.getMessage()).toString();

        strCipherText = sErrMsg;



    }

    return strCipherText;



}



public static String decrypt(String inData,String inKey)

{

    String methodName = "decrypt";

    String strDecryptedText = null;

    try

    {



        byte inkeyBytes = inKey.getBytes("utf-8");

        final SecretKeySpec clientkey = new SecretKeySpec(inkeyBytes, "AES");

        cipher = Cipher.getInstance("AES/CBC/PKCS5Padding");

        final IvParameterSpec iv = new IvParameterSpec(new byte[16]);

        cipher.init(Cipher.DECRYPT_MODE,clientkey,iv);

        byte decodedData = new BASE64Decoder().decodeBuffer(inData);

        byte byteDecryptedText = cipher.doFinal(decodedData);

        strDecryptedText = new String(byteDecryptedText);



    }

    catch (Exception e) {

        String sErrMsg = "Text to be decrypted: " + inData;

        sErrMsg = new StringBuffer(methodName).append(sErrMsg) .append( "Message:") .append(e.getMessage()).toString();

        strDecryptedText = sErrMsg;

    } 



    return strDecryptedText;



}



public static void main(String args) 

{

    String inKey= "84hf763bht096hnf";

    String inData= "Vikram";

    System.out.println("Word to be encrypted is "+inData);

    String encrypted = EncrUtil.encrypt(inData,inKey);

    System.out.println("Encrpted word is"+encrypted);



}

}

The Oracle Pl/sql function i tried for replicating is

CREATE OR REPLACE PACKAGE BODY SYS.enc_dec

AS

encryption_type    PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES

                                 + DBMS_CRYPTO.CHAIN_CBC

                                 + DBMS_CRYPTO.PAD_PKCS5;

encryption_key     RAW (32) := UTL_I18N.STRING_TO_RAW ('84hf763bht096hnf', 'AL32UTF8');

 FUNCTION encrypt (p_plainText VARCHAR2) RETURN RAW DETERMINISTIC

 IS

    encrypted_raw      RAW (2000);

 BEGIN

 DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(encryption_key));

    encrypted_raw := DBMS_CRYPTO.ENCRYPT

    (

       --src => UTL_RAW.CAST_TO_RAW (p_plainText),

       src =>UTL_I18N.STRING_TO_RAW (p_plainText,'AL32UTF8'),

       typ => encryption_type,

       key => encryption_key

    );

   RETURN encrypted_raw;

 EXCEPTION

  WHEN OTHERS THEN

  DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM, 1, 100));

 END encrypt;

 FUNCTION decrypt (p_encryptedText RAW) RETURN VARCHAR2 DETERMINISTIC

 IS

    decrypted_raw      RAW (2000);

 BEGIN

    decrypted_raw := DBMS_CRYPTO.DECRYPT

    (

        src => p_encryptedText,

        typ => encryption_type,

        key => encryption_key

    );

    RETURN (UTL_I18N.RAW_TO_CHAR(decrypted_raw,'AL16UTF8'));

 EXCEPTION

  WHEN OTHERS THEN

  DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM, 1, 100));

 END decrypt;

END;

Encryption done by above code is not same as that done in java
Please let me know where i am going wrong in oracle function or
Please tell me how to add the java methos in oracle, in tutorials i couldn't find how to include imports of java in oracle.

I was able to move the Java code to Oracle. I am writing this for people who are facing similar issue.

1. Log on to server where Oracle database is installed through Putty (command prompt in case of windows)


2. Place the Java file in server using WinSCP.


3. 
   

   Find the Java compiler present in oracle home using command

   
   
   

   find / -name javac
   
   

   
   


4. 
   

   Compile the Java file using Oracle's Java compiler as the Java version used by the database and Java version usually used will be different

   
   
   

   $ORACLE_HOME/jdk/bin/javac /home/vikram/EncrUtil.java
   
   

   
   
   

   Note: Instead of compiling on the database server, a .class or .jar file can be directly loaded into the database. But make sure to use the same version of Java as used by the database.

   
   
   

   To find the java version used by oracle

   
   
   

   $ORACLE_HOME/jdk/bin/java -version
   
   java version "1.5.0_17"
   
   Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_17-b03)
   
   Java HotSpot(TM) 64-Bit Server VM (build 1.5.0_17-b03, mixed mode)
   
   

   
   


5. 
   

   Set ORACLE_HOME if it is not set

   
   
   

   export ORACLE_HOME=/database/ora11gr2/product/11.2.0/dbhome_1/
   
   

   
   


6. 
   

   Add loadjava's directory in the path (that's the database's bin directory)

   
   
   

   export PATH=$PATH:$ORACLE_HOME/bin
   
   

   
   
   

   Note: In case class not found exception is found go through “loadjava” and check if path present is properly set or variables are defined is not define those

   
   


7. 
   

   Load the class into the database

   
   
   

   loadjava -user <user>/<password>@xxx.xxx.xxx.xxxx:1521:<instance> 
   
            -thin –resolve /home/vikram/ EncrUtil.class
   
   

   
   
   
   
   
   • 
     -resolve is used so that .class file can be resolved before loading into the database as its easy to debug Java outside database.
   
   
   • 
     -thin is used for thin client
   
   
   
   


8. 
   

   Once the class is loaded, create a wrapper function on top it, so that it can be used in database

   
   
   

   CREATE OR REPLACE FUNCTION TEST_DECRYPTOR(indata IN VARCHAR2)
   
   RETURN VARCHAR2 AS
   
   LANGUAGE JAVA NAME ' EncrUtil. decrypt (java.lang.String) return java.lang.String';
   
   CREATE OR REPLACE FUNCTION TEST_ENCRYPTOR (indata IN VARCHAR2)
   
   RETURN VARCHAR2 AS
   
   LANGUAGE JAVA NAME ' EncrUtil. encrypt (java.lang.String) return java.lang.String';
   
   

   
   


9. 
   

   This function can then be used just like any other Oracle function:

   
   
   

   SELECT TEST_ENCRYPTOR('Vikram') FROM DUAL;
   
   

   
   

A little late but the answer may be useful:

In Java this line should be changed:

final IvParameterSpec iv = new IvParameterSpec(new byte[16]);

into this:

final IvParameterSpec iv = new IvParameterSpec(new String(Base64.decode('vector_inicializacion')).getBytes());

where vector_inicializacion is a any hexadecimal of 16 but in PL/SQL it must be the same.

and

strDecryptedText = new String(byteDecryptedText);

Should be replaced by

strDecryptedText = new String(Base64.encodeBytes(byteDecryptedText))

In PL:

encrypted_raw := DBMS_CRYPTO.ENCRYPT

    (

       --src => UTL_RAW.CAST_TO_RAW (p_plainText),

       src =>UTL_I18N.STRING_TO_RAW (p_plainText,'AL32UTF8'),

       typ => encryption_type,

       key => encryption_key

       IV => UTL_RAW.CAST_TO_RAW ('vector_inicializacion') 

    );

ORA-29531: no method encrypt in class EncrUtil

why this error may appear?