Click here to Skip to main content
15,867,686 members
Articles / Programming Languages / SQL
Tip/Trick

RSA Encryption in PL/SQL

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
12 Aug 2020CPOL2 min read 8.7K   5  
Encrypt data with RSA Public Key
How to encrypt data with a RSA Public Key in an Oracle 11g PL/SQL package (with some JAVA Helper)

Introduction

I was recently in need to encrypt certain information using a provided RSA public key, from an Oracle PL/sQL package, in order to be able to communicate with a web service that authenticates in that manner.
After doing some research on the internet, I could not find any working solution (free). Found many concepts, and ideas, and bits and pieces, or paid software, but it took me more time and effort than usual to get to a working solution, and I had to make most of it myself.

So I decided to post it for public knowledge.

Background

First of all, it's important to make sure your environment is configured in the proper way:

  1. You need to make sure your ORACLE DB has JAVA VM installed. You can check it by:
    SQL
    select comp_name, version, status from dba_registry;

    If it is not there, then you have to login as SYS and execute the following:

    SQL
    SQL> @$ORACLE_HOME/javavm/install/initjvm.sql;

    Once JavaVM is installed, you are all set.

  2. For the purpose of this article, we will encrypt a password with a Public Key, so it could be sent and decrypted on the other end with the corresponding private key. But we will need:
    • Our Password (we will use "password")
    • Our Public Key file (I will call it "rsa.public")

In my case, I'm using Oracle (11g) 11.2.0.4.

Using the Code

The first thing we need to understand is that ORACLE does not supply (in version 11g) a library for asymmetric cryptography. DBMS_CRYPTO package will only supply Symmetric cryptography.

So, we need to create our own class to deal with it. And we will need to do it in Java.
Create or replace and compile Java source named rsa_crypto as:

Java
import javax.crypto.*;
import java.security.spec.*; 
import java.security.*; //PublicKey

public class RSACrypto
{
  public static byte[] Encrypt(byte[] cert, byte[] data) 
         throws Throwable //NoSuchAlgorithmException, InvalidKeySpecException, 
                          //InvalidKeyException, IllegalBlockSizeException, 
                          //NoSuchPaddingException, BadPaddingException
  {
      byte[] encryptedData = null;
      try {

        //Generate Public Key with Cert
        KeyFactory keyFactory = KeyFactory.getInstance("RSA");
        X509EncodedKeySpec pubKeySpec = new X509EncodedKeySpec(cert);
        PublicKey pubKey = keyFactory.generatePublic(pubKeySpec);
        
        //Encrypt
        Cipher cipher = Cipher.getInstance("RSA/ECB/OAEPWithSHA1AndMGF1Padding");  
        cipher.init(Cipher.ENCRYPT_MODE, pubKey); 
        encryptedData = cipher.doFinal(data);

        return encryptedData;
     } catch (Exception e) {
        System.out.println(  "Unexpected error - " + e.getMessage());
        encryptedData = e.getMessage().getBytes();
        throw e;
     }
  }
}

Our function will receive 2 byte arrays (certificate content and data to encrypt) and will return a byte array with the encrypted Data.

We will create our package, and inside its body, we will declare the following private function:

Java
function rsa_encrypt(cert raw, data raw) return raw
     AS LANGUAGE JAVA
      NAME 'RSACrypto.Encrypt(byte[], byte[]) return byte[]';

Then we will have our login function, that will make use of our JAVA function in the following way:

Java
c_certini              constant varchar2(50) := '-----BEGIN PUBLIC KEY-----';
c_certend              constant varchar2(50) := '-----END PUBLIC KEY-----';
c_dirname              constant varchar2(255) := 'DIRX';
c_key_filename         constant varchar2(255) := 'rsa.public';

function login return number is
     
      passpost  raw(1024);
      res    varchar2(1024);
      
      v_fexist  boolean;
      v_flen    number;
      v_bsize   binary_integer;
      v_key    utl_file.file_type;
      v_cert   raw(2048);
      v_buffer varchar2(2048);
     
  begin
            
      -- Check and Read Certificate
      utl_file.fgetattr(c_dirname, c_key_filename, v_fexist, v_flen, v_bsize);
      if not v_fexist then
         dbms_output.put_line(c_dirname || '/' || c_key_filename || ' Not Found.');
         return -3;
      end if;
      v_key := utl_file.fopen(c_dirname, c_key_filename,'R');
      utl_file.get_raw(v_key,v_cert,v_flen);
      utl_file.fclose(v_key);
      dbms_output.put_line('Cert Read: ' || utl_raw.cast_to_varchar2(v_cert));
      v_buffer := utl_raw.cast_to_varchar2(v_cert);      
      
      -- Strip beginning and end.
      v_buffer := replace(v_buffer,c_certini,'');
      v_buffer := replace(v_buffer,c_certend,'');
      v_cert := utl_encode.base64_decode(utl_raw.cast_to_raw(v_buffer));
      dbms_output.put_line('Cert Strip: [' || v_buffer || ']');
      
      --Call to JAVA function
      begin
             passpost:=rsa_encrypt(v_cert,utl_raw.cast_to_raw('Password'));
      exception when others then
             dbms_output.put_line('Cannot encrypt ' || SQLERRM);  
             return -4;
      end;
      if passpost is null then
         return -2;
      end if;
      res:= utl_raw.cast_to_varchar2(utl_encode.base64_encode(passpost));
      dbms_output.put_line('Encrypted Signature: ' || res);     
        
      return 0;
      
  end;

Points of Interest

Basically we: open file, read its contents, and pass it to Java function Base64Decoded.
Java function will create a public key with the Content received, and encrypt the data with it and return it.
We will base 64 encode the returned data, and then we can use it for anything else we need.

History

  • 12th August, 2020: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Chief Technology Officer
Argentina Argentina
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
-- There are no messages in this forum --