Re: Re: Tunnelling SQL over HTTP with MDB2

Hi Greg, Greg Beaver wrote:

Olivier Guilyardi wrote:

I'm currently on a professional project where such remote database access is needed. There is a simple solution : encapsulating SQL queries into HTTP requests. But wouldn't it be great if that was handled as part of the abstraction that MDB2 offers ?

I think the authentication/security level is pretty darn critical. In addition, I don't think you'll find it is either more flexible or faster to send the entire SQL query over HTTP. Far better is define a web service that accepts data, and then does the necessary database access internally. Passing arbitrary SQL over HTTP is a recipe for complete and total disaster. You'll be giving everyone the same access to the database that the local user has, which probably includes the ability to do nice things like "DELETE FROM tablename." A web service can make this simply impossible to do, and is far more truly flexible. You may think your only problem is security, but in truth, you will have to find a way to debug SQL remotely, and will only encourage disorganized spaghetti code.

About security: I do not follow you. If the tunnel is secure I don't see where the problem is. As an example, what about the thousands of people using SSH to administer remote servers ? They're root, they can rm the whole filesystem, but SSH makes it safe. MDB2 could implement an "internal HTTP tunnel" that handles such security transparently. In this situation, I don't see how debugging SQL would differ from using MDB2 as one currently do. If I understand correctly you propose to make a webservice that's specifically designed for my needs, and, as such, tries to reduce security risks as much as possible.

Your only decision is what kind of web service to set up. XML-RPC and SOAP are pretty similar in that they allow you to do this kind of thing:

I agree that putting SQL queries into higher level methods (getWidgets(), addWidget(), etc...) that run on the server, and making these available through XML_RPC or SOAP sounds good. It may be even a good way to separate, say, the Controller from the Model layer, but that's another question. However, a webservice and a SQL tunnel are two different approaches, and I don't see why one is so obviously better than the other to you. It's a design choice, and depends on the context.

REST is looser, and only requires that every resource has a unique URI. In your case, I think REST may be a better choice, because you're clearly looking for flexibility of access to the data in terms of querying.

REST looks great. But you are discussing the details of the webservice implementation while I'm simply not convinced a webservice is better than a tunnel.

Depending on what information needs to be retrieved, instead of defining a single url that you query for data (http://www.example.com/xmlrpc.php, for instance) you will instead do a similar process to database design. Say you want to retrieve a widget by ID, you might have widgets at: http://www.example.com/widgets.php/id/5 http://www.example.com/widgets.php/id/6 Read-only access to widgets would go through widgets.php. The data can be returned as XML, text, serialized PHP, whatever. All you need is a Content-type header. To modify data, you would provide a separate interface, such as: http://www.example.com/savewidget.php data would be sent via POST like a normal web form. Response from savewidget.php could simply tell you where to access the new data, perhaps using an XML file like so (anything is possible, this is a rather brainless example): <result> <success/> <widgeturl>http://www.example.com/widgets.php/id/7</widgeturl> </result> The strength of this approach is that you distribute your web application across multiple requests, allowing (for instance) moving resources to separate servers should scalability become an issue. You also are not locked into a single API, and can upgrade the server without affecting older installations.

This is a lot of interfaces specifications where my only need is to access a remote server. I somehow see your point with load balancing, and must admit I did not think about how this is feasible with tunnelling.

Also, security is handled via simple mechanisms: SSL and HTTP Auth. This removes the need to tunnel altogether. Debugging works on several fronts, as you will have the HTTP access log to track access to widgets as well as other sources.

Maybe that "tunnel" is not the right word. A diagram (see below) should help clarifying. I think that what I call tunnel actually is a web service that allows to send arbitrary SQL queries... That's a little confusing, but still coherent I think.

In any case, sending raw SQL over HTTP is just about the worst idea ever :) - particularly because the issues with doing so will not become apparent until you have deployed the entire application and have to rewrite it from scratch. Not fun.

That's very affirmative. It depends how it's done IMO.

Remote data access is the primary reason web services were devised in the first place, so I would recommend scouring Google's archives for examples of usage of REST (Representative State Transfer, not Restructured Text), XML-RPC and SOAP, and decide which is best for your situation.

I don't agree. "Remote data access" is a very generic term. How can you, for example, compare : 1 - a developer like me who is the only one to have control on both the client and server side. 2 - public/commercial web services provided by UPS, Amazon, Ebay, and the like, where the client side is totally out of control ? These are both "remote data access", but under very different circumstances... Now, let me go into the details of my prefered solution :

                      +-------------+
                      | Application |
                      +------+------+
                             |
       +---------------------|------------------------------------+
       | MDB2                |                                    |
       |             +-------+----+    +------------------------+ |
       |             | Public API +----+ MDB2_Driver_HTTPTunnel | |
       |             +------------+    +---------+--------------+ |
       |                                         |                |
       +-----------------------------------------|----------------+
                                                 |
                                           +-----+-----+
                                           | SOAP_WSDL |
                                           +-----+-----+
                                                 |
                                               HTTPS
                                                 |
                                         +-------+-------------+
                             +-----------+ Services_WebService |
                             |           +---------------------+
                             |
       +---------------------|------------------------------------+
       | MDB2                |                                    |
       |             +-------+----+    +---------------+          |
       |             | Public API +----+ MDB2_Driver_* |          |
       |             +------------+    +----------+----+          |
       |                                          |               |
       +------------------------------------------|---------------+
                                                  |
                                         +--------+--------+
                                         |    Database     |
                                         +-----------------+

Benefit : just use MDB2 as usual, only the dsn changes... -- og