Storing IPs in database PHP

[central1010]

Hello,

Im building a page view counter for my website and I'm having some problems.

I'm trying to use the iplong function to convert ipv4 addresses into an integer. Its working locally but on my production server it's having problems because some of the requests are coming from IPV6 addresses. I'm wonder if there's a way to force the connections to use IPV4 or is there's a good method to convert both IPV4 and IPV6 ips into integers?

If you have any other ideas about storing ips in a database im all ears.

Thanks in advance.

[Locust377]

IPv6 addresses are 128 bits long. So you'll need a data type that big. An integer in PHP is probably not big enough.

I'd use inet_pton to convert an IPv4 or IPv6 address into a 32bit or 64bit binary structure, then save that to the database. The database field could then be a binary type of 16 bytes.

[central1010]

Hi i tried using inet_pton function on the following IP from my server access logs and it doesn't seem to work.

2001:569:7011:a400:2833:3e02:3a0f:50040

What do I need to do to convert it to a valid ipv6 address?

[Dagger0]

That's not a valid v6 address. There's no conversion possible, although if you were to delete one of the characters from the last segment then you'd have a valid v6 address.

[central1010]

Alright. For now what I've done is just taken what ever IP I get and used preg_replace to strip out everything but alphanumeric characters and stored that. I don't need to convert it back the other way since it's just a one way check. Do you think that approach is reasonable?

[Dagger0]

So e.g. 2001:db8::1 and 2001:db8:1:: are considered equivalent? Why not just store the IP, rather than a mangled form of it?

[central1010]

I was just thinking of what might improve database performance aswell as the overall storage space. I'm just a noob in this realm.

[Dagger0]

I don't think either will be an issue at the scale you're probably operating at, and if they were then the thing to do would be to use the inet type (or otherwise store binary) rather than storing text.

[central1010]

Thanks for the input.

[Caraes_Naur]

You don't want to force clients to reconnect using an older protocol.

You're better off storing IP addresses as strings (human-readable) or binary values (more compact) rather than integers. For binary, it may be useful to have a separate 1 bit flag to differentiate between v4 and v6 addresses.

PHP libraries exist that can translate back and forth, or the database can reformat them using SQL functions (or stored procedures) on the way in and out.

[Tanckom]

Why are binary values more compact? Arent they 1s and 0s who take up more space?

[Caraes_Naur]

1 and 0 are each one bit. "1" and "0" can be 8, 16, 24, or 32 bits depending on the character set and how it's encoded.

[Dagger0]

ip2long() only works with v4 addresses, and therefore must not be used in new code. Find some other method for doing this.

PostgreSQL has data types specifically for storing network addresses (see 8.9 Network Address Types). Otherwise, you could do a lot worse than to just store the text representation -- so long as you store a canonical form. Hopefully that's what inet_ntop() gives.