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 ?
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.
Try this:
SELECT bot_name
FROM bots
WHERE __input_ip__ REGEXP concat('^', replace(ip_address, '.', '\.'))
(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__
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With