How to reverse an LDAP query?

Hello,

I have developed a (Oracle 9i) packaged funcion to get a user's email
address from their username in an LDAP server.
How can I reverse this so I get the username from the email address?

Below is the query and the package. Any help would be highly
appreciated!

Thanks,

Gabor

----------------------------------------
-- The Query
----------------------------------------
set time on
set timing on
set serveroutput on size 1000000
col input format a20
col output format a20
col c_attr_name format a20
col c_value format a44

define V_HOST = '172.17.116.72';
define V_PORT = '389';
define V_USER = 'V24mobile01ac';
define V_PSWD = 'Password123';
define V_ROOT = 'DC=officeqa,DC=adrootqa,DC=bmogc,DC=net';
define V_FILTER = 'CN=PUATest'
define V_ATTR = 'mail';
define V_DBUG = 'TRUE';

select '&V_FILTER => &V_ATTR' input,
pkg_ldap.f_ldap_search (
'&V_HOST',
'&V_PORT',
'&V_USER',
'&V_PSWD',
'&V_ROOT',
'&V_FILTER',
'&V_ATTR',
'TRUE' ) output
from dual;

----------------------------------------
-- The Package
----------------------------------------

@set_run_sql
create or replace package pkg_ldap as
function f_ldap_search(
a_host varchar2,
a_port varchar2,
a_user varchar2,
a_pswd varchar2,
a_root varchar2,
p_filter varchar2,
p_attr varchar2,
a_dbug varchar2 default 'false') return varchar2;
procedure p_save (
a_run_id number,
a_attr_idx number,
a_attr_name varchar2,
a_value_idx number,
a_value varchar2 );
end pkg_ldap;
/
show errors

create or replace package body pkg_ldap as
function f_ldap_search(
a_host varchar2,
a_port varchar2,
a_user varchar2,
a_pswd varchar2,
a_root varchar2,
p_filter varchar2,
p_attr varchar2,
a_dbug varchar2 default 'false') return varchar2 as

v_run_id number;
v_rtn varchar2(4000) := null;
i_rtn pls_integer;
my_session dbms_ldap.session;
my_attrs dbms_ldap.string_collection;
my_message dbms_ldap.message;
my_entry dbms_ldap.message;
entry_idx pls_integer;
my_attr_name varchar2(256);
my_ber_elmt dbms_ldap.ber_element;
attr_idx pls_integer;
value_idx pls_integer;
my_vals dbms_ldap.string_collection;
my_val varchar2(4000);

begin
i_rtn := -1;
select s_ldap.nextval into v_run_id from dual;

-- Initialize the ldap session and bind to the directory
my_session := dbms_ldap.init ( a_host, a_port );
i_rtn := dbms_ldap.simple_bind_s ( my_session, a_user, a_pswd );

-- Issue the search
my_attrs(1) := p_attr;
i_rtn := dbms_ldap.search_s (
my_session,
a_root,
dbms_ldap.scope_subtree,
p_filter,
my_attrs,
0,
my_message);
i_rtn := dbms_ldap.count_entries ( my_session, my_message );

-- Loop through the entries
entry_idx := 1;
my_entry := dbms_ldap.first_entry ( my_session, my_message );

while my_entry is not null
loop

-- Print the current entry
if a_dbug = 'TRUE'
then dbms_output.put_line ( '+entry' || entry_idx );
end if;

-- Loop through the attributes of the entry
attr_idx := 1;
my_attr_name := dbms_ldap.first_attribute ( my_session, my_entry,
my_ber_elmt );

while my_attr_name is not null
loop

-- Print the current entry
if a_dbug = 'TRUE'
then dbms_output.put_line ( '+ attribute' || attr_idx || '=' ||
my_attr_name );
end if;

-- Loop through the attribute values
my_vals := dbms_ldap.get_values ( my_session, my_entry,
my_attr_name );

if my_vals.COUNT > 0
then for value_idx in my_vals.first..my_vals.last
loop
my_val := substr ( my_vals(value_idx), 1, 200 );
if a_dbug = 'TRUE'
then dbms_output.put_line ( '+ value' || value_idx || '=' ||
my_val );
end if;

p_save (
v_run_id,
attr_idx,
my_attr_name,
value_idx,
my_val );

if my_attr_name = my_attrs(1)
then v_rtn := my_val;
end if;
end loop;
end if;

-- Iterate to the next attribute
my_attr_name := dbms_ldap.next_attribute ( my_session, my_entry,
my_ber_elmt );
attr_idx := attr_idx + 1;

end loop;

-- Free ber_element
dbms_ldap.ber_free ( my_ber_elmt, 0 );

-- Iterate to the next entry
my_entry := dbms_ldap.next_entry ( my_session, my_entry );
entry_idx := entry_idx + 1;

end loop;

-- Free LDAP message
i_rtn := dbms_ldap.msgfree ( my_message );

-- Unbind from the directory
i_rtn := dbms_ldap.unbind_s ( my_session );

return v_rtn;

end f_ldap_search;

procedure p_save (
a_run_id number,
a_attr_idx number,
a_attr_name varchar2,
a_value_idx number,
a_value varchar2 ) as
pragma autonomous_transaction;
begin
insert into t_ldap values (
s_ldap.nextval,
a_run_id,
a_attr_idx,
a_attr_name,
a_value_idx,
a_value );
commit;
end p_save;
end pkg_ldap;
/
show errors;

----------------------------------------
-- 2 objects the package uses
----------------------------------------

create table t_ldap (
c_id number,
c_run_id number,
c_attr_idx number,
c_attr_name varchar2(4000),
c_value_idx number,
c_value varchar2(4000));
create sequence s_ldap;
.

Relevant Pages

  • Re: Help trying to debug this DUMB pl/sql code
    ... I have a package I'm trying to run through a debugger and find some ... The package uses a defined type VARRAY: ... p_save_out OUT VARCHAR2, ... Puget Sound Oracle Users Group ...
    (comp.databases.oracle.server)
  • Re: jdbc + CallableStatement +Incorrectly registered parameters
    ... > Oracle and java. ... > I've created a package with a function to find buss connection ... > FUNCTION getdirect(from_city varchar2, to_city varchar2) RETURN ... > CallableStatement stmt = db.prepareCall; ...
    (comp.lang.java.databases)
  • Re: Oracle <= 9i / 10g File System Access via utl_file Exploit
    ... I don't think so its any new vulnerability or exploit ... Traversal via this package ... -- for any special privileges (CONNECT and RESOURCE ... in varchar2, p_filename in varchar2) as ...
    (Bugtraq)
  • Re: Help trying to debug this DUMB pl/sql code
    ... I have a package I'm trying to run through a debugger and find some ... The package uses a defined type VARRAY: ... p_save_out OUT VARCHAR2, ... If I try to debug using SQL Developer, ...
    (comp.databases.oracle.server)
  • Re: Any suggestions with this updated code????
    ... This Oracle sproc is so ... --Create a package to declare a ref cursor globally ... street_no in varchar2, street_name in varchar2); ... create or replace package body property1 as ...
    (microsoft.public.vb.database.ado)