4

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?

Improve this question
4
  • You know .NET gives you this for free via WCF Data Services msdn.microsoft.com/en-us/data/odata.aspx
    – Michael Brown
    Commented Aug 13, 2012 at 1:19
  • 3
    This way of designing systems corelates with SOA (Service Oriented Arhitecture). Basically, you have database operations as a service. This may be an unofficial policy for you, but it is used quite often. It is more scalable and easy to mentain, as @tdammers stated.
    – Coral Doe
    Commented Aug 13, 2012 at 7:30
  • You sure you need to be selecting everything (*)? ;-)
    – Anonymous
    Commented Aug 13, 2012 at 9:00
  • If the application server and the database server are on the same machine and if the db is not used by other enterprise applications, then you don't need this service layer. However, if you plan to share this database in other applications or from other domains/platforms (say an Windows Forms application), then for security reasons, this becomes a must.
    – NoChance
    Commented Aug 3, 2017 at 1:44

2 Answers 2

Reset to default
9

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.

Improve this answer
1
  • Should we create a stored procedure for each sql statement in our application? For example a simple select statement
    – Suhail Gupta
    Commented Jul 19, 2016 at 6:29
9

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.

Improve this answer
2
  • Thanks for your input! The strange part of it is, before I started here they never used ADO.Net parameters to prevent injection. I had to show him what it was. He was using a homemade SQL cleaner method on each parameter before he concatenated it to the SQL string. I completely understand your answer, but since the beginning I've felt like I've been the one making sure these sites are secure. I'll give this a day or two before I choose who to give a check mark to.
    – Isaac Fife
    Commented Aug 13, 2012 at 0:35
  • 5
    @IsaacFife: sounds like you've already hit that happy moment in the life of a junior programmer where you discover that some of the senior programmers are just older than you, not actually better programmers. :-)
    – Carson63000
    Commented Aug 13, 2012 at 2:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.