将IPv6存储在UNKNOWN DB中的最佳方法？ [英] 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）？

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函数仅随 php 5.3.0 ，即使可以对它们进行反向工程）。我真的很喜欢 Jake 所做的事情和他的关于DB中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字符串到 varchar（45）。但是，那些想遵循复杂路线的人将如何使用PHP（反向工程作为djmaze（AT）dragonflycms（。）org 或作为上建议使用MagicicalTux，并以二进制形式存储和检索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)

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