Hi "Tom",

We're just beginning to test our application in earnest with Oracle 12.2 and have run into a significant problem with a coding change to UTL_HTTP for handling SSL sites.

There is a new parameter called "https_host" for which the application must supply further information in order to avoid the following error:
ORA-24263: Certificate of the remote server does not match the target address

I have found that the value for this is impossible to predict. For example, to connect to: https://www.oracle.com, I needed to specify 'www-cs-01.oracle.com' as neither 'www.oracle.com' or '*.oracle.com' worked. I have no reason to believe that 'www-cs-01.oracle.com' will always work for https://www.oracle.com (Oracle example aside, I can't apply any such presumptions to other systems).

For carrying out specific connections to individual URLs, this isn't a huge deal as we can use openssl to get the CN from the certificate. For example:

echo -n | openssl s_client -connect www.oracle.com:443

Our application, however, has an overnight PL/SQL batch job which validates the URLs that have been stored by the cataloguers so that they can verify that they have not changed and have been entered syntactically correct etc. This overnight task could therefore lead to thousands of URLs being reported as problematic when the end user's experience in a browser is entirely without problem, unless I can work out how to get at the certificate information programmatically in order to supply the value for 'https_host'. We produce this in PL/SQL so that it runs on our Windows as well as Linux and Solaris deployments.

One commonly used site for our customers has the URL: ebookcentral.proquest.com. For this I need to specify an https_host of 'ssl362589.cloudflaressl.com'. This clearly bears no evident relation to the URL given. We cannot ask our customers to enter this certificate information for a variety of reasons (staff resources, expertise, ...).

We do want to validate the SSL certificates for such URLs. We also cannot ignore the error above if there has been an interception.

We can't be the only application to attempt to validate ssl certificates automatically. There are browser add-ons designed to protect the user from malicious sites on-the-fly. Do browsers (or such add-ons) simply not go to this level of detail?

Can you suggest a way that we can continue to operate an effective automated process for checking URLs?

Thank you,
Tim

[oracle@lin122 tmp]$ cat host.dat
www.oracle.com
www.outlook.com

[oracle@lin122 tmp]$ cat ssl_check.sh
#/bin/bash
/usr/bin/cat $1 | while read HOST 
do
/usr/bin/echo -n | /usr/bin/openssl s_client -connect $HOST:443 2>/dev/null | \
  /usr/bin/awk '/Certificate chain/ , /Server certificate/' | \
  /usr/bin/grep ' 0 s:' | /usr/bin/awk -v H=$HOST -F= '{print H","$NF}'
done

create or replace directory tmp as '/tmp';

create table secure_host (
  name     varchar2(50),
  ssl_name varchar2(50)
)
organization external (
  type oracle_loader
  default directory tmp
  access parameters (
    records delimited by newline
    preprocessor tmp:'ssl_check.sh'
    fields terminated by ','
    missing field values are null
    (
      name     char(50),
      ssl_name char(50)
    )
  )
  LOCATION ('host.dat')
)
reject limit unlimited;

SQL> select * from secure_host;

NAME         SSL_NAME
-------------------------------------------------- --------------------------------------------------
www.oracle.com        www-cs-01.oracle.com
www.outlook.com        outlook.com