Solved

Sending SQL queries as strings from client to WCF service.

Posted on 2011-03-14
17
733 Views
Last Modified: 2012-05-11
Experts,

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.
0
Comment
Question by:Labelsoft
  • 8
  • 6
  • 2
  • +1
17 Comments
 
LVL 10

Assisted Solution

by:hosneylk
hosneylk earned 40 total points
ID: 35126636
well one of the main problems that would arise from this is the security threat of sql injection..

http://en.wikipedia.org/wiki/SQL_injection
0
 
LVL 3

Author Comment

by:Labelsoft
ID: 35126660
@hosneylk:
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?
0
 
LVL 10

Assisted Solution

by:hosneylk
hosneylk earned 40 total points
ID: 35126685
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 :)
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 420 total points
ID: 35126764
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.
0
 
LVL 3

Author Comment

by:Labelsoft
ID: 35127070
So we got:

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

What else?
0
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 420 total points
ID: 35127091
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.
0
 
LVL 3

Author Comment

by:Labelsoft
ID: 35127191
@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.
0
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 420 total points
ID: 35127244
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.
0
 
LVL 3

Author Comment

by:Labelsoft
ID: 35127868
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?
0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 420 total points
ID: 35128087
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.
0
 
LVL 3

Author Comment

by:Labelsoft
ID: 35128457
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:

GetCustomerByID
GetCustomerByStoreID
GetCustomerByName
GetCustomerByBirthDate

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?
0
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 420 total points
ID: 35128507
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.
0
 
LVL 3

Author Comment

by:Labelsoft
ID: 35128605
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. ;)
0
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 420 total points
ID: 35128638
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.
0
 
LVL 3

Author Comment

by:Labelsoft
ID: 35128755
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.
0
 
LVL 10

Assisted Solution

by:Nash2334
Nash2334 earned 40 total points
ID: 35132859
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

SELECT
   CustomerID
  ,CustomerName
FROM
  dbo.Customer AS c
WHERE
  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.
0
 
LVL 3

Author Closing Comment

by:Labelsoft
ID: 35146829
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.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

To properly understand GitHub, let’s divide it into two words ‘Git’ and ‘Hub’. Git is basically a ‘Distribution Version Control’ (DVC) and ‘Source Code Management’ (SCM) system widely used by software programmers while Hub means the efficient centre…
The task of choosing a web design company to build a website for your business should never be taken in a light manner. Provided the fact that your website will act as a representative to your business and will be responsible for imposing an online …
The purpose of this video is to demonstrate how to set up the WordPress backend so that each page automatically generates a Mailchimp signup form in the sidebar. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question