Ulf Wendel

Is the HTTP Plugin for MySQL secure?

| 1 Comment

The HTTP Plugin for MySQL offers three APIs: REST-like CRUD, REST-like JSON DOCUMENT and SQL. The SQL API lets you run any SQL you want. Including, for example, DROP mysql.users if you mess up your setup. Insecure? It depends on your viewpoint.

It’s more than just another protocol…

On the first look HTTP is just another network protocol for sending requests to MySQL. HTTP is the protocol of the web. Whether you need to integrate MySQL in a larger setup and use web services for data exchange or you want to access MySQL from a JavaScript client that is restricted to HTTP/Websocket. HTTP is the natural choice. CouchDB convinced many when it introduced the idea.

HTTP Client   Standard client
|   |
HTTP Protocol   MySQL C/S Protocol
|   |

Standard clients use the properitary binary MySQL Client/Server Protocol to communicate with MySQL. The optional HTTP Plugin makes MySQL also listens to HTTP requests. It is you choice whether you want to make MySQL speak HTTP or not. HTTP is a clear-text protocol. Whether clear-text or binary makes no difference: security by obscurity does not work.

Both the MySQL Client/Server Protocol and the HTTP Protocol can be run over secure channels. This usally means SSL. SSL is available for both protocols, which means it’s 1:1 again. Let’s do a litte cheat-sheet:

  HTTP MySQL C/S Security Rating
Obscurity Clear-text Binary 0 : 0
Encryption Supports SSL Supports SSL 0 : 0

The transport layer

The MySQL Client/Server Protocol either runs over TCP/IP, Unix Domain Sockets or Windows named-pipes. Unix Doman Sockets are an inter-process communication method for processes on one machine. If you deploy the MySQL Server and its clients on the same machine, you can use this method of communication. It ensures that data exchanged never appears on the network. The data stays on the machine, which adds to the overall security.

Although you may use TCP/IP to connect from a client to a MySQL Server on the same machine and the data should stay on the machine, this is no common deployment. Just by using the TCP/IP stack instead of a local IPC method, you loose a tiny bit of security. The TCP/IP stack could send data to the network, the local IPC can’t. It has no means to do so.

I assume most deploy MySQL and its clients on different machines. Then TCP/IP is used. TCP/IP is the only choice for the HTTP Plugin. Although you can use SSL to secure the connection, it just a little worse than MySQL C/S:

  HTTP MySQL C/S Security Rating
Transport TCP/IP TCP/IP, Unix Domain Socket, Windows pipe 0 : 0.5

The access methods: SQL and NoSQL

Todays MySQL speaks three protocols: HTTP, MySQL Client/Server Protocol and Memcache (InnoDB Memcache Plugin, MySQL Cluster support).

HTTP Client   Standard client   Memcache client
|   |   |
HTTP Protocol   MySQL C/S Protocol   Memcache Protocol
|   |   |

This is done to offer SQL and Not-Only-SQL access methods. The Not-Only-SQL access methods bypass the SQL processing layers in MySQL. A decade ago already, MySQL Connectors folks estimated that SQL parsing can take 50% of the total execution time of a simple query. Yet, it required innovative MySQL expert users and the NoSQL movement until the popular Memcache protocol creeped into MySQL and killed the 50% overhead. In addition to SQL commands, MySQL also accepts key-value style commands. The latter use lower-level APIs inside the server. Thus, they are faster. The Memcache key-value style APIs don’t know about SQL injection or the like – no SQL used…

The HTTP Plugin development version we show internally maps all HTTP requests to SQL. It has three APIs. REST-like CRUD and REST-like DOCUMENT perfectly qualify for taking the lower-level API route. Whether we will do that is undecided. If so, SQL injection or the like does not matter – no SQL used…

Currently, in the development version, we don’t use the lower-level APIs. That’s no secret, we released the source. In the source one will find the use of SQL and escaping. We could have, had we bothered, used prepared statements. The initial HTTP Plugin development version works exactly like trivial proxy written in a language like PHP, Python or the like. As much as such a proxy can be secured, the current code with its all SQL mapping could be secured.

I won’t put any rating entry on the cheat-sheet as this is an implementation detail of a development version.

The user authentication

Things are now down to one third of the HTTP Plugin – the SQL endpoint – versus a standard MySQL Client.

Both a standard MySQL client and a HTTP Plugin client execute SQL as a certain MySQL user. The actions of the client are restricted by the permissions granted. If you allow your client to run DELETE FROM mysql.user you may find yourself in an uncomfortable situation soon.

If SSL is used, it is valid to accept MySQL user credentials from the HTTP client and log the client in as the MySQL user given. This clear-text user credential exhange over SSL is also done with the MySQL Client/Server Protocol. It is implementation details of the various pluggable authentication methods MySQL offers that restrict HTTP clients to login as a MySQL user of their choice when SSL is used only. For example, some of the authentication methods require a handshake procedure.

  HTTP MySQL C/S Security Rating
MySQL user password security Clear-text over SSL Clear-text over SSL and other methods 0 : 0

Without SSL, the HTTP client defaults to a preconfigured user. We used HTTP Basic Authentication, which is a rather undesireable HTTP authentication method, as a simple and immediately to understand method of getting user credentials from a HTTP client. Of course, there are better one!

Other HTTP authentication methods are also more complicated and distract. Distract from the SSL explanation. Distract from the option that one could and should see the authentication process as two-staged. The first step is the login towards the HTTP Plugin, then a second step maps the HTTP user to a MySQL user. Wouldn’t it be nice if for non-SSL connections HTTP user ‘Ulf’ could be mapped to MySQL user ‘evil’ and HTTP user ‘Johannes’ could be mapped to MySQL ‘caretaker’? Here, the MySQL users password would not be taken from the HTTP world, it would be taken from some plugin config.

First stage (network)
HTTP user -> HTTP Authentication method (Basic Auth, OAuth, …)
Second stage (inside the Plugin)
HTTP user -> Mapping to MySQL user

No rating. I find it hard to compare with MySQL where you always have the password in clear-text at the client.

What people missed, …

The user authentication and mapping alone is still not enough. See slide 100! Slide 100 is what comes after the safe harbour statement. In other words this is pure speculation and idea spreading. No promise we will ever make this happen.

Here’s the illustration from the slide, slightly simplified (it’s 2am – time for shortcuts ;-)):

HTTP Client
Improved security, more freedom
HTTP Plugin
app/ v8 JavaScript sql/, crud/, docs/ – built-in

The MySQL permission system alone is too coarse to fully match the capabilities of any of todays self-baken HTTP-to-MySQL proxies. You can limit a MySQL user to be allowed to read from a single table only. But, the person can read all rows from the table. You could try to use a view but probably, you would just implement some filtering in your proxy. “Just implement some filtering” becomes a problem when the HTTP Plugin is your proxy. There’s no script language, you can’t do that. Unless, there was a script language built-in to the proxy…

  HTTP MySQL C/S Security Rating
Application based filtering Implementation detail not bound to protocol Implementation detail not bound to protocol 0 : 0.5 (see text)

All in all, a HTTP Plugin can get pretty close to todays solutions. It’s not there yet.

Happy hacking!

@Ulf_Wendel Follow me on Twitter

One Comment

  1. Ulf, how do you cannot encode binary data with unicode characters, can you expand on this a bit? I thought it would be impossible and you would have to encode hex or base64 or something like this.
    (I also fail to understand why are you using \uXXXX for German , if you have UTF8, but this does not seem to be illegal, just odd)

Leave a Reply

Required fields are marked *.