Stockage des IP : le mystère de l’adresse 127.255.255.255

17 février 2011 par stephane

Il est assez courant d’avoir besoin de stocker des adresses IP dans une base MySQL et malheureusement il n’est pas très courant que la manière de faire soit optimisée. Cet article vous propose de faire le point sur le sujet, ainsi que sur une erreur qu’on rencontre quand on fait presque bien les choses, mais pas tout à fait.

Comme il n’existe pas de type natif sous MySQL pour les adresses IP, la première idée consiste à se servir d’une chaîne de caractères. Une adresse IPv4 fait au maximum 15 caractères (aaa.bbb.ccc.ddd), on peut donc prendre un champ VARCHAR(15) qui utilisera au maximum 16 octets par valeur, soit 15 au maximum pour l’adresse plus 1 pour stocker la taille. Pour l’exemple, voici une table visites suivant cette idée :


CREATE TABLE visites (
  user_id int(11) NOT NULL DEFAULT '0',
  ip varchar(15) NOT NULL DEFAULT '',
  total int(11) NOT NULL DEFAULT '0'
)

Ce système fonctionne très bien mais il conduit à créer des tables volumineuses quand le nombre d’adresses devient important (plusieurs millions par exemple). Heureusement MySQL propose deux fonctions très intéressantes qui permettent de convertir une adresse IP en entier et vice-versa : il s’agit des fonctions INET_ATON() et INET_NTOA(), ATON comme ‘Address TO Number’ et NTOA comme ‘Number TO Address’, n’est-ce pas ? Démonstration :

mysql> SELECT INET_ATON('127.0.0.1');
+------------------------+
| INET_ATON('127.0.0.1') |
+------------------------+
|             2130706433 |
+------------------------+
1 row in set (0.00 sec)


mysql> SELECT INET_NTOA(2130706433);
+-----------------------+
| INET_NTOA(2130706433) |
+-----------------------+
| 127.0.0.1             |
+-----------------------+
1 row in set (0.00 sec)

Le gros avantage de ces 2 fonctions est qu’il devient possible de stocker des adresses IP dans une colonne INT, soit 4 octets par ligne au lieu de 16. Notre table visites deviendrait donc :

CREATE TABLE visites (
  user_id int(11) NOT NULL DEFAULT '0',
  ip int(11) NOT NULL DEFAULT 0,
  total int(11) NOT NULL DEFAULT '0'
)

Jusque-là, tout va bien ! Les problèmes vont commencer quand vous allez laisser votre application remplir la table et qu’au bout d’un certain temps, vous allez chercher à regarder les adresses IP collectées. En effet, il est très probable que de nombreuses lignes correspondent à 127.255.255.255 et que vos utilisateurs non avertis viennent vous demander à qui appartient cette adresse qui ressort tout le temps…

L’explication est assez simple quand on sait comment fonctionne la fonction INET_ATON(). Pour une adresse aaa.bbb.ccc.ddd, chaque valeur a 256 combinaisons (de 0 à 255), et la manière la plus facile pour obtenir un entier unique à partir d’une telle adresse est de faire le calcul suivant :

aaa * 256^3 + bbb * 256^2 + ccc * 256^1 + ddd * 256^0

INET_ATON() utilise cette petite formule. En sachant que 256 = 2^8, elle devient :

aaa * 2^24 + bbb * 2^16 + ccc * 2^8 + ddd

et dès que aaa est supérieur ou égal à 128, soit 2^7, le résultat devient supérieur à 2^7 * 2^24 = 2^31.

Un champ INT peut stocker 2^32 valeurs, mais comme par défaut, le champ accepte aussi bien les valeurs négatives que les valeurs positives, la valeur maximum descend à 2^31 -1 (à cause de zéro!), ce qui signifie que toutes les adresses dont le premier chiffre est supérieur à 128 provoqueront une erreur si votre sql_mode est suffisamment strict ou seront tronquées à la valeur 2^31 – 1 si votre sql_mode. Et justement, la conversion vers une IP de 2^31 – 1 donne 127.255.255.255 !

La correction consiste à bien s’assurer que le champ stockant les IP est déclaré en unsigned, ce qui nous donne la table :

CREATE TABLE visites (
  user_id int(11) NOT NULL DEFAULT '0',
  ip int(11) unsigned NOT NULL DEFAULT 0,
  total int(11) NOT NULL DEFAULT '0'
)

Si vous avez le problème, sachez qu’il n’est malheureusement pas possible de récupérer la véritable valeur des lignes tronquées.

Mots-clefs : ,

7 commentaires sur “Stockage des IP : le mystère de l’adresse 127.255.255.255”

  1. Xavier dit :

    Comment fait-on pour l’ipv6 ? ;-)

    On est en 2011 !

  2. sebclick dit :

    J’ai pensé la même chose que Xavier en lisant l’article ;-)

  3. Pierre dit :

    Je ne connaissais pas trop les fonctions INET_ATON() et sa réciproque, merci pour le partage, car je stockais souvent sous forme varchar justement.

    Par contre, quand je sais que mon champ, de type entier, ne contiendra « jamais » de valeur négative, alors il passe direct en unsigned ( par principe )

  4. stephane dit :

    Ah, j’avais tendu la perche en espérant que quelqu’un connaisse une solution pratique pour l’ipv6 :) .
    Je ne connais à l’heure actuelle que des bidouilles, par exemple http://www.koopman.me/2008/04/storing-ipv6-ips-in-mysql/

  5. stephane dit :

    @Pierre : c’est une bonne idée en effet de mettre automatiquement les champs entiers en unsigned quand on sait qu’ils n’auront jamais de valeurs négatives. Tout le monde n’a pas ce réflexe !

  6. Denis dit :

    Il n’ a aucun mystère là-dedans. Il est normal qu’un service réseau broadcaste le sous-réseau 127.0.0.0/8.

  7. stephane dit :

    Certes. Mais il n’est pas question ici de service réseau, juste du stockage d’une adresse IP dans une ligne d’une table. Et il n’est à première vue pas vraiment logique qu’en voulant stocker une adresse, on se retrouve avec une autre adresse enregistrée.

Laisser une réponse