Storing IP Address and Hostname

Databases
1

I would like to store a User’s IP Address and Hostname.

What Data-Type and Field-Size should I be using?

I am using the following PHP functions…


	// Capture User's IP Address.
	$userIPAddress = $_SERVER['REMOTE_ADDR'];
			
	// Capture User's Host Name.
	$userHostname = gethostbyaddr($_SERVER['REMOTE_ADDR']);

Thanks,

Debbie

2

IP adddresses are in IPv4 or IPv6 format. Do a quick google to find the max length of each string and set your field size accordingly.

3

This is a MUCH more complex topic than you give it credit.

Debbie

4

I don’t see why it should be. I’ve always found it straight forward. The only thing I can’t remember off the top of my head is the IPv6 format so that is why I sggested you google it.

5

the php functions you are using to get the data have NO RELEVANCE on what data type and length you choose to store the data in the database.

There is nothing complicated about it. Figure out the length of the IP6 and use that, guess the longest length of your hostname and double that and there you go.

Sorry not complicated at all.

6

As an aside, I wouldn’t store the hostname of each and every visitor at all.

  1. Doing a lookup of the hostname takes time, and we’re not talking microseconds here, it can be quite costly if the route to the visitor is long. This means it will take longer for the site to load on their end, which is a bad thing.

  2. You don’t really need them as hostnames rarely, if ever, change, so there is not really a need to store them. If at any point in time you want to know someone’s host name you can just do a reverse lookup from their IP then and there.

  3. Some spammers have their reverse DNS set to “localhost”. So if you do a reverse lookup and then ban the hostname you’re effectively letting the server ban itself, so php can’t connect to mysql anymore, you can’t send or receive mail anymore, etc, etc. Obviously this is a Bad Idea ™.

7

Actually, while not microseconds we MAY be talking about milliseconds. A few months back when I was launching a site I decided to test it and set up a script that timed hostname lookups with php’s gethostbyaddr() and logged the results in a file. Today I have 261380 timings over the period of 4 months. Here are the results:

  • 93.65% took less than 10ms (<0.0010s)
  • 99.45% took less than 0.1s
  • 0.489% (1278) took between 0.1s and 1s
  • 0.0658% (172) took more than 1s
  • the maximum was 10.5s

I wouldn’t find these timings troubling at all. However, I’ve heard that they can vary greatly depending on the server so it’s best to test it first.

I like getting the hostname for the purpose of storing it in a log file or log table in the db. It’s very convenient to view the logs and have the hostname straight in the notepad or table view.

8

You might also love MySQL’s conversions for the IP addresses to numbers and back to IPs.
INET_ATON(‘192.168.1.1’) Results: 3232235777
INET_NTOA(‘3232235777’) Results: 192.168.1.1

Also, PHP has the ip2long(), and long2ip() functions.

In the database, you can design INT or VARCHAR according to you needs.
gethostbyaddr() always gives a string.

Also, for the REMOTE_ADDR, try to find out a true IP first.

And finally, don’t miss a real solution - IP to Country that shows how to handle the ranges of IPs.

9

Will those work with IPv6?

10

As mentioned above, normally it does not take that long time to get the hostname information.

Keep in mind that ip2long returns an signed value, so you will need to convert it to unsigned.

In addition please keep in mind that both the PHP and MySQL functions are for ipv4 only… So if your server is enabled to accept ipv6 requests, you need to make certain that you check what version the IP is first.

If your writing a new application now, I would recommend using inet_ntop and inet_pton instead (PHP) since that support both ipv4 and ipv6. MySQL is also getting INET6_ATON() and INET6_NTOA() from ver.5.6.