6 Replies Latest reply: Nov 20, 2012 4:11 PM by ltps RSS

    SQL to extract specific attributes from an x509 digital certificate

    ltps
      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
          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
            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
              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
                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
                  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
                    Very nice. Exactly right. Thank you very much.