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