Doug Setzer posted this comment in response to my recent "A seriously elegant SQL Injection" post and I thought it may be of interest to others so have promoted it to a post...

Well, I'll step up and say that I am the "mate" who had this done.  Tim's right - *always* sanitize your inputs.  In my defence, this was a site that I inherited from a previous contractor.  I'm not entirely absent of blame, I still should have done a security sweep through the code.

I'd like to document the steps that I went through once this was identified to try and avoid this kind of thing in the future.

1. Edit every web page that executes a query to sanitize any parameters that are passed in.  Since the site was classic ASP, I used my "SQLStringFieldValue" function:
   www.27seconds.com/kb/article_view.aspx?id=50
2. Modify the DB user account that is used to have *read only* access to the database
3. Modify the pages that DO write to the database to have *read/write* access to the specific tables that are being changed.  This limits the number of places that SQL Injection can occur to a smaller set than was previously possible.  I still sanitize all of my input, but I'm extra spastic in these database calls.
4. Add database auditing (triggers writing to mirror tables with audit event indicator & date/time) to see when data changes occur.  This is still problematic with the pages that have "write" permissions to the tables, but again- that footprint is much smaller.

My future plans are to move to a view/stored procedure based architecture.  I can then limit write permissions to just the stored procedures and read permissions to just the views.  My grand gusto plans are to move to using command objects & parameters, but I'd sooner re-write the entire site.

Although Doug's attack wasn't the same nihaorr1.com attack that's going around atm it was similar so I would imagine other's will find this useful.

It still amazes me how many developers still fail to sanitise strings, only last week I came across another site (in PHP) that was allowing simple SQL injections to be used to log into their administration system. It was down to a problem with the sanitization string, but why not at least check your site before it goes live? It takes 2 minutes and even less to fix...

For those of you who need a few pointers, there's a good discussion or two about sanitising strings on the 4 Guys From Rolla site.

Another post from DougSetzer from 27Seconds.com :)

At my "day job", the systems guys are building new Windows 2003 servers to upgrade our aging Windows 2000 servers.  The plan is to:
 - Build the new Windows 2003 server
 - Install IIS
 - Install .NET
 - Run the IIS migration tool from the old Win2k server

That all went as well as could go - little things got mixed up and had to be corrected.  But, the server would let you request plain HTML files and ASPX files, but classic ASP pages were returned blank.  In poking around Google and the server, we came to find that we had to enable ASP content via:
 - IIS Manager
 - Web Services Extensions
 - Specifically allow Active Server Pages

But, we were still having the same issues.  Stopping and restarting IIS didn't help. Nor did a server reboot.

I found a blog post that mentioned checking that the ASP ISAPI has the correct path.  It tried a random thought that Microsoft has changed the default name of the "Windows"/"Winnt" folder -- Windows NT4, 2000, etc. all use "Winnt", where as Windows 2003 uses the "Windows" folder.  Sure enough, double checking the path to the ASP ISAPI had the wrong path and fixing this path fixed our issues with classic ASP files.

Thanks to Doug Setzer from 27 Seconds Inc for these thoughts, he doesn’t blog (yet) so thought I would be interested in posting it –not sure why as it would seem he’s my only reader…!

I tend to agree with Doug on this one, I remember seeing Code Smith in action at a previous Developer Developer Developer day and although it does look, I have similar concerns as Doug, IMSHO I'm concerned it makes the developer lazy and forget his roots. IIRC many generators offer the ability to make your own templates but again, by doing so, how much time do you really save?

Not being a code generator user* I’m also somewhat biased but I’d be interested to hear other peoples thoughts.

Tim

*That said, I do have a number of “tools” that I user to generate the repetitive code but these mainly rotate around Excel and are specific to my coding methods.

The decision to use code generators is a long and tricky decision.  I'll admit from the start, my slant on code-generation tools is against them.

For one of my other clients, I use a simple code generation application that they provided to me (the guy wrote it).  It does a decent job and for their coding style, it works out fairly well.  It takes a very simplistic template and can output (to an extent) customizable code.

The problem that I have with all code generation tools is your ability to make changes.  Very rarely do you know all of the data, or the extents of repeating data, or the nuances of the code that you have to develop.  So - how does the application handle changes?  In my case and this (very) simplistic code generation tool - plain and simple, it doesn't.  I have to hand-code changes (.NET, ASP.NET and database procedures).

And the last thing - the nuances of the code that you have to develop.  How well does a generic tool handle special situations?  Again, I know a free/custom-built tool is a far cry from a $500/$2000 tool - but, anything that is outside of a simple CRUD/1-table procedure and again, I'm hand-coding.  I can honestly say, 50% of what I'm doing is "special" and doesn't fit into cookie-cutter boxes.

The code generator that my customer gave me did exactly that - it generated code for a database.  It was up to me to build the pages and it just gave me an easy database access API.  I like this more than tools like Iron Speed because nothing is being done "for" me, I can control the code it outputs and nothing, nothing is "behind the scenes".  With Iron Speed, they're doing a LOT "for" you.  My fear is what happens if it breaks?  Who do you call?  Where do you look?  And, the purist in me - what does their code really look like?  Are they doing poor-design things that ultimately give you performance woes?

Unfortunately, with tools such as this Iron Speed - the only way to know if it'll work for you is to make the plunge, spend the $500 for the pro. version and see how it goes.  You can help get yourself a glimpse, looking under the covers by downloading the trial and putting together some little apps in a month.  And, I see that they have a public support forum:
http://sjc.ironspeed.com/tool/mb/ironspeed

See what people are saying they love? what they hate? etc.

And, of course - Google is our best friend:
http://www.adtmag.com/article.aspx?id=8421
http://www.developerreviewed.com/ironspeedreview.htm
http://www.angrycoder.com/article.aspx?cid=10&y=2003&m=6&d=8
http://aspadvice.com/blogs/ssmith/archive/2006/04/26/IronSpeed-and-My-Custom-Reports-App.aspx
http://www.411asp.net/func/review?tree=411asp/software/aspcodee&id=5642310&rid=

I hope this helps - it's a lot to digest, but generally what I'm seeing is that it's good for small/simple apps.

-Doug

We're nearing the end of a large system for the lovely people over at Consolidated Communications Management Ltd and we've just come across a nice idea to make sense of database responses. Typically in the past we've simply returned -1/0 for failure and a positive integer (usually an id) of it was successful. The new method should add a little more conformity to our code. We already use a common Database Access Layer which handles the opening/closing of our connection, adding parameters etc but I’ve just added an enum with the common database responses, thought it may be of use to others:

public enum DatabaseResponse

{...}

{
    Success = 0,
    Init = -1,
    Found = -2,
    NotFound = -3,
    Duplicate = -4
}

This should cover the common responses from our database however to make it a little more futureproof we'll keep -1 through to -10 reserved for this data level and then start more specific responses from -11 (i.e. BadPassword).

Using it is simple (this is not real code before you ask!):

public bool Save()

{...}

{
    DatabaseLayer DBLayer = new DatabaseLayer();

    //Check whether we should be saving or inserting this record
    if (this.Id == (int)DatabaseLayer.DatabaseResponse.Init)

    {...}

    {
        //The Id's set to initialised only so insert
        //The create method simply inserts the values into the database and returns
        //either the Id of the newly inserted record or our database response
        this.Id = DBLayer.Create(this);

        if (this.Id >= (int)DatabaseLayer.DatabaseResponse.Success)

        {...}

        {
            //The insert was a success
            return true; 
        }

        else

        {...}

        {
            //The insert failed for some reason -you could throw an error 
            //to catch or simply bubble the response upto to the code behind layer
            return false; 
        }

    }

    else

    {...}

    {
        //When updating the record don't use the Id to store the response, instead
        //throw it into a temp object and use that so the Id's still stored in the object
        int response = DBLayer.Update(this);

        if (response == (int)DatabaseLayer.DatabaseResponse.Success)
            return true; //All's ok
        else
            return false; //Something went wrong -it could be the item wasn't found etc
    }

}

After the fun and games we had with IE and sorting out an old CSS design for www.technikfloor.co.uk on Friday when I saw this pie chart I thought it was an excellent summary of a modern web designer’s life.

Personally I feel my Teal coloured slice is smaller but hey!

Originally posted at: http://www.dangerouslyawesome.com/2006/07/02/throwing-a-wobbly/