Re: [exim] Regex or sg how

Pàgina inicial
Delete this message
Reply to this message
Autor: Craig Jackson
Data:  
A: exim-users
Assumpte: Re: [exim] Regex or sg how


> -----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