How to reverse an LDAP query?

From: Gabor2 <grevesz_at_gmail.com>
Date: Mon, 12 Jan 2009 14:44:33 -0800 (PST)
Message-ID: <31745971-8c8f-4743-b77c-8aae2a07880b_at_g3g2000pre.googlegroups.com>

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

---

_at_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; Received on Mon Jan 12 2009 - 16:44:33 CST