Getting Started with MySQL Proxy
Pages: 1, 2, 3, 4

Query Injection

Next, let's exploit one of the ad hoc features of MySQL Proxy: query injection. It's a unique ability of this tool. When required, it can create a queue of queries, and send them to the server, after assigning to each query an ID code.

Figure 2. Query injection

In the image, the server receives three queries, and of course it sends back three result sets. When an injection has taken place, the result set gets processed by another function, read_query_result, where you can deal with the result sets according to their ID. In the example, for ID 2 and 3 you just get something from SHOW STATUS and by comparing their values you can measure the impact of the main query on the server. Since you use the SHOW STATUS values only for internal calculation, you don't send that result set to the client (which is just as good, since the client is not expecting it), but you discard it.

Figure 3. Processing the injected queries

The result set of the query sent by the client is duly returned. It's transparent for the client, but in between you managed to collect statistical results, which are displayed on the proxy console.

For a full example, see the query injection tutorial in the Forge.

Macros

Macros are just another way of using the query rewriting facility. It's one of the most striking usages of the Proxy. You can rewrite the SQL language, or make it closer to your tastes. For instance, many people who use the MySQL command-line client type cd and ls instead of use and show tables. With MySQL Proxy, they can use cd and ls and get the expected result. This juicy example of macro creation and usage is available in an early blog post. Rather than repeating all of it here, I invite you to look at your first macros with MySQL Proxy.

Creating Result Sets: Shell Commands from MySQL

The Proxy receives a request from a client, and then it has to give back a result set. Most of the time, this is straightforward. Passing the query to the server, getting the result set, passing the result set to the client. But what happens if we need to return something that the server is not able to provide? Then we need to build a result set, which is composed of a set of column names, and a bi-dimensional array with the data.

Dataset Creation Basics

For example, if I wanted to return a warning about a deprecated feature, I could create a result set like this:

 proxy.response.resultset = {
     fields = {
         { 
            type = proxy.MYSQL_TYPE_STRING, 
            name = "deprecated feature", 
         },
         { 
            type = proxy.MYSQL_TYPE_STRING, 
            name = "suggested replacement", 
         },
     },
     rows = {
          { 
             "SHOW DATABASES", 
             "SHOW SCHEMAS" 
          }
     }
 }
 -- and then, send it to the client
 return proxy.PROXY_SEND_RESULT

The above structure, when received by the client, would be shown as:

+---------------------+-----------------------+
| deprecated feature  | suggested replacement |
+---------------------+-----------------------+
| SHOW DATABASES      | SHOW SCHEMAS          |
+---------------------+-----------------------+

That's to say that you can fabricate every result set that meets your needs. For more details, see Jan Kneschke's example.

Shell Commands from MySQL Client

And now for something completely different, let's see how to use our freshly acquired knowledge to execute shell commands through the Proxy. We said already that the Proxy's behavior can be altered with Lua scripts. And Lua is a complete language, meaning that you can do almost everything with it, including executing shell commands. Combine this knowledge with the ability to create data sets, and we come up with the idea of asking for shell commands from a MySQL client and having the Proxy return their results as if they were normal database records.

Figure 4. Running shell commands through the Proxy

Pages: 1, 2, 3, 4

Next Page