Geek asks and answers

Discovery of IPv6 address in PHP and its proper storage in MySQL. How?

I read a few of the questions already asked, and I found this to be useful, although I have not tried it. Working with IPv6 addresses in PHP

However, let's say in MySQL there is a table of "bans." How can I store an IPv6 address? The method must be universal, that is, the field must contain either ipv4 or ipv6 addr. This should also apply to my ip_addr field in my users table.

I usually checked if(getip == $bans['ip']) { do something } But my getip function for ipv4 afaik, and I wonder if it will work.

Function i used

function getip()
{
    if(isset($_SERVER['REMOTE_ADDR']))
    {
        $ip = $_SERVER['REMOTE_ADDR'];
    }
    elseif(isset($_SERVER['HTTP_X_FORWARDED_FOR']))
    {
    if(preg_match_all("#[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}#s", $_SERVER['HTTP_X_FORWARDED_FOR'], $addresses))
    {
        foreach($addresses[0] as $key => $val)
        {
            if(!preg_match("#^(10|172\.16|192\.168)\.#", $val))
            {
                $ip = $val;
                break;
            }
        }
    }
}

if(!isset($ip))
{
    if(isset($_SERVER['HTTP_CLIENT_IP']))
    {
        $ip = $_SERVER['HTTP_CLIENT_IP'];
    }
    else
    {
        $ip = '';
    }
}

$ip = preg_replace("#([^.0-9 ]*)#", "", $ip);
return $ip;
}
+4
source share
2 answers

However, let's say in MySQL there is a table of "bans." How can I store an IPv6 address?

VARCHAR (40).
IPv6 max 40 :

2001:0DB8:0000:0000:0000:0000:1428:57ab

coulmn IPv4

0

mysql ipv6 . VARCHAR(), .

BINARY (16), BIGINT UNSIGNED DECIMAL.

, 2 000 000 IP- 100 .

CREATE TABLE ipv6_address_binary (
    id SERIAL NOT NULL AUTO_INCREMENT PRIMARY KEY,
    addr BINARY(16) NOT NULL UNIQUE
);

CREATE TABLE ipv6_address_twobigints (
    id SERIAL NOT NULL AUTO_INCREMENT PRIMARY KEY,
    haddr BIGINT UNSIGNED NOT NULL,
    laddr BIGINT UNSIGNED NOT NULL,
    UNIQUE uidx (haddr, laddr)
);

CREATE TABLE ipv6_address_decimal (
    id SERIAL NOT NULL AUTO_INCREMENT PRIMARY KEY,
    addr DECIMAL(39,0) NOT NULL UNIQUE
);

IP- . twobigints 1 , - .

.

:

X_ [HIGH/LOW] / 64- X

NETMASK_LOW 0, AND , true. .

SELECT COUNT(*) FROM ipv6_address_twobigints
WHERE haddr & NETMASK_HIGH = NETWORK_HIGH
AND laddr & NETMASK_LOW = NETWORK_LOW

SELECT COUNT(*) FROM ipv6_address_binary
WHERE addr >= NETWORK
AND addr <= BROADCAST

SELECT COUNT(*) FROM ipv6_address_decimal
WHERE addr >= NETWORK
AND addr <= BROADCAST

:

Average response times

BINARY_InnoDB  0.0119529819489
BINARY_MyISAM  0.0139244818687
DECIMAL_InnoDB 0.017379629612
DECIMAL_MyISAM 0.0179929423332
BIGINT_InnoDB  0.782350552082
BIGINT_MyISAM  1.07809265852
+9

More articles:


All Articles