CODEWITHSUNDEEP
database-designip-addressipv6
mpen
mpen

Reputation: 282955

Saving an IP address to DB

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...

Upvotes: 0

Views: 364

Answers (4)

Javi Stolz
Javi Stolz

Reputation: 4755

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()

Upvotes: 0

yfeldblum
yfeldblum

Reputation: 65445

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.

Upvotes: 0

Joe
Joe

Reputation: 42636

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

Upvotes: 2

rfusca
rfusca

Reputation: 7705

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

Upvotes: 3

Related Questions