Prevention
1.    Sanitize user input.
It’s absolutely vital to sanitize user input to insure that it does not contain dangerous code. If the field is for a zip code in the US then it only needs to accept numbers. Even further US zip codes do not exceed 10 numbers so why even bother giving the space. Sure that could someday change, but it’s fairly constant. This can be done in the form of Try/Catch statements in code or other methods such as URLScan.

It is important to realize that only secure code can protect protect the system. A firewall provides no help under this kind of attack. An intrusion detection system may mitigate the problem to some extent, but IDS rule sets only cover a small portion of the attack surface.

2.    Limit database permissions and segregate users.
A web application should use a database connection with the most limited rights possible. Query-only access from the user end should be limited to only essential tables. Another method along these lines is to use multiple connection strings. Once the web application determined that a set of valid credentials had been passed via the login form, it can then switch that session to a database connection with more rights.  SA rights should never be used for any web-based application.

3.    Use stored procedures for database access
Assuming the stored procedures themselves are written properly, the control offered by this method is immense.

Even having taken these mitigation steps, it’s nevertheless still possible to miss something and leave the server open to compromise. Other procedures, like putting the machine in a DMZ, means that even getting complete control of the webserver doesn’t automatically grant full access to everything else. This won’t stop everything, but it makes it a lot harder.

There is an ISAPI filter floating around the internet that was made by Microsoft to mitigate SQL injections. It’s fairly generic and will not help in all situations, in fact it has been known to cause more problems than it fixes. This is a quick fix solution and will not properly address the root problem. Additionally it may break functionality of the site it’s trying to protect. For example, I’ve seen it break the ability for customers to fill out forms or perform orders using shopping cart software.

Recovery from backups of information after a SQL injection attack.
There are a number of scrubber scripts floating around on the internet that claim to clean up SQL injections. For the most part these scripts are to generic and don’t solve the root problem (See comments above.) Therefore, besides wasting a DBA’s entire day or week, the only option is to restore from backup. Hopefully your company has had enough foresight to have some sort of disaster recovery plan that includes the frequent backing up of your database along with it’s transaction logs so a point in time restore is possible. Additionally, it’s important to realize that restoring the database will not prevent it from getting injected again. The restore is a band-aid. The code for the site needs to be fixed if the problem is to be prevented.