tags:

views:

145

answers:

3

I have the following table

Table bots{
    ip_address varchar(15),
    bot_name varchar(32)
}

Given that some bots have static ips and others won't, the table has entries such as 192.168.0 and 192.168.1.15

Now I have to see if a given ip belongs to a bot. I was thinking something along these lines

SELECT bot_name
FROM bots
WHERE __input_ip__  REGEXP '^ip_address'

but this won't work for the obvious reason that it is looking for a string that starts with ip_address.

So my question is, how can I include a field name within a sql regular expression ?

+1  A: 

Try this:

SELECT bot_name
FROM bots
WHERE __input_ip__  REGEXP concat('^', replace(ip_address, '.', '\.'))
Andrew Hare
Won't the `.` characters in the ip_address be interpreted by the regex as 'any character'? They should be escaped with backslashes, probably with `replace()`
Adam Bellaire
@Adam - very good point!
Andrew Hare
+3  A: 

You might want to consider storing the IP address as an INT UNSIGNED. Also store the netmask so you can tell the difference between a static address and a subnet.

INSERT INTO bots (ipaddress, netmask, bot_name) 
VALUES (INET_ATOI('192.168.1.0'), INET_ATOI('255.255.255.0'), 'Wall-E');

Then you can query to see if an input IP address matches:

SELECT bot_name
FROM bots
WHERE __input_ip__ & netmask = ipaddress & netmask;

Using integers for IP addresses instead of CHAR(15) is a common optimization. Even storing the 8 bytes for the IP address and the netmask is little more than half the storage of the CHAR(15). And the bitwise operations are likely to be a lot faster than the regular expression matching, and it's easier to avoid the corner cases like in @Gumbo's comment.

Bill Karwin
+1 This is a better approach - nice answer.
Andrew Hare
This is a good idea, but of course it's limited to IPv4.
bobince
I like this approach, but I think I must be missing somethingINSERT into bots(ip_address, network_mask, name)VALUES (INET_ATON('192.168.0.0'), INET_ATON('255.255.255.0'), 'Wall-E'), (INET_ATON('192.168.1.15'), INET_ATON('255.255.255.0'), 'Bender');#Returns wall-eSELECT nameFROM botsWHERE ip_address = INET_ATON('192.168.0.15') # Returns an empty setSELECT nameFROM botsWHERE ip_address = INET_ATON('192.168.1.15')
Scott
Okay, sorry about the mixup, I though you'd always use '0' for the variable part of a dynamic IP address (though I don't know why I'd think this). I've edited the answer above to apply the netmask to both the ip_address and the input.
Bill Karwin
A: 

(This is a response to Andrew's answer but doesn't fit in a comment.)

WHERE __input_ip__  REGEXP concat('^', replace(ip_address, '.', '\.'))

Good plan, except that in MySQL \ is a (non-standard SQL) string literal escape, so to get one in the regexp you'd probably need '\\\.'!

...except in ANSI mode it wouldn't. Argh! To be compatible you'd have to get the backslash another way:

WHERE __input_ip__ REGEXP CONCAT('^', REPLACE(ip_address, '.', CHAR(92, 46)))

Ugh. Maybe better to forget regex and do it with string ops:

WHERE LEFT(__input_ip__, CHAR_LENGTH(ip_address))=__input_ip__
bobince
Well done! Thanks for pointing that out!
Andrew Hare