Decrypting S/MIME with PL/SQL

DidiSoft ORA_SMIME (part of OraRSA) can decrypt S/MIME encrypted enveloped data. In order to decrypt such encrypted Email, we need a private key (usually a .pfx/.pkcs12 file; Java key store can also be used) and its private key. This tutorial will illustrate the process.

Methods for the Message body Methods for the Attachment
DECRYPT_EMAIL DECRYPT_ATTACHMENT
GET_ATTACHMENTS_COUNT
GET_ATTACHMENT_CONTENT_TYPE
GET_ATTACHMENT_FILE_NAME

Table of examples

Decrypting the Email body text

The email body text is usually either plain text or HTML. Some emails can contain both, in which case the plain text will be returned by ORA_SMIME.DECRYPT_EMAIL. Loading of the private key is using the same process as with digital signatures:

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
DECLARE 
  -- file read handle
  file_handle BFILE;
  -- input Email BLOB
  email_blob BLOB;  
  -- PFX/p12 private key and chain storage
  pfx_blob BLOB;  
  pfx_password VARCHAR2(200); 
 
  -- decrypted Email information
  decrypted_text CLOB;
BEGIN
    -- load Email into a BLOB
    file_handle := BFILENAME('EMAILS_DIR', 'SMIME Encrypted.eml'); -- directory name must be Upper case
    DBMS_LOB.createtemporary(email_blob, TRUE);
    DBMS_LOB.OPEN(file_handle, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.LoadFromFile( email_blob, file_handle, DBMS_LOB.GETLENGTH(file_handle) );
    DBMS_LOB.CLOSE(file_handle);
 
    -- load .PFX (key storage) into a BLOB
    file_handle := BFILENAME('KEYS_DIR', 'PKCS12_Credential_sales@didisoft.com.pfx');    
    pfx_password := '4Yvv8RH33D81';    
    DBMS_LOB.createtemporary(pfx_blob, TRUE);
    DBMS_LOB.OPEN(file_handle, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.LoadFromFile( pfx_blob, file_handle, DBMS_LOB.GETLENGTH(file_handle) );
    DBMS_LOB.CLOSE(file_handle);
 
    decrypted_text := ORA_SMIME.DECRYPT_EMAIL(message => email_blob,private_key => pfx_blob, private_key_password => pfx_password);    
    DBMS_OUTPUT.put_line(decrypted_text);END;

Getting the count of attachments

Attachments are addressed by their sequential index. Thus we need to know upfront how many are they. This is performed with ORA_SMIME.GET_ATTACHMENTS_COUNT. Each attachment is addressed by its index, with the first having index 0 and the last one being attachments_count-1:

30
31
32
33
34
35
36
37
decrale
  attachments_count PLS_INTEGER;
BEGIN
...
   attachments_count := ORA_SMIME.GET_ATTACHMENTS_COUNT(message => email_blob,
                                                        private_key => pfx_blob, 
                                                        private_key_password => pfx_password);
    DBMS_OUTPUT.put_line('Count of attachments = ' || attachments_count);

Getting the file name of an attachment

The filename of the attachment is very useful for further processing of the file contents. Each attachment is addressed by its index:

30
31
32
33
34
35
36
37
decrale
  attachment_filename VARCHAR2(255);
BEGIN
...
   attachment_filename := ORA_SMIME.GET_ATTACHMENT_FILE_NAME(message => email_blob,private_key => pfx_blob, 
                                                             private_key_password => pfx_password, 
                                                             attachment_index => 0);
      DBMS_OUTPUT.put_line('Attachment 1 file name: ' || attachment_filename);

Getting the content type of an attachment

The content type of the attachment is also useful for controlling the processing of the file contents:

30
31
32
33
34
35
36
37
decrale
  attachment_contenttype VARCHAR2(255);    
BEGIN
...
   attachment_contenttype := ORA_SMIME.GET_ATTACHMENT_CONTENT_TYPE(message => email_blob,private_key => pfx_blob, 
                                                             private_key_password => pfx_password, 
                                                             attachment_index => 0);
      DBMS_OUTPUT.put_line('Attachment 1 content type: ' || attachment_contenttype);

Getting the body of an attachment

The actual content of the attachment is retrieved with ORA_SMIME.DECRYPT_ATTACHMENT which returns the data as BLOB. If the attachment is actually a text-based file then we need to take further actions to convert the BLOB into CLOB (not illustrated here):

30
31
32
33
34
35
36
37
decrale
  attachment BLOB;    
BEGIN
...
   attachment := ORA_SMIME.DECRYPT_ATTACHMENT(message => email_blob,private_key => pfx_blob, 
                                                             private_key_password => pfx_password, 
                                                             attachment_index => 0);
      DBMS_OUTPUT.put_line('Attachment 1 content type: ' || attachment_contenttype);

Exceptions

Exception handling shall follow the general exception handling strategy of OraRSA.