Sending SQL queries as strings from client to WCF service.


This is a Software Architecture question and not a technical question. Let me start to explain what the problem is with the following simplified example:

- My boss wants to make two cliënt applications (for instance CustomerWinform + CustomerASPWebsite) and a WCF service called CustomerService.

- He wants CustomerWinform and CustomerASPWebsite to construct the SQL queries they need themselves and pass these trough to CustomerService. CustomerService will execute the received SQL query, apply logic when needed and send data back.

- So quite literally:Enduser clicks on button in CustomerWinForm and the query "SELECT * FROM Customer WHERE Id = 1" will be concatenated together. This string will be passed to method GetCustomer(query As String) in CustomerService. The service will execute the query and return data.

This breaks so many common practices, I don't even know where to begin. I need other experts with good arguments why not to do this. So I can convince the others why we shouldn't do this. I'm confident that we shouldn't. Unless ofcourse the experts on this forum tell me this is the best thing ever since paperclips.
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Carl TawnConnect With a Mentor Systems and Integration DeveloperCommented:
Firstly, your boss sounds like an idiot :)

Ok, previous examples were probably overly simplified. Your business objects would likely reside in a separate DLL shared between all the tiers so any change to the business object only takes place in that one place. Besides, you could simply return data as XML instead then you wouldn't need to change anything anyway.

A more likely scenario is that you don't add or remove columns, but your table becomes so big that you decide to separate some data out into an archive, or your rows become unwieldy so you split data across tables instead. If your SQL stuff is handled by the service then any structural changes like that become transparent to clients as the service would take care of pulling data from different tables back into one combined result set.

What about query performance? Sending arbitary commands to the SQL server means that it has no chance to cache execution plans or optimise execution.

>> "As for switching datasources, we haven't switched in over 10 years from MSSQL. Why would we suddenly do so now?"
That's perfectly valid. You probably won't, but the flexibility to cope with it if it happens is there.

You may want to ask your boss why he even wants to bother with a service. Why not just expose the SQL Server to the whole world? After all, what he is proposing (allowing people to send arbitary commands to SQL through a service) is no more secure than allowing them to query the SQL Server directly.
hosneylkConnect With a Mentor Commented:
well one of the main problems that would arise from this is the security threat of sql injection..
LabelsoftAuthor Commented:
Thank you my good man, nice and quick! That's indeed an important one.

But you said: "well one of the main problems..." Can you give me more problems?
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

hosneylkConnect With a Mentor Commented:
let me see..

i guess there's the problem of network traffic. but then again if a web service is really necessary in this case i don't think there's a way of avoiding this.

and then there's the problem of it being harder to debug since all the statements will have to be executed as dynamic sql.

i'll let you know if i can think of any other issues with this method :)
Carl TawnConnect With a Mentor Systems and Integration DeveloperCommented:
SQL Injection would mean injecting code into queries to perform malicious actions. But if this web service is publicly available, and not properly secured, then that won't even be necessary because anybody could start throwing arbitary sql commands at your service without the hassle of even having to inject anything.
LabelsoftAuthor Commented:
So we got:

- Security (messing with the sql)
-  Maintainability (much harder to debug dynamic sql)

What else?
Carl TawnConnect With a Mentor Systems and Integration DeveloperCommented:
Well there is REALLY bad design, which you kind of alluded to in your original question. You basically have a WCF service that is simpy acting as a middle-man that will have no useful purpose except to forward queries to the database, and two clients that use a WCF service yet both still require specific knowledge about the structure of the database.
LabelsoftAuthor Commented:
@Carl Tawn:
Yes, I said exactly that and then I got asked: Why is it so bad that the client has knowledge about the structure of the DB?

I have a few arguments, but I'm interested in yours.
Carl TawnConnect With a Mentor Systems and Integration DeveloperCommented:
It's all about separation of concerns. Ideally your service should be providing data services, sending and receiving data objects as required. Your client applications should be talking to that service without knowing, or even caring, where the data is coming from. For example, your web application might want to display information about customers so it should make a connection to the service and say "oi! service! I want to know about customers", to which the service would reply "ok, here is a collection of Customer objects". The web application doesn't care if those customers come from a database, and xml file, pixie dust, etc.

At the moment if your database schema changes both of your applications need to know about the change, and both will need to be updated. If you return a set of well defined objects from a service then only the service needs to know about the change to the schema.

Also, if you ever want/need to switch your data source to a different database, or to xml files, or want to pull data from a cloud somewhere then you only need to reconfigure the WCF service, the client applications need never know about the change.
LabelsoftAuthor Commented:
I'm so totally with you on this. In fact, I'm wondering if my alter ego didn't create an account which is called "carl tawn".

But seriously for a sec. I'm going to play my boss again.

On the first remark he'd say: "Oh yeah? If you add a column to a table in the DB and you want to present this on the website, you will have to change this on 3 spots. You'll need to change the query in the service, add a property to the object you return and add a column to the grid to display the values in the new column in. In my solution you only need to change it on 1 spot, namely the query in the aspx, the grid is databound to the dataset the service returns."

"As for switching datasources, we haven't switched in over 10 years from MSSQL. Why would we suddenly do so now?"

This is how my boss would answer. What would you say to that?
LabelsoftAuthor Commented:
Haha, maybe some more background info before my boss gets totally thrashed online. He is very stubborn though. :)

He came up with his idea because he's afraid we'll be making a method for every query and even every parameter. So for instance CustomerService will look like:


And so on. So I tried to convince him that this is a matter of desiging your services smartly, in particular the interfaces. He still wants to bother with services because he claims we could still implement business logic in the services the way he wants to implement them. I don't see how though, I totally agree with your last comment.

But it's arguments like: "What if we split data ascross tables instead" or "we need flexibility to cope with varying datasources" plus "the danger of arbitrary sql statements" which is going to win the day for me I hope.

What do you think about his fear of having exponential growth in methods across services?
Carl TawnConnect With a Mentor Systems and Integration DeveloperCommented:
It depends how you got about it. Personally if I had a lot of variations like you are suggesting then I would simply create a "payload" object. Basically an object that defines a set of properties, or even key/value pairs, that you pass to a method of the service, which the service can interpret and then call the appropriate internal method.
LabelsoftAuthor Commented:
That is a solution, yes. I was thinking myself in the above simplified example to provide two methods:

1) GetCustomer(id, name, birthdate)
2) GetCustomerByStore(store)

And then when 1 is called the appropiate query will be build in the Data Access Layer of the CustomerService. This has the benefit of 1 spot where the query is built, so fairly maintainable and thus debuggable. Would ofcourse be better not to have any dynamic queries at all, but oh well...

Point being, you and I came up with 2 possible solutions in 5 minutes to cope with prevention of massive interface chaos. And it didn't involve  passing entire SQL through. ;)
Carl TawnConnect With a Mentor Systems and Integration DeveloperCommented:
I don't know your bosses history, but if he doesn't come from an OO world the i guess it's a bit more difficult to visualize your data as objects.
LabelsoftAuthor Commented:
Well yeah, you laid the underlying problem perfectly bare indeed. It's hard to argue because he's kind of oldschool. So 'soft' arguments like: "You're breaking seperation of concern" will not win you the argument. You have to dig deep and remember why exactly you keep making that Data Access Layer you know and provide 'hard' arguments. Keeps you sharp, but is sometimes a bit annoying.

For instance your comment about splitting up a large table into two is a perfect example why you can't have queries in your presentation layer. It was an "oh yeah" moment for me and i'm sure I can take this into the argument.
Nash2334Connect With a Mentor Commented:
You don't need 5 different GetCustomer queries, that is ridiculous.  All you need is one with nullable parameters.  We use the following pattern all the time in stored procedures:

@CustomerID int

  dbo.Customer AS c
  c.CustomerID = ISNULL(@CustomerID, c.CustomerID)

This would act as both a SELECT ALL (passing NULL into the Customer ID parameter) and a SELECT specific (by passing in a specific ID).  You can extend this to as many parameters as you wish.  You can then pass whichever parameters you'd like over the service calls.

Passing a dynamically generated SQL query to a service is great until the first TRUNCATE command is executed successfully.  If you boss makes you implement this design you should have him sign a waiver for you.
LabelsoftAuthor Commented:
I think we didn't get to the bottom of it. But we came a long way. All comments were valid. Nice job. I distributed the points.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.