I'm trying to block certain IP addresses and IP ranges from performing certain actions. Right now, I SELECT * FROM blocked WHERE ip=$_SERVER['REMOTE_ADDR'] and if ->num_rows returns > 0, I redirect the user.
This works with full IP addresses but if I put 199.90.*.* for example, into the database, how might I match that? Also, what would be the best way to store the IPs in the database?
The answer to your question is to use like:
SELECT *
FROM blocked
WHERE $_SERVER['REMOTE_ADDR'] like replace(ip, '*', '%')
The wildcard in like is '%' rather than '*', so this replaces the asterisk with the percent sign. I am guessing that you would actually store the string like '199.199.%.%', but the above is for clarity.
Although this technically solves your problem, it has other issues because MySQL may not use an index for these comparisons (see here). The impact depends on how large the blocked table is. If it has 100 rows, this may not be an issue. If it has 100,000 then it probably is.
An alternative to using wildcards for the blocked table is to have FromIP and ToIP as columns. Then something like "199.199.." would simply be stored as "199.199.000.000" and "199.199.999.999". The query would be:
where $_SERVER['REMOTE_ADDR'] between FromIP and ToIP
And you would have an index on blocked(FromIP, ToIP).
I would first check for full IP address, then mask out the last byte with *, and check for that, then the last 2 bytes, and so on. This way you go from specific to less specific, and if once you get a hit, the IP is blocked. It's probably not the most efficient way, but I guess you have to do it only once, at login-time.
I would store IP addresses for simplicity as strings.
Another solution could be to store IP address as byte-array, but that wouldn't make anything easier I guess, and would be much less maintainable with for example the command-line tool SQL tool.
Your best solution would be to use INET_ATON function.
Given the dotted-quad representation of an IPv4 network address as a string, returns an integer that represents the numeric value of the address in network byte order (big endian).
So, if you want to look for an IP in a certain range, you would use a Query like this:
SELECT * FROM blocked WHERE INET_ATON($_SERVER['REMOTE_ADDR']) between INET_ATON(this_ip) and INET_ATON(this_other_ip).
As I think, the best way to store IPv4 addresses in a MySQL Data base is using that function, to convert them to an int value and store it like that. And If you want to get the IP from it's int representation use INET_NTOA.
Storing IP's as int, the query ends like this:
SELECT * FROM blocked WHERE INET_ATON($_SERVER['REMOTE_ADDR']) between this_ip and this_other_ip.
And instead of using 199.90.*.* use (199.90.0.0).
Hope this Helps.