How to embed mysql db content into 3rd party sites without providing remote access

Question

I am writing a set of HTML based resources, stored in a mysql db on our server. The resources contain images references, which are stored as a relative paths.

I can login to an admin panel and create and edit resources very easily.

This bit is all done, and working well.

However, we are going to want to provide this resource in two ways: packaged and hosted. The one that is providing real problems is the hosted solution:

We want to host the database and all image resources, however we want to give access to the resources via a set of templates for other users to host on their own site. This is so they can brand the templates accordingly, and have the resource available at their own URL e.g.

http://www.example.com/discojoe

There are two questions I have on this process, which are causing me real headaches:

• I realise the obvious security implication of providing even read-only access to our mysql server. The only alternative I think of is some server side code running on our server, which when requested, squirts the requested data back to the user. This removes the need for them to have any mysql connection.

Are there any examples of the above type of scenario online I can read up on, or can you give some pointers on how I would go about addressing this solution?

• For users whom we stream the data to (if the above solution is sensible), how could I go about updating the image SRC value on the fly within the HTML content. Whilst I want it stored as a relative URL in the database, when I retrieve the data from the database, I want to convert all image srcs from relative to absolute, with the absolute URL that I specify.

Answer 1

I realise the obvious security implication of providing even read-only access to our mysql server. The only alternative I think of is some server side code running on our server, which when requested, squirts the requested data back to the user. This removes the need for them to have any mysql connection.

You could create an REST API(I would return JSON) using predefined queries with PDO prepared statements(safe against SQL-injections). With a little bit of care you could make it pretty safe. Ofcourse if the resources should be protected, you must also add authentication to your system using simple API keys for example. I think you could generate these key easily the same way you prevent CSRF($token = md5(uniqid(rand(), TRUE));). Maybe you should add a little bit more entropy, but I think this is going to be sufficient. But if you want to really do it correctly you should use oauth instead.

---

with a little bit of mod_rewriting you could write pretty URLs.

For users whom we stream the data to (if the above solution is sensible), how could I go about updating the image SRC value on the fly within the HTML content. Whilst I want it stored as a relative URL in the database, when I retrieve the data from the database, I want to convert all image srcs from relative to absolute, with the absolute URL that I specify.

I think you could use any of the many available template languages to achieve this. Even jquery has one built-in

Answer 2

Create a REST-style web service. That is, set up an HTTP server that responds to data requests by using some server code to load up your templates, alter the URLs and other things (relative to absolute), and sends it to the client as fragments of HTML (or even CSS).

Your user, running on another web server, can use an HTTP client package to consume your web service, incorporate the resulting code fragments into her page, and send it out.

Alternatively you could build your code fragments so they function in iframe objects. In that case your user would build her code to deliver iframe objects to her end-users with references to your server in them.

Finally, your web service could deliver XML or JSON, and be consumed by AJAX-style javacscript in the end-user's browsers.

You are absolutely right to prevent direct access to your mySQL table server from random clients.