I’m working as a junior programmer, and the senior programmer above me has instructed me to follow a certain unofficial policy for constructing new queries on our web development projects. Generally, we are developing an intranet site for some client, and they always have databases. He wants me to have a class containing a method for each query that the website will perform. This class calls web methods in a web service, hosted on the same machine. Those web methods use ADO to run stored procedures that perform simple queries. Sometimes the queries need parameters and sometimes they don’t. When I say simple queries, I mean simple…select * from table where column=@parameter
I feel like there are several extra steps here, and I’m hoping someone can explain why he might want this as our standard procedure for using databases in web apps. He says each step adds a layer of security. I’m genuinely interested in how all this provides security. Is all this necessary? Why or why not?
4
The idea behind this is probaby to keep a low degree of coupling. Your application talks to the web service only, and the web service talks to stored procedures only. There are no SQL statements in your front-end code, only web service calls; and there are no SQL statements other than stored procedure calls in the web service. There are several advantages to this:
- It’s more maintainable. All the SQL queries are in one place, so you never have to go hunt them down (and possibly overlook a crucial one).
- Each component (web service, front-end, database) can be swapped out individually for testing and debugging.
- Many types of errors remain contained within one module, e.g. there cannot be any SQL syntax error in the front-end, because it doesn’t contain any SQL at all.
- If at some point you need to migrate to newer versions of your platform components, you can migrate each component individually, mitigating risks. For example, you may choose to move the front-end to a newer .NET, while the database server still runs the old version.
- It’s more scalable. If at some point you decide to split database and front-end over different servers, you can – just configure the front-end to talk to a different host for the web service, or configure the web service to connect to a different database host, or even both (which would create a physically separated three-tier structure). Additionally, you can implement caching and load balancing at several levels, including the intermediate web service. And if at some point you decide you need to shard your database, you can probably implement the required logic in the web service, without affecting the front-end at all.
And there’s the responsibility thing: By moving your actual queries to the database, they become part of the DBA’s realm. If you have a decent DBA, he’s probably much better at SQL than you are (especially when it comes to obscure yet business-critical edge cases that can have dramatic effects on performance), and it’s a good thing to have him at least check your SQL before it goes into production.
1
I was talking to a DBA about this a few weeks ago. Essentially, it gives them an added layer of security against you making a mistake. If you don’t parameterise that query properly, there is a chance that someone malicious can inject SQL into your database that can do serious harm.
And this is where the conflict arises. You think you’re always going to do your job properly, so there’s no need for that layer of security. And you might well be right. But I’ve seen senior developers write ad-hoc queries that are vulnerable to SQL Injection, so I can understand a DBA insisting on that policy.
It’s his neck on the line (or at least him who is going to get a call at 4am) if you make a mistake that destroys the database.
2