SQL Injection using System Variables in MySQL

For BSides Manchester 2015, the UK pen-testing company aptly named ‘Pentest Ltd’ held a SQL injection challenge where the injection point required structuring the payload in a specific manner with MySQL voodoo to keep the payload under 90 characters, and bypass a basic WAF.
I was fairly certain the lab could also be accomplished using MySQL variables, but was unable to get the job done. Low and behold, it totally was possible and it turned out I overcomplicated the solution which they revealed could be achieved with the following:

This was something I’d never looked at before, and it just didn’t cross my mind to store the query result and retrieve it using a variable with one hit using a UNION. I was trying to do this over two queries and therefore my variable would always be empty/null when i tried to retrieve it, as MySQL variables are scoped to a SESSION ( a single database connection ), and are emptied after the first query completes and the application closes its connection.
This lead me to going completely down the wrong rabbit hole trying to solve the challenge, but also into discovering something reasonably interesting: SQLi with System Variables.Ok so this is effectively a new technique of SQLi, specific to MySQL (i think). However, it’s also most often useless; since it requires one of two rather outlandish conditions to be met for an application to be exploitable via this method, both of which would usually mean that the application can be exploited much more trivially. These conditions are:

  • You need to be able to inject at the very begging of the query

OR (as was pointed out to me by SQLMap’s Miroslav Stampar)

  • The MySQL database needs to be configured to allow stacked queries

In the event you can do this though, this method should provide a few benefits over a more tradition SQLi technique:

  1. The ability to inject into small input-length areas
  2. The ability to execute arbitrarily long queries into these areas
  3. The ability to bypass a decent amount of WAFs or poor SQLi defence mechanisms (blacklists)
  4. The ability to add a level of stealth to an otherwise fairly obvious attack
  5. The ability to make forensic analysis of the attack potentially more complicated

Ok so if you read the MySQL SYSTEM reference documentation here, you’ll see that there are a lot of these SYSTEM variables which alter the configuration of the database. 40 of these variables hold string values and are either a GLOBAL (permanent) variable or can be set to either a GLOBAL or SESSION variable.
The online reference doesn’t say what the constraints are on each of these 40 SYSTEM/GLOBAL string variables; and I soon found some only accept specifically formatted string data, and that others are capped to short lengths… so I made a short application which just tried to set each one to a longer and longer length arbitrary string to find out which of these variables could be of use in storing arbitrary, and arbitrarily long, string data.
It turns out there is just one SYSTEM/GLOBAL variable out of the lot which is fit for this purpose and wont break MySQL if you fill it with crap => init_slave
My idea was not to store the result of a query within the init_slave variable and then select it, like in the BSides challenge, but to actually store a query there, prepare and execute it.

If you can inject the above queries into an application, you ultimately run the contents of init_slave.

Because of neither prerequisites were met for these types of queries to work in the challenge, I gave up on this line of thinking. Although, for fun, I revisited it afterwards to see what would have been possible if those prerequisites were met.
In the event user input is restricted via a basic WAF or similar, like in the challenge, the identified SQL words might render the injection point realistically exploitable. We can potentially get around this scenario with some creative concatenation:

All we need to get past the filter is “SET”, “GLOBAL”, “CONCAT”, “PREPARE” and “EXECUTE”. Thats a fairly reasonable ask.
In the event our input is also length restricted, again like the challenge, we can take this method to the extreme and concat a single character at a time.

The longest required query for this method when adding one character at a time is 46 characters; almost half of the BSides challenge. Although in reality your payload will need a bit of manipulating to fit the particular injection point.
The only issue with this technique is that it would require a lot more requests to be made to the application; which may or may not be an issue.
However, this could be  seen as a positive, since being able to trickle one, relatively dumb looking, payload per week to a vulnerable application could help the attack go unnoticed.
Ultimately, this is a rather obscure method of SQLi, but it might be the only available method in some extreme edge-cases. So it might be worth adding it to the memory banks for a rainy day.
A ‘FIX’?
At first I wasn’t convinced this was a bug which needed fixing, and that it might just be abuse of normal operations. The more I think about it though, the more ridiculous it seems to me that SYSTEM variables can be used in a prepared statement in the first place. I’ve found no legitimate use-case for this whatsoever.
Since the init_slave value cannot be copied into a SESSION variable and then executed over two queries (the variable would empty), we only have to worry about not letting init_slave (or any other ‘vulnerable’ SYSTEM variables) from being used as part of a prepared statement. So a fix could easily be created by prohibiting that from happening.
Edit: I’ve submitted a MySQL bug report, with the above as the recommended behaviour: http://bugs.mysql.com/bug.php?id=81986.  Lets see if it gets laughed at.
Edit2: As expected, the powers that be at MySQL/Oracle refused to acknowledge this as bug in MySQL, let alone one with security implications (because executing system configuration strings as a query is a solid feature, right?). Full transcript of communication is here: 81986. I pretty much did a mic-drop at the end, there could be more correspondence from Oracle but I won’t be bothering to look. Its probably the only time I’ll ever submit anything directly to them, the process was slow, painful and the engineer assigned to my case was the human embodiment of why we have vulnerable software.
-hiburn8