This discussion is archived
6 Replies Latest reply: Nov 20, 2012 2:11 PM by ltps RSS

SQL to extract specific attributes from an x509 digital certificate

ltps Newbie
Currently Being Moderated
I am curious whether there is a way to use SQL to extract specific attributes from an x509 digital certificate. I know how to do it using openssl - no problem. But what if the pem file is stored in a datebase column and I want to extract the expiration date. From the openssl command line, I can just do this to extract the certificate's expiration date:

openssl x509 -noout -in certificate.pem -dates | grep notAfter | awk -F= '{print $2}'
(extract just the dates | take only the line with the expiration date | print only the second column in the line delimited by =)

But if certificate.pem is stored in an Oracle database field, how to achieve the same result? Is there anything like "DBMS_OPENSSL"?

I got to thinking about V$WALLET in an Oracle database. The only columns here are CERT_ID, DN, SERIAL_NUM, ISSUER, KEYSIZE, and STATUS - no certificate, which suggests that it is stored elsewhere. As you can tell, I'm new at this.

Thanks for your help!
  • 1. Re: SQL to extract specific attributes from an x509 digital certificate
    jeneesh Guru
    Currently Being Moderated
    Dont know, will this help..
    with sample_cert as
    (
     select 'Certificate:
         Data:
             Version: 1 (0x0)
             Serial Number: 7829 (0x1e95)
             Signature Algorithm: md5WithRSAEncryption
             Issuer: C=ZA, ST=Western Cape, L=Cape Town, O=Thawte Consulting cc,
                     OU=Certification Services Division,
                     CN=Thawte Server CA/emailAddress=server-certs@thawte.com
             Validity   
                 Not Before: Jul  9 16:04:02 1998 GMT
                 Not After : Jul  9 16:04:02 1999 GMT
             Subject: C=US, ST=Maryland, L=Pasadena, O=Brent Baccala,
                      OU=FreeSoft, CN=www.freesoft.org/emailAddress=baccala@freesoft.org
             Subject Public Key Info:
                 Public Key Algorithm: rsaEncryption
                 RSA Public Key: (1024 bit)
                     Modulus (1024 bit):
                         00:b4:31:98:0a:c4:bc:62:c1:88:aa:dc:b0:c8:bb:
                         33:35:19:d5:0c:64:b9:3d:41:b2:96:fc:f3:31:e1:
                         66:36:d0:8e:56:12:44:ba:75:eb:e8:1c:9c:5b:66:
                         70:33:52:14:c9:ec:4f:91:51:70:39:de:53:85:17:
                         16:94:6e:ee:f4:d5:6f:d5:ca:b3:47:5e:1b:0c:7b:
                         c5:cc:2b:6b:c1:90:c3:16:31:0d:bf:7a:c7:47:77:
                         8f:a0:21:c7:4c:d0:16:65:00:c1:0f:d7:b8:80:e3:
                         d2:75:6b:c1:ea:9e:5c:5c:ea:7d:c1:a1:10:bc:b8:
                         e8:35:1c:9e:27:52:7e:41:8f
                     Exponent: 65537 (0x10001)
         Signature Algorithm: md5WithRSAEncryption
             93:5f:8f:5f:c5:af:bf:0a:ab:a5:6d:fb:24:5f:b6:59:5d:9d:
             92:2e:4a:1b:8b:ac:7d:99:17:5d:cd:19:f6:ad:ef:63:2f:92:
             ab:2f:4b:cf:0a:13:90:ee:2c:0e:43:03:be:f6:ea:8e:9c:67:
             d0:a2:40:03:f7:ef:6a:15:09:79:a9:46:ed:b7:16:1b:41:72:
             0d:19:aa:ad:dd:9a:df:ab:97:50:65:f5:5e:85:a6:ef:19:d1:
             5a:de:9d:ea:63:cd:cb:cc:6d:5d:01:85:b5:6d:c8:f3:d9:f7:
             8f:0e:fc:ba:1f:34:e9:96:6e:6c:cf:f2:ef:9b:bf:de:b5:22:
             68:9f' cert
      from dual
    )
    select regexp_substr(cert,'(Not After : )(.*)$',1,1,'m',2) exp_dt
    from sample_cert;
    
    EXP_DT                 
    ------------------------
    Jul  9 16:04:02 1999 GMT 
  • 2. Re: SQL to extract specific attributes from an x509 digital certificate
    ltps Newbie
    Currently Being Moderated
    This is excellent. Thank you. I would like to take it one step further. Let's assume that we have not already extracted text or dates from the original certificate. This means that we are storing in the Oracle database only the results of a command like this:

    openssl req -x509 -config "D:\Program Files\GnuWin32\conf\openssl.conf" -days 365 -newkey rsa:2048 -keyout my2048cert.pem -out my2048cert.pem

    With results that look like this:

    -----BEGIN RSA PRIVATE KEY-----
    MIIEpAIBAAKCAQEAvPCeJBOAzv2TAg4iqjYYT5R+XnoXQT9GD3noejoWhP20GG+9
    B8jVIO9HOyswZg/ZUMBPsjscRSfAU8wXtgCItsr/ouUoO/QP4nI/er2T8Q2xl3Rn
    fcpnceslXWx1lkqIK/uKwconvEmDFCr715ykEbZj1WvmsuDxY+qeO+dojrQ8340S
    xacU2v+7qsF+/1EJAG1ssbqeGrmvF3rtxZhHWEulCD9LjQBhwkEwg4CkM33pjMMV
    dQzbr0l0ctIyBu1k3wXhd4aWM1u5VPwFMo5LJ0NRZY0kDn3cYsxJD44lXNW7AX30
    nNTAbRNyY9iBxCdVq/a32KB5erttSylwVMdBfwIDAQABAoIBAEvh05Gnp5b6bp1n
    sfJskWz8tZgaLtbMo8W5b24PgFXfUwJxdhj3RunYT+JqoSCMgvoUkq2BYgRAR9Uf
    5fH52u6XLv5sTLFvfyWyPt573zv0BPxQCvSRYuR0zWftFHjM7E3F09+q4jSTzao+
    7TCWP6rBdu0TTe9PuoMnim+CrBKLjdpRHTmo0+fxT4SPY/NZXfzlZe3mMVH3ffCv
    8fk19VEd8WhK7R4e0qdEVaG5+o1oIc0n0aRAbf5LtSnFE8b4QyWxdVtOhZv9/od9
    DaDEog2jq4O33dHDAzaeqMngF3bqAOeFnyi+fezCLrtec2AM875EsKBixe2maYwj
    DKJPWRkCgYEA6hyqv1uakBEhK2+8dim8/MjtgclEf8g1M5Huxzsin/hUsrUnzhgL
    BtvPtAsCcjh7uRLNaCOhJlJL9Z8eoMHd4P8eFjA4YkVb+egILs6GW0jh5vdBQ/EH
    fAMALYCGAhDUamgCO8PL2iNwt7HR3Hkr5Yu3rJiXEaxkoIwvG2QhJF0CgYEAzprI
    p0jN/nbHBZYsXxtrDI39ajM0gRRPtbELKCxexeAGe9bY/vz/YoZIH0jZRHnKfu8Q
    t6z2PAr5H7sBLRw18RRVM8IgpZBrKE6QBIN2kCbXrHAciI4LGR1mDEiYwuRZjTKW
    BkOHaOqF+Wdd6RanGHF5ZsMU/HcGWAIhbs7SX4sCgYEAheAw0E7s+zegdTtnVVcW
    6KNqFk7nKf9uVPg9yJaLfVk/7hXkFlnvbFOk2ft1waWFMteKuOOvEr/7ChGdLSbP
    4AtjxCJ9C81IBIforgOSF399F1wLaXPa6NK6dkrJ10KSherGZDDaUPr1SUaZkO+F
    57VXKo7zzLp9rMWdZpeC0E0CgYAcVAjTNx5Xl72uPGsVF1NUQ1vN5ft6/2f2h7H5
    7iuIgjEhZdIXFGWIAtV8xKZVx0VGpkzqgmK41OBXw3Jj1wxmY91S3XlQej7XJf6F
    P+lP1qOX1z9yUdeft/I4GPQpNNo6jAcaEMROVGnOSqgLv7lllpmIx5LnEwLhqlkf
    MmiSBQKBgQCtdB/7KbFnxX1rXeI9C1QTki6KJt6yN0UBv+3tQDBekPh89vsOyfBQ
    Ubvkg+cACkQwaXl8ArcitSz+Id+Mylu4jrEXTj9VbpJ5d0SQtKdGIZvd3mvatGZX
    tMPYlRLfG7j7Qoi4Wn3QI5SpQgVVSh3y1eejp5MbC97y82l5F4etzg==
    -----END RSA PRIVATE KEY-----
    -----BEGIN CERTIFICATE-----
    MIIDljCCAn4CCQD2F4J0d4f7rTANBgkqhkiG9w0BAQQFADCBjDEMMAoGA1UEChMD
    V0dVMQ0wCwYDVQQLEwRNU0lBMR4wHAYJKoZIhvcNAQkBFg90d2lsbDYzQHdndS5l
    ZHUxFzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MQswCQYDVQQIEwJVVDELMAkGA1UE
    BhMCVVMxGjAYBgNVBAMTEVRob21hcyBDIFdpbGxpYW1zMB4XDTEyMTExNjA2NTEx
    OFoXDTEzMTExNjA2NTExOFowgYwxDDAKBgNVBAoTA1dHVTENMAsGA1UECxMETVNJ
    QTEeMBwGCSqGSIb3DQEJARYPdHdpbGw2M0B3Z3UuZWR1MRcwFQYDVQQHEw5TYWx0
    IExha2UgQ2l0eTELMAkGA1UECBMCVVQxCzAJBgNVBAYTAlVTMRowGAYDVQQDExFU
    aG9tYXMgQyBXaWxsaWFtczCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEB
    ALzwniQTgM79kwIOIqo2GE+Ufl56F0E/Rg956Ho6FoT9tBhvvQfI1SDvRzsrMGYP
    2VDAT7I7HEUnwFPMF7YAiLbK/6LlKDv0D+JyP3q9k/ENsZd0Z33KZ3HrJV1sdZZK
    iCv7isHKJ7xJgxQq+9ecpBG2Y9Vr5rLg8WPqnjvnaI60PN+NEsWnFNr/u6rBfv9R
    CQBtbLG6nhq5rxd67cWYR1hLpQg/S40AYcJBMIOApDN96YzDFXUM269JdHLSMgbt
    ZN8F4XeGljNbuVT8BTKOSydDUWWNJA593GLMSQ+OJVzVuwF99JzUwG0TcmPYgcQn
    Vav2t9igeXq7bUspcFTHQX8CAwEAATANBgkqhkiG9w0BAQQFAAOCAQEAAoOfoSSo
    sN78pSuDAAD95AgATzmCFxLvV9LWrIkFOF6mLwpnvR9UvjoOfQRWjVNnHKZ+1SPw
    oRo9KnMaWBltnXgKktfNYsSqCgZ889VaFlZfOL7TTq+jWqG9sRNXcJHkC+o07cxS
    IZSXNwjV+c6NMXUJWTf6OWKLcBmQ3Ze0hwiyW06QZvSd7psYqAH4AJHyiOXROG44
    eNtbFWxQ5Gg/tgXnEr/vbr2twXuflmmoNkjh94mR9Vf0QJY6/1I6qYkJ4n+xX2kR
    yisfvWztlYT+1m/z8jzeJtAkxRIloTIacLrIjaQKpKKsL4rAb0Fd0SUPP6HkIt72
    +vQ4jwHbxRhV7g==
    -----END CERTIFICATE-----


    Your example has us storing the results of an interpreted certificate using a command like this:

    openssl x509 -noout -in my2048cert.pem -text

    Then picking out the dates, which from the command line we could do like this:

    openssl x509 -noout -in certificate.pem -dates

    What I'm hoping to find is a way to use SQL or PL/SQL to pick the text out of the certificate itself then do just what you have done to pick out the expiration date. In other words, we will need to simulate openssl functionality using SQL or PL/SQL.


    Thank you for your help.
  • 3. Re: SQL to extract specific attributes from an x509 digital certificate
    jeneesh Guru
    Currently Being Moderated
    Are you storing it as a column in a table?

    What is the datatype of the column?

    or, are you expecting to run OS command using PL/SQL - The below links may help you :
    Executing Operating System Commands from PL/SQL

    Access operating system command output from SQL or PL/SQL

    Edited by: jeneesh on Nov 16, 2012 12:46 PM
  • 4. Re: SQL to extract specific attributes from an x509 digital certificate
    ltps Newbie
    Currently Being Moderated
    Thank you. I am storing the data in a column of CLOB or large VARCHAR2. Yes, I am open to calling OS commands using PL/SQL, but as a second option, if there is no way to achieve the result without that.
  • 5. Re: SQL to extract specific attributes from an x509 digital certificate
    odie_63 Guru
    Currently Being Moderated
    You can use a small Java stored procedure to extract the required fields.

    http://docs.oracle.com/javase/6/docs/api/java/security/cert/CertificateFactory.html
    http://docs.oracle.com/javase/6/docs/api/java/security/cert/X509Certificate.html
    SQL> create table cert_storage (id integer, cert clob);
     
    Table created
     
    SQL> insert into cert_storage values (1,
      2  '-----BEGIN CERTIFICATE-----
      3  MIIDljCCAn4CCQD2F4J0d4f7rTANBgkqhkiG9w0BAQQFADCBjDEMMAoGA1UEChMD
      4  V0dVMQ0wCwYDVQQLEwRNU0lBMR4wHAYJKoZIhvcNAQkBFg90d2lsbDYzQHdndS5l
      5  ZHUxFzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MQswCQYDVQQIEwJVVDELMAkGA1UE
      6  BhMCVVMxGjAYBgNVBAMTEVRob21hcyBDIFdpbGxpYW1zMB4XDTEyMTExNjA2NTEx
      7  OFoXDTEzMTExNjA2NTExOFowgYwxDDAKBgNVBAoTA1dHVTENMAsGA1UECxMETVNJ
      8  QTEeMBwGCSqGSIb3DQEJARYPdHdpbGw2M0B3Z3UuZWR1MRcwFQYDVQQHEw5TYWx0
      9  IExha2UgQ2l0eTELMAkGA1UECBMCVVQxCzAJBgNVBAYTAlVTMRowGAYDVQQDExFU
     10  aG9tYXMgQyBXaWxsaWFtczCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEB
     11  ALzwniQTgM79kwIOIqo2GE+Ufl56F0E/Rg956Ho6FoT9tBhvvQfI1SDvRzsrMGYP
     12  2VDAT7I7HEUnwFPMF7YAiLbK/6LlKDv0D+JyP3q9k/ENsZd0Z33KZ3HrJV1sdZZK
     13  iCv7isHKJ7xJgxQq+9ecpBG2Y9Vr5rLg8WPqnjvnaI60PN+NEsWnFNr/u6rBfv9R
     14  CQBtbLG6nhq5rxd67cWYR1hLpQg/S40AYcJBMIOApDN96YzDFXUM269JdHLSMgbt
     15  ZN8F4XeGljNbuVT8BTKOSydDUWWNJA593GLMSQ+OJVzVuwF99JzUwG0TcmPYgcQn
     16  Vav2t9igeXq7bUspcFTHQX8CAwEAATANBgkqhkiG9w0BAQQFAAOCAQEAAoOfoSSo
     17  sN78pSuDAAD95AgATzmCFxLvV9LWrIkFOF6mLwpnvR9UvjoOfQRWjVNnHKZ+1SPw
     18  oRo9KnMaWBltnXgKktfNYsSqCgZ889VaFlZfOL7TTq+jWqG9sRNXcJHkC+o07cxS
     19  IZSXNwjV+c6NMXUJWTf6OWKLcBmQ3Ze0hwiyW06QZvSd7psYqAH4AJHyiOXROG44
     20  eNtbFWxQ5Gg/tgXnEr/vbr2twXuflmmoNkjh94mR9Vf0QJY6/1I6qYkJ4n+xX2kR
     21  yisfvWztlYT+1m/z8jzeJtAkxRIloTIacLrIjaQKpKKsL4rAb0Fd0SUPP6HkIt72
     22  +vQ4jwHbxRhV7g==
     23  -----END CERTIFICATE-----')
     24  ;
     
    1 row inserted
     
    SQL> commit;
     
    Commit complete
     
    SQL> create or replace and compile java source named testx509src as
      2  import java.security.cert.*;
      3  import java.io.*;
      4  import java.sql.*;
      5  import oracle.sql.CLOB;
      6  import oracle.sql.TIMESTAMPTZ;
      7  
      8  public class TestX509 {
      9      public static TIMESTAMPTZ getExpirationDate(CLOB cert)
     10              throws SQLException, IOException, CertificateException {
     11  
     12          Connection conn = (Connection) DriverManager.getConnection("jdbc:default:connection:");
     13          BufferedInputStream is = new BufferedInputStream(cert.getAsciiStream());
     14  
     15          CertificateFactory cf = CertificateFactory.getInstance("X.509");
     16          X509Certificate c = (X509Certificate) cf.generateCertificate(is);
     17          is.close();
     18  
     19          return new TIMESTAMPTZ( conn, new Timestamp(c.getNotAfter().getTime()) );
     20  
     21      }
     22  }
     23  /
     
    Java created
     
    SQL> CREATE OR REPLACE FUNCTION CERT_GetExpirationDate(cert in clob)
      2  RETURN timestamp with time zone
      3  AS LANGUAGE JAVA
      4  NAME 'TestX509.getExpirationDate(oracle.sql.CLOB) return oracle.sql.TIMESTAMPTZ';
      5  /
     
    Function created
     
    SQL> select CERT_GetExpirationDate(cert)
      2  from cert_storage
      3  where id = 1;
     
    CERT_GETEXPIRATIONDATE(CERT)
    --------------------------------------------------------------------------------
    16-NOV-13 07.51.18.000000000 AM +01:00
     
    If you need to access more fields, it would be better to wrap them into an Oracle object type and have the Java method return an instance of that object.
  • 6. Re: SQL to extract specific attributes from an x509 digital certificate
    ltps Newbie
    Currently Being Moderated
    Very nice. Exactly right. Thank you very much.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points