Free US Zip Code Database

So as one of my sites grew I quickly found out that my zip code database was horribly incomplete with only 33234 records and it even had duplicates for some zip codes. I did some searching and came across a post about a zip code csv file but the post goes to what looks like an empty page with no file. A little digging lead me to the zip code file download page that gives you a csv with entries like this:

"78701","Austin","TX","30.27127","-97.74103","-6","1"

Awesome! This csv file has 43191 unique records and has entries for each zip code my users have reported as missing. I started by looking up how to import a csv into MSSQL and followed an article called Using BULK INSERT to Load a Text File from which I came up with my query.

BULK INSERT ZipCodes FROM 'c:\zipcodes.csv' WITH (FIELDTERMINATOR = '"",""', ROWTERMINATOR = '\n')

It didn’t work and after a couple hours of trial and error I threw my hands up in the air and abandoned the bulk insert and fell back on another option of using phpMyAdmin. It sounds crazy but I setup the database in MySQL and imported the csv on the first attempt! Now to get it back to to MSSQL I exported the database as single row imports that looked like this:

INSERT INTO `ZipCodes` VALUES('78701', 'Austin', 'TX', '30.27127', '-97.74103', -6, 1);

A few quick find and replaces to change the database name and remove the ` characters and I was able to run them as queries on my MSSQL database in blocks of 5500 to prevent timeouts.

INSERT INTO [databaseName].[dbo].[ZipCodes] VALUES('78701', 'Austin', 'TX', '30.27127', '-97.74103', -6, 1);

It’s crucial to have a fairly complete zip code database for distance searching on my free online dating site and I’m glad I got this sorted out and updated.

Blocking IP addresses by country on IIS shared hosting

On a shared hosting environment you often won’t have access to ban IP addresses on the IIS level and my shared host didn’t offer the Firewall module for Plesk so I had to come up with a programmatic way of blocking large blocks of IPs. You can setup in your web.config file something called a HTTPModule that works before the page is delivered to the client computer. For this sample I added a key and value to my web.config file that holds my comma separated IP list that I want to block. Since I’m running a site that is focused on the United States there isn’t much overlap on the /24 block area 255.255.255.* so I am only checking the first 3 digit blocks for matches. When a request comes in IpTwentyFourBlockingModule will check the users IP address against the key value blockiptwentyfour to see if there is a match and if it is a match will return 403 forbidden to the client browser, banned!

Coming up with a IP list is another problem. I found IP Location Tools that gives out an API that generates a updated list of IPs for a given country. The problem is the list gets broken out into IP blocks other than /24, you’ll see all ranges of IP blocks that get very complicated very fast and more than I wanted to try and pull off in a days work. I wrote a Flash ActionScript 3 application to consume this data and give me a list of unique IPs 255.255.255 that I could then drop in as the value for blockiptwentyfour. I then added IP tracking for new users and have had to ban some rogue /24 blocks that escaped this list but its kept them at bay and more manageable for now. I’ve already had a colleague suggest that this is still only a stopgap at best and I should develop some kind of throttling system to help prevent spam when I’m not around to watch the site like a hawk.

web.config

1
2
3
4
5
6
7
8
9
10
<configuration>
  <appSettings>
    <add key="blockiptwentyfour" value="41.210.29,41.210.28" />
  </appSettings>
  <system.web>
    <httpModules>
      <add name="IpTwentyFourBlockingModule" type="IpTwentyFourBlockingModule" />
    </httpModules>
  </system.web>
</configuration>

IpTwentyFourBlockingModule.cs – put this is in your App_Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
#region Using
 
using System;
using System.Web;
using System.Configuration;
using System.Collections.Specialized;
 
#endregion
 
/// <summary>
/// Block the response to certain IP addresses
/// </summary>
public class IpTwentyFourBlockingModule : IHttpModule
{
 
    #region IHttpModule Members
 
    void IHttpModule.Dispose()
    {
        // Nothing to dispose; 
    }
 
    void IHttpModule.Init(HttpApplication context)
    {
        context.BeginRequest += new EventHandler(context_BeginRequest);
    }
 
    #endregion
 
    /// <summary>
    /// Checks the requesting IP address in the collection
    /// and block the response if it's on the list.
    /// </summary>
    private void context_BeginRequest(object sender, EventArgs e)
    {
        string ip = HttpContext.Current.Request.UserHostAddress;
        string[] tempIpArray = ip.Split('.');
        string iptwentyfour = tempIpArray[0] + "." + tempIpArray[1] + "." + tempIpArray[2];
 
        if (_IpAdresses.Contains(iptwentyfour))
        {
            HttpContext.Current.Response.StatusCode = 403;
            HttpContext.Current.Response.End();
        }
    }
 
    private static StringCollection _IpAdresses = FillBlockedIps();
 
    /// <summary>
    /// Retrieves the IP addresses from the web.config
    /// and adds them to a StringCollection.
    /// </summary>
    /// <returns>A StringCollection of IP addresses.</returns>
    private static StringCollection FillBlockedIps()
    {
        StringCollection col = new StringCollection();
        string raw = ConfigurationManager.AppSettings.Get("blockiptwentyfour");
 
        foreach (string ip in raw.Split(','))
        {
            col.Add(ip.Trim());
        }
 
        return col;
    }
}

collectipaddresses.as

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
import flash.events.*;
import flash.net.*;
 
var myRequest:URLRequest = new URLRequest("http://iplocationtools.com/country_query.php?country=CI,SN,GH,NA,NG")
var loader:URLLoader = new URLLoader();
 
loader.dataFormat = URLLoaderDataFormat.TEXT;
loader.addEventListener(Event.COMPLETE, handleComplete);
loader.load(myRequest);
 
function handleComplete(event:Event):void
{
	var loader:URLLoader = URLLoader(event.target);
	//trace(loader.data.replace(/\n/g, "|"));
	var ipArray:Array = loader.data.replace(/\n/g, "|").split("|");
	trace(ipArray.length);
	setTwentyFourBlock(ipArray);
}
 
function setTwentyFourBlock(ipArray:Array)
{
    var	shortIpArray:Array = new Array();
 
	var	lastUniqueIp:String = "";
 
	for each(var ip:String in ipArray)
	{
		var tempIpArray:Array = ip.split(".");
		var stringCurrentIp:String = tempIpArray[0]+"."+tempIpArray[1]+"."+tempIpArray[2]+".0";
		if(stringCurrentIp != lastUniqueIp)
		{
			lastUniqueIp = stringCurrentIp;
			shortIpArray.push(stringCurrentIp);
		}
	}
 
	trace(shortIpArray.length);
	trace(shortIpArray.join(','));
}

All of this work has really kept my free online dating site almost spam free for now but I’m adding IP blocks I’ve missed from time to time.

Validation of viewstate MAC failed.

A very popular error, so popular and in so many places still unresolved. I spent a while trying to track down what was causing this error on my new hosting environment. I felt it would be a great idea to go ahead and repost my successful solution. I first did a hefty bit of searching on Google of course. Going through dozens of blogs and forums that seemed to all have similar answers for how to solve it. Turn off event validation they would say! Everything I read on that was telling me it would be a security nightmare to have a public application with page validateRequest=”false” enableEventValidation=”false” viewStateEncryptionMode=”never” in your web config file. I tried it anyway and I still got “Validation of viewstate MAC failed.” and I was so frustrated!

I kept searching until I found http://www.developmentnow.com/articles/machinekey_generator.aspx that generated a machine key and the xml required for my web config file. I put that in there an set the page attribute enableViewStateMac to true and uploaded my changes. I waited, 5 minutes no error, 5 hours no error, 24 hours no error. I exclaimed with joy when I could refresh my free online dating search page after being idle a while and not see that terrible error. I hope this helps out someone else as I spent way to long tracking down an answer to this problem.