Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

Typically when creating a simple tool or something that has to use a database, I go through the fairly long process of first creating a webservice that connects to a database then creating methods on this webservice that do all the type of queries I need.. methods like

List<Users> GetUsers()
{
    ...
}
User GetUserByID(int id)
{  
    ...
}
//More Get/Update/Add/Delete methods

Is it terrible design to simply make the webservice as secure as I can (not quite sure the way to do something like this yet) and just make a couple methods like this

SqlDataReader RunQuery(string sql)
{
     ...
}

void RunNonQuery(string sql)
{
     ...
}

I would sorta be like exposing my database to the internet I suppose, which sounds bad but I'm not sure.

I just feel like I waste so much time running everything through this webservice, there has to be a quicker yet safe way that doesn't involve my application connecting directly to the database (the application can't connect directly to database because the database isn't open to any connections but localhost, and where the appliction resides the standard sql ports are blocked anyway)

Especially when I just need to run a few simple queries

share|improve this question
22  
It is an absurdly bad idea. I'd post this as the answer, but 6 word answers just feel wrong... –  Telastyn Jul 4 '12 at 15:13
    
2  
Why is it a 'fairly long process' ? The code to make the connection only has to be written once. And you should be able to serialize the result directly to JSON. All that's left is passing the web service arguments into a SQL prepared statement. All in all sounds like about three lines of code per service, plus the configuration to connect the service to some URL. –  kevin cline Jul 4 '12 at 15:41
1  
Every body said its bad very few suggested alternatives! It does not help to just criticize. –  Emmad Kareem Jul 4 '12 at 20:36
2  
If your webservices are just a thin wrapper over a database then you should give WCF DataServices a go with Entity Framework as your ORM. ALso you cannot expose a SqlDataReader over a web service. –  Pratik Jul 4 '12 at 22:09
show 4 more comments

8 Answers

up vote 11 down vote accepted

It is terrible design and you know it. You're leaving yourself at mercy of anyone with even a slight malevolent intent. Apart from being vulnerable to regular sql injection, you expose an entire webservice to throw sql at, bypassing even the need to inject it. Doing such a thing with your private database is simply unwise, doing it with a client's production database could also potentially open you to legal consequences if contents are leaked.

Use an ORM if you don't use one already, it might give you the time savings you're looking for.

share|improve this answer
    
not only that, but it leads to a lot of duplicate code. Every time you need to get a User, you will need to write SQL code to get that users data. If you ever change the database design to add an extra column, you will need to update EVERY call to get a User. If you just had a GetUser function, you only ever need to update a single function if your database changes. –  Gavin Coates Oct 24 '12 at 9:40
add comment

I just feel like I waste so much time running everything through this webservice, there has to be a quicker yet safe way that doesn't involve my application connecting directly to the database (the application can't connect directly to database because the database isn't open to any connections but localhost, and where the appliction resides the standard sql ports are blocked anyway)

Why not just change your database to open it to any connections? By writing this web service that's what your doing. (in a bit of an arse backwards way)

I would also have huge concerns that HTTP encoding could change the meaning of some of your sql statements causing errors. (potentially destructive ones)

share|improve this answer
add comment

The Only Real issue with this is security.. Then there is that fact that you have an API that's not defined at all, but you may not care about this.

But if this is a "Private" web service the (requires credentials and has a very small set of trusted users) I see nothing wrong with it.

Ok, I Confess.. I have (to create distributed apps) Open DB Ports over the internet and Connected my FAT apps directly to the DB over the internet, relying on only the DB security for protection. This has saved me weeks of work.


I'd also like to point out that all online DB management tools basically do something similar to this (MySql Admin for Example)..

share|improve this answer
2  
Even excepting the security issue, an incompetently written query passed in could bring your system to a grinding halt. Or two queries coming in producing a deadlock--you would have NO control over this. –  Matthew Flynn Jul 4 '12 at 17:06
1  
@MatthewFlynn Most web services don't protect against this either.. Again, I am only saying this is Ok in a "Private", environment (See my definition above, the key word is "Trusted users", that means trusted not to do any thing stupid ) –  Morons Jul 4 '12 at 17:11
add comment

This is bad on 2 levels:

  • Obviously security is a concern
  • Your other problem is one of design. If you Call GetSomePeople from the wweb service it is easier to later decouple your app from the database. All you need to do is implement GetSomePeople in whatever new technology you choose.

The final question is why would you want to do this? You spent so much time asking this question that something is already telling you not to do it. If you just went ahead with the web service you could have probably already been done :)

Good design is never a waste of time!

share|improve this answer
    
As far as why I asked the question, it's because I was looking for a better way than using something like a webservice, but it's really one of the only things I know how to do. When an application is changing all the time does it not seem tedious and a little ridiculous to change your code in probably more than 3 spots just so something like your 'GetUser' method returns one more value (bad example but you get the picture)? What you think would be a 30sec job turns into a 15 minute job –  Kyle Jul 4 '12 at 21:07
    
Sadly keeping your design simple can cause headaches later on in the process, like the example of decoupling. No matter how small a tool is its possible that someone else or some other tool can also make use of your webservice, so you are promoting re-use of code. In my experience even my small utilities with bad design decisions need to be refactored later, and then I regret not doing it right the first time around. Better design does sometimes result in a little more effort on the outset, but will always profit you in the long run. –  Tjaart Jul 5 '12 at 11:09
add comment

Yes this is a bad idea, from a security point of view. It could allow for a select credit_card_numbers from credit_card_accounts or drop table users to be executed if anyone figures out what your service does and how to send messages directly to it.

Is there a reason you can't have stored procedures (one for each query/statement you might need to execute) that take a few key inputs that the webservice passes to them, and a webservice operation for each stored proc? You could even just create one operation and it could take a stored procedure name and list of inputs to it. Not as good as separate operations, but still better than arbitrary SQL.

The only reason I can think of for allowing the webservice to take arbitrary SQL and execute it would be for a sandbox/testing environment which is on an isolated network and if anything bad happens, it can be wiped and restored to the base image easily.

share|improve this answer
    
I'm just trying to figure out a way whenever I make a simple change, or add something new I don't have to make changes in 3 different layers/spots. I used to run everything off of stored procedures, but that became such a burden I switched all the webservice methods to just use parameterized queries within the method. –  Kyle Jul 4 '12 at 15:52
    
@Kyle: Ok, so if you're application is going through many fast changes, it might be OK to pass SQL directly to the web service. For now. I'd still recommend having a "clean-up" phase before the deployment where you properly convert the SQL statements into stored procedures. And if you must pass SQL to the webservice, at least encrypt it. –  FrustratedWithFormsDesigner Jul 4 '12 at 16:00
    
I could deal with something like that, but now I'm curious how most people handle a situation like this? As most people (in this thread anyway) have big frowns on their faces, I'm assuming no one really uses a method like this. They probably just connect directly to the database from the application, implement something like a web service before final deployment, more commonly probably using a development database until apps ready, then switch? –  Kyle Jul 4 '12 at 21:14
add comment

Have you looked for a framework? e.g. If you are programming in Java I'd recommend investing 5 days learning the basics of Spring with its REST and Hibernate tools. Or find a similar framework for your programming language.

I learned long ago that if a problem requires writing lots of trivial code then some nice programmers elsewhere have probably built some tools/framework to make it easier.

A good framework will solve a lot of your problems, including some you haven't thought of yet. It will also make it more maintainable for another programmer. And you get some good buzzwords for your resume.

share|improve this answer
    
Entity framework in .Net serves the same purpose. –  Tjaart Jul 5 '12 at 11:11
add comment

I'll play Devil's advocate here, and say 'maybe not'. The major issue is security, which I'll come to later.

Hands up if you are a fan of the relational model. For many domains, it's the only one that makes sense. SQL is the only serious language we have for asking arbitrary (not predefined) questions about a given dataset.

With a traditional HTTP based web service, you are restricted to asking the questions the API designer thought of (and could be bothered to implement). Of course, you can grab as much data as you can and do the 'joins' on your side, missing out on the benefits of the relational model. This route is fraught with bugs, duplicated effort, brittleness, and the rest.

If you want consumers of your data API to be able to ask questions that you don't know in advance, SQL is the best language.

Of course security is why your question sets off so many red flags, and there aren't established best practices to follow, because almost no one allows SQL-level access in the first place.

Some databases allow you to restrict users to a set of particular views, and can prevent destructive (INSERT, DELETE, etc.) operations, preventing many of the injection issues raised by other answers. Allowing only view based access would also allow a degree of decoupling from the actual table structure.

Finally, Tom Squires rightly points out that HTTP isn't adding much value here. You may as well just allow direct DB connections.

share|improve this answer
add comment

Of course this is bad from a security front, as everyone has pointed out.

But it's at least as bad from the database management perspective. How do you ensure:

  • the queries are competently written?
  • will use indexes?
  • will deal with competing insert/updates gracefully
  • will not run into dead-locking issues?

In short, there is no way in which this is a good idea.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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