Lesson Learned: Better Web Application Security
A while back I posted a link to a site that explained SQL injection with a real life example. Although I always thought I understood application security, I never seem to go a month without learning something new, and our recent go-live with two web applications kept that record going.
One application is our new corporate web site, and the other is the content management system that powers the public website. With the public website we were given a read only user id for the data access portion, which is very typical, but then when we tested the id, we were getting all sorts of table permission errors. When we showed our DBA, he enlightened us with the information that this id was read-only AND only had rights to execute stored procedures (no SELECT rights on individual tables). Even though we had all the data access from the application being done via web services we had to track down any and all usages of direct data access that the web services themselves used.
Now, we have always done as much data access with stored procedures as possible, but the simplified .NET data binding model let us do things the easy way, but not always the smart way. For those not familiar with SQLDataSources, they are objects that exist in a .NET page/application that provide a mechanism for objects on the page to access data. You provide SELECT, INSERT, UPDATE, and DELETE commands to these data source objects and then bind various controls to the source (individual fields, entire forms, etc..).
What caused all the headaches were the simple little keyword fields where we kept using “SELECT * FROM someKeywordTable” in the SELECT statement of a data source object and binding these results to a dropdown list. Every one of these lookups failed, and they all had to be converted to stored procedures. Nothing too bad, just a little embarrassing; if only it would have stopped there.
So here is another little goodie: the security rights given to a MSSQL stored procedure at compile time, is dependant upon the table names that the command parser recognizes in your SQL code. Sounds simple? What about dynamic SQL, where strings like table names are passed into the stored procedure, a SQL command string is built, and the EXEC command used? Well, under this tighter security scheme, dyanmic SQL is a no-go since the parser does not see the table names at compile time, and we had one big stored procedure that built big command strings, and used different tables depending on user input.
Lesson Learned: get your DBA participation from the beginning, and setup your dev environment the same way as prod.
July 3rd, 2006 at 2:42 pm
For all the power SQL gives you from a reporting stand point, the nightmares that occur with securing an application makes me be thankful for Domino. I’ll take the pain getting the data out of my applications as long as I am sure that I know exactly who is getting the data out and putting it back in. I do wonder when someone is going to package a RDBMS environment that marries the design, security, and data together like Notes.
Sean—
July 3rd, 2006 at 3:44 pm
Now if they would only update the Domino’s rendering engine, fix the XML streams so they return valid XML, and give developers access to simple things like server sessions, I might feel compelled to go back. It was nice to have it all in one package, but I think I have come to like the flexible, extendable approach, even if it means learning a few lessons the hard way.