System Views, Stored Procedures and SET NOCOUNT

Problems with data providers and SET NOCOUNT

Its standard good practise to always use SET NOCOUNT ON in your stored procedures to prevent system messages about the number of rows affected by DML statements from being returned. However as well as being good practise I have found that it can actually cause issues in certain cases depending on the data access provider used to connect to your SQL database from your front end application. If you have read my article about migrating from 32 bit to 64 bit applications you will see that I mention certain issues there. I have experienced problems when moving from MDAC to SQLOLEDB when stored procedures contain multiple DML statements and SET NOCOUNT has not been set as it seems these system messages are being returned as a recordset and whereas MDAC will ignore them SQLOLEDB doesn't. Therefore you may experience errors such as "Operation not allowed when the object is closed" or "item or ordinal does not exist in collection" because these system messages are being returned or accessed before your intended recordset is.


Solution - Add SET NOCOUNT ON

Therefore the solution is simple, make sure all stored procedures have this command set at the top of them. However if you have a large database containing hundreds of stored procedures then this would be quite a task to carry out manually. Therefore I came up with a way to automate this task when upgrading large database systems by using the system views available in SQL Server.

The idea is pretty simple.

• Find out which stored procedures do not contain the SET NOCOUNT statement.
• Script out those stored procedures and update them to contain the statement.
• Run the script to ALTER the stored procs and update the database
• Use standard functions and pure TSQL to accomplish the task.

Syscomments and Sysobjects

In SQL 2000 and 2005 all stored procedure and user defined functions have their source code available for viewing from the syscomments system view. The system views cannot be updated directly so its a case of using them to generate ALTER statements to run if anything requires changing.

You can view all the stored procedures without the SET NOCOUNT statement with the following SQL.

SELECT c.*,o.*
FROM sys.syscomments as c
JOIN sys.sysobjects as o
ON o.id = c.id
WHERE xtype='P' --stored procs
AND Name LIKE 'usp_%' --my prefix
AND LOWER(text) NOT LIKE '%set nocount on%'
ORDER BY o.Name

One of the things you should be aware of is that the text column which contains the code is nvarchar(4000) and procedures or functions that have a code length greater than 4000 will be split up over multiple rows. The id column relates to the system object_id which can be used to join to sysobjects and the colid will contain the subsection of code. Therefore you will notice that the previous SQL is not 100% correct as a procedure that is split over 4 rows maybe returned even if it does have SET NOCOUNT ON set because only the first section with a colid of 1 will not match the filter.


Solution to the problem

The final script I used to solve this solution can be accessed here:

Download SQL script to insert missing SET NOCOUNT ON statements to stored procedures

I have split it into two separate loops mainly to get round limitations of displaying large strings from variables in query analyser and the system stored procedure I have used to return the complete code from syscomments which is called sp_helpText.

There are multiple ways that this task could have been achieved but I wanted to keep it a purely TSQL solution and although its not the most elegant piece of code ever writen it has done the job it was designed to do which was to update 100+ stored procedures so that I didn't have to do it by hand.

To view more solutions to common database problems that I have solved by using the system views please see

http://blog.strictly-software.com/2009/05/system-tables-sysprocesses.html

http://blog.strictly-software.com/2009/02/sql-system-views.html

http://blog.strictly-software.com/2009/03/sql-server-2005-script-fulltext-indexes.html

http://blog.strictly-software.com/2008/09/script-find-text-in-database.html

SQL Server 2005 Script Fulltext Indexes

Generate Script for all Fulltext indexes within a Fulltext Catalog

One of the things that I have found disappointing about SQL 2005 Management Studio apart from the severe slowness of the GUI compared to 2k is the fact that there is no option (that I can find) that will allow you to generate the necessary scripts for fulltext indexes. If you select a fulltext catalog under the storage section and choose to script a create you will get the following:

CREATE FULLTEXT CATALOG [System_Help]
IN PATH N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData'
WITH ACCENT_SENSITIVITY = OFF
AUTHORIZATION [dbo]

Which is just the script to rebuild the catalog and not the actual indexes within the catalog.

Obviously this is not very useful if you are wanting to quickly re-create the indexes along with the catalog and hopefully they will remedy this in SQL 2008. However until that day comes you can either rebuild the indexes by hand using the GUI or use a script like the one I have created which will:

• Script a CREATE FULLTEXT CATALOG statement for the catalog.
  
• Loop through each fulltext index within the catalog and script the necessary CREATE FULLTEXT INDEX statements.
  

An example of the SQL generated by my script is below. Its a catalog I created purely for testing that contains two indexes.

CREATE FULLTEXT CATALOG SystemHelp
WITH ACCENT_SENSITIVITY = OFF
GO
CREATE FULLTEXT INDEX ON [dbo].[tbl_CLIENTS] (
CompanyName Language 1033
,description Language 1033
,email Language 1033
,address1 Language 1033
)
KEY INDEX PK_tbl_CLIENTS
ON SystemHelp
WITH CHANGE_TRACKING AUTO
GO
CREATE FULLTEXT INDEX ON [dbo].[tbl_SYSTEM_HELP] (
Title Language 1033
,Article Language 1033
,Tags Language 1033
,PageName Language 1033
,RelatedSection Language 1033
)
KEY INDEX PK_tbl_SYSTEM_HELP
ON SystemHelp
WITH CHANGE_TRACKING AUTO
GO

Obviously you can take my script modify and extend it to incorporate any missing settings that you require as I have created it purely for moving my own existing indexes. However as the script stands its another good example of using the system views to solve common DBA problems and I am still scratching my head about why you can script almost every other object in SQL 2005 apart from the fulltext indexes. Maybe there is a hidden button I have not found yet! If anyone knows where it is please show me.

View the generate fulltext index SQL script by clicking this link

View another article about using SQL system views to aid database development.

SQL System Views

Using SQL 2005 System Views

SQL has numerous system views containing META data about the system. In fact I would say in SQL 2005 there are probably too many system views as they have kept views from previous versions to be backwards compatible and therefore you are spoilt for choice when it comes to accessing system data. For example the following 4 SELECT statements are all examples of accessing meta data about the tables you have created within a database. Notice how each SELECT uses a different system view.

SELECT TOP 1*
FROM INFORMATION_SCHEMA.TABLES
ORDER BY  TABLE_NAME

SELECT TOP 1 *
FROM sys.tables
WHERE type='U'
ORDER BY Name

SELECT TOP 1 *
FROM sys.sysobjects
WHERE xtype='U'
ORDER BY Name

SELECT TOP 1 *
FROM sys.objects
WHERE type = 'U'
ORDER BY Name

Each statement should return the same table name with varying amounts of related data.

Using System Views to create SQL

There are so many different ways that a working knowledge of the system views can be beneficial but one of the ways is to help you write SQL statements which would otherwise take a long time by hand. For example today at work I had to change the value of a certain column from A to B. The column was a key used for identifying websites. It resided in a system that used 4 databases and appeared in nearly 80% of all the tables, so writing the statements by hand would have taken a long time. The idea was to sync up our development and production server so that this column value was the same for both. Therefore I knew the names of the databases that needed updating as well as the name of the column, the existing value and the new value. I needed to output all the relevant UPDATE statements (177 in total) to do the required changes.

Filtering databases using sys.databases

After setting up some variables I created a local temp table which I populated with the names of the databases that I needed to create UPDATE statements for. I used the system view sys.databases for this e.g

CREATE TABLE #DB(DB INT,DBNAME NVARCHAR(200))

--set DBs we want to check for
INSERT INTO #DB
SELECT database_id,Name
FROM master.sys.databases
WHERE Name LIKE 'mydatabase_system_%'

Then I used the undocumented system stored procedure sp_MSForEachDB to loop through all the databases on the server checking each one to see if it also matches a database in my temp table #DB and if so I run a SELECT statement that takes data from 4 more system views to populate another temporary table #TABLES. This temp table will hold all the information I need to create the necessary SQL including the database name, table name and schema owner.

EXEC sp_MSForEachDB 'USE [?];
IF DB_ID() IN(SELECT DB FROM #DB)
BEGIN
INSERT INTO #TABLES
SELECT d.Name,u.Name, o.Name, c.Name
FROM sys.objects as o
JOIN sys.columns as c
ON o.object_id = c.object_id
JOIN sysusers as u
ON u.uid = o.schema_id
JOIN sys.databases as d
ON d.database_id = db_id()
WHERE c.Name = ''SiteFk''
AND o.type = ''U''
AND o.name LIKE ''tbl_%''
AND c.is_identity=0
ORDER BY o.Name
END'

Notice that I am using the latest system views that come with SQL 2005 and I am returning data that will enable me to create the necessary UPDATE statements with fully qualified names.

Once I have populated my working tables I can create the UPDATE statements. As the column I am updating is used in numerous foreign key constraints I need to disable these constraints before carrying out the update and then re-enable them once the update has completed. Therefore for each table in my loop I output 3 SQL statements.

-- Create ALTER statement to disable foreign key constraints
SELECT @SQL = 'ALTER TABLE '+QUOTENAME(DB)+'.'+QUOTENAME(DBUser)+'.'+QUOTENAME(DBTable)+' NOCHECK CONSTRAINT ALL'+@NL+@NL
FROM #TABLES
WHERE RowNo = @Row

-- Create UPDATE sql to change old value to new value
SELECT @SQL = @SQL + 'UPDATE '+QUOTENAME(DB)+'.'+QUOTENAME(DBUser)+'.'+QUOTENAME(DBTable)+' '+@NL + 'SET ' + DBCol + ' = ' + CAST(@NewVal as varchar(5)) + @NL + 'WHERE '+DBCol + ' = ' + CAST(@OldVal as varchar(5)) + ';' + @NL+@NL
FROM #TABLES
WHERE RowNo = @Row

-- Create ALTER statement to enable foreign key constraints
SELECT @SQL = @SQL + 'ALTER TABLE '+QUOTENAME(DB)+'.'+QUOTENAME(DBUser)+'.'+QUOTENAME(DBTable)+' CHECK CONSTRAINT ALL'+@NL+@NL
FROM #TABLES
WHERE RowNo = @Row

Each loop iteration will PRINT out the value for the @SQL variable and after running the code within a second I have 531 SQL statements to run including 177 UPDATE statements e.g:

ALTER TABLE [mydatabase_system_examplea].[dbo].[tbl_BANNER_LOCATIONS] NOCHECK CONSTRAINT ALL

UPDATE [mydatabase_system_examplea].[dbo].[tbl_BANNER_LOCATIONS]
SET SiteFK = 5000
WHERE SiteFK = 116;

ALTER TABLE [mydatabase_system_examplea].[dbo].[tbl_BANNER_LOCATIONS] CHECK CONSTRAINT ALL

So even with the time it took me to write this little script it still outweighed the amount of time it would have taken me to do the work manually e.g find all tables across 4 databases that included the column I needed to update and then write the necessary SQL. Plus if I need to do something similar in the future I can just tweak the script.

To view the full SQL script that I used click here

Using Firebug Lite for Debugging

Debugging with Firebug Lite for IE, Safari, Opera and Chrome

I tend to do all my client side development in Firefox purely because of all the great add-ons available such as the developer toolbar, hack bar, YSlow and Firebug which is a great tool for debugging.

Once the code is working then its a case of testing in the other main browsers and if you purely rely on the inbuilt tools available you will find varying degrees of uselessness. Safari and Chrome both have inbuilt consoles which can be accessed for logging debug statements. Operas Dragonfly is pretty similar to Firebug however I have found so many annoying issues when trying to use opera.postError to output debug messages that I don't bother with it. Then at the bottom of the pile for useless unhelpful error messages is IE. I have always wondered why IE could never get their act together when it comes to Javascript error messages. An accurate line number that relates to the actual file that raised the error is not too much to ask is it? It obviously knows that an error was raised so why doesn't it know what and where. If the other browsers can manage it then IE really should be able to get it right by now. I have seen IE 8 has some better features with the ability to add breakpoints but I still find it very annoying that when you have a page that includes multiple references to external script files that when an error is raised they cannot narrow it down to file rather than page level.

So for these other browsers I use Firebug Lite which has 90% of the features that the full version of Firebug has and most importantly it gives you a wonderful console to output all your debug messages to.

You can download the latest version of Firebug Lite from this link and to use it on your site you can either include a reference to a local version of the file or use the bookmark link option which lets you include it on any site you want. For sites I am developing I will create a local copy of the file that I then reference in a global include file that will make sure the script is only loaded if:

• The browser is not Firefox, as I use the full version of Firebug for that browser.
  
• The version of the site is the development server. Check a constant or an IP address.
  
• A global Debug flag is enabled.

Displaying Firebug Lite

The default view mode for the latest version of Firebug Lite is for it to open up at the bottom of your screen whether you want to or not. Obviously this can be quite annoying as it takes up quite a bit of screen space and you would usually only want to view the full console when you required it rather than all the time. This is where having a local version of the firebuglite.js file comes in handy. If you take the time to go over the code you will see the Firebug file contains a number of objects and if you edit the Firebug.init method which sets up the console you can insert the following line of code to minimize the console so that it sits at the bottom of the screen until you require it.

win.minimize();

I put that at line 232 and it works fine for me in all these browsers IE6, IE7, Chrome, Safari and Opera.

Loading Firebug after the page has loaded

There maybe reasons why you don't want to include the Firebug-lite.js file on all pages and the use of a bookmark or lazy loader is your choice option. However if you try and access the console before its loaded you will raise errors. Safari and Chrome will always have a console available so if you want to log your debug to Firebugs console rather than their inbuilt versions then you need to be checking the following:

if((typeof(firebug)!="undefined") && firebug.env && firebug.env.init)

rather than just using

if(typeof(window.console)!="undefined")

The earlier versions of FirebugLite had a simpler object model and you could reference firebug.console directly. The firebug.env object seems to hold the current status of the console, whether it has loaded fully and what display state its currently in i.e minimised, maximised, popup etc. So the check for firebug.env.init is to make sure the console is fully loaded and initialised.

Debugger Wrapper Object

If you are like me you will include debug function calls all throughout your code and rather than wait until the applications gone tits up and have to add them in later I tend to include calls to a wrapper debug function as I write my initial code. The benefit of this is that it saves time when I do need to debug plus I can run a simple clean up process to remove them all before the code goes live. However if you are including Firebug Lite from a bookmark or lazy loader you need to cache all your messages up until the console has loaded and then you can flush them all out.

This is where a simple debug wrapper object such as the one I use comes in handy as it will handle the caching, flushing and output of debug messages as well as in Safari and Chrome control whether you want to use the inbuilt console or the Firebug console.

The code is pretty simple and you can configure the behaviour by setting the debugger properties defined at the top of the object e.g

var Debugger = {
ready: false, // once a console is loaded will be set to true
debugCache: [], // holds debug messages until console is loaded and ready
hasCache: false, // flag when cache has messages
debugCount: 0, // message counter
debugInterval: null, // setInterval timer pointer
forceFirebug: true, // if true for Safari/Chrome we will use firebug lite for debug not their own console
debug: true,  // flag to enable/disable debug in case you only want to output certain sections

To output any debug messages just call the ShowDebug function passing in the message you want to output.

ShowDebug("Output this debug message");

You can control whether debug is on or off throughout a page by just toggling the debug property e.g

Debugger.debug = false; //turn off debug messages

ShowDebug("This will not be shown");

Debugger.debug = true; //turn debug messages back on

ShowDebug("This will be shown");

I have tested this in IE 6, IE 7, Opera 9.61, Chrome and Safari 3.2 loading in Firebug Lite with the bookmark and it works fine. The only thing I have noticed is in the WebKit based browsers the styling is a bit off but then that's nothing to do with me.

To download the Debugger object source code click here

CAPTCHAS

CAPTCHAs don't you just love completing them?

Everybody hates filling in CAPTCHAS and even the most complex ones can be beaten either by using bots that make use of OCR (optical character recognition) to take apart the image and calculate the letters used or for those that cannot be beat they just link directly to the site using it and offer free porn to users who complete them.

Obviously for a spammer to go to that amount of effort to beat the CAPTCHA there has to be something worthwhile at the other end like a free email account to send out spammy emails. The other major reason to use automated CAPTCHA breakers is to insert comment spam mainly for links back to nefarious sites or to malware infected sites. So if all CAPTCHAS can be broken either by bots or by humans doing the work for bots is there any point in using them? Well yes I would say as unless you are running a site that offers something worthwhile like email accounts then the chances are having even a simple CAPTCHA system will reduce a large percentage of spam requests.

However a standard image based CAPTCHA is not the only means so here are some others.

Simple Robot Identification Tests

Use Javascript to identify humans

The idea is to only allow humans to submit the form and not bots so you could go down a simple route of using JavaScript to submit the form as 99.9% of bots do not run script. However you also have the problem that roughly 10% of your human traffic don't either. You could place a message at the bottom of the form asking the user to enable Javascript to submit the form. With the message itself hidden by JS so that users with it enabled don't see it.

Identify robots using IP and User-Agent

For those bots you can positively identify as crawlers by IP and User-Agent you can obviously prevent them from submitting the form however most spammers will spoof the agent and go through proxies and other cloaking mechanisms. Identifying a bot as a bot 100% of the time is the holy grail webmasters are seeking so if there was a way of doing this CAPTCHAs wouldn't be needed in the first place.

Using CAPTCHAs to help digitise books for online use

This is a neat idea where you know that those 10 or more seconds spend deciphering the image has not been a total waste of your time. The reCAPTCHA is based on a small portion of word from a book. The image is a section from a scanned version of a page and your answers are compared with other peoples responses to validate the likelihood of a sentence being correct. This is a good idea as even if you got one word wrong from a sentence you could still pass the test if the rest of the words were correct as the system checks the answer you gave for new words that have not been validated with words it knows the answer for.

Using hidden input fields to trick robots

This idea involves adding hidden input fields to your form which you want the robot to complete but not the human. When the form is submitted you check whether a value has been added to this field and if so you can block the request.

You can use either type="hidden" to hide the input or preferably use CSS and a class name that relates to a style e.g display:none; A bot could easily detect the element was hidden and ignore it the same way it could easily read inline styles to work out it was hidden. However with a class name it would have to read in the stylesheet to find out whether the class related to a hidden style or not which is obviously more effort but not impossible.

Also the aim is to trick the bots into filling it out without also tricking any form auto-complete systems such as Googles toolbar from doing the same. I have found problems with older versions of the toolbar when you give the input a name such as "EmailConfirm2" it would complete it as it mentioned a word used within the autofill profile. You could give the field a totally random name but then a clever bot would ignore it knowing it was a trick.

You can give it a name that relates to other visible form elements but prefix it or modify it slightly. Also make sure you place the field outside the flow of the other visible controls as I have found with Googles latest toolbar that it will complete inputs hidden with CSS if they are placed between other visible elements e.g between Name and Email or within Address1 and Address2. Therefore this method is not totally reliable.

Check submitted values for similarity

Also a lot of spammers will submit the same value for all form fields for example on a simple registration form of Name, Email, Confirm Email, Password and Confirm Password the spambot will enter an email address for all 5 controls. Unless you set up validation rules to ensure that email addresses are not used for passwords or names then it would submit the form. What you could do is check whether the same value has been used for Name, Email and Password and block the user. Only a percentage of bots do this and I myself when testing a new site often supply the same email address for all parts of a registration form to quickly get on the system.

Question and multi-part CAPTCHAS

This type which is not as popular as the common garden CAPTCHA is where the user is asked a question about the image. You may have four numbered animals on the image and the question would be "Which one makes the noise mooo" and you would have to pick the image related to the cow. Or the question maybe "what colour is the sky" which you may answer well its England in January so its grey and then find yourself blocked. The problem is making enough questions that can only be interpreted in one way as you are basing your CAPTCHA on a subjective question that you hope everyone will answer the same way.

Another form of this CAPTCHA which I have started using myself is the combination CAPTCHA where the distorted image holds a series of numbers. The user is then presented with a sum based question based on those numbers for example "Subtract the second number from the first and then multiply it by the third number". The sum and image is generated server side with the answer held in a database related to a key. Only the key and the question is passed to the client and the user has to answer the question within a set time period and within a set number of attempts to pass. Not only does this method not use Javascript so its available to all users it also has the affect of weeding out users who cannot do simple math.

A variation on this math based system which I have just read myself tonight so was pretty interested to see other people using is a similar math based solution when the question is only shown to the user if Javascript is disabled. If its enabled then Javascript is used to solve the CAPTCHA behind the scenes using some encrypted keys. This way the user is spared the agony of remembering their junior school times tables :). This solution is available as plug in so can be used by those not wanting to write their own.

So which one do I use?

The problem with the IT world is that its full of people who like a challenge and want to prove they can do anything. Therefore you will always have developers who will spend time writing clever bots designed to beat any form of CAPTCHA. So as with all security methods the best approach is a layered one that makes use of multiple techniques. The idea being the more hoops there are to jump through correctly the more likely you are to trip up some of those devious spammers and hackers.

If you can make your CAPTCHA solution slightly different from all the others then you also have a good chance of it not being beaten. Unless you are offering a golden honeypot on the other side of the submitted form i.e free email then there is no money in defeating it. If you are just a regular site then you want to stop the majority of spammers without making it too much trouble for your users to complete. Remember a lot of spammers are human as well so you will never stop all spam.

Related Links

reCAPTCHA - help digitise books for online use whilst completing CAPTCHAS

Invisible CAPTCHA validator control

ISAPI URL Rewriting - Hot Linking

Banning Image Hot Linking

I was updating my ISAPI rules the other day implementing some new rules to identify XSS hacks and new SQL injection fingerprints and I came across some articles on the web about banning image hot linking which I thought was a cool idea. If you have spent time and effort designing images then there is nothing worse than someone just stealing them or even worse hot linking to the image so that your bandwidth is also taken up hosting their images!

I implemented the rules with a redirect to a logging page that logged the referral property to my traffic DB so that I could see which sort of requests were being made for images that didn't originate from the site they belonged to. After a week or so of logging I had enough data to come to the conclusion that I wouldn't be able to implement a hot linking ISAPI rule in the majority of the commercial applications I work on. This is for the simple reason that most sites I develop send out one or more HTML emails to users that themselves make use of hot linking to images and logos e.g using full URLs back to the image on the webserver. Unless I could come up with a rule that could handle every type of webmail or email client then there would always be someone somewhere in the world opening up their latest site generated email, clicking the "load images" button only to get nothing in return.

There are so many different mail clients out there developed in hundreds of countries that no rule could keep up with all the possibilities. From viewing my logging I had at least 2 different Polish mail clients as well as a number of Russian, Chinese and god knows what. So if you are developing a commercial application that has to send out HTML marketing or other forms of email I would advise against it.

However for small or personal sites then there is nothing wrong with implementing a rule to ban hot linkers. One of the cool ideas I read about was to return a banner advert or some other imagery that would annoy the user. However if you are going to use these rules you should know that as soon as the linker finds out you have blocked them they will just download the image from your site and host it themselves if they really wanted it. The original reason for this type of rule from what I have read is to implement it periodically to log those sites who are linking maybe without even blocking the image and then to issue cease and desist orders to the offenders.


The ISAPI Rules for IIS

I have to work with IIS at my current company and we use both 32 bit and 64 bit servers which means we have different versions of the ISAPI DLL installed on each and therefore slightly different syntax. The component I use is ISAPI Rewrite.

You will notice that the rules are slightly different due to the regular expression engine differences between versions.

Both versions do a number of conditional checks to ensure that
-The referer is not blank
-The referer is not the current site which is obviously okay
-The referer is not an image search bot. Looking at the most popular robots.
-The referer is not an email client.
-The user-agent is not a popular search engine bot.
-The image in question must be a gif, jpeg, jpg, png or bmp
-If all those conditions are matched I redirect to a 403 forbidden page.


Version 2.7 ( 32 bit server - httpd.ini)

Notice that I capture the host in the first conditional and then use a back reference to that matched value in the third conditional which ensures only hosts that are not the current site get matched.

RewriteCond Host: (.+)
RewriteCond Referer: .+
RewriteCond Referer: (?!https?://\1.*).*
RewriteCond Referer: (?!https?://(?:images\.|www\.|cc\.)?(cache|mail|live|google|googlebot|yahoo|msn|ask|picsearch|alexa)).*
RewriteCond Referer: (?!https?://.*(webmail|e?mail|live|inbox|outbox|junk|sent)).*
RewriteCond User-Agent: (?!.*(google|yahoo|msn|ask|picsearch|alexa|clush|botw)).*
RewriteRule .*\.(?:gif|jpe?g|png|bmp) /403.aspx [I,O,L]


Version 3 (64 bit server - .htaccess)

RewriteCond %{HTTP_REFERER} ^.+$
RewriteCond %{HTTP_REFERER} ^(?!https?://(?:www\.)?mysite\..*) [NC]
RewriteCond %{HTTP_REFERER} ^(?!https?://(?:images\.|www\.|cc\.)?(cache|mail|live|google|googlebot|yahoo|msn|ask|picsearch|alexa).*) [NC]
RewriteCond %{HTTP_REFERER} ^(?!https?://.*(webmail|e?mail|live|inbox|outbox|junk|sent).*) [NC]
RewriteCond %{HTTP_USER_AGENT} ^(?!.*(google|yahoo|msn|ask|picsearch|alexa|clush|botw).*) [NC]
RewriteRule .*\.(jpe?g|png|gif|bmp) /403.aspx [NC,L]


Quirks and Differences

As always nothing is ever simple especially when you want to implement the same rule across two different versions of an application. As well as the obvious syntax differences with the flags and HTTP header names I found the following:

• Using the IIS converter tool to convert the 2.7 rules to version 3 did not convert all the rules. It could not handle the back references and therefore I explicitly match the site domain in the v3 rules.
• The negative lookahead asserts differ between versions. I could not get them working in 2.7 without putting the trailing .* outside the grouping e.g (?!https?://\1.*).* whereas in v3 they are within the grouping e.g ^(?!https?://(?:www\.)?mysite\..*)
• The documentation recommends NOT using the ^ and $ when using rules with conditions because internally all conditions are combined together to create one rule and this can lead to unexpected behaviour.
• I could not get the Ignore Case flags [I] working in version 2 with the negative lookaheads. As soon as I added the flag the rules would not match. This does not seem to be a problem in version 3 and the equivalent flag [NC] works fine.

Apart from those quirks both sets of rules have been tested on live systems and work fine. However if you are going to use rules such as these you are always going to run into problems with new mail clients or user-agents that come along oh so very frequently and unless you are going to constantly update your ini files you will have a considerable percentage of false positives.

The full ISAPI Rewrite documentation can be found here: http://www.isapirewrite.com/docs/

Cool Javascript regular expressions

Using Lambda Functions for HTML Parsing

One of the cool features that made me scratch my head when I first came across it but now love to bits about Javascript is the ability to use lambda expressions. A lambda expression basically means that you can use a function as the argument for another function. This is best seen with the replace method where you can use a function as the replacement value for a matching string test e.g

somevar = something.replace(/pattern/, function(match, submatch){
if(/another pattern/.test(submatch)){
return match;
}else{
return "";
}
});

One of the ways that I have found to use this feature is within my WYSIWYG widget to parse user generated HTML content and to strip out any HTML tags or attributes that are not allowed to be entered.

The function starts off with a regular expression that matches all HTML tags and also provides a grouping that returns the actual HTML tag name.

theHTML = theHTML.replace(/<[/]?([^> ]+)[^>]*>/g, function(match,HTMLTag)

I can then use a function as my replacement value that will either return an empty string and remove the whole tag if its not allowed or otherwise run another replacement to handle attributes.

match = match.replace(/ ([^=]+)="[^"]*"/g, function(match2, attributeName)

This function does a similar job of replacing the attribute with an empty string if its not allowed or otherwise returning the sub group that matches the attribute/value pair. This ends up being a very cool way of parsing HTML content using the power of regular expressions.

The whole function is below:

// Set up my regular expressions that will match the HTML tags and attributes that I want to allow
var reAllowedAttributes = /^(face|size|style|dir|color|id|class|alignment|align|valign|rowspan|colspan|width|height|background|cellspacing|cellpadding|border|href|src|target|alt|title)$/i
var reAllowedHTMLTags = /^(h1|h2|a|img|b|em|li|ol|p|pre|strong|ul|font|span|div|u|sub|sup|table|tbody|blockquote|tr|td)$/i

function ParseHTML(theHTML){
// Start of with a test to match all HTML tags and a group for the tag name which we pass in as an extra parameter
theHTML = theHTML.replace(/<[/]?([^> ]+)[^>]*>/g, function(match,HTMLTag)
{
// if the HTML tag does not match our list of allowed tags return empty string which will be used as a
// a replacement for the pattern in our inital test.
if(!reAllowedHTMLTags.test(HTMLTag)){
return "";
}else{
// The HTML tag is allowed so check attributes with the tag

// Certain attributes are allowed so we do another replace statement looking for attributes and using another
// function for the replacement value.
match = match.replace(/ ([^=]+)="[^"]*"/g, function(match2, attributeName)
{
// If the attribute matches our list of allowed attributes we return the whole match string
// so we replace our match with itself basically allowing the attribute.
if(reAllowedAttributes.test(attributeName)){
return match2;
}else{
return "";  // not allowed so return blank string to wipe out the attribute value pair
}
});

}
return match;

}); //end of the first replace

//return our cleaned HTML
return theHTML;
}

Another good thing about this feature is that as well as being able to pass the match string in to the replacement function as a parameter you can also pass in any number of sub groups as extra parameters. So using my parseHTML function as an example again instead of only capturing the attribute name in my check for valid attributes I could also capture the attribute value and then pass that as an extra parameter to my replacement function like so:

match = match.replace(/ ([^=]+)="([^"]*)"/g, function(match2, attributeName, attributeValue)

So you could test for the validity of the supplied values if you wanted to. Maybe if you were allowing the class attribute you would want to check to make sure only certain class names were used.

This is brilliant for use in client side widgets and also as server side code for parsing user supplied HTML content. Remember even if you are using crusty ASP classic and writing your code in VB Script which has a really poor Regular Expression engine compared to Javascript you can still make use of this cool feature as there is nothing stopping you mixing and matching VB Script and Javascript on the server.