$sql_select_policy help

Hello everyone,

I'm tuning up my $sql_select_policy in order to improve
functionality  
of amavis on my mail server and I'm struggling with
something which  
I'd imagine would be simple.  I need to extend this query as
follows:

" ... OR alias.goto LIKE ('%RECIPIENT%')"

where RECIPIENT is the individual recipient address for the
message  
in question and the % signs are wrapping it in order to
treat it as a  
needle in a haystack.  I need the same recipient address
which  
appears in the first set of single quotes when the %k macro
is  
expanded in the typical policy lookup query (per the various
README  
examples)...

Can anyone help me?

Thanks,
Daniel


------------------------------------------------------------
-------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and
take
control of your XML. No limits. Just data. Click to get it
now.
http://sourcefor
ge.net/powerbar/db2/
_______________________________________________
AMaViS-user mailing list
AMaViS-userlists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user

AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/

Daniel,

> I'm tuning up my $sql_select_policy in order to improve
functionality
> of amavis on my mail server and I'm struggling with
something which
> I'd imagine would be simple.  I need to extend this
query as follows:
>
> " ... OR alias.goto LIKE ('%RECIPIENT%')"
>
> where RECIPIENT is the individual recipient address for
the message
> in question and the % signs are wrapping it in order to
treat it as a
> needle in a haystack.  I need the same recipient
address which
> appears in the first set of single quotes when the %k
macro is
> expanded in the typical policy lookup query (per the
various README
> examples)...

Here below is a patch to 2.5.1 to provide a placeholder %a,
which
is much like the %k, except that it only expands to the
exact mail address,
the same as the first entry provided by the %k expansion.

It will enable you to do what you want, for example:

$sql_select_policy =
"SELECT *,users.id FROM users LEFT JOIN policy ON
users.policy_id=policy.id".
  " WHERE users.email IN (%k) OR alias.goto LIKE
concat('%', %a, '%')".
  " ORDER BY users.priority DESC";

although I'm not sure if this is really what you want:
letting a
world-provided string be interpreted as a SQL pattern looks
like asking for trouble.

Anyway, there may be other legitimate uses of LIKE,
perhaps a:   %a LIKE '%xxx%' , so her it is:


--- amavisd.orig	Thu May 31 14:10:01 2007
+++ amavisd	Mon Jun 18 16:07:31 2007
 -12088,6
+12088,9 
   my(extras_tmp) = !ref $extra_args ? () : $extra_args;
   local($1); my(pos_args); my($sel_taint) =
substr($sel,0,0); # taintedness
-  $sel =~ s{ ( %k | ? ) }  # substitute %k for keys and ?
for each extra arg
-           { push(pos_args, $1 eq '%k' ? $keys_ref
: shift extras_tmp),
+  $sel =~ s{ ( %k | %a | ? ) }  # substitute %k for keys,
%a for exact mail
+                                 # address, and ? for each
extra arg
+           { push(pos_args, $1 eq '%k' ? $keys_ref
+                           : $1 eq '%a' ? $keys_ref->[0]
 # same as first in %k
+                           : shift extras_tmp),
              $1 eq '%k' ? join(',', ('?') x $n) : '?'
}gxe;
   $sel = untaint($sel) . $sel_taint;  # keep original
clause taintedness


Mark

------------------------------------------------------------
-------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and
take
control of your XML. No limits. Just data. Click to get it
now.
http://sourcefor
ge.net/powerbar/db2/
_______________________________________________
AMaViS-user mailing list
AMaViS-userlists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user

AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/

On Mon, Jun 18, 2007 at 04:14:20PM +0200, Mark Martinec
wrote:
> Daniel,
> 
> > I'm tuning up my $sql_select_policy in order to
improve functionality
> > of amavis on my mail server and I'm struggling
with something which
> > I'd imagine would be simple.  I need to extend
this query as follows:
> >
> > " ... OR alias.goto LIKE
('%RECIPIENT%')"
> >
> > where RECIPIENT is the individual recipient
address for the message
> > in question and the % signs are wrapping it in
order to treat it as a
> > needle in a haystack.  I need the same recipient
address which
> > appears in the first set of single quotes when the
%k macro is
> > expanded in the typical policy lookup query (per
the various README
> > examples)...
> 
> Here below is a patch to 2.5.1 to provide a placeholder
%a, which
> is much like the %k, except that it only expands to the
exact mail address,
> the same as the first entry provided by the %k
expansion.

> 
> It will enable you to do what you want, for example:
> 
> $sql_select_policy =
> "SELECT *,users.id FROM users LEFT JOIN policy ON
users.policy_id=policy.id".
>   " WHERE users.email IN (%k) OR alias.goto LIKE
concat('%', %a, '%')".
>   " ORDER BY users.priority DESC";
> 
> although I'm not sure if this is really what you want:
letting a
> world-provided string be interpreted as a SQL pattern
looks
> like asking for trouble.

  Without digging into it would *seem* like allowing that
would risk
having email addresses with single quotes (') injected into
them for an
SQL injection attack.

  -- Clifton

-- 
    Clifton Royston  --  cliftonriandicomputing.com /
cliftonrlava.net
       President  - I and I Computing * http://www.iandicomput
ing.com/
 Custom programming, network design, systems and network
consulting services

------------------------------------------------------------
-------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and
take
control of your XML. No limits. Just data. Click to get it
now.
http://sourcefor
ge.net/powerbar/db2/
_______________________________________________
AMaViS-user mailing list
AMaViS-userlists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user

AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/

Clifton,

> > $sql_select_policy =
> > "SELECT *,users.id FROM users LEFT JOIN
policy ON
> > users.policy_id=policy.id". " WHERE
users.email IN (%k) OR alias.goto
> > LIKE concat('%', %a, '%')". " ORDER BY
users.priority DESC";

> Without digging into it would *seem* like allowing that
would risk
> having email addresses with single quotes (') injected
into them for an
> SQL injection attack.

You are wrong, the %a and %k are transformed into a '?',
and actual values are provided by binding arguments.
See man DBI.

  Mark

------------------------------------------------------------
-------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and
take
control of your XML. No limits. Just data. Click to get it
now.
http://sourcefor
ge.net/powerbar/db2/
_______________________________________________
AMaViS-user mailing list
AMaViS-userlists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user

AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/

Hi Mark,

Thank you for making this nice addition for me!  It works
perfectly  
and accomplishes exactly what I needed...  Will I need to
continue  
patching this in or are you committing it to the main
branch?  Just  
checking...

RE: your concerns about the SQL pattern issues, I'm assuming
I  
wouldn't need to be quite as concerned about this as my
postfix  
server doesn't even call on amavis until the recipient
address has  
been validated as local...  Does this make sense?  Also, how
would  
this same concern not apply to the recipient address listed
in the %k  
macro which has been in the code all along?

Cheers,
Daniel


On Jun 18, 2007, at 7:14 AM, Mark Martinec wrote:

> Daniel,
>
>> I'm tuning up my $sql_select_policy in order to
improve functionality
>> of amavis on my mail server and I'm struggling with
something which
>> I'd imagine would be simple.  I need to extend this
query as follows:
>>
>> " ... OR alias.goto LIKE
('%RECIPIENT%')"
>>
>> where RECIPIENT is the individual recipient address
for the message
>> in question and the % signs are wrapping it in
order to treat it as a
>> needle in a haystack.  I need the same recipient
address which
>> appears in the first set of single quotes when the
%k macro is
>> expanded in the typical policy lookup query (per
the various README
>> examples)...
>
> Here below is a patch to 2.5.1 to provide a placeholder
%a, which
> is much like the %k, except that it only expands to the
exact mail  
> address,
> the same as the first entry provided by the %k
expansion.
>
> It will enable you to do what you want, for example:
>
> $sql_select_policy =
> "SELECT *,users.id FROM users LEFT JOIN policy ON 

> users.policy_id=policy.id".
>   " WHERE users.email IN (%k) OR alias.goto LIKE
concat('%', %a,  
> '%')".
>   " ORDER BY users.priority DESC";
>
> although I'm not sure if this is really what you want:
letting a
> world-provided string be interpreted as a SQL pattern
looks
> like asking for trouble.
>
> Anyway, there may be other legitimate uses of LIKE,
> perhaps a:   %a LIKE '%xxx%' , so her it is:
>
>
> --- amavisd.orig	Thu May 31 14:10:01 2007
> +++ amavisd	Mon Jun 18 16:07:31 2007
>  -12088,6 +12088,9 
>    my(extras_tmp) = !ref $extra_args ? () : $extra_args;
>    local($1); my(pos_args); my($sel_taint) =
substr($sel,0,0); #  
> taintedness
> -  $sel =~ s{ ( %k | ? ) }  # substitute %k for keys
and ? for  
> each extra arg
> -           { push(pos_args, $1 eq '%k' ? $keys_ref
: shift  
> extras_tmp),
> +  $sel =~ s{ ( %k | %a | ? ) }  # substitute %k for
keys, %a for  
> exact mail
> +                                 # address, and ? for
each extra arg
> +           { push(pos_args, $1 eq '%k' ? $keys_ref
> +                           : $1 eq '%a' ?
$keys_ref->[0]  # same  
> as first in %k
> +                           : shift extras_tmp),
>               $1 eq '%k' ? join(',', ('?') x $n) : '?'
}gxe;
>    $sel = untaint($sel) . $sel_taint;  # keep original
clause  
> taintedness
>
>
> Mark
>
>
------------------------------------------------------------
---------- 
> ---
> This SF.net email is sponsored by DB2 Express
> Download DB2 Express C - the FREE version of DB2
express and take
> control of your XML. No limits. Just data. Click to get
it now.
> http://sourcefor
ge.net/powerbar/db2/
> _______________________________________________
> AMaViS-user mailing list
> AMaViS-userlists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/amavis-user

> AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
> AMaViS-HowTos:http://www.amavis.org/ho
wto/



--
daniel duerr   |   president   |   emerald city
entertainment group
dd 
emeraldcityeg.com   |   +1 (831) 621-1710 x103



------------------------------------------------------------
-------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and
take
control of your XML. No limits. Just data. Click to get it
now.
http://sourcefor
ge.net/powerbar/db2/
_______________________________________________
AMaViS-user mailing list
AMaViS-userlists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user

AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/

Daniel,

> Thank you for making this nice addition for me!
> It works perfectly and accomplishes exactly what I
needed...

I'm glad it does.

> Will I need to continue patching this in or are you
committing it
> to the main branch?  Just checking...

Will be in 2.5.2, in few days.

> RE: your concerns about the SQL pattern issues, I'm
assuming I
> wouldn't need to be quite as concerned about this as my
postfix
> server doesn't even call on amavis until the recipient
address has
> been validated as local...  Does this make sense?

For incoming mail, when plus addressing is enabled (and
there is
no reason it shouldn't be), one may add any junk in local
part
after a separator, e.g. "user+xx_%x.***"example.com.

For outgoing mail, your users are allowed to specify any
recipient localpart under a valid domain name.

> Also, how would this same concern not apply to the
recipient
> address listed in the %k macro which has been in the
code all along?

The:
  users.email IN (fixed tainted strings)
is safe because only exact string matches are used,
and the list of strings is prepared by amavisd code
by stripping out parts of the recipient address,
in a well defined order from specific to a more general
form.

The:
  alias.goto LIKE tainted_regexp
appears potentially problematic, a recipient address
could contain characters which have special meaning
to SQL pattern matching, e.g. a recipient address
like ax_y.example.com would also match axy.example.com
(I know the '_' is not supposed to be used in host names,
but it is a valid character for DNS, and few mailers
enforce it).

If SQL regexp matching were used instead of the simple
LIKE,
it would be even easier to cheat.

It would be alright to do it the other way around though,
e.g. tainted_string LIKE admin-specified-regexp
which would be more similar to the:  users.email IN (%k)
functionality.

  Mark

------------------------------------------------------------
-------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and
take
control of your XML. No limits. Just data. Click to get it
now.
http://sourcefor
ge.net/powerbar/db2/
_______________________________________________
AMaViS-user mailing list
AMaViS-userlists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user

AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/

Thanks for the points, Mark.

On Jun 19, 2007, at 5:49 PM, Mark Martinec wrote:

> Daniel,
>
>> Thank you for making this nice addition for me!
>> It works perfectly and accomplishes exactly what I
needed...
>
> I'm glad it does.
>
>> Will I need to continue patching this in or are you
committing it
>> to the main branch?  Just checking...
>
> Will be in 2.5.2, in few days.
>
>> RE: your concerns about the SQL pattern issues, I'm
assuming I
>> wouldn't need to be quite as concerned about this
as my postfix
>> server doesn't even call on amavis until the
recipient address has
>> been validated as local...  Does this make sense?
>
> For incoming mail, when plus addressing is enabled (and
there is
> no reason it shouldn't be), one may add any junk in
local part
> after a separator, e.g. "user+xx_%x.***"example.com.
>
> For outgoing mail, your users are allowed to specify
any
> recipient localpart under a valid domain name.
>
>> Also, how would this same concern not apply to the
recipient
>> address listed in the %k macro which has been in
the code all along?
>
> The:
>   users.email IN (fixed tainted strings)
> is safe because only exact string matches are used,
> and the list of strings is prepared by amavisd code
> by stripping out parts of the recipient address,
> in a well defined order from specific to a more general
form.
>
> The:
>   alias.goto LIKE tainted_regexp
> appears potentially problematic, a recipient address
> could contain characters which have special meaning
> to SQL pattern matching, e.g. a recipient address
> like ax_y.example.com would also match axy.example.com
> (I know the '_' is not supposed to be used in host
names,
> but it is a valid character for DNS, and few mailers
> enforce it).
>
> If SQL regexp matching were used instead of the simple
LIKE,
> it would be even easier to cheat.
>
> It would be alright to do it the other way around
though,
> e.g. tainted_string LIKE admin-specified-regexp
> which would be more similar to the:  users.email IN
(%k)
> functionality.
>
>   Mark
>
>
------------------------------------------------------------
---------- 
> ---
> This SF.net email is sponsored by DB2 Express
> Download DB2 Express C - the FREE version of DB2
express and take
> control of your XML. No limits. Just data. Click to get
it now.
> http://sourcefor
ge.net/powerbar/db2/
> _______________________________________________
> AMaViS-user mailing list
> AMaViS-userlists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/amavis-user

> AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
> AMaViS-HowTos:http://www.amavis.org/ho
wto/



--
daniel duerr   |   president   |   emerald city
entertainment group
dd 
emeraldcityeg.com   |   +1 (831) 621-1710 x103



------------------------------------------------------------
-------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and
take
control of your XML. No limits. Just data. Click to get it
now.
http://sourcefor
ge.net/powerbar/db2/
_______________________________________________
AMaViS-user mailing list
AMaViS-userlists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user

AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/