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

  • 27
    It is an absurdly bad idea. I'd post this as the answer, but 6 word answers just feel wrong...
    – Telastyn
    Commented Jul 4, 2012 at 15:13
  • 1
    Usually in 3-tire or multi-tier projects , execution of all your queries is done in a Data-access layer, Not in a web-service layer.
    – Yusubov
    Commented Jul 4, 2012 at 15:16
  • 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. Commented Jul 4, 2012 at 15:41
  • 1
    Every body said its bad very few suggested alternatives! It does not help to just criticize.
    – NoChance
    Commented Jul 4, 2012 at 20:36
  • 3
    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.
    – softveda
    Commented Jul 4, 2012 at 22:09

10 Answers 10


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.

  • 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. Commented Oct 24, 2012 at 9:40

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)


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)..

  • 4
    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. Commented Jul 4, 2012 at 17:06
  • 3
    @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
    Commented Jul 4, 2012 at 17:11
  • I would say that there is no such thing as a 'trusted' user... Even high previlege users should have sanity checks / security applied to them, because they may 1. Have their account compromised, 2. Become malicious after a falling out at work, 3. Do stupid things when tired/ill/stressed, and many more reasons. This is simply a cop-out answer and you should not really count this as a reasonable argument
    – Milney
    Commented Dec 20, 2016 at 13:06
  • Your last sentence is also completely wrong. you say 'all online DB management tools basically do something similar to this' - but they explicitly state these portal should NOT be exposed to the internet and are for LAN or VPN usage. Please please please don't expose a database connection directly to the internet it is such a bad idea
    – Milney
    Commented Dec 20, 2016 at 13:09
  • and if 'most' of your web services dont do this security either then I am very glad to have not had to work with you as you obviously do web services very wrong. Please learn security before you mis-inform others on the internet
    – Milney
    Commented Dec 20, 2016 at 13:11

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!

  • 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
    Commented Jul 4, 2012 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
    Commented Jul 5, 2012 at 11:09

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.

  • 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
    Commented Jul 4, 2012 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. Commented Jul 4, 2012 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
    Commented Jul 4, 2012 at 21:14

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.

  • Entity framework in .Net serves the same purpose.
    – Tjaart
    Commented Jul 5, 2012 at 11:11

You don't say anything about what kind of 'simple tools' you are talking about, so you just get knee-jerk answers based on what people assume it to be.

If the tools are desktop apps distributed to untrusted users over the internet, then obviously it would be an incredible bad idea to allow them to execute arbitrary sql on your database server. On the other hand if the tool is an admin tool run by yourself or a webapp running on an trusted server then it could be fine to cut out the middleman and call directly to the database. So the answer is: It depends!

In any case, you solution seem kind of half baked. If you don't trust the clients enough to give them a direct database connection, then you shouldn't allow them to execute arbitrary SQL either. If your sysadmin disabled database connections for security reasons, then you should probably not try to circumvent this by "tunneling" arbitrary SQL through a web service.


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.


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.


People are not really criticizing just telling you it is not a good idea.

WCF is an alternative.

It is not like exposing the database to the internet - it is exposing the database to the internet. Ports are locked down for a reason. It is not just a security risk in general but clearly you would be violating your security policy. The person / group that locked those ports would be very unhappy with you.

As for "Especially when I just need to run a few simple queries"
Nothing saying you cannot have general method

void RunNonQuery(Int id)

Maybe not the best design but at least you are not passing SQL

With WCF I think you can return a DataTable and SqlDataReader so you could do like

SqlDataReader RunQuery(Int id)

Again maybe not the best design but it is secure

Lets say there was no sensitive data you could have a read only connection with a short timeout to protect against denial of service attacks

SqlDataReader RunQuery(String SQL)

But I seriously doubt you would get that past the security people
If you want to do something like that for sure get it approved by security
You don't want someone to just find out you are doing that
You need to get permission up front

Simple tool or something that has to use a database
Is it just a database
If the data does not need to be shared then consider an embedded database

Does the tool need to be desktop app? Can it be web app?

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