Store IP Address in Data Base best way

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 fi开发者_StackOverfloweld I can list in this table that you think could be useful against spammer.

I use MS SQL 2008 as my database.

---

IP v4 addresses are 4 bytes, IP v6 addresses are 16 bytes, so store them as a varbinary(16) field. I see from your question tags that you are using .Net so you will be able to get these bytes easily using IPAddress.GetAddressBytes(). The following code will be useful to you if you're using Entity Framework or a similar ORM.

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Net;

    [Required, MinLength(4), MaxLength(16)]
    public byte[] IPAddressBytes { get; set; }

    [NotMapped]
    public IPAddress IPAddress
    {
        get { return new IPAddress(IPAddressBytes); }
        set { IPAddressBytes = value.GetAddressBytes(); }
    }

---

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

---

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.

---

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.

---

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.

---

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.