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).
分享
