A seriously elegant SQL Injection -how it was sorted
Thursday, May 29, 2008 2:32:33 PM (GMT Standard Time, UTC+00:00)
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.
- 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 - Modify the DB user account that is used to have *read only* access to the database
- 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.
- 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.