PGP encryption inside the Oracle(c) Database

In this chapter we are going to study how to OpenPGP encrypt data inside Oracle DB with the help of DidiSoft OraPGP PL/SQL paclage.

1. Encrypting VARCHAR2
2. Encrypting CLOB
3. Encrypting BLOB
4. Encrypting RAW
5. Invoking from Java
6. Exception handling

When we perform OpenPGP encryption we need the public key of the recipient. Of course if we need the data encrypted only for our own needs, we are going to use our own public key. The package accepts public keys as sever file system path location or as ASCII armored string in VARCHAR2 or BLOB fields. Check the  chapter regarding how to use OpenPGP keys with ORA_PGP if you still haven’t. The examples here illustrate for short only using  keys as file system path.

Integrity Protection

Integrity protecting OpenPGP data consists of embedding a Modification Detection Code (MDC packet) inside the encrypted data. After the press noise from the EFAIL attack major OpenPGP implementations require integrity protection to be activated (e.g. GnuPG 2.2.8 and all future versions reject messages that are not protected).

In order to turn On integrity protection for the current session, we must activate it before performing any encryption with:

1
2
3
BEGIN
  ORA_PGP.SET_INTEGRITY_PROTECT(TRUE);
  ORA_PGP.ENCRYPT...

1. Encrypting VARCHAR2

The encryption method expect the public key to be provided as an absolute file path on the server or in ASCII armored format. The example below uses absolute file path. In order the package to be able to access the key the invoking user must have access permissions for it.

The output will be OpenPGP encrypted message in ASCII armored format returned as VARCHAR2 data.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DECLARE
  MESSAGE VARCHAR2(2000);
  PUBLIC_KEY VARCHAR2(2000);
  v_Return VARCHAR2(2000); -- we need sufficient space here, or an error will be raised
BEGIN
  MESSAGE := 'Hello World';
  PUBLIC_KEY := 'c:\PGPKeys\recipient_public_key.asc';
 
  v_Return := ORA_PGP.ENCRYPT(
    MESSAGE => MESSAGE,
    PUBLIC_KEY => PUBLIC_KEY
  );
 
  -- print the encrypted data
  DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
END;
/

1. Encrypting CLOB

We can encrypt short CLOB fields using the same code as with the VARCHAR2 above. In order to avoid CLOB to VARCHAR2 conversion we are going to use the dedicated method ORA_PGP.ENCRYPT_CLOB which produces OpenPGP encrypted data in ASCII armored format as CLOB field. By default the encrypted data is assigned internally an artificial “message.txt” label. You can change it with an overloaded method that accepts an additional data_file_label parameter (like in the BLOB example below) :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DECLARE
  MESSAGE CLOB;
  PUBLIC_KEY VARCHAR2(2000);
  v_Return CLOB;
BEGIN
  MESSAGE := 'Hello World';
  PUBLIC_KEY := 'c:\PGPKeys\recipient_public_key.asc';
 
  v_Return := ORA_PGP.ENCRYPT_CLOB(
    MESSAGE => MESSAGE,
    PUBLIC_KEY => PUBLIC_KEY
  );
 
  -- print the encrypted data
  DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
END;
/

2. Encrypting BLOB

The two method signatures for encrypting BLOB data are
ORA_PGP.ENCRYPT_BLOB(message BLOB, public_key BLOB|VARCHAR2, ascii_armor BOOLEAN) return BLOB
ORA_PGP.ENCRYPT_BLOB(message BLOB, data_file_label VARCHAR2, public_key BLOB|VARCHAR2, ascii_armor BOOLEAN) return BLOB

The message parameter is the source data to be encrypted and the public_key is the encryption key. The ascii_armor parameter decides shall the PGP output format be binary or ASCII.

The mystical data_file_label parameter (available as of version 1.3.1) assigns a file name to the encrypted data. The idea is that if the encrypted BLOB is decrypted to a file outside of the database, that decrypted file name will have the value in data_file_label. 

In the example below we use a sample source table with definition:

table documents
+------------------------+
| document_id varchar(10)|
| file_data blob         |
+------------------------+

The example call below will produce binary OpenPGP format:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
DECLARE
  MESSAGE BLOB;
  PUBLIC_KEY VARCHAR2(200);
  v_Return BLOB;
  ascii_armor_output BOOLEAN := FALSE;
BEGIN
  MESSAGE := NULL;
  PUBLIC_KEY := 'c:\PGPKeys\public_keys.asc';
 
  SELECT file_data INTO MESSAGE
  FROM documents
  WHERE document_id = 'A103';
 
  v_Return := ORA_PGP.ENCRYPT_BLOB(
    message => MESSAGE,
    data_file_label => 'Report_A103.doc', -- file name assigned to the encrypted data, can be skipped
    public_key => PUBLIC_KEY,
    ascii_armor => ascii_armor_output
  );
 
END;
/

2. Encrypting RAW

For every other case, we can use ORA_PGP.ENCRYPT_RAW by first convering the data into RAW format. Here we also have the option to specify shall the output be ASCII armored or .pgp binary:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DECLARE
  public_key VARCHAR2(500) := 'c:\Projects\PGPKeys\public_key.asc';
  ascii_armor_output BOOLEAN := TRUE;
  message  CLOB;     
  encrypted_message RAW(32000);
  v_Return RAW(32000); 
BEGIN
    message := 'Hello World';
 
    encrypted_message := ORA_PGP.ENCRYPT_RAW(
      UTL_I18N.STRING_TO_RAW(message),
      PUBLIC_KEY,
      ascii_armor_output);
END;
/

3. Invoking from Java

The encryption functions can be invoked from Java code just like any other PL/SQL procedure:

import java.sql.*;
 
public class OraPGPTests {
 
	private static final String JDBC_ORACLE_CONNECTION = "jdbc:oracle:thin:@localhost:1522:orcl2";
 
	public void testEncryptString() throws SQLException {
		DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
 
		Connection conn = DriverManager.getConnection
				  (JDBC_ORACLE_CONNECTION, "user", "pass");		
 
		String message = "Hello World";
 
		// ENCRYPT_STRING
		CallableStatement cstmt = conn.prepareCall("{?=call ORA_PGP.ENCRYPT(?,?)}");
		cstmt.registerOutParameter(1, Types.VARCHAR);
		cstmt.setString(2, message);
		cstmt.setString(3, "c:\\Projects\\PGPKeys\\public.key");
		cstmt.executeUpdate();
		String encryptedString = cstmt.getString(1);
 
		System.out.println(encryptedString);
	}
}

Exception handling

Exception handling is available as of version 1.1. Please check the dedicated chapter for Exception handling in OraPGP in order to study the custom mechanism provided by the package.

Summary

This chapter illustrated how to invoke the ORA_PGP encryption PL/SQL functions. You can find complete examples in the \Examples sub folder inside the product ZIP archive.

List of methods used:

ORA_PGP.ENCRYPT Encrypts VARCHAR2 data. The output is ASCII armored
ORA_PGP.ENCRYPT_BLOB Encrypts BLOB data. Allows specification of the output format
ORA_PGP.ENCRYPT_CLOB Encrypts CLOB data and produces CLOB. The output is ASCII armored
ORA_PGP.ENCRYPT_RAW Encrypts RAW data. Allows specification of the output format