Saving an IP adddress to DB

Question

I want to save a user's IP address to my database just in case any legal issues come up and we need to track down who performed what action. Since I highly doubt I will ever actually need to use this data (well, maybe for counting unique hits or something) do you think I can just dump the REMOTE_ADDR into a field? If so, what should the length of that field be? 39 chars should fit an IPv6 address, no? I don't know if I'll ever get any of those, but just in case...

Answer 1

If you happen to be using Postgres, there are specific data types for network addresses.

Answer 2

In SQL Server, we've used VARBINARY[16] for IPs for IPv4 (4 bytes) and IPv6 (16 bytes).

But in your case, if it's just for occasional manual review and not for machine processing, just store the string. (And yes, 39 chars would be the max.)

Answer 3

Store some full and unambiguous representation of the data. When you need to query the data, and when you figure out what type of query on the data you need, that is when you figure out how to transform the data so that you can then query it efficiently.

If you are storing the IP address of, say, the user's first visit in any given user session, you might consider performing a reverse-DNS lookup on the IP address. This will yield information about the user's ISP. In case legal issues do come up, now you have more information. But because these lookups are expensive (in terms of time taken), you can do this as a background task and might want do it for initial hits only, not subsequent hits from the same IP address within any given user session.

Answer 4

The most effective method to store IP addresses in a DB field is converting them to an integer instead of using a string.

CREATE TABLE `table` (
...
  `ip` int(10) unsigned NOT NULL,
...

As you mention REMOTE_ADDR I assume you will be using PHP. You can convert the IP addres to a suitable value using ip2long()

sprintf('%u',ip2long($_SERVER['REMOTE_ADDR']))

To convert back an IP fetched from DB you can use long2ip()