Last week, I had the opportunity to visit with the CEO of a successful web applications development company. He told me that — in his system — users are authorized on every page throughout the entire web application.
Although I didn’t think the situation was appropriate to nail him down on the details, it made me think about security in my own web applications. Most of my web applications code is written in Classic ASP that I inherited from previous developers. And most of that code contains numerous SQL Server database calls.
Some of you may know what I’m talking about:
1) Issue a SQL statement that retrieves some basic information from the database.
2) If there is a valid record, use the ID from the previously retrieved row and get some more information.
3) If that worked out all right, do something else (an UPDATE or an INSERT, for example).
And sometimes, this goes on an on for many lines of code. I still remember the “old developer” telling me that all of this performs lightning fast on his development system. Yeah, right, one user. Big deal. So, by the time I implemented Stored Procedures (to reduce bandwidth problems and query timeouts, among other things), the “old developer” started feeling a bit uneasy and eventually stopped working for us.
At any rate, here I am still trying to mildly refactor a web application, using Stored Procedures (SP) to spiff up things. Oh, you’re wondering where I’m going with this? The point is that SQL Server SPs are an excellent way to implement system security, too.
For the last little while, I have been rewriting some of the web application code, and while I was ripping out old and redundant ASP-based SQL, and replaced it with SPs, I started validating some basic things in every SP.
For example, in our human resources related web application, the logged in user can add and edit personnel information. And although there’s never been a great risk as far as “hackers” getting into the system to change somebody’s employment history and so forth, I started making sure that this won’t happen.
So, at the top of every SP, I now query the database to find out if the user who is logged in and the employee whose information is getting edited belong to the same company. If they don’t, the SP returns a custom error code. Next, I check the IDs of anything that needs to get updated. Sometimes, that means going to more than one table to “make the connection” and retrieve the needed information. If that ID matches up with the expected data, let’s go ahead and log everything “BEFORE” to the change table, then make the actual update. If there’s a mis-match, however, we’ll raise a custom error and exit the SP.
And so forth and so on. It’s about as mundane as it sounds, and sometimes it really gets to me to be coding this kind of security into every SP (even though I have my own macro tool and other keystroke-saving techniques). However, when a client calls and claims that he can’t do a certain thing because our “stupid” system won’t let him, it’s nice to be able to look up some information in the change table and in our web application log table, and then tell the client that he’s not supposed to intercept HTTP-requests, alter form values and then try to pass the information t our system, expecting it to work.
Another thing that’s neat is to watch a co-worker try to mess with all kinds of SQL injection (both manually and automated) and not get anywhere. Hey, I’m not saying I’m the sharpest knife in the kitchen drawer, and I’m always looking for ways to improve what I’m doing — all I’m saying is that it’s nice when intentionally designed system security starts paying off.
