> -----Original Message-----
> From: exim-users-bounces@???
> [mailto:exim-users-bounces@exim.org] On Behalf Of Craig Jackson
> Sent: Thursday, December 13, 2007 9:37 PM
> To: exim-users@???
> Subject: Re: [exim] Regex or sg how
>
>
>
> > -----Original Message-----
> > From: exim-users-bounces@???
> > [mailto:exim-users-bounces@exim.org] On Behalf Of Phil Pennock
> > Sent: Tuesday, December 11, 2007 2:30 AM
> > To: Craig Jackson
> > Cc: exim-users@???
> > Subject: Re: [exim] Regex or sg how
> >
> > On 2007-12-10 at 20:33 -0600, Craig Jackson wrote:
> > > I have written a Mysql stored procedure to whitelist
> > $recipients, by
> > > parsing that variable. It did not occur to me to use a stored
> > > procedure for this -- looks a lot harder to do.
> >
> > I'm not a MySQL user; PostgreSQL is my poison of choice when I feel
> > compelled to twist my brain with SQL. (Cheaper than long-sleeved
> > white coats (not that I've checked into the price of those, you
> > understand)).
> >
> > Here's what I came up with for PostgreSQL using PL/pgSQL;
> please note
> > that I am not a heavy DB user and there's probably lots of ways to
> > optimise this. Or heck, you could write it in C and load
> it into Exim
> > after all via ${dlfunc}; hopefully what I did can be
> translated into
> > MySQL so will give you ideas.
> >
> > Note that I'm such a lightweight DB user that I hadn't even gotten
> > around to using my idle DB for any real work and it didn't have
> > PL/pgSQL loaded. I connected as root to template1, did "CREATE
> > LANGUAGE plpgsql;" and all was well thereafter. The \copy line in
> > what follows is a psql load from client side of the list of
> > two-level-tlds which someone else posted to this thread.
> >
> > CREATE DATABASE basic_resources;
> > \c basic_resources
> >
> > CREATE TABLE two_level_tlds(tld varchar(40) NOT NULL); \copy
> > two_level_tlds(tld) FROM 'two-level-tlds'
> > CREATE UNIQUE INDEX i_two_level_tlds_tld ON two_level_tlds(tld);
> >
> > CREATE FUNCTION domain_possibilities(varchar)
> > -- given domain a.b.c.d.e return
> > [a.b.c.d.e],[b.c.d.e],[c.d.e],[d.e]
> > -- does not pay attention to any semantic value of the
> components
> > RETURNS SETOF varchar
> > STABLE STRICT
> > AS $$
> > DECLARE
> > dom varchar := $1;
> > BEGIN
> > WHILE dom LIKE '%._%' LOOP
> > RETURN NEXT dom;
> > dom := substr(dom, position('.'
> > IN dom)+1);
> > END LOOP;
> > RETURN;
> > END;
> > $$ LANGUAGE plpgsql;
> > CREATE FUNCTION trim_domain(varchar)
> > -- given domain a.b.c.d.e find the shortest domain from
> > domain_possibilities
> > -- which is not a gTLD or ccTLD registrar; if none
> found, return
> > original domain
> > RETURNS varchar
> > STABLE STRICT
> > AS $$
> > DECLARE
> > dom varchar;
> > tmp record;
> > tmp2 record;
> > BEGIN
> > SELECT INTO tmp * FROM
> > domain_possibilities($1) ORDER BY
> > char_length(domain_possibilities) ASC LIMIT 1;
> > IF NOT FOUND THEN
> > RETURN $1;
> > END IF;
> > dom := tmp.domain_possibilities;
> > SELECT INTO tmp2 tld FROM
> > two_level_tlds WHERE tld = dom;
> > IF NOT FOUND THEN
> > RETURN dom;
> > END IF;
> > FOR tmp IN SELECT * FROM
> > domain_possibilities($1) ORDER BY
> > char_length(domain_possibilities) ASC LOOP
> > dom := tmp.domain_possibilities;
> > SELECT INTO tmp2 tld FROM
> > two_level_tlds where tld = dom;
> > IF NOT FOUND THEN
> > RETURN dom;
> > END IF;
> > END LOOP;
> > RETURN $1;
> > END;
> > $$ LANGUAGE plpgsql;
> > GRANT EXECUTE ON FUNCTION domain_possibilities(varchar) TO PUBLIC;
> > GRANT EXECUTE ON FUNCTION trim_domain(varchar) TO PUBLIC;
> >
> > --
>
>
> Phil,
>
> Thanks for the nice code. But I am such a poor computer
> person in general that I didn't quite understand it. So I
> came up with this Mysql stored procedure that I'm sure has
> numerous flaws that I can't find. It took me a long time to do this.
>
> DELIMITER //
> DROP PROCEDURE IF EXISTS db.ehlo //
> CREATE PROCEDURE db.ehlo (IN HELO VARCHAR(255), IN SENDERDOM
> VARCHAR(255))
> BEGIN
> DECLARE DOM VARCHAR(255);
> DECLARE DOMTMP VARCHAR(255);
> DECLARE LOC INT;
> DECLARE TLDOM VARCHAR(30);
> DECLARE SADOM VARCHAR(255);
> SET DOM=HELO;
> SET SADOM=SENDERDOM;
> SET LOC=LOCATE('.',DOM);
> SET DOMTMP=RIGHT(DOM,LENGTH(DOM)-LOC);
> SELECT tld INTO TLDOM FROM tlds WHERE tld=DOMTMP LIMIT 1;
> LOOPY: WHILE LOC > 1 DO
> IF TLDOM IS NOT NULL THEN
> UPDATE whitelist SET ehlo=DOM WHERE domain=SADOM;
> LEAVE LOOPY;
> END IF;
> SET DOM=DOMTMP;
> SET LOC=LOCATE('.',DOMTMP);
> SET DOMTMP=RIGHT(DOMTMP,LENGTH(DOMTMP)-LOC);
> SELECT tld INTO TLDOM FROM tlds WHERE tld=DOMTMP
> LIMIT 1; END WHILE; END; //
>
> But I still have a big problem. Even though this works fine
> from the command line as root user, it doesn't do anything at
> all when called from Exim. No record is updated. No errors.
> Nothing. The Mysql user Exim uses does have Exec permission.
> And Exim is passing the correct data as can be seen because
> that data is also being sent to logs.
>
> Do you see any thing I'm missing?
>
> MACRO:
> CAPTURE_EHLO = CALL ehlo \
> ('${quote_mysql: ${lc:$sender_helo_name}}','${quote_mysql:
> ${lc:$sender_address_domain}}')
>
> WARN in rcpt acl:
> warn log_message = HELOSAD: $sender_helo_name
> $sender_address_domain
> condition = ${lookup mysql{CAPTURE_EHLO}{yes}{no}}
>
> Any ideas greatly appreciated.
> Craig
>
One more note: exim -bh test shows this success is running the call.
>>> check condition = ${lookup mysql{CALL ehlo ('${quote_mysql:
${lc:$sender_helo_name}}','${quote_mysql:
${lc:$sender_address_domain}}')}{yes}{no}}
>>> = yes
>>> warn: condition test succeeded
LOG: H=(mx222.tklaw.com) [64.18.3.82] Warning: HELOSAD: mx222.tklaw.com
tklaw.com
