Skip to Main Content
  • Questions
  • Retrieve data from Have I Been Pawned compromised password URL, create APEX REST Service

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Joseph.

Asked: April 21, 2021 - 11:26 am UTC

Last updated: May 03, 2021 - 4:32 am UTC

Version: 20

Viewed 1000+ times

You Asked

I am using Oracle Cloud Infrastructure (OCI) Autonomous Transaction Processing (ATP) I would like to be able to check for a password's existence in the Have I been pawned compromised password list without loading the file into oracle. (SHA1 encrypt password a user is trying to change to and then compare to list to see if it is in a public database of exposed passwords to warn user) They offer a url: https://api.pwnedpasswords.com/range/ that returns all password hashes (SHA1) that start with the first 5 characters provided that can be checked for the password in question. I have unsuccessfully tried various combinations to setup a web service in APEX. It works fine in Postman.
I created an APEX Rest source with the https://api.pwnedpasswords.com/range/:hash link and it populates the :hash variable with a suitable hex example (21BD1). The URL returns a series of complete hex strings like:
0018A45C4D1DEF81644B54AB7F969B88D65:3
00D4F6E8FA6EECAD2A3AA415EEC418D38EC:2
011053FD0102E94D6AE2F8B83D76FAF94F6:1
012A7CA357541F0AC487871FEEC1891C49C:2
0136E006E24E7D152139815FB0FC6A50B15:3
01A85766CD276B17DE6DA022AA3CADAC3CE:3
However, it errors saying it is unable to parse it as XML or JSON. I tried manually defining the data profile as a varchar2(100), but same error.

I also tried utl.http request, but APEX gave me insufficient ACL privilege's and it appears to me since I am using ATP it won't let me do that? If that would work it would be an acceptable solutions as well.
BTW, I tried the livesql link this morning and it gave a bad gateway error: The connection pool named: |apex|| is not correctly configured, due to the following error(s): Exception occurred while getting connection: oracle.ucp.UniversalConnectionPoolException: Cannot get Connection from Datasource: java.sql.SQLRecoverableException: IO Error: Unknown host specified

Thanks
Joe

and Connor said...

You can grant ACL permissions on ATP, so something like this should get you started

<code>
SQL> begin
2 dbms_network_acl_admin.append_host_ace(
3 host => 'api.pwnedpasswords.com',
4 ace => xs$ace_type(
5 privilege_list => xs$name_list('connect','resolve'),
6 principal_name => 'ADMIN',
7 principal_type => xs_acl.ptype_db));
8 commit;
9 end;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> set serverout on
SQL> declare
2 l_req utl_http.req;
3 l_ret utl_http.resp;
4 l_data varchar2(32767);
5 begin
6 utl_http.set_wallet('');
7 l_req := utl_http.begin_request(' https://api.pwnedpasswords.com/range/21BD1' );
8 l_ret := utl_http.get_response(l_req);
9
10 begin
11 loop
12 utl_http.read_text(l_ret,l_data,32766);
13 dbms_output.put_line(l_data);
14 end loop;
15 exception
16 when utl_http.end_of_body then
17 utl_http.end_response(l_ret);
18 end;
19 exception
20 when others then
21 utl_http.end_response(l_ret);
22 raise;
23 end;
24 /
0018A45C4D1DEF81644B54AB7F969B88D65:3
00D4F6E8FA6EECAD2A3AA415EEC418D38EC:2
011053FD0102E94D6AE2F8B83D76FAF94F6:1
012A7CA357541F0AC487871FEEC1891C49C:2
0136E006E24E7D152139815FB0FC6A50B15:3
01A85766CD276B17DE6DA022AA3CADAC3CE:3
01B13C6C273B75F11356FF4EBD41D21B521:1
024067E46835A540D6454DF5D1764F6AA63:3
02551CADE5DDB7F0819C22BFBAAC6705182:1
025B243055753383B479EF34B44B562701D:2
02A56D549B5929D7CD58EEFA97BFA3DDDB3:8
02F1C470B30D5DDFF9E914B90D35AB7A38F:5
03052B53A891BDEA802D11691B9748C12DC:6
03268C190C25E8B9B25DCAEB4DA4FA86FD7:4
041F514246F050C31B6B5B36CD626C398CA:1
043542C12858C639D087F8F500BCDA56267:6
044768D0FA7FFF8A0E83B45429D483FF243:3
046777167728C6DB88EFB85C768111B5D54:1
0468315C5C239C84A8EDB77F1DD6017B045:7
04901F088E4A095874751D4BA633A4A9F03:10
052AB1E9A6B0361650FEBD3927F358AB32E:2
05401579254EAF7C32F3BE70F69CEF3FC93:1
0606685ED7BAF525819C72E14B36B3FF7A2:1
06937FFB24E094C5EDDB7250A64D2042FDA:1
07E083A008CFDB5DE37009BD4142223A61B:2
07F8A2FC956E4C3C5DF4902C9EC117CF5BD:2
0870E31E0A48BA8E294588C3999F84DF11C:1
0940EEA53606EAAAB7D6349BC912471B0C0:2
09F0E95AD04208708D0A089C5EAA6F2921F:1
0A3BEE7D598DF887735594C775B289FCF78:1
0A5247632B48D93F893DB9A02CFC4AE9A62:1
0AAE510116A031329E55558FC175C6EC5E6:1
0AE1C55CE2A1A5244C99E02B232DB1B49D1:7
0AE7EB158C458747CC88CAC87B2A65ED110:2
...
...

<./code>

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks, and small typo

Joe Kerr, April 27, 2021 - 2:49 pm UTC

Thanks! This works great.

One slight typo in the URL on line 7. There is a space between the beginning quote and https that causes an improper URL format error.
Connor McDonald
May 03, 2021 - 4:32 am UTC

Ah...thats a bug in our asktom formatter - it rewrote the link as a true http link and added the space.

I'll look at fixing that bug. Thanks for letting us know.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library