Solved

Sending SQL queries as strings from client to WCF service.

Posted on 2011-03-14
17
729 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
Comment Utility
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
Comment Utility
@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
Comment Utility
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
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 420 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
@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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 3

Author Comment

by:Labelsoft
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
These instructions are based on installing Owncloud on your new raspberry pi connected with a usb HDD. What do you need Part A? A Raspberry Pi, model B. A boot SD card for the Raspberry Pi. A usb HDD An Ethernet cable to connect to the lo…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now