Getting "ORA-29024: Certificate validation failure", when using UTL_HTTP to POST to Google Checkout URL
Jimbo1
I'm running Oracle 10g Release2, XE Edition.
I'm trying to use the UTL_HTTP Package to submit a POST request to the
Google Checkout Test Server.
Now, if I run my script to try to send a POST request to http://www.google.co.uk,
it works, i.e.
DECLARE
l_url VARCHAR2(200) := 'http://www.google.co.uk';
l_request UTL_HTTP.REQ;
l_response UTL_HTTP.RESP;
BEGIN
l_request := UTL_HTTP.BEGIN_REQUEST( l_url , 'POST' );
UTL_HTTP.END_REQUEST( l_request ) ;
END;
/
However, if I run it to post to the "https://"-prefixed address, the
following script will get an ORA-29024 Exception, i.e.
DECLARE
l_merchant_url VARCHAR2(200) := 'https://sandbox.google.com/
checkout/api/checkout/v2/reports/Merchant/'||'&MERCHANT_ID';
l_request UTL_HTTP.REQ;
l_response UTL_HTTP.RESP;
BEGIN
l_request := UTL_HTTP.BEGIN_REQUEST( l_merchant_url, 'POST' );
UTL_HTTP.END_REQUEST( l_request ) ;
END;
/
The full exception string I'm getting is:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1029
ORA-29024: Certificate validation failure
ORA-06512: at line 8
I entered my sandbox MERCHANT_ID when prompted for the substitution
variable above.
Now, the questions I have are:
1. Do I need to obtain some kind of SSL Certificate from Google?
2. If the answer to the last question is "Yes", do I then need to
register it in the database using Wallet Manager?
Thanks in advance for any help or advice.
James
joel garry
> Hello there,
>
> I'm running Oracle 10g Release2, XE Edition.
>
> I'm trying to use the UTL_HTTP Package to submit a POST request to the
> Google Checkout Test Server.
>
I claim to know nothing about it, but see
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_http.htm#i998681
or google set_wallet.
jg
--
@home.com is bogus.
http://www3.signonsandiego.com/stories/2009/feb/18/1b18stanford2200-sec-accuses-financier-massive-fra/?uniontrib
Jimbo1
>
> or google set_wallet.
>
> jg
> --
Thanks Joel,
I'm actually on my way down that route at the moment. I'm starting to
take a look at the Oracle Advanced Security manual and how to set up
wallets for use with SSL. I'll post up my solution on this thread, if
I can solve it.
I'm into very new territory here for me. I'd describe myself as a very
competent Oracle database developer, but very green when it comes to
networks and communication protocols, etc........... I usually work as
a contractor in medium - large companies, so all that kind of thing is
dealt with by network people. This piece of work is for a friend's
small business website, built using APEX and running on an Oracle XE
database. The only techies are me and him, and we're both primarily
database developers.
This is certainly going to be an interesting challenge! ;)
Jimbo1
I've solved this problem and am now successfully talking to the Google
Sandbox Server with UTL_HTTP via a Secure Socket Layer (SSL).
I'm going to roughly document my approach here, as this entire
experience has been pretty frustrating and has taken me around 8 hours
to reach the solution.
Firstly, it is absolutely essential to obtain the SSL certificates for
Google's Live Checkout Server, and the Sandbox (Test) Server. You can
obtain those via Internet Explorer; the instructions can be found on
this blog: http://eternal-donut.blogspot.com/2008/07/tip-5-using-utlhttp-and-ssl.html
The URLs I obtained my certificates from were:
1. https://checkout.google.com/checkout/sell/ (Live Server)
2. https://sandbox.google.com/checkout/sell/ (Test Server)
The only thing to bear in mind is that when it comes to choosing an
Export File Format for the Certificate, do NOT choose the "Base-64
encoded X.509 (.CER)" format. Instead, choose the "Cryptographic
Message Syntax Standard - PKCS #7 Certificates (.P7B)" option, and
tick the checkbox for "Include all certificates in the certification
path if possible".
You can also export the certificate from Firefox, via the "Page Info"
Window.
After obtaining the certificates, use Oracle's Wallet Manager Software
to import them into your Wallet. Instructions for that can be found in
the Oracle Advanced Security Manual.
When it comes to testing using UTL_HTTP, you MUST ensure that you open
a new session after importing the SSL certificates into your Wallet,
as I've learned (the hard way) that existing sessions point to the
wallet contents that were present when the session was opened. If you
don't realise/know this, it can cause a lot of additional frustration
during testing, when you keep getting the ORA-29024 exception AFTER
you've imported the SSL certificates................. ;)
Once you've got the Wallet set up, run the following test script to
ensure you can see the wallet contents from the database session:
SELECT UTL_HTTP.REQUEST('https://metalink.oracle.com',
NULL,
'file:'||'<wallet_location>',
'<wallet_password>')
FROM dual;'
NOTE 1: <wallet_location> represents the path to your wallet file
that you set up in Wallet Manager. In my case, it's "c:\oraclexe
\wallets\". Do NOT include the wallet filename and extenstion in this
path.
NOTE 2: <wallet_password> is the password to your wallet that you set
up in Wallet Manager.
If you've configured everything correctly, you will get an HTML
response back from the metalink server.
For Google Checkout, all I then needed to do was run this test query:
SELECT UTL_HTTP.REQUEST('https://sandbox.google.com/checkout/api/
checkout/v2/checkout/Merchant/&MY_MERCHANT_ID',
NULL,
'file:'||'<wallet_location>',
'<wallet_password>')
FROM dual;
If this is successful, you will get an HTML page back from the Google
Checkout Sandbox Server.
Hopefully, this post will be of great assistance to anybody else who's
trying to get Oracle talking to Google Checkout Server to Server.
Palooka
Palooka
Jimbo1
>
> > The URLs I obtained my certificates from were:
>
> > 1.https://checkout.google.com/checkout/sell/(Live Server)
No worries; my pleasure. ;)
Just one little thing I overlooked. If you want to use Firefox to
capture SSL certificates, you must have the "Certificate Viewer Plus"
Add-on installed.