0

I really don't like the concept of opening my SQL server(s) to the internet - even if I can lock down the firewall. However I've always been working directly with databases. I'm building a system now which involves 1 SQL Server database, a web application in ASP.NET/C#, and a few windows applications in Delphi XE2. But from the beginning, I'd like to put some sort of 'filter' around the database so I don't have to open it up.

I know there are many things out there for this, but don't know anything about them or what to get for my scenario. I'd like to keep it native to SQL Server; I don't plan on using any other type of database engine.

It needs to be connected from client to server by other means than the standard SQL connection, like a filter. It creates its own encrypted packets and transfers data its own way. I will have a wrapper class for both Delphi and C# which will pretty much be identical - and be able to stream its data into the DLL to interact with the DB.

Now there are three different ways I can go about this...

  • Complete SQL Server wrapper, most likely no source code, might even have its own language (I don't want to pick up another database language), and independent from my project as its own separate system.
  • Open-source wrapper, preferably in Delphi (XE2), or if not then C#, specific protocols for my system, entirely dedicated to my project, and in the final form of a DLL which can be used on both the Website (in C#) and the applications (in Delphi).
  • Web Service - however I only have 1 hosting spot (Paying for 1 site, 2nd site will be a double charge on me). I can't host any additional web services or windows services; it has to be integrated with the website. Otherwise, I would have done a web service for this.

I would much prefer the second option, and do not want to go anywhere close to the first one, and can't do the third one at all.

So any good libraries for database layers? And might there be some already installed in Delphi XE2? I'm thinking maybe an encrypted XML packet?

As an example, let's say I have a table for 'Customers'. In both my website and applications, I should never have any SQL script like select * from Customers or no SQL script in general. Instead, I will have a wrapper around the database. So I can call a function such as DBGetCustomers(Conditions: TGetCustomersConditions): TDBCustomers; where TGetCustomersConditions is some way of filtering the query, and TDBCustomers represents the results from the query.

There could also be a function DBAddCustomer(Item: TCustomerToAdd): TInsertSuccess; where TCustomerToAdd represents what to insert, and TInsertSuccess represents any result, such as error message(s) or rows affected. I do not intend for it to be working exactly like this, but just to explain the concept of any wrapper in general. When the app sends to request to the server, it still has not converted anything to a SQL Query. By the time the request gets to the server (which is able to connect to the database), then the server alone decodes everything to the SQL query.

Improve this question
4
  • I'd love to know from these downvoters how this question is 'not useful' - I highly doubt I'm the only person in the world who wants to put a layer around their database for security. Commented Feb 2, 2012 at 14:17
  • Unfortunately you just have to get used to it (+1). I have had a few ridiculous down votes from people who clearly never understood the concepts involved. Not sure what the point of allowing anonymous down-voting is - and I reckon a reason should be mandatory! Commented Feb 2, 2012 at 21:14
  • I have insisted on the same thing - If someone wants to downvote, it should require at least 20 characters description of why. Commented Feb 3, 2012 at 0:55
  • Guys, please add this discussion to Encouraging people to explain down votes. Commented Feb 3, 2012 at 5:00

4 Answers 4

Reset to default
3

What's the problem even if you have just one "hosting spot"? A web service is just a "site". And a web server can easily host multiple site even with a single IP address. Anyway, what you're looking for is an "application server" in a multi-tier design.

While Java invested heavily in that direction, MS did not. Delphi has Datasnap, which is a so-so framework, don't know if the new "restful" interface is easily callable from C#, it looks to have security flaws though. The .NET way of doing it is using WCF, as long as it uses a standard protocol you can call if from Delphi has well.

You could also look at RemObjects DataAbstract. It's not open source, but it is a mature library.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

+ 1 DataAbstract seems to support Open Data Protocol (according to odata.org/producers) with Server libraries for .NET, Mono (Linux & OS X) and Delphi.
What's the problem even if you have just one "hosting spot"? A web service is just a "site". - The server is not mine, I am paying for one site. Another site (such as a web service) I will have to pay for double hosting. I'd rather not get into the details. +1 none the less.
Unless there's a way to put a web service inside of another site? Like a single page which has only the task of receiving/responding to HTTP requests?
A web service could be a URL inside the existing site, I guess, but I really don't how much control you have on the web server.
1

One of the 'traditional' ways to do this is via webservices (although this technique is now considered by some to be a bit dated).

One disadvantage is that it is not a generic wrapper you can throw around the database, but it has the advantage that you can limit access to the database easily to specific stored procedures for example, which will maximise security, and is a standard technique if you wish to provide limited authorised access to external applications.

Improve this answer

3 Comments

+1, and I've actually used web services like this before too. But in this particular case though, I only have 1 hosting spot, 1 database, and very limited access to the server. Otherwise I actually would have done that. I'll add that as a 'third' option...
+1 why are web services outdated? Check out Open Data Protocol, used by many vendors including SAP. Microsoft, IBM
It should have said "out of fashion" :)
1

If you already use a ASP.NET C# web application, you could also add a WCF (Windows Communication Foundation) based web service to your web site. This can provide database access to external applications, which need to connect the web service somehow. It should even be possible to use the same HTTP standard port for both the normal web site pages and the service, by mapping the web service to a specific context path like www.example.com/services/servicename

Improve this answer

Comments

1

Kbmw allows you to make a ntier db architecture.

However since you have a web application involved, a better option would be WCF (as already suggested)

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.