IP Address - MySQL data type

Question

I have a database with Country-IP addresses.

My table has two fields.

LBound, tinyint
UBound, tinyint

When I query the table like this:

select * from allowed where '129.250.224.1' BETWEEN lbound AND ubound

I get 3 results:

12.205.104.0    13.16.137.9
129.250.2.43    129.250.3.137
129.250.16.47   129.253.255.255

But I only want to return:

129.250.16.47   129.253.255.255

Is this a data type issue? If so, what datatype should I be using?

score 1 · Answer 1 · edited Jan 02 '17 at 19:24

1

You could use

 INETT_ATON

and

  INET_NTOA

eg :

 SELECT INET_ATON('10.0.5.9');
    -> 167773449

In this way you can convert the IP in a number properly and filter correctly

edited Jan 02 '17 at 19:24

Gurwinder Singh

answered Jan 02 '17 at 18:41

ScaisEdge

How do I use it with this? die("select * from allowed where '$ADDRESS' BETWEEN lbound AND ubound"); – RickyD Jan 02 '17 at 19:56
How do I use it on Lbound & UBound? – RickyD Jan 02 '17 at 20:03
NM. Got it. Thanks. – RickyD Jan 02 '17 at 20:16

score 0 · Answer 2 · edited May 23 '17 at 12:16

You can use inet_aton function to convert IP address text into numeric value and compare. Like this:

SELECT *
FROM allowed
WHERE inet_aton('129.250.224.1') BETWEEN inet_aton(lbound) AND inet_aton(ubound);

Regarding which type to use to store the IP address, I'd use UNSIGNED INT or VARBINARY(16).

2 Answers2