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.

Message body Attachments
DECRYPT_EMAIL
IS_ENCRYPTED		 DECRYPT_ATTACHMENT
GET_ATTACHMENTS_COUNT
GET_ATTACHMENT_CONTENT_TYPE
GET_ATTACHMENT_FILE_NAME

Table of examples

Check if an Email is encrypted

The ORA_SMIME methods for decrypting Emails are usable only if an email is encrypted. We can check that with the method ORA_SMIME.IS_ENCRYPTED:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

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); 
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);
 
  IF ORA_SMIME.IS_ENCRYPTED(email_blob) THEN
      DBMS_OUTPUT.put_line('Email is encrypted ');
  END IF;
END;

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. The method used is ORA_SMIME.GET_ATTACHMENT_FILE_NAME. 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 (see next topic). It is done with the method ORA_SMIME.GET_ATTACHMENT_CONTENT_TYPE:

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

Depending on the content type we can get the attachment either as BLOB (for binary files like images) or CLOB (for text files). Binary attachments are retrieved with ORA_SMIME.DECRYPT_ATTACHMENT_BINARY (or ORA_SMIME.DECRYPT_ATTACHMENT) and text-based attachments can be extracted with ORA_SMIME.DECRYPT_ATTACHMENT_TEXT:

30
31
32
33
34
35
36
37
38
39
40
41

decrale
  binary_attachment BLOB;    
  text_attachment CLOB;    
BEGIN
...
   binary_attachment := ORA_SMIME.DECRYPT_ATTACHMENT(message => email_blob,private_key => pfx_blob, 
                                                             private_key_password => pfx_password, 
                                                             attachment_index => 0);
   text_attachment := ORA_SMIME.DECRYPT_ATTACHMENT_TEXT(message => email_blob,private_key => pfx_blob, 
                                                             private_key_password => pfx_password, 
                                                             attachment_index => 1);
      DBMS_OUTPUT.put_line('Text attachment: ' || text_attachment );

Exceptions

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