Parsing a base64 X509 certificate in an Oracle stored procedure?

Question

I have a certificate x509 in base 64 binary format. How can I retrieve the information about the certificate with Oracle? I have to get serial number of this certificate. Any ideas?

What you mean by 'Oracle', the database? Please provide more context. — home, Oct 28 '12 at 07:31
in my package body i am getting base64 certificate by webservice. webservice return me certificate in base64binary in xml format. but i nead to define which serial number has this certificate. — Hamza Ostonov, Oct 28 '12 at 08:06
Which bit of this task has got you stumped? If it's the base64 encoding, check out this previous SO answer: http://stackoverflow.com/questions/3804279/base64-encoding-and-decoding-in-oracle — APC, Oct 28 '12 at 11:54
it is ok with converting to base64 and from. but how can i get serial number of certificate from varchar2 ? — Hamza Ostonov, Oct 29 '12 at 07:42
Theoretically you could write a Java stored procedure (Java has support for working with x509 certificates). See if this example works : https://community.oracle.com/thread/2466746 — David Balažic, Apr 15 '16 at 19:10

David Balažic · Answer 1 · 2016-04-18T18:40:30.080

There is a solution on the Oracle forum : SQL to extract specific attributes from an x509 digital certificate

The code (the original is for certificates stored as CLOB, I modified it for BLOB and to return the serial number):

create or replace and compile java source named testx509src
 as
  import java.security.cert.*;
  import java.io.*;
  import java.sql.*;
  import oracle.sql.BLOB;
  import oracle.sql.NUMBER;

  public class TestX509 {
      public static NUMBER getSerialNumber(BLOB cert)
              throws SQLException, IOException, CertificateException {

          Connection conn = (Connection) DriverManager.getConnection("jdbc:default:connection:");
          BufferedInputStream is = new BufferedInputStream(cert.getBinaryStream());

          CertificateFactory cf = CertificateFactory.getInstance("X.509");
          X509Certificate c = (X509Certificate) cf.generateCertificate(is);
          is.close();

          return new oracle.sql.NUMBER(c.getSerialNumber()); 

      }
  }
/

CREATE OR REPLACE FUNCTION CERT_getSerialNumber(cert in blob)
  RETURN NUMBER
  AS LANGUAGE JAVA
  NAME 'TestX509.getSerialNumber(oracle.sql.BLOB) return oracle.sql.NUMBER';
/

SQL> select CERT_GetSerialNumber(cert) serial from cert_storage where id = 1;

serial
-----------------------
243435653237

xorcus · Answer 2 · 2013-08-16T13:49:08.700

After you base64-decode a certificate, you most likely get a DER-encoded ASN.1 structure of a X.509 v3 certificate (enough keywords to continue searching for an answer).

I am not aware of any PL/SQL implementation of ASN.1 parser, parsing DER-encoded content, but it is possible to learn ASN.1 structures (sequence, integer, etc.) and their binary representation in DER format, and then do the parsing in PL/SQL, byte by byte. => The serial number is close to the beginning of the DER-content, so you do not need to support parsing every ASN.1 element to extract the serial number.

You could have to look at X.509 certificate structure/template, explaining how a certificate is constructed from basic ASN.1 elements, then parse/extract the elements and get the info you're interested in.

More detailed description of what's in a certificate: X.509 certificate consists of some data fields like version, serial number, valid from/to dates, issuer DN (distinguished name), subject DN, subject public key, signature hash algorithm, etc. This info is then "signed" by the certificate issuer: the issuer creates a hash code (e.g. using SHA-1 algorithm) from the info mentioned above, and then encrypts it using issuer's private key (RSA encryption). Having issuer's public key and trusting the issuer, one could use the issuer's public key to decrypt the hash code encrypted by the issuer, then create a hash code from certificate details using the same algorithm, and finally compare the computed hash with the one the issuer created. If these match, it means that no one modified the details, so if issuer is trusted, the details found in the certificate can be trusted as well.

X.509 certificate begins with (data types shown to the right):

Certificate                SEQUENCE
    Data                   SEQUENCE
        Version            [0] { INTEGER }
        Serial Number      INTEGER

Each element starts with a tag byte indicating the element type, followed by the element length, followed by the element content. If the element contains fewer than 128 bytes, the length field requires only one byte to specify the content length. If it is more than 127 bytes, bit 7 of the Length field is set to 1 and bits 6 through 0 specify the number of additional bytes used to identify the content length. In case of X.509 certificate, Version is wrapped in a context-specific tag [0].

Books explaining ASN.1 can be downloaded for free from the web.

Here's an example for analysing the beginning of a certificate:
    30 82 02 D7 30 82 02 40 A0 03 02 01 02 02 01 01 ...
Interpretation:
    30 = Start of Certificate SEQUENCE
    82 = sequence length are the following two bytes
    02 D7 = sequence length 0x02D7 (Big Endian order of bytes)
    30 = Start of Data SEQUENCE
    82 = sequence length are the following two bytes
    02 40 = sequence length 0x0240 (Big Endian order of bytes)
    A0 = start of context-specific element [0]
    03 = length of context-specific element [0]
    02 01 02 = content of context-specific element [0] (Version INTEGER)
        (02=start of Version INTEGER,
         01=length of the integer,
         02=Version value (zero-based, so value 02 actually means v3))
    02 = Start of Serial Number INTEGER
    01 = Length of Serial Number INTEGER
    01 = The serial number itself
    ...

Of course, in your case, length of the serial number may be bigger than one byte shown here.

Parsing a base64 X509 certificate in an Oracle stored procedure?

2 Answers2