Archive for July 2009
An alternative title for this article could be “When is a Data Access Layer not a Data Access Layer?”
In the last couple of years I’ve come across a “pattern” that is used for database access that I think deserves the title of anti-pattern. I’m going to call it the “DB Class” pattern because the examples of it I’ve come across have used the name “DB”, “DBCommon” and similar to name the class.
The way it works is that the class has methods that are passed the name of a stored procedure or a sql statement and an array of parameters. The methods generally return data tables or scalar values or data sets.
Here is an example of a call to such a method:
DataTable customers = DB.GetTable("dbo.GetCustomers", params);
Where “dbo.GetCustomers” is the name of a stored procedure and “params” is a SQLParameter array.
What’s wrong with this pattern is that the calling code has to know the structure of the database – name of stored procedure, names and types of the parameters. A true Data Access Layer hides the database from the other parts of the application.
A better example of a call to such a method would be:
DataTable customers = DB.GetCustomers(nameStartsWith, custType);
Where the method parameters are standard .NET data types, not SQLParameters.
An even better example would be:
List<Customer> customers = DB.GetCustomers(nameStartsWith, custType);
Where a collection of objects is returned. This would be using a Transaction Script pattern.
I’ve seen this “DB Class pattern” used in two ways:
- The methods are called directly from all over the place. For example, in the code-behind of an aspx page or in an event handler of a Windows Form.
- The methods are called by a “data access layer”. For example, in the code-behind a call is made to a method in a data access class, then the data access class makes the call to DB.
The 1st way exposes the database structure to the harsh light of day – not a good idea. Try to make some database changes or change the parameters of a stored procedure and you will be doing text searches and endless regression testing to make sure you didn’t break something.
The 2nd way is not as bad, at least the stored procedure and the parameters are hidden from the UI code but these methods return generic DataTable objects and then the code that made the call has to know the names of the table columns, which brings us back to the same issue as before – code outside the data access layer has to know the database structure.
If you really have to use DataSets and DataTables for passing your data around then use Typed DataSets. Visual Studio has designers and wizards that make it easy and they also create a “TableAdapter” class that encapsulates your database interactions.
Better still is to use a Domain Model pattern and an ORM tool that converts data from a database into objects and collections of objects. NHibernate, LLBLGen and now Entity Framework are just three of the many choices available.
So, to conclude, I think this “DB Class” is an anti-pattern, and if no one else has named it as such then I claim that distinction and all the honors antendent to it 🙂
More on Anti-Patterns:
I’m glad to see that the folks at Preemptive Solutionsare not just sitting on their hands waiting for the money to roll in. They are busy expanding the features of Dotfuscator and making it into more than “just” a code obfuscator. Here is something from their developer blog that looks real interesting:
“So I’m happy that Dotfuscator CE is also joining the club. Dotfuscator CE can now inject the following behaviors:
- Session tracking to determine what applications have been executed, what versions of those applications, and for how long.
- Feature usage tracking to determine what features are being used, in what sequence, and for how long.
- Application expiry to encode an end-of-life date, transmit alerts when applications are executed after their expiry date, and terminate expired application sessions. We call this “Shelf Life”.
- Tamper defense to detect the execution of tampered applications, transmit incident alerts, and terminate tampered sessions.
On the server side, we’ve created a free version of our Runtime Intelligence Services Portal at free.runtimeintelligence.com. To send session tracking, feature usage, and other notifications to the free service, just select the free endpoint as the destination for your messages when configuring Dotfuscator for injection (I’ll show you where to do this below). When you log in to the portal using the company ID you created, you can view the data your application is sending.”
These are features for the VS2010 version of Dotfuscator, but they sound good to me.
I’ve never had to deal with a SQL Injection attack before, mainly because in all the sites I’ve ever created I pass any input from the site to the database in parametrized form. I have never built SQL statements on the fly by adding user supplied values in strings.
But this week a new client received such an attack which brought down the corporate web site. So I had to get involved and figure out what to do.
When I looked into the database I found all character fields were filled with “”. (BTW, if you Google that string you will find it all over the place, so the incompetent programmer who created my client’s site is not alone.)
The attacker managed to inject the script because:
1. The guy who created the site put inline sql all over the place. Here is a sample:
sql = "select * from content where id = '" & Request.QueryString("id") & "'"
2. He never checked any user input or any query string for dangerous content.
3. When he set up the database he gave admin privileges to the database userid the web site used.
That was his side of the story. The attackers side went like this. They used the query string to inject a huge sql statement that they hid in a hex encoded number.
page.aspx?id=29;DECLARE%20@S%20NVARCHAR(4000);SET%20@S=CAST(0x44 ... 200%20AS%20NVARCHAR(4000));EXEC(@S);--
I cut out the full number because it was almost 2k digits long. This huge string of hex digits was cast into a very clever set of sql statements that opened a cursor on the sysobjects and syscolumns tables and updated every character type column by adding that script tag to it.
One clever thing about this is that all of the articles I read when I was researching what to do mentioned looking out for strings such as “select”, “drop”, “insert” and “update” but not one mentioned “declare”, “set” or “cast”. Of course catching the “;” would have prevented the attack and most articles did mention that.
So, how were the attacks handled?
I created a function that handled the single quote issue and nuked the naughty words(like “declare”, “select”, etc.). Then me and two other developers went through the 500+ locations in the code where the !@#%$^ who created the site had built his sql statements by concatenating strings with variables and we wrapped every variable and every Request.QueryString(“paramname”) in a call to the function. Tedious in the extreme, but necessary.
And it worked. I know that because they have tried the attack several times since (robots never give up) and it has not succeeded. So when you do a Google search for that script tag you won’t find my client’s web site in the results.
So, how do you avoid SQL Injection Attacks?
- Never build sql statements by concatenating strings and variables.
- Use parameters (such as SQLParameter or OLEDBParameter or whatever the equivalent is in your language of choice) in your database calls.
- Validate user input and URL query strings so you detect and reject potentially dangerous entries.
- Limit how much a user can enter in text boxes so they can’t add on a bunch of sql statements.
- If you are stuck with an existing site that is wide open to attack, then wrap every variable and every use of the URL query string in a call to a sanitizing method.
- Get educated on SQL Injection Attacks. Read the resources below and anything else you need to until you get the idea.
Some resources on SQL Injection Attacks: