How to query MySQL DB from client in a secure manner

Question

I'm doing a personal number-crunching project, and I'd like to launch multiple programs on multiple computers (maybe even on Amazon's servers someday), and have them all storing and sharing data in a common SQL database, located on my web hosting account.

The hosting company won't allow foreign connections directly to the SQL server, but I was thinking about writing a thin PHP script that would reside on the server and receive SQL commands from the remote programs using HTML POST commands, and pass back results as html. Then I could just use an HTTP library to pass the SQL commands straight into the remote server and get back results.

Obviously its a security issue to send naked SQL commands to a server. I was thinking about using some kind of shared-key encryption to send the post commands, and the results would be fine coming back unencrypted.

So, my question is, what am I not thinking about? I'm not an expert on web security, and I'm obviously missing something. Is there some major security hole here that's impossible to fill? Or is there some other method or library to do this that I haven't found?

Answer 1

It might be better storing the SQL as stored procedures on the database and call these SP from your script. This way you don't have to send any plain SQL (just parameters) and it will be easier to maintain.

What you mean with client? As client from a web application (JavaScript), you may perform it by making an Ajax call and return the data as Json. If you mean a windows client, it's more interesting to create a WebService instead of just a php page and use that in your application.

As for encryption, I think going over an SSL is more or less the best/only way to ensure full security.

Answer 2

If your hosting company allows SSH, tunnel through an SSH session to your MySQL server with your mysql client.

To reduce the volume of what you send for each query, as the previous answer suggests, create stored procedures for those queries and invoke only them with appropriate parameters.