Re: Tunnelling SQL over HTTP with MDB2

Olivier Guilyardi wrote: > Hi, > > As you all know many hosting providers only permit access through the > database server from the web server, not from the outside. > > 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. > > There's a project out there named "PHP Tunnel" that does that : > http://www.mysqlfront.de/manual/fphptunnel.html > > But wouldn't it be great if that was handled as part of the abstraction > that MDB2 offers ? > > One would call $mdb2->query() as usual and that could run on a local or > remote-over-http server.... > > This would simply require to write a new MDB2 driver, and a little > server-side script to perform the real database queries. > > The only critical part I see is at the authentication/security level. > > Or have I missed something ? Is this already possible ? 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. 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: $remote->saveAdminData($user, $pass, $whatever); and the code is sent to the server and then executed. 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. 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. 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. 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. 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. Greg