Verify pgp signed data with Oracle PL/SQL

In this chapter we are going to show how to verify OpenPGP signed VARCHAR2 and BLOB fields with the help of DidiSoft OraPGP.

In order to verify a digitally signed .pgp data we use the public key of the message sender to check the digital signature. A second operation in the process is to extract also the data contained in addition to the digital signature. This examples bellow apply to OpenPGP signed and OpenPGP clear text signed data. They are not suitable for OpenPGP detached signatures.

1. Verifying signed VARCHAR2
2. Verifying signed BLOB
3. Invoking from Java

1. Verifying VARCHAR2

The digital signature verification method expect the public key to be provided as an absolute file path (in that case we need permissions) or in ASCII armored format. The example below uses absolute file path.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

DECLARE
  MESSAGE VARCHAR2(200);
  PUBLIC_KEY VARCHAR2(200);
  DECRYPTED_MESSAGE VARCHAR2(2000); -- we need sufficient space here, or an error will be raised
  VERIFICATION_RESULT BINARY_INTEGER;
BEGIN
  MESSAGE := NULL;
  PUBLIC_KEY := 'c:\PGPKeys\public_key.asc'; -- be sure to have granted access permissions
 
  SELECT
     signed_data INTO MESSAGE
      FROM
        documents
      WHERE
        document_id = 'A103';
 
  VERIFICATION_RESULT := ORA_PGP.VERIFY(
    MESSAGE => MESSAGE,
    PUBLIC_KEY => PUBLIC_KEY,
    DECRYPTED_MESSAGE => DECRYPTED_MESSAGE
  );
 
  -- print the encrypted data
  DBMS_OUTPUT.PUT_LINE('extracted data ' || DECRYPTED_MESSAGE);
 
  IF VERIFICATION_RESULT = ORA_PGP.SIGNATURE_VERIFIED THEN
    DBMS_OUTPUT.PUT_LINE('Signature is good.');
  ELSIF VERIFICATION_RESULT = ORA_PGP.SIGNATURE_BROKEN THEN
    DBMS_OUTPUT.PUT_LINE('Signature verification failed!');
  ELSIF VERIFICATION_RESULT = ORA_PGP.PUBLIC_KEY_NOT_MATCH THEN
    DBMS_OUTPUT.PUT_LINE('Supplied public key does not match the signature');
  ELSE   
    DBMS_OUTPUT.PUT_LINE('No signature in message');
  END IF;
END;

2. Verifying BLOB

In the example below we assume that a table exists with definition :

 documents
+--------------------+
|document_id char(4) |
|file_data BLOB      |
+--------------------+

In contrast to the ORA_PGP.VERIFY method which also return the data from the signed .pgp document, when dealing with BLOBs we use the ORA_PGP.VERIFY_BLOB only to verify the signature check result and afterwards the ORA_PGP.EXTRACT_BLOB methods to extract the data.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

DECLARE
  MESSAGE BLOB;
  PUBLIC_KEY VARCHAR2(200);
  DECRYPTED_MESSAGE BLOB;  
  VERIFICATION_RESULT PLS_INTEGER;
BEGIN
  MESSAGE := NULL;
  PUBLIC_KEY := 'c:\PGPKeys\public_keys.asc';
 
  -- table documents(document_id char(4), file_data BLOB)
  SELECT
     file_data INTO MESSAGE
      FROM documents
      WHERE document_id = 'A103';
 
  VERIFICATION_RESULT := ORA_PGP.VERIFY_BLOB(
    MESSAGE => MESSAGE,
    PUBLIC_KEY => PUBLIC_KEY
  );
 
  DECRYPTED_MESSAGE := ORA_PGP.EXTRACT_SIGNED_BLOB(v_Return);
 
  IF VERIFICATION_RESULT = ORA_PGP.SIGNATURE_VERIFIED THEN
    DBMS_OUTPUT.PUT_LINE('Signature is good.');
  ELSIF VERIFICATION_RESULT = ORA_PGP.SIGNATURE_BROKEN THEN
    DBMS_OUTPUT.PUT_LINE('Signature verification failed!');
  ELSIF VERIFICATION_RESULT = ORA_PGP.PUBLIC_KEY_NOT_MATCH THEN
    DBMS_OUTPUT.PUT_LINE('Supplied public key does not match the signature');
  ELSE   
    DBMS_OUTPUT.PUT_LINE('No signature in message');
  END IF;
END;

3. Invoking from Java

The signature verification 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 testVerifyString() throws SQLException {
		DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
 
		Connection conn = DriverManager.getConnection
				  (JDBC_ORACLE_CONNECTION, "user", "pass");		
 
		String message = ".. pgp signed or clear text signed message here ..";
 
		// ENCRYPT_STRING
		CallableStatement cstmt = conn.prepareCall("{?=call ORA_PGP.VERIFY(?,?)}");
		cstmt.registerOutParameter(1, Types.INTEGER);
		cstmt.setString(2, message);
		cstmt.setString(3, "c:\\Projects\\PGPKeys\\public.key");
                cstmt.registerOutParameter(4, Types.VARCHAR);
		cstmt.executeUpdate();
 
		String extractedString = cstmt.getString(4);
		boolean signatureVerified = (cstmt.getInt(1) == 1)?true:false;
 
		System.out.println(extractedString);
	}
}

Summary

This chapter illustrated how to invoke the ORA_PGP digital signature verification PL/SQL functions.

You may also be interested to check the OpenPGP signing functions provided by the package.