CODEWITHSUNDEEP
mysqlsql
Jimmy Vidzem
Jimmy Vidzem

Reputation: 5

Select an entire range of ip's with a statement

Good day.

I have a problem with a spammer, using my site for his filthy purposes. The spammers IP is similar to 111.111.11.11 - completely static except for the last two numbers. Can I somehow target the range 00 - 99 in one single SQL query?

This is how I know I can deal with it (no action in the example):

SELECT * FROM myTable WHERE ip = 111.111.11.00
SELECT * FROM myTable WHERE ip = 111.111.11.01
SELECT * FROM myTable WHERE ip = 111.111.11.02
/* etc. */

Can I somehow wildcard the last two numbers like:

SELECT * FROM myTable WHERE ip = 111.111.11.??

or loop through a range like (preferrably directly in phpmyadmin)

SELECT * FROM myTable WHERE ip = 111.111.11.00 -> 111.111.11.99

I've been looking through the MySQL reference manual, but I am not quite sure what I am looking for. Any help is much appreciated.

Kind regards, Jimmy

EDIT: The like-operator was what I was looking for. One Wordpress plugin glitch exploiter is gonna get what one deserves. Thanks for all the suggestions!

Upvotes: 0

Views: 73

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Thorten's like is the simplest way to handle your particular problem. If you had a more generic range, then you can use right():

WHERE RIGHT(ip, 13) BETWEEN '111.111.11.00' AND '111.111.11.99'

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

Here's one idea. Note that, as written, this solution cannot use an index...

SELECT * FROM my_table WHERE INET_ATON(ip) BETWEEN 1869548288 AND 1869548387;

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94939

SELECT * FROM myTable WHERE ip BETWEEN '111.111.11.00' AND '111.111.11.99'

or

SELECT * FROM myTable WHERE ip LIKE '111.111.11.__'

Upvotes: 0

Related Questions