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.