SQL Injection: Preventative Measures So You Don't Get Bad Publicity

Just yesterday, news broke out about the recent infection of BusinessWeek Magazine’s website. According to Sophos, Russian hackers used a SQL injection technique to insert malicious code into BusinessWeek’s database. Visitors to certain pages of the website would then download the code and be infected with a virus.

With another high-profile website infected, I thought it would be a good time to review some basic preventative techniques that ALL website developers should implement, regardless of which server-side scripting language they use.

I know how time-consuming it can be to figure out how hackers will try to exploit your code, and then implementing fixes that will hopefully protect your site, and your visitors. But in the end, the time spent is time well invested when you think of the alternative: having your website plastered all over news sites and blog sites about how your site lost confidential information, or infected a million computers with a virus!

The simplest and most basic form of protection that every developer should use is validating form data. This means that if you are expecting a form field to be of a certain type, make sure the data conforms to that type. For example, if you expect a telephone number, make sure the data entered only contains numbers, dashes, spaces, dots, and maybe the letter ‘x’ or ‘ext’ for extensions. This way, hackers can’t post data to your forms with specially crafted SQL to insert malicious code into your database, or pull private information about your users. Regular expressions are a great way to accomplish these sorts of checks.

Once you’ve validated the data coming from the user, the next step is to clean the data before it touches your database. When I say “clean the data”, I mean escaping quotes, removing semi-colons (which are used to signal the end of SQL statements), and escaping other characters with special meanings in SQL. Nearly all scripting languages have built-in functions to clean data; PHP has a function called mysql_real_escape_string, and ColdFusion has a tag called cfqueryparam. All your dynamic SQL statements should clean each and every input that is used to create the statement, even your SELECT statements!

One other thing you can do is protect your database structure from prying eyes. All languages will return error messages when there is an error in the SQL statement being executed. The key is making sure that no one sees those errors, except for the developers. Unless the site is a development site that requires some form of authentication to view, all error messages should be logged, or emailed to the developers, and the user is redirected to an error page. The less information a hacker has, the harder it is for him to determine what will work and what won’t.

Great, now you’ve implemented these three preventative measures across your whole site. You’re protected right? WRONG! Hackers are very clever people, and will think of attack vectors you would never even consider! So after you’ve protected your site, you must be vigilant and monitor your site for suspicious activity. The only way to do this is through tedious review of log files. Remember the third preventative measure? The one about logging errors? Those logs are an invaluable piece of information. By examining those errors, you can see what approach hackers are using to get to your data, and you can take additional steps to protect your data!

Another log file you probably want to look at is the access log. This log file will tell you what URI requests are being sent to your web server, and from which IP address. You can look for any odd looking requests that contain SQL statements and investigate further. There are some great tools to help you out on this front. URLScan is an excellent tool for IIS. You can use it to block HTTP requests based on certain rules. So if the request has say “create” in the query string, URLScan will block the request before it’s even processed by your application. You can learn more about some common uses of URLScan in this article. For Apache, ModSecurity will accomplish similar goals as URLScan.

I’ve listed a few techniques and tools that you can and should use to protect your website. But, by no means is this list exhaustive. It’s only the basics, and serves as an eye-opener as to what is out there to help you in your quest for total website security. With new technologies, there will be new security holes, and the only way to keep your site safe is to be diligent, and know what holes may affect you.