在 UNKNOWN DB 中存储 IPv6 的最佳方式? [英] Best way to store an IPv6 in UNKNOWN DB?

问题描述

我知道这个问题已经被问过很多次了，但我找不到任何最终的解决方案；所以这里是:

I know that this question has been asked quite a few times, but I could not find anywhere a final resolution; so here goes:

如何使用最佳实践在数据库中存储 IP(IPv4 和 IPv6)而不知道数据库?这是用于数据库抽象目的的情况，例如 PHP PDO.

How do you store an IP (both IPv4 and IPv6) using best-practice, within a DB, without knowing the DB? This in case for DB abstraction purposes, such as PHP PDO.

顺便提一下，我有 PHP 5.2.17，因为我在 Windows 上需要 PEAR.

On a minor note, I have PHP 5.2.17, as I needed PEAR on windows.

人们一直建议将其存储为 varbinary(16) 并使用 mysql 函数 inet6_ntop 和 inet6_pton 作为字符串来回传递 IP；如使用 IPv6 功能扩展 MySQL 5正在建议.在 PHP 中，inet_pton() 和 inet_ntop() 函数可以将 IPv4 和 IPv6 来回转换为二进制格式，如 ThiefMaster这个问题是在暗示，但它是不清楚如何将二进制内容传递到 SQL INSERT/UPDATE 字符串中(这些 php 函数仅提供 windows 上的 php 5.3.0，即使可以对这些进行逆向工程).我真的很喜欢 Jake 和 他关于数据库中 IP 的整数表示的结果，这可能在一些遥远的地方派上用场，不可预见的未来，如果我要将其实现到我的数据库中，但是我不确定使用 PHP PDO 进行数据库抽象的数据库交叉兼容性.这篇文章似乎提供了关于存储二进制值的接近答案，但不是未转义的二进制注入串成一个潜在的危险?另外，如果你按照这条路线走，如果一些开发者想要做一些快速查找，有多少 DB 可以将 varbinary(16)/int(128bit) 转换为代表性 IP?

People have been suggesting to store it as a varbinary(16) and use mysql functions inet6_ntop and inet6_pton to pass IPs back and forth as strings; like Extending MySQL 5 with IPv6 functions is suggesting. In PHP, the functions inet_pton() and inet_ntop() can convert IPv4 and IPv6 back and forth to binary format as ThiefMaster in this question is suggesting, but it is unclear how one would pass binary content into a SQL INSERT/UPDATE string (and these php functions are only provided with php 5.3.0 on windows, even though it is possible to reverse engineer these). I really like what Jake did and his results with regards to integer representations of IPs in DBs, and this may come in handy in some distant, unforeseen future, if I were to implement this into my DB, but then I'm unsure about DB cross-compatibilities for DB abstraction using PHP PDO. This post seems to provide a close answer about storing binary values, but isn't unescaped binary injection into strings a potential hazard? Also, if you follow this route, how many DBs can convert a varbinary(16)/int(128bit) into a representational IP, if some developer wanted to do some quick lookups?

在我看来，最简单的方法是插入 ip string as-is 转换成 varchar(45).但是那些想要在 PHP 中遵循复杂路线的人如何(逆向工程 作为 djmaze(AT)dragonflycms(.)org 或作为 MagicalTux at FF dot st 建议)使用 inet_ntop() 和 inet_pton() 函数，以二进制形式存储和检索 IPv6?有人可以举一个使用来自 <?php $strIP = "2001:4860:b002::68"; 的 PDO 的例子吗??>，使用 INSERT 然后 SELECT 准备好的语句?

It seems to me that the most simple way is to insert the ip string as-is into a varchar(45). But how would those who want to follow the complicated route, in PHP (reverse-engineered as djmaze(AT)dragonflycms(.)org or as MagicalTux at FF dot st is suggesting) using the inet_ntop() and inet_pton() functions, store and retrieve an IPv6 as binary? Can someone give an example using PDO from <?php $strIP = "2001:4860:b002::68"; ?>, using an INSERT and then SELECT prepared statements?

如您所见，我已经完成了我的研究，但我不清楚这种 IPv6 的最终良好实践.

As you can see, I've done my research, but the ultimate good-practice of this IPv6 isn't clear to me.

推荐答案

就像您的研究表明的那样，将 IP 地址存储为规范形式的字符串、二进制字符串或整数既有好处也有问题.也许在数据库中存储 IP 地址有一个中间立场.

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));
  }
}

还有一些例子:

/* 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'));

哪些产品:

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

这篇关于在 UNKNOWN DB 中存储 IPv6 的最佳方式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！