Complete PHP+MySQL IPv4 & IPv6 solution?

I'm not very knowledgeable about networking topics, but I have to store IP addresses for my project, and I want to be prepared to handle both开发者_如何学运维 IPv4 and IPv6. The best solution I've read appears to be two BIGINT unsigned fields where one is null in the case of IPv4:

How to store IPv6-compatible address in a relational database Does anybody have the complete solution?

I need code to go from a string address (like $_SERVER['HTTP_CLIENT_IP'] produces) to numeric values and vice versa.

Thank you very much for any help. I want to make sure I'm doing this correctly.

Or you can use a database like PostgreSQL if that is an option. It has native data types for storing and searching IPv4 and IPv6 addresses and prefixes. Input and output are done in string representation (usually).

If you have to use MySQL it really depends on how you want to use the addresses. If you want to search for subnets, group by prefix etc then integers are the most useful. If you just need to store them then varchar is the way to go.

I actually store the ordinals the way humans write them. So 8 16 bit unsigned integer fields for IPv6 and 4 8 bit unsigned integer fields for IPv4. For me, this makes searching for certain networks simple, though I can see that 2 unsigned bigints can also be simple.

Or you could store it in the format that you have it in most often in your code and will retrieve it as such. Which seems to be a string, one that's about 40 characters long. If you do that you'll want to use a canonical representation like that suggested in rfc5952.

I can't just write the conversion code for you; you'll need to provide some example of what you tried that didn't work, and why.

For IP address (format) verification, I'm currently using this as part of something I'm working on - not 100% certain that it's entirely correct as yet - need to throw more data at it (and I don't like the naming convention I've used on the private members either - but that's an easy fix with refactoring):

class IPAddress {

  //IP Address string
  private $ip_address;

  //IPv4 verification (RegExp insert)
  private $match_ipv4;

  //IPv6 verification (RegExp insert)
  private $match_ipv6;

  /**
   * Constructor function
   *
   * The $sIPAddress parameter is optional -
   * it allows you to set the IP address in
   * the object at creation.
   *
   * @param  string  $sIPAddress
   * @return void
   */
  public function __construct($sIPAddress=null) {
    //setup regexp inserts
    //IPv4 decimal octets match
    $sDecOctet = "([0-9]|[1-9][0-9]|1[0-9][0-9]|2[0-4][0-9]|25[0-5])";

    //IPv4 match
    $this->match_ipv4 = "({$sDecOctet}\.){3}{$sDecOctet}";

    //Hex 16 match
    $sH16 = "[0-9a-fA-F]{1,4}";

    //Char32 match
    $sLS32 = "({$sH16}:{$sH16}|{$this->match_ipv4})";

    //IPv6 match
    $this->match_ipv6 = "((({$sH16}:){6}"
    . "|::({$sH16}:){5}"
    . "|({$sH16})?::({$sH16}:){4}"
    . "|(({$sH16}:){0,1}{$sH16})?::({$sH16}:){3}"
    . "|(({$sH16}:){0,2}{$sH16})?::({$sH16}:){2}"
    . "|(({$sH16}:){0,3}{$sH16})?::{$sH16}:"
    . "|(({$sH16}:){0,4}{$sH16})?::"
    . "){$sLS32}"
    . "|((({$sH16}:){0,5}{$sH16})?::{$sH16}"
    . "|(({$sH16}:){0,6}{$sH16})?::"
    . "))";

    //set the IP address if required
    if(!is_null($sIPAddress)) {
      $this->setIPAddress($sIPAddress);
    }
  }

  /**
   * IP Address setter
   *
   * Sets the IP address string - this can
   * be either IPv4 or IPv6 format.
   *
   * @param  string  $sIPAddress
   * @return void
   */
  public function setIPAddress($sIPAddress) {
    $this->ip_address = $sIPAddress;
  }

  /**
   * IP Address getter
   *
   * Returns the IP address string - this
   * can be either IPv4 or IPv6 format.
   *
   * @return string
   */
  public function getIPAddress() {
    return $this->ip_address;
  }

  /**
   * IPv4 RegExp getter
   *
   * Returns Regular Expression used to
   * validate IPv4 addresses.
   *
   * @return string
   */
  public function getIPv4RegExp() {
    return '/^' . $this->match_ipv4 . '$/';
  }

  /**
   * IPv6 RegExp getter
   *
   * Returns the Regular Expression used to
   * validate IPv6 addresses.
   *
   * @return string
   */
  public function getIPv6RegExp() {
    return '/^' . $this->match_ipv6 . '$/i';
  }

  /**
   * IPv4 validation
   *
   * Validates the stored IP address
   * against the IPv4 pattern and returns
   * a boolean denoting whether the address
   * if of IPv4 format or not.
   *
   * @return bool
   */
  public function validateIPv4() {
    return ip2long($this->ip_address) && ip2long($this->ip_address) !== -1 ? true : false;
  }

  /**
   * IPv6 validation
   *
   * Validates the stored IP address
   * against the IPv6 pattern and returns
   * a boolean denoting whether the address
   * if of IPv6 format or not.
   *
   * @return bool
   */
  public function validateIPv6() {
    return preg_match($this->getIPv6RegExp(), $this->ip_address) ? true : false;
  }

  /**
   * General validity check
   *
   * Validates the stored IP address against
   * both the IPv4 and IPv6 patterns - if
   * EITHER matches then true is returned
   * (it's a correctly formatted IP address).
   *
   * Otherwise it's not a valid IP address
   * and false is returned.
   *
   * @return bool
   */
  public function isValid() {
    return $this->validateIPv4() || $this->validateIPv6() ? true : false;
  }

  /**
   * Reserved state checker
   *
   * This method checks wheter the stored IP address
   * is part of the local network range (i.e. it's in
   * the private reserved IP address range)
   *
   * A boolean is returned denoting this reserved state
   * unless the IP address itself is invalid - in which
   * case null is returned.
   *
   * @return bool
   */
  public function isReserved() {

    //IPv4 format
    if($this->validateIPv4()) {
      return $this->_getIPv4IsReserved($this->ip_address);
    }

    //IPv6 format
    elseif($this->validateIPv6()) {
      //IPv4 masking
      // this falls over if the IPv4 part is short-handed
      // for instance ::ffff:192.0.2.128 can be written as ::ffff:c000:280
      $reIPv4Masking = '/^((0{1,4}:){6}|(0{1,4}:){1,5}ffff:|::ffff:)(([0-9]{1,3}\.){3}[0-9]{1,3})/';

      //standard reserved IPv6 addresses
      //local loopback = 0:0:0:0:0:0:0:1 || ::1
      if(preg_match('/^(0{1,4}:){1,7}1|::1|fc00:.*$/i', $this->ip_address)) {
        return true;
      }

      //if this is really an IPv4 address stacked in IPv6...
      elseif(preg_match($reIPv4Masking, $this->ip_address)) {
        $sIPv4Address = preg_replace($reIPv4Masking, "$2", $this->ip_address);
        return $this->_getIPv4IsReserved($sIPv4Address);
      }

      //not reserved
      else {
        return false;
      }
    }

    //invalid format
    else {
      return null;
    }
  }

  /**
   * IPv4 reserved state checker
   *
   * Private method to determine whether an IPv4 address is in
   * one of the reserved private brackets (e.g. it's probably local)
   *
   * Returns a boolean denoting whether it's a reserved IPv4 address
   * or null should the IP address fail validation
   *
   * @param  string  $sIPv4Address
   * @return bool
   */
  private function _getIPv4IsReserved($sIPv4Address) {
    $sIP = long2ip(ip2long($sIPv4Address));
    $reIPv4 = '/([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})/$'; //just a quick and dirty RegExp without sanity checking since we've already done that

    if(preg_match($reIPv4, $sIP)) {
      //break the IP address into parts and cast to integers
      $iIPp1 = VParse::toInt(preg_replace($reIPv4, "$1", $sIP));
      $iIPp2 = VParse::toInt(preg_replace($reIPv4, "$2", $sIP));
      $iIPp3 = VParse::toInt(preg_replace($reIPv4, "$3", $sIP));
      $iIPp4 = VParse::toInt(preg_replace($reIPv4, "$4", $sIP));

      //check for reserved IP addresses
      // 127.0.0.1 (local loopback)
      // 10.0.0.0 - 10.255.255.255
      // 172.16.0.0 - 172.31.255.255
      // 192.168.0.0 - 192.168.255.255
      if( ($iIPp1 == 127 && $iIPp2 == 0 && $iIPp3 == 0 && $iIPp4 == 1) || $iIPp1 == 10 || ($iIPp1 == 172 && $iIP2 >= 16 && $iIP2 <= 31) || ($iIPp1 == 192 && $iIPp2 == 168) ) {
        return true;
      }

      //not part of the standard private IP address ranges
      else {
        return false;
      }
    }

    //invalid format
    else {
      return null;
    }
  }

//end class
}

EDIT: just noticed this relies on my variable parsing class VParse - you can pretty much replace any instance of VParse::toInt() with PHP's standard (int) type casting functionality.

Few tips on choosing the column type for IP Addresses.

When IPs are stored as human readable strings, the maximum length of IPv4 and IPv6 is VARCHAR(15) and VARCHAR(39) respectively.

We can store IPs(both IPv4 & IPv6) in binary form by setting data type to VARBINARY(16). We can use PHP functions inet_pton() and inet_ntop() to store and retrieve IP from DB.

Pros of using VARBINARY type: By storing IPs in binary form it will consume less disk space, smaller index size means better performance(fetching/inserting etc.), lesser memory(RAM) will be used to cache the data/indexes.

Cons of using VARBINARY type: The value stored is not human-readable.