Re: [exim] CIDR and MySQL

Magnus Holmgren wrote:
> On Sunday 26 November 2006 04:58, Wakko Warner wrote:
> > I noticed a couple months ago that someone wanted to know how to use CIDR
> > notation with mysql.
> >
> > I came up with a nasty way of doing this.
> >
> > The following fragment can be used as a conditional expression.
> > ((substring_index(@a,'.',1)<<24)|(substring_index(substring_index(@a,'.',2)
> >,'.',-1)<<16)|(substring_index(substring_index(@a,'.',-2),'.',1)<<8)|substri
> >ng_index(@a,'.',-1))&(~(4294967295>>substring_index(@t,'/',-1))&4294967295)=
> >((substring_index(@t,'.',1)<<24)|(substring_index(substring_index(@t,'.',2),
> >'.',-1)<<16)|(substring_index(substring_index(@t,'.',-2),'.',1)<<8)|substrin
> >g_index(substring_index(@t,'.',-1),'/',1))
>
> Urgh. MySQL has INET_ATON and INET_NTOA built-in since version 3.23.15. And if
> you want to store CIDR masks I think it's better to store that as a separate
> field and do any splitting outside the database.

I looked for that for some time lastnight and never found it. That will
definately simplify the query! Thanks.

> So:
>
> INET_ATON(@a) & (-1 << (32 - @len)) = @t & (-1 << (32 - @len))
>
> is my suggestion (assuming that the address to test is already stored in
> binary form).
>
> This is strictly IPv4. Don't forget to make your applications IPv6 ready!

I already knew it wasn't IPv6. I don't know much about it. I guess I'll
have to come up with something else when the time comes. =)

--
Lab tests show that use of micro$oft causes cancer in lab animals
Got Gas???