Store IP Address in Data Base best way

Question

Hi, I need create a table to store Black listed IP address in case of spammers in my blog. I would like to know:

• What is the best datatype to store IP addresses.
• What others field I can list in this table that you think could be useful against spammer.

I use MS SQL 2008 DB

Thanks for your time

Answer 1

"Octects in four tinyint columns." http://sqlserver2000.databases.aspfaq.com/how-should-i-store-an-ip-address-in-sql-server.html

Answer 2

Simplest thing is to store a char(15), assuming IPV4.

This is the simplest form to use, does not required complex conversions and calculations and unless you are storing very large amounts of data (millions of records), performance should not factor in.

Answer 3

Spammers are unlikely to keep using the same IP address so you'd be fighting a losing battle on that one. Better to implement a CAPTCHA than to try and eliminate IPs or use other data from the connection to 'filter' spammers. Most of this is done by automated tools so CAPTCHA is the best route.

Answer 4

In general, I think this would depend upon what type of queries you are going to fire. If queries are simple based on complete comparison of ipaddress or ipaddress is not part of a filter, I would say maintining it simply as varchar would be good approach. If you want to search with complicated queries such as rows belonging to ip addresses in one subnet etc then you may store the ipaddress as four int values in four columns.

Answer 5

Store it in the most easily used form. So if your blogging software gives you the IP address as a string, store it as a string. If the software gives you the incoming IP address as an unsigned integer, store it in that way. This will save you from having to convert the IP into usable form for every incoming connection.