Home » SQL & PL/SQL » SQL & PL/SQL » ORA-29268, 400 - Bad Request trying to send Tweet using REST Api from PL/SQL using UTL_HTTP.
ORA-29268, 400 - Bad Request trying to send Tweet using REST Api from PL/SQL using UTL_HTTP. [message #682590] |
Fri, 30 October 2020 08:42  |
 |
scotthillierfrontera
Messages: 5 Registered: October 2020
|
Junior Member |
|
|
Hey guys
I'm trying to write a PL/SQL function that lets me send a tweet using REST API from Oracle PL/SQL
I've successfully tested this using Postman, and I'm trying to recreate now in Oracle PL/SQL but whatever I do I get a 400 Bad Request when I try and use the utl_http.set_header command.
In the details below, I've changed the values of the various keys, so it's not that, I'm just hiding them here.
I know its not ACL problems as I've got that set up as initially I had ACL errors which after creating ACL I now dont have.
In the Postman console, this is what I have :
POST xttps://api.twitter.com/1.1/statuses/update.json?status=Test%20Tweet
(orafaq thinks thats a link thats why Ive changed it to xttps)
Request Headers
Authorization: OAuth oauth_consumer_key="XXX",oauth_token="YYY",oauth_signature_method="HMAC-SHA1",oauth_timestamp="1604040422",oauth_nonce="uAmCX0NFeym",oauth_version="1.0",oauth_signature="KhtY25DnOvYHgqK6SG83UvMU5y4%3D"
User-Agent: PostmanRuntime/7.26.1
Accept: */*
Cache-Control: no-cache
Postman-Token: 8901fa98-f367-4219-a33e-23d38d316650
Host: api.twitter.com
Accept-Encoding: gzip, deflate, br
Connection: keep-alive
Cookie: personalization_id="v1_QQUZtSSL3H/TMxogtXMEcw=="; lang=en; guest_id=v1%3A160397589280743625
Content-Length: 0
I'm trying to write a PL/SQL function that lets me send a tweet using REST API from Oracle PL/SQL
I've successfully tested this using Postman, and I'm trying to recreate now in Oracle PL/SQL but whatever I do I get a 400 Bad Request when I try and use the utl_http.set_header command.
In the details below, I've changed the values of the various keys, so it's not that, I'm just hiding them here.
I know its not ACL problems as I've got that set up as initially I had ACL errors which after creating ACL I now dont have.
In the Postman console, this is what I have :
POST xttps://api.twitter.com/1.1/statuses/update.json?status=Test%20Tweet
Request Headers
Authorization: OAuth oauth_consumer_key="XXX",oauth_token="YYY",oauth_signature_method="HMAC-SHA1",oauth_timestamp="1604040422",oauth_nonce="uAmCX0NFeym ",oauth_version="1.0",oauth_signature="KhtY25DnOvYHgqK6SG83UvMU5y4%3D"
User-Agent: PostmanRuntime/7.26.1
Accept: */*
Cache-Control: no-cache
Postman-Token: 8901fa98-f367-4219-a33e-23d38d316650
Host: api.twitter.com
Accept-Encoding: gzip, deflate, br
Connection: keep-alive
Cookie: personalization_id="v1_QQUZtSSL3H/TMxogtXMEcw=="; lang=en; guest_id=v1%3A160397589280743625
Content-Length: 0
and that works fine.
Below is the code that I have trying to recreate the same thing from PL/SQL, that errors every time.
I've never done this before so apologies if I'm missing something obvious, but I could really do with some help! Thanks.
DECLARE
http_req utl_http.req;
http_resp utl_http.resp;
l_http_method VARCHAR2(5) := 'POST';
l_content VARCHAR2(140) := utl_url.escape('SAHTestTweet');
l_oauth_request_token_url CONSTANT VARCHAR2(500) := 'xttps://api.twitter.com/1.1/statuses/update.json';
l_oauth_consumer_key CONSTANT VARCHAR2(500) := 'XXX';
l_oauth_consumer_secret CONSTANT VARCHAR2(500) := 'YYY';
l_oauth_token CONSTANT VARCHAR2(500) := 'AAA';
l_oauth_secret CONSTANT VARCHAR2(500) := 'BBB';
l_oauth_nonce VARCHAR2(500);
l_oauth_signature_method CONSTANT VARCHAR2(10) := utl_url.escape('HMAC-SHA1');
l_oauth_timestamp VARCHAR2(100);
l_oauth_version CONSTANT VARCHAR2(5) := utl_url.escape('1.0');
l_oauth_base_string VARCHAR2(2000);
l_oauth_key VARCHAR2(500) := l_oauth_consumer_secret
|| '&'
|| l_oauth_secret;
l_oauth_header VARCHAR2(2000);
l_sig_mac RAW(2000);
l_base64_sig_mac VARCHAR2(100);
l_update_send VARCHAR2(2000);
l_line VARCHAR2(1024);
resp_name VARCHAR2(256);
resp_value VARCHAR2(1024);
l_random VARCHAR2(25);
BEGIN
-- Oracle Wallet
utl_http.set_wallet('file:/home/app/oracle/product/11.2.0/dbhome_1/owm/wallets/oracle', 'PASSWORDHERE');
-- Get the timestamp
SELECT
utl_url.escape((sysdate - TO_DATE('01-01-1970', 'DD-MM-YYYY')) *(86400))
INTO l_oauth_timestamp
FROM
dual;
-- RANDOM oauth_nonce
SELECT
dbms_random.string('A', 25)
INTO l_random
FROM
dual;
SELECT
utl_url.escape(utl_encode.base64_encode(utl_i18n.string_to_raw(l_random, 'AL32UTF8')))
INTO l_oauth_nonce
FROM
dual;
-- Build up base string
l_oauth_base_string := l_http_method
|| ' '
|| utl_url.escape(l_oauth_request_token_url)
|| '?'
|| 'status'
|| '='
|| l_content
|| '&'
|| utl_url.escape('oauth_consumer_key'
|| '='
|| l_oauth_consumer_key
|| '&'
|| 'oauth_nonce'
|| '='
|| l_oauth_nonce
|| '&'
|| 'oauth_signature_method'
|| '='
|| l_oauth_signature_method
|| '&'
|| 'oauth_timestamp'
|| '='
|| l_oauth_timestamp
|| '&'
|| 'oauth_token'
|| '='
|| l_oauth_token
|| '&'
|| 'oauth_version'
|| '='
|| l_oauth_version);
dbms_output.put_line(l_oauth_base_string);
-- create signature
l_sig_mac := dbms_crypto.mac(utl_i18n.string_to_raw(l_oauth_base_string, 'AL32UTF8'), dbms_crypto.hmac_sh1,
utl_i18n.string_to_raw(l_oauth_key, 'AL32UTF8'));
dbms_output.put_line('Combined sig: ' || l_oauth_key);
-- encode signature
l_base64_sig_mac := utl_raw.cast_to_varchar2(utl_encode.base64_encode(l_sig_mac));
dbms_output.put_line('MAC Signature (Base64-encoded): ' || l_base64_sig_mac);
-- add tweet to end of URL
l_update_send := l_oauth_request_token_url
|| '?status='
|| l_content;
dbms_output.put_line('BEGINNING REQUEST ' || l_update_send);
http_req := utl_http.begin_request(l_update_send,
l_http_method,
utl_http.http_version_1_1);
dbms_output.put_line('REQUEST BEGUN URL ' || l_update_send);
utl_http.set_response_error_check(true);
utl_http.set_detailed_excp_support(true);
utl_http.set_body_charset(http_req, 'UTF-8');
-- Create Header
l_oauth_header := 'OAuth '
|| 'oauth_consumer_key="'
|| l_oauth_consumer_key
|| '",'
|| 'oauth_token="'
|| l_oauth_token
|| '",'
|| 'oauth_signature_method="'
|| l_oauth_signature_method
|| '",'
|| 'oauth_timestamp="'
|| l_oauth_timestamp
|| '",'
|| 'oauth_nonce="'
|| l_oauth_nonce
|| '",'
|| 'oauth_version="'
|| l_oauth_version
|| '",'
|| 'oauth_signature="'
|| utl_url.escape(l_base64_sig_mac)
|| '"';
dbms_output.put_line('HEADER: ' || l_oauth_header);
-- Set Header Fields
utl_http.set_header(r => http_req, name => 'Authorization', value => l_oauth_header);
(The line above is where it falls over)
utl_http.set_header(r => http_req, name => 'Accept-Encoding', value => 'gzip, deflate, br');
utl_http.set_header(r => http_req, name => 'Connection', value => 'keep-alive');
utl_http.set_header(r => http_req, name => 'Content-Length', value => '0');
utl_http.set_transfer_timeout(to_char('60'));
utl_http.write_text(r => http_req, data => l_content);
http_resp := utl_http.get_response(r => http_req);
dbms_output.put_line('GETTING RESPONSE HEADERS! ');
FOR i IN 1..utl_http.get_header_count(http_resp) LOOP
utl_http.get_header(http_resp, i, resp_name, resp_value);
dbms_output.put_line(resp_name
|| ': '
|| resp_value);
END LOOP;
dbms_output.put_line('Getting content:');
BEGIN
LOOP
utl_http.read_line(http_resp, resp_value, true);
dbms_output.put_line(resp_value);
END LOOP;
EXCEPTION
WHEN utl_http.end_of_body THEN
dbms_output.put_line('No more content.');
END;
utl_http.end_response(r => http_resp);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('HTTP ERROR: ' || sqlerrm);
dbms_output.put_line(utl_http.get_detailed_sqlerrm);
END;
The DBMS output I'm getting looks right generally :
POST xttps://api.twitter.com/1.1/statuses/update.json?status=SAHTestTweet&oauth_consumer_key=XXX&oauth_nonce=53334A3365474E5A636D52765A304E3353323532536E4252533270515330523654513D3D&oauth_signature_method=HMAC-SHA1&oauth_timestamp=1604062895&oauth_token=YYY&oauth_version=1.0
Combined sig: g9nkIeE1j5VKUam1hCMXJuanMAXWuhvq8wDQugmQh9fzjnzVLQ&o3m9EgdWOLbcjN7KUACeXXdWyVyrvB8Zx1bCCgt7Z1Shi
MAC Signature (Base64-encoded): j3fvwUjuy6KaHxY487tHMMcwP3U=
BEGINNING REQUEST xttps://api.twitter.com/1.1/statuses/update.json?status=SAHTestTweet
REQUEST BEGUN URL xttps://api.twitter.com/1.1/statuses/update.json?status=SAHTestTweet
HEADER: OAuth oauth_consumer_key="XXX",oauth_token="YYY",oauth_signature_method="HMAC-SHA1",oauth_timestamp="1604062895",oauth_nonce="53334A3365474E5A636D52765A304E3353323532536E4252533270515330523654513D3D",oauth_version="1.0",oauth_signature="j3fvwUjuy6KaHxY487tHMMcwP3U="
HTTP ERROR: ORA-29268: HTTP client error 400 - Bad Request
I'm guessing it's only something small as everything seems to match the Postman console.
Any ideas anyone ?
|
|
|
|
|
|
Re: ORA-29268, 400 - Bad Request trying to send Tweet using REST Api from PL/SQL using UTL_HTTP. [message #682619 is a reply to message #682610] |
Tue, 03 November 2020 05:09   |
 |
scotthillierfrontera
Messages: 5 Registered: October 2020
|
Junior Member |
|
|
Thanks for trying John - that never would have worked because I'd altered the tokens in this example code as they have to remain hidden.
I finally got to the answer, which I'll put here in case anyone else finds themselves in the same position.
Turns out the Twitter documentation on how to build the signature is significantly incorrect (at least when calling from Oracle).
The document says your signature header should have "include entities" in it, this causes the code not to work.
Remove this from the signature string, start with consumer key.
Next, the Twitter documentation says the signing key, which is consumer secret & oauth token secret should be percent escaped either side of the &, this is wrong, these 2 parts should NOT be escaped.
Finally, the list of header fields MUST begin with Authorization, the documentation shows Accept, Connection, User-Agent, Content-Type coming before Authorization and this does not work.
This leaves my WORKING code as :
DECLARE
http_req utl_http.req;
http_resp utl_http.resp;
l_http_method VARCHAR2(5) := 'POST';
l_tweet_content VARCHAR2(140) := 'Test2' ;
l_oauth_request_token_url CONSTANT VARCHAR2(500) := 'https://api.twitter.com/1.1/statuses/update.json';
l_oauth_consumer_key CONSTANT VARCHAR2(500) := '1112QVhZR8Mpv60B0Zzdaqzxh';
l_oauth_consumer_secret CONSTANT VARCHAR2(500) := '222kIeE1j5VKUam1hCMXJuanMAXWuhvq8wDQugmQh9fzjnzVLQ';
l_oauth_token CONSTANT VARCHAR2(500) := '3331548309900005376-bn4mDbpI00d0wGW4ikEvSR7xmuIA3d';
l_oauth_token_secret CONSTANT VARCHAR2(500) := '4449EgdWOLbcjN7KUACeXXdWyVyrvB8Zx1bCCgt7Z1Shi';
l_oauth_signature_method CONSTANT VARCHAR2(10) := 'HMAC-SHA1';
l_oauth_nonce VARCHAR2(500);
l_oauth_timestamp NUMBER;
l_oauth_version CONSTANT VARCHAR2(5) := '1.0';
l_sig_base_string VARCHAR2(2000);
l_sig_parameter_string VARCHAR2(2000);
l_signing_key VARCHAR2(500) := l_oauth_consumer_secret||'&'||l_oauth_token_secret;
l_oauth_header VARCHAR2(2000);
l_sig_mac RAW(2000);
l_oauth_signature VARCHAR2(100);
l_update_send VARCHAR2(2000);
l_line VARCHAR2(1024);
resp_name VARCHAR2(256);
resp_value VARCHAR2(4000);
l_random VARCHAR2(25);
BEGIN
-- Oracle Wallet
utl_http.set_wallet('file:/home/app/oracle/product/11.2.0/dbhome_1/owm/wallets/oracle', 'Oracle123!Oracle123!');
-- Get the timestamp
SELECT (sysdate - TO_DATE('01-01-1970', 'DD-MM-YYYY')) *(86400)
INTO l_oauth_timestamp
FROM dual;
-- RANDOM oauth_nonce
SELECT dbms_random.string('A', 25)
INTO l_random
FROM dual;
SELECT utl_encode.base64_encode(utl_i18n.string_to_raw(l_random, 'AL32UTF8'))
INTO l_oauth_nonce
FROM dual;
-- Build up parameter string for signature calcuclation
l_sig_parameter_string :=
utl_url.escape('oauth_consumer_key',TRUE)
||'='
||utl_url.escape(l_oauth_consumer_key ,TRUE)
||'&'
||utl_url.escape('oauth_nonce',TRUE)
||'='
||utl_url.escape(l_oauth_nonce,TRUE)
||'&'
||utl_url.escape('oauth_signature_method',TRUE)
||'='
||utl_url.escape(l_oauth_signature_method,TRUE)
||'&'
||utl_url.escape('oauth_timestamp',TRUE)
||'='
||utl_url.escape(TRUNC(l_oauth_timestamp),TRUE)
||'&'
||utl_url.escape('oauth_token',TRUE)
||'='
||utl_url.escape(l_oauth_token,TRUE)
||'&'
||utl_url.escape('oauth_version',TRUE)
||'='
||utl_url.escape(l_oauth_version,TRUE)
||'&'
||utl_url.escape('status',TRUE)
||'='
||utl_url.escape(l_tweet_content,TRUE)
;
l_sig_base_string := l_http_method
|| '&'
|| utl_url.escape (l_oauth_request_token_url,TRUE)
|| '&'
|| utl_url.escape (l_sig_parameter_string ,TRUE);
-- create signature
l_sig_mac := dbms_crypto.mac(utl_i18n.string_to_raw(l_sig_base_string, 'AL32UTF8'),
dbms_crypto.hmac_sh1,
utl_i18n.string_to_raw(l_signing_key, 'AL32UTF8')
);
-- bas64 encode signature
l_oauth_signature := utl_raw.cast_to_varchar2(utl_encode.base64_encode(l_sig_mac));
-- add tweet to end of URL
l_update_send := l_oauth_request_token_url
|| '?status='
|| utl_url.escape(l_tweet_content,TRUE);
http_req := utl_http.begin_request(l_update_send,
l_http_method,
utl_http.http_version_1_1);
utl_http.set_response_error_check(true);
utl_http.set_detailed_excp_support(true);
utl_http.set_body_charset(http_req, 'UTF-8');
utl_http.set_transfer_timeout(to_char('60'));
-- Create Authorization Header
l_oauth_header := 'OAuth '
|| utl_url.escape('oauth_consumer_key',TRUE)
|| '='
|| utl_url.escape(l_oauth_consumer_key,TRUE)
|| ', '
|| utl_url.escape('oauth_nonce',TRUE)
|| '='
|| utl_url.escape(l_oauth_nonce,TRUE)
|| ', '
|| utl_url.escape('oauth_signature',TRUE)
|| '='
|| utl_url.escape(l_oauth_signature,TRUE)
|| ', '
|| utl_url.escape('oauth_signature_method',TRUE)
|| '='
|| utl_url.escape(l_oauth_signature_method,TRUE)
|| ', '
|| utl_url.escape('oauth_timestamp',TRUE)
|| '='
|| utl_url.escape(TRUNC(l_oauth_timestamp),TRUE)
|| ', '
|| utl_url.escape('oauth_token',TRUE)
|| '='
|| utl_url.escape(l_oauth_token,TRUE)
|| ', '
|| utl_url.escape('oauth_version',TRUE)
|| '='
|| utl_url.escape(l_oauth_version,TRUE)
;
-- Set Header Fields
utl_http.set_header(r => http_req, name => 'Authorization', value => l_oauth_header);
utl_http.set_header(r => http_req, name => 'Accept', value => '*/*');
utl_http.set_header(r => http_req, name => 'Connection', value => 'keep-alive');
utl_http.set_header(r => http_req, name => 'Content-Length', value => '0');
utl_http.set_header(r => http_req, name => 'Content-Type', value => 'application/x-www-form-urlencoded');
utl_http.set_header(r => http_req, name => 'Host', value => 'api.twitter.com');
utl_http.set_header(r => http_req, name => 'Cache-Control', value => 'no-cache');
utl_http.write_text(r => http_req, data => utl_url.escape(l_tweet_content,TRUE));
http_resp := utl_http.get_response(r => http_req);
FOR i IN 1..utl_http.get_header_count(http_resp)
LOOP
utl_http.get_header(http_resp, i, resp_name, resp_value);
dbms_output.put_line(resp_name
|| ': '
|| resp_value);
END LOOP;
dbms_output.put_line('Getting content:');
BEGIN
LOOP
utl_http.read_line(http_resp, resp_value, true);
dbms_output.put_line(resp_value);
END LOOP;
EXCEPTION
WHEN utl_http.end_of_body
THEN dbms_output.put_line('No more content.');
END;
utl_http.end_response(r => http_resp);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('HTTP ERROR: ' || sqlerrm);
dbms_output.put_line(utl_http.get_detailed_sqlerrm);
END;
|
|
|
Re: ORA-29268, 400 - Bad Request trying to send Tweet using REST Api from PL/SQL using UTL_HTTP. [message #682620 is a reply to message #682619] |
Tue, 03 November 2020 05:14   |
John Watson
Messages: 8805 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
And it works for me, too 
Well, as far as it can:
cache-control: no-cache, no-store, max-age=0
content-length: 62
content-type: application/json; charset=utf-8
date: Tue, 03 Nov 2020 11:12:38 GMT
server: tsa_f
set-cookie: personalization_id="v1_1Z0inHJflCVlKII/8YwxSQ=="; Max-Age=63072000; Expires=Thu, 03 Nov 2022 11:12:38 GMT; Path=/; Domain=.twitter.com; Secure; SameSite=None
set-cookie: guest_id=v1%3A160440195815225618; Max-Age=63072000; Expires=Thu, 03 Nov 2022 11:12:38 GMT; Path=/; Domain=.twitter.com; Secure; SameSite=None
strict-transport-security: max-age=631138519
x-connection-hash: 1136378f9bfb21a845cf144ff32a0cc9
x-response-time: 103
Getting content:
{"errors":[{"code":89,"message":"Invalid or expired token."}]}
No more content.
PL/SQL procedure successfully completed.
|
|
|
|
Goto Forum:
Current Time: Fri Mar 31 05:20:56 CDT 2023
|