Django as a mysql proxy server?

Question

I'm in the process of building a Django powered site that is backed by a MySQL server. This MySQL server is going to be accessed from additional sources, other than the website, to read and write table data; such as a program that users run locally which connects to the database.

Currently the program running locally is using the MySQL/C Connector library to connect directly to the sql server and execute queries. In a final release to the public this seems insecure, since I would be exposing the connection string to the database in the code or in a configuration file.

One alternative I'm considering is having all queries be sent to the Django website (authenticated with a user's login and password) and then the site will sanitize and execute the queries on the user's behalf and return the results to them.

This has a number of downsides that I can think of. The webserver will be under a much larger load by processing all the SQL queries and this could potentially exceed the limit of my host. Additionally, I would have to figure out some way of serializing and transmitting the sql results in Python and then unserializing them in C/C++ on the client side. This would be a decent amount of custom code to write and maintain.

Any other downsides to this approach people can think of?

Does this sound reasonable and if it does, anything that could ease working on it; such as Python or C libraries to help develop the proxy interface?

If it sounds like a bad idea, any suggestions for alternative solutions i.e. a Python library that specializes in this type of proxy sql server logic, a method of encrypting sql connection strings so I can securely use my current solution, etc...?

Lastly, is this a valid concern? The database currently doesn't hold any terribly sensitive information about users (most sensitive would be their email and their site password which they may have reused from another source) but it could in the future which is my cause for concern if it's not secure.

Answer 1

This is a completely valid concern and a very common problem. You have described creating a RESTful API. I guess it could be considered a proxy to a database but is not usually referred to as a proxy.

Django is a great tool to use to use to accomplish this. Django even has a couple packages that will assist in speedy development, Django REST Framework, Tastiepy, and django-piston are the most popular. Of course you could just use plain old Django.

Your Django project would be the only thing that interfaces with the database and clients can send authenticated requests to Django; so clients will never connect directly to your database. This will give you fine grained permission control on a per client, per resource basis.

The webserver will be under a much larger load by processing all the SQL queries and this could potentially exceed the limit of my host

I believe scaling a webservice is going to be a lot easier then scaling direct connections from your clients to your database. There are many tried and true methods for scaling apps that have hundreds of requests per seconds to their databases. Because you have Django between you and the webserver you can implement caching for frequently requested resources.

Additionally, I would have to figure out some way of serializing and transmitting the SQL results in Python and then unserializing them in C/C++ on the client side

This should be a moot issue. There are lots of extremely popular data interchange formats. I have never used C/C++ but a quick search I saw a couple of c/c++ json serializers. python has JSON built in for free, there shouldn't be any custom code to maintain regarding this if you use a premade C/C++ JSON library.

Any other downsides to this approach people can think of?

I don't think there are any downsides, It is a tried and true method. It has been proven for a decade and the most popular sites in the world expose themselves through restful apis

Does this sound reasonable and if it does, anything that could ease working on it; such as Python or C libraries to help develop the proxy interface?

It sounds very reasonable, the Django apps I mentioned at the beginning of the answer should provide some boiler plate to allow you to get started on your API quicker.