Hacking Network addresses in your database

Published 9.01.2005 by jm | E-mail this post

Storing an IP address in a database table

This is credited to Arjen Lentz

Say you have an IP address, 192.168.0.10 and want to store that in a database table. You could of course store it in a CHAR(15) and that is in fact what many people do. But you probably want to search on this field and therefore want it indexed also. So can we do better than using a 15 byte character field? We sure can.

MySQL has two built-in functions: INET_ATON() and INET_NTOA(). They are actually based on the equivalent inet_aton() and inet_ntoa() which are C library functions present on pretty much every TCP/IP capable system. Why? These two functions are used allover the place in any TCP/IP stack implementation or even application.
The INET_ATON() function converts Internet addresses from the numbers-and-dots notation into a 32-bit unsigned integer, and INET_NTOA() does the opposite. Isn't that handy!

Let's put it to the test:

mysql> SELECT INET_ATON('192.168.0.10') AS ipn;
+------------+
| ipn        |
+------------+
| 3232235530 |
+------------+

mysql> SELECT INET_NTOA(3232235530) AS ipa;
+--------------+
| ipa          |
+--------------+
| 192.168.0.10 |
+--------------+

So you can store an IP address in an INT UNSIGNED (4 bytes) which is of course much more efficient and faster than a CHAR(15). Naturally, you can call the function while you're inserting, so something like this is fine also:

INSERT INTO tbl VALUES (..., INET_ATON('192.168.0.10'), ...)

In MySQL 5.0, you can even do this transformation inside a LOAD DATA INFILE command, without using temporary columns:

LOAD DATA INFILE 'filename'
INTO TABLE tbl
...
(col1, ..., @ipa1, ..., coln)
SET ipn = ATON_ATON(@ipa);

So in the list of columns you assign this column to a server-side variable, and then assign the transformed value to the proper column in the SET clause. Quite elegant, really.

As a followup, Perth Penguinista states:

PostgreSQL also deals with network addresses, and I looked this up since a peer is currently migrating his LoB application away from MS-SQL-Server and said app deals with network addresses.

It turns out that PostgreSQL’s network address handling is a real cornucopia, with functions available to return stuff like the broadcast address, netmask, mask width or most-abbreviated representation of the network described (either directly or by an address within it).

It has two datatypes (three, if you count the MAC-address type), one (appropriately enough, cidr) for holding strict CIDR specifications and a another (inet) slightly looser type which will store and operate on an address and a mask a la Samba’s “interfaces” configuration directive.

PostgreSQL has so very many useful datatypes (geometry, spatial, etc) and such a plethora of operators and functions for them (e.g. the INTERVALs (including infinite intervals), OVERLAPS and EXTRACT operators in the time functions) that it’s sometimes very difficult to refrain from using them lest they need to be recreated for every other “lesser” database which the application might use. It’s richer than a chocolate mudcake in datatypes.

If the human body was never exposed to ailments, it would be impressivly vulnerable to the slightest cold. If our country was never exposed to hacking, it would be oppressivly vulnerable to cyber terrorism. With out the creation of a malicious hacking, Afganistan could have destroyed America's economy with a ping flood. This is why I encourange maclicious hacking, as an ethical practice. Without strengthening our defenses, we are weak. This site is focused on security through knowledge. I detest the fact that so many companies are being exploited because malicious hackers know their security holes before they do. For that reason, I hope to educate where the exploits lay. This isn't a 100% information base, as I only publish things I have been able to implement on myself. No credit is needed anywhere . However if you are a publisher, I would appriciate credit. I am an advocate of open source, so copy and paste and call it your own if you like. If my work is good enough for you to plagerize then that is my biggest compliment . If my work is good enough, I will be approached and asked to write more ... this is natural selection of the digital age .

Previous hacks

Previous Hacks

This link kills spam

spam IP addresses

Cost of the War in Iraq

(JavaScript Error)

Two very recommended books:

. . The only hacking forum I have found worth mentioning here