PDO: how to save packed IPv6 representation into the database

Asked Jun 19 '16 at 07:21

Active Jun 19 '16 at 07:21

Viewed 51 times

Database:

CREATE TABLE `users_IPs` (
  `IP6` decimal(16,0) NOT NULL,
  PRIMARY KEY (`IP6`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

We want to insert an IPv6 address into this table:

$dsn = 'mysql:dbname=test;host=localhost';
$user = 'user';
$password = 'secret';

try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    $error = 'ERROR (1-CONNECT) ACCOUNT NOT READY'.$e->getMessage();
    return false;
  }

$sql = "INSERT INTO users_IPs SET IP6=:IP6
              ON DUPLICATE KEY UPDATE IP6=:IP6";

//Here we get an array of 16 unsigned bytes
//representing the IPv6 address
$IP6 = inet_pton("fe80::2e0:4cff:feb0:f8d");

$stmt = $dbh->prepare($sql);

//the problem is here
//tried PDO::PARAM_LOB, but it doesn't work
$stmt->bindParam(':IP6', $IP6, PDO::PARAM_LOB);
$result = $stmt->execute();

if ($result === false)
{
    print $stmt->errorInfo();
    return false;
}

$sql = "SELECT IP6 FROM users_IPs;";
$stmt = $dbh->prepare($sql);
$result = $stmt->execute();
$IP = $stmt->fetchColumn(0);

//string(1) "0" <- ? Just 1 byte
var_dump($IP);
//PHP Warning:  inet_ntop(): Invalid in_addr value
$IP =  inet_ntop($IP);
//outputs: bool(false)
var_dump($IP);

In the database the value for this column is "0". So, it seems that PDO doesn't save $IP6 as an array of bytes, but converts it in some way. Which make the value incorrect.

asked Jun 19 '16 at 07:21

user4035

22,508
11
59
94

You cannot use `:IP6` twice in a query. Use `:IP6A` and `:IP6B` instead and bind twice. – KIKO Software Jun 19 '16 at 07:29
Use `INSERT IGNORE` instead of `ON DUPLICATE KEY UPDATE` – zerkms Jun 19 '16 at 07:31
@KIKOSoftware you can if emulation mode is turned on – Your Common Sense Jun 19 '16 at 07:33
@KIKOSoftware Tried it, but the values is still "0" – user4035 Jun 19 '16 at 07:34
@My Common Sense: Yes, that's true: "You cannot use a named parameter marker of the same name more than once in a prepared statement, unless emulation mode is on.": http://php.net/manual/en/pdo.prepare.php – KIKO Software Jun 19 '16 at 07:35
Btw, ipv6 is 128 bits, not obvious how you want to fit it into 16 digits number. – zerkms Jun 19 '16 at 07:36
@zerkms Divide 128 by 8 = 16 bytes – user4035 Jun 19 '16 at 07:36
@user4035 "Divide 128 by 8 = 16 bytes" --- this makes very little sense. – zerkms Jun 19 '16 at 07:38
16 bytes <> 16 decimal digits – Your Common Sense Jun 19 '16 at 07:38
@YourCommonSense True. In this case what is the correct database type for it. – user4035 Jun 19 '16 at 07:41
@KIKOSoftware Thx, I'll try VARBINARY(16) – user4035 Jun 19 '16 at 07:42

PDO: how to save packed IPv6 representation into the database

0 Answers0