I recently looked into Googles APIs which contain a number of very useful frameworks for quickly developing content for a site. With just a few lines of code you can add content such as videos, search facilities for numerous feeds and news, geo-location data and much much more. You can read my blog article about developing with the Google APIs here. You can also visit the site I developed using those frameworks which is a football based site called Hattrick Heaven that delivers regional based news, blogs, videos and league information from around the world.

I seem to be spending more and more time lately analysing my sites traffic trying to stop precious bandwidth slipping away to content scrappers andother bad bots. In the old days I didn't really give much attention to crawlers as the only bots were GoogleBot and other search engine bots and they were considered a good thing to let loose on your site. Nowadays you have Chinese Hackbots and Script Kiddies let loose with Paros type exploit finders. Then you have all the job rapists that hammer our sites trying to steal every possible job posted. Added to that all the content pre-fetchers from toolbars and other "security" based add-ons that bump up traffic with no benefit to the site as well as all the French goverment copyright and trademark infringment bots. Then you have the spoofers bots pretending to be IE7 and Firefox Users with agent switchers pretending to be Googlebot. At least the switchers give you an agent even if it is meaningless. A lot of my traffic, both malicious and harmless, is now coming from people hiding the agent which is really handy when you want to determine whether a user is a bot or not in real time rather than later from the log files. Its all getting very complicated which means a lot of homework. In fact I don't think I have read up so much on a web related subject since I made my own WYSIWYG editor and that was a hell of a lot of Javascript related articles I had to read.

I am currently devising a security policy for my new site which will take all this into account and come up with some basic rules to help me with my user related logic. Items such as:

• Banning Blank User-agents straight away. If you cannot identify yourself then you cannot use my site sorry!
• Treating all access to the robots.txt file as confirmation that the user is a bot. If you manaully access the file then tough no more flash movies for you.
• If your going to hit my pages so quickly that it causes high CPU and script timeouts then its off to the 403 sin bin for you.

Click the following link to see a full list of my rules that robots should obey and then read the following to see an example of one of the job rapist bots I banned here.

We have heard of SQL injection and also Denial of service attacks however you may not have heard of the specific term "SQL Denial of Service Attack" where the attacker tries to exploit the fact that a) Most sites use an SQL backend and b) Most sites allow the users to enter search criteria to bring back results. Therefore this particular type of attack exploits poorly written search queries that use the LIKE keyword to run CPU intensive queries that return no results and therefore search the whole database. These queries will max out the SQL servers CPU, take a long time to return results and therefore if the attacker can run a number of search requests they may also consume all available connections to the database making the site unusable. These SQL DOS Attacks could be a very effortless way for an attacker to take down a site. Read the details in my blog article here: SQL Denial of Service Attacks.

You will also find traces of SQL DOS attacks in the query statistics SQL keeps in memory with the DMVs (dynamic management views) they introduced in SQL 2005. Running a report such as this beast will help you find queries that have maxed out your servers CPU or caused huge wait times. Plus its a very handy one stop report for showing you a database or a whole servers worth of systems performance as it runs up to 20 specific reports (CPU, I/O, Wait times, Index Fragmentation etc).

More and more websites are falling victim to automated SQL injection attacks originating from China, Russia and other Eastern European countries. Even though the techniques hackers use to attack sites with SQL injection is widely known it seems from posts that I have read from developers who have suffered a successful attack that the first place to point the finger of blame is still browsers and operating systems and someone will always ask:

"Is there a patch for Internet Explorer that fixes this hole".

In truth the cause of a successful SQL injection attack is always going to be poorly written front end code. I don't know whether future developers are being taught about injection techniques at college (which they surely should be doing) However I personally think that a lot of sloppy code comes about not through ignorance but rather the developers natural inclination to copy and paste code from other sources and especially for newbies doing so when they have little or no idea about what the code they are using is actually doing. If the code being copied is from an old system or from an ancient but well ranked web page detailing how to write SELECT statements using string concatonation then the developer could be saving time now only for it to be taken up when the site goes live and gets hit by one of the thousands of hackbots around as soon as the page appears in Google. Nowadays a developer only has themselves to blame if they don't by now a) understand how SQL injection works and b) knows how to prevent it with correct parameter sanitisation.

View my blog to read articles detailing:

How to quickly clean up a hacked system and details of plasters which can be applied to quickly reduce the threat of future hacks.

View a list of SCRIPT URL references that you should be on the lookout for that the latest bots are using.

You've just been hacked by one of those automated hack bots. You're going to fix those gaping holes in the application but first you need to clean up your database and remove the thousands of links to those virus infected sites. You have no time to write your own script and no money to buy one of those clean up packages. If your stuck and you use SQL 2005 or 2000 for your backend then this clean up script will help you out by doing exactly what those costly database clean up applications will but without the GUI or the cost. Clean Up Script use this script to find out how much data has been corrupted by searching for one or multiple hack strings at a time. Once you know how much data has been affected you can run the script to output all the necessary clean up SQL statements or run them automatically.

• SQL Performance Tuning Queries Improving your backend database by adding relevant indexes, rewriting queries, redesigning tables and reducing locks can increase the performance of your system by a huge amount. The key is finding those SQL queries that need to be worked on. These reports will help idendify problematic queries, indexes and tables and aid your database tuning. Even if your not a DBA this script will help you diagnose and rectify problems with your system.
• Find Problematic and Blocked Processes. Blocking is one of the primary causes of a slow website. Once one process blocks another a blocking chain can quickly develop which reaches a point that the processes being released are slower than those being locked. This can slow down your site affecting performance dramatically. Use these reports to find long running queries, blocks, deadlocks and also identify queries which could need a rewrite to aid performance.
• Script - Performance Report. This comprehensive all in on SQL report should be in the toolkit of any DBA worth their salt. Identify problems for one or more databases or for the whole server. Find missing or under-used indexes. See how fragmented your indexes are, which queries are consuming all your I/O, CPU and memory and much more.
• Script - Find Text in Database. If you are looking for injected scripts or other malicious code thats found its way into your system then this is the script for you. See how badly your system has been affected or automatically clean it at the same time. This should be another tool in the DBA or Security experts library of useful scripts.
• Using SQL System Views to aid your SQL development. There are so many system views, DMVs and system tables that even the most experienced developers don't know all of them. If your planning on writing TSQL code thats going to modify large parts of the system or just want to find out key stats to aid specific report then a knowledge of the system views will aid you without fail.
• Update Stored Procedures Automatically. Migrating from one database server to another can be a right pain especially when your database providers have changed as well. Its good practise to use SET NOCOUNT ON to reduce network traffic as well ss system memessages that the server returns when DML statements are carried out. The problem with newwer providers is that procs that have multiple DML statements require SET NOCOUNT ON to prevent issues at the the front end. Having to add thses commands one by one would be a nightmare this is why I created this script to add all missing statements in at the touch of a button.
• How to generate Fulltext Index Scripts. One of the downsides about SQL 2005 is that you cannot script out a Full Text Index only the Catalog which means moving from one server to another can bit a nightmare. This script of mine makes use of the system viewss to generts all your would ever need as well as save a fair few man hours.

• JavaScript Unpacker Tool A tool for reverse engineering packed JavaScript code. Make sure websites that you visit with NoScript are not trying to run malicious code or even just work out how that cool widget works without jumping through multiple hoops. Just paste in the packed content, hit the unpack button and view the original code.
• IE 8 Settings Script A function that returns the core details about Internet Explorer such as the user-agent, version, document mode and browser mode. Now that IE 8 comes with options to change the rendering engine and javascript engine this script will help determine the settings a user has configured.
• Debugger Object A wrapper object to abstract the debugging process with a simple ShowDebug function call you can output to Firebug, Firebug-Lite or inbuilt browser consoles.
• CSS Font Style Object An object that helps return accurate computed style values in Internet Explorer as well as other browsers. Gets round the problem in IE of the currentStyle method returning a size as a percentage, auto or in units other than pixels.
• Querystring Parser. A flexible helper object that parses the querystring.
• Lazy Form Validator. Quickly add detailed and customisable form validation to a page with the inclusion of one small object. Manage the validation by using classes. Handles a number of data types: Text,Date,Integers,Numerics,URLs,Emails,Phone Nunbers.
• Browser Object. A lighweight browser detection object that gives you information about the current users browser,OS,flash,JScript settings.
• Linked List Object. An object that handles multiple groups of linked lists on a page. Each group can contain multiple lists that link together in a hierarchal chain where the selected options in one list determine the available options in another.
• Social Bookmark Object Include a file similar to the one below on your own blog or website and let your site visitors drive traffic to your sites. The object uses Javascript to create the relevant links required by each of the various social bookmarking sites used in the file.