juin
2012
Un article de zinzineti
La norme SQL n’a rien proposé jusqu’à ce jour pour stocker les types de données network (IPv4, IPv6, masque de sous-réseau,…) . Les « grands » éditeurs de SGBD ne disposent pas encore de types de données capable de stocker et de manipuler aisément les données de types IP par exemple. Dans ce domaine PostGreSQL est largement en avance. Pour illustrer mes propos prenons un exemple simple :
Imaginer une application web qui stocke l’adresse IP des ordinateurs des utilisateurs dans une table nommée « TableDesIPs » pour faciliter un certain nombre d’opérations : identification, redirection, restriction, filtrage, … SQL Server et ORACLE sont aujourd’hui incapables de donner une réponse favorable à la requête suivante : SELECT IP_VISITEUR FROM TableDesIPs WHERE IP_VISITEUR > IP_MIN AND IP_VISITEUR < IP_ADR_MAX
C’est à dire lister les adresses IP membre d’une plage d’adresse IP. La borne inférieure de la plage est IP_MIN et la borne supérieure IP_MAX.
Par contre PostGreSQL se démarque en implémentant les types de données : CIDR et INET qui permettent de stocker et de manipuler aisément les données de type IP. Voyons quelques rustines pour stocker des données IPv4 et MAC-48 sous SQL Server
Quelques rustines pour stocker des données IPv4 sous SQL Server
=> Stocker IPv4 sous SQL Server
–> Création de la fonction de validation d’IPv4
RETURNS SMALLINT
AS
BEGIN
DECLARE @IsOK SMALLINT;
-- Vérifier si on que des nombres en dehors des points
IF (ISNUMERIC(REPLACE(@IsIPv4,'.',''))=1
-- Vérifier s'il y a 3 points dans la chaine
AND LEN(@IsIPv4) - LEN(REPLACE(@IsIPv4,'.','')) = 3
-- Vérifier que les nombres de bits pour l'IPV4 est compris entre 0 et 255
AND 0 <= CAST(PARSENAME(@IsIPv4,1) as SMALLINT) AND CAST(PARSENAME(@IsIPv4,1) as SMALLINT) <= 255
AND 0 <= CAST(PARSENAME(@IsIPv4,2) as SMALLINT) AND CAST(PARSENAME(@IsIPv4,2) as SMALLINT) <= 255
AND 0 <= CAST(PARSENAME(@IsIPv4,3) as SMALLINT) AND CAST(PARSENAME(@IsIPv4,3) as SMALLINT) <= 255
AND 0 <= CAST(PARSENAME(@IsIPv4,4) as SMALLINT) AND CAST(PARSENAME(@IsIPv4,4) as SMALLINT) <= 255
)
SELECT @IsOK = 1
ELSE
SET @IsOK = 0
RETURN @IsOK
END
–> Test de Stockage d’IP
INSERT TableIPv4 VALUES('10.276.33.25')
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint « CK__TableIPv4__IPv4__090A5324″. The conflict occurred in database « AdventureWorks », table « dbo.TableIPv4″, column ‘IPv4′.
The statement has been terminated.
INSERT TableIPv4 VALUES('10.226.33.25')
(1 row(s) affected)
Comment stocker des adresses mac sous SQL Server ?
Rappelons que l’adresse MAC-48 est constituée de 48 bits (6 octets) se représente sous la forme hexadécimale avec les octets séparés par deux point (:) ou par un tiret (-)
Exemples :
——–
08-00-2B-01-02-03
08:00:2B:01:02:03
=> Stocker ADRESSE MAC sous SQL Server
–> Création de la fonction de validation MAC-48
RETURNS SMALLINT
AS
BEGIN
DECLARE @IsOK SMALLINT;
-- Vérifier si on que des nombres en dehors des points
IF (LEN(REPLACE(REPLACE(@MacAddress,'-',''),':',''))= 12
-- Vérifier s'il y a 3 points dans la chaine
AND (@MacAddress LIKE '[0-9a-fA-F][0-9a-fA-F]-[0-9a-fA-F][0-9a-fA-F]-[0-9a-fA-F][0-9a-fA-F]-[0-9a-fA-F][0-9a-fA-F]-[0-9a-fA-F][0-9a-fA-F]-[0-9a-fA-F][0-9a-fA-F]'
OR @MacAddress LIKE '[0-9a-fA-F][0-9a-fA-F]:[0-9a-fA-F][0-9a-fA-F]:[0-9a-fA-F][0-9a-fA-F]:[0-9a-fA-F][0-9a-fA-F]:[0-9a-fA-F][0-9a-fA-F]:[0-9a-fA-F][0-9a-fA-F]'
)
)
SELECT @IsOK = 1
ELSE
SET @IsOK = 0
RETURN @IsOK
END
–> Test de Stockage de MAC-48
INSERT TableMacAddress48 VALUES('08-00-2G-01-02-03')
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint « CK__TableMacA__MacAd__0CDAE408″. The conflict occurred in database « AdventureWorks », table « dbo.TableMacAddress48″, column ‘MacAddress48′.
The statement has been terminated.
INSERT TableMacAddress48 VALUES(’08-00-2B-01-02-03′)
(1 row(s) affected)
——————————————————
— Etienne ZINZINDOHOUE
——————————————————
Oui c’est sûr : quelque soit la solution choisie, il va bien falloir s’y mettre à IPv6 !
Pour revenir au sujet, je trouve l’approche de Microsoft intéressante (merci pour le lien !), mais si je pense que ce n’est pas la meilleure, car les calculs sur les sous-réseaux, masques réseau et autres me semblent plus complexes que si on utilise un entier pour une IPv4. Mais ça reste déjà plus efficace qu’un stockage en chaine de caractères.
Concernant IPv6, je suis actuellement confronté (en tant qu’admin réseau) à ce problème car très peu (pour pas dire aucun) logiciels internes et progiciels ne gèrent l’IPv6, et quand j’en parle à des développeurs, leur mines déconfites me dit dire qu’on est vraiment pas prêt au niveau des outils, alors qu’on va devoir bientôt être capable de gérer IPv4 et IPv6. Donc Messieurs/dames, au boulot !
@Greg01 : Merci pour ton commentaire, au fait MS propose de stocker chaque partie d’une IPv4 dans différente colonne de type tinyint. c-a-d pour une adresse IP qui est sous la forme A1.A2.A3.A4 il faut quatre colonnes de type tinyint : une pour A1, une pour A2, une pour A3 et une pour A4. c’est comme si on applique un split à l’IP avec le point comme séparateur. Après pour les opérations de trie et de comparaison il faut utiliser « PARSENAME »…. plus plus d’infos voir : http://msdn.microsoft.com/fr-fr/library/aa224788(v=sql.80).aspx
et ça c’est pour l’IPv4.
à ma connaissance MS ne propose rien pour le stockage de l’IPv6 !!!
Une pression de la communauté des utilisateurs de SQL Server ou Oracle peut amener ces éditeurs à se pencher sérieusement sur le stockage des données de type networks (IPv4, IPv6, …)
A +
Sympa l’article mais , (enfin) à l’heure de son lancement, quid de l’IPv6 !!!
Certes l’IPv4 est encore très utilisée, mais il va quand même falloir s’habituer à la considérer comme obsolète en proposant systématiquement une solution IPv6 en regard d’une solution IPv4.
D’autant plus que aujourd’hui, contrairement à il y a ne serait-ce qu’1 an, l’IPv6 ça marche quand même plutôt bien.
Greg01 >
Une IPv4 tient dans un entier 32 bits (Int32). No soucy
Pour l’adresse MAC, il faut 48 bits. Ça tient dans un Long (64bits).
Par contre pour une adresse IPv6, ça va être plus compliquer. Il faut 128 bits, il n’y a pas beaucoup de langages capables de manipuler des entiers si grands (pour le moment en tout cas).
Bonjour,
Je me permet de réagir sur votre article car je trouve incroyable de stocker une ip sous forme de chaine de caractères (avec les problèmes que vous évoquez), alors qu’une ip peut facilement se coder par un entier. Et cela résout tout les problèmes de tri, recherche, comparaison, etc.
Voici un exemple de code qui permet « d’encoder » et « décoder » une ip en entier : http://www.cppfrance.com/codes/CODAGE-DECODAGE-ADRESSE-IP-CODE_10450.aspx
Je n’ai pas cherché, mais je suppose qu’il est assez simple de faire la même chose pour l’adresse mac, ipv6, etc.