Best way to store an IPv6 in UNKNOWN DB?

Like your research shows there are benefits and problems with storing IP addresses as strings in canonical form, binary strings or as integers. Maybe there is a middle ground to store IP addresses in a database.

How about storing them as strings, but expanded to the full maximum length. That way you can still compare them (==, >, <, etc) but they are also still readable and you don't need special input and output encoding of special characters.

An example of how you could do this:

function expand_ip_address($addr_str) {  /* First convert to binary, which also does syntax checking */  $addr_bin = @inet_pton($addr_str);  if ($addr_bin === FALSE) {    return FALSE;  }  /* Then differentiate between IPv4 and IPv6 */  if (strlen($addr_bin) == 4) {    /* IPv4: print each byte as 3 digits and add dots between them */    return implode('.', array_map(      create_function('$byte', 'return sprintf("%03d", ord($byte));'),      str_split($addr_bin)    ));  } else {    /* IPv6: print as hex and add colons between each group of 4 hex digits */    return implode(':', str_split(bin2hex($addr_bin), 4));  }}

And some examples:

/* Test IPv4 */var_dump(expand_ip_address('192.0.2.55'));/* Test IPv6 */var_dump(expand_ip_address('2001:db8::abc'));/* Test invalid */var_dump(expand_ip_address('192:0:2:55'));

Which produce:

string(15) "192.000.002.055"string(39) "2001:0db8:0000:0000:0000:0000:0000:0abc"bool(false)