Wednesday, 9 November 2016

Adventures in SQLi

Adventures in SQLi

It has been a while since I've posted any real content on this blog, mainly just due to being so busy with project work and not being able to get my hands dirty. However a friend reached out for some help with a client who suspected they had SQLi vulnerabilities in their webapp, I confirmed quickly that yes there's indeed injection vectors, however I always try and provide proof of concept queries that actually select something meaningful.

This was one of the harder SQLi challenges I've come across which required a great deal of thought and some unusual techniques which I've never seen documented before, this was done with the help of my brother who has a very good knowledge of SQL.

If you don't know much about MySQL and SQLi using UNION queries then first read my guide here. This will familiarize you with the basics of UNION based SQLi and give you a point of comparison to see what I did differently here.

Testing for vulnerability

Our attack vector is a search form on the front page of an e-store, for obvious reasons I've masked the domain name. Let's put in hello as our search term, we find that our URL parameter on the search results page is searchStr.


Let's put an apostrophe in and try and break the SQL. hello'


Bingo, an error page 

1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[[:>:]]' OR I.description RLIKE '[[:<:]]hello'[[:>:]]' OR I.productCode RLIKE '[' at line 1

Looks like we're dealing with a prebuilt shopping cart CMS called CubeCart. The entire query is dumped to screen which is handy, but I'll just use the first part

SELECT DISTINCT(I.productId), I.*, AS SearchScore FROM CubeCart_inventory AS I, CubeCart_category AS C WHERE ( RLIKE '[[:<:]]hello'[[:>:]]' OR 

So it looks like they are using a RLIKE which is RegExLIKE, and they're padding the search team with RegEx symbols which I've never seen used before, I'm taking a stab in the dark that this is essentially the equivalent of a LIKE '%hello%'

Let's see if we can escape out of the string and comment out the rest of the query and close this off to not throw any errors, this means closing off our open parenthesis and adding a comment, our input becomes


Notice that comments caused by double hyphen require a space between them and other SQL, also note that + in the URL is converted to the space character, you could equally use %20. What I expected was for this to be valid SQL and query abnormally but fundamentally still parse and not error, but what we get instead is this error.

SELECT DISTINCT(I.productId), I.*, AS SearchScore FROM CubeCart_inventory AS I, CubeCart_category AS C WHERE ( RLIKE '[[:<:]]hello')[[:>:]].*[[:<:]]--[[:>:]]' OR 

OK what is going on here? It's immediately obvious that they're conditionally building this query, and when they detect a search string that contains a space that it's treated as multiple search terms rather than a single string.

This means that more of these RegEx terms are inserted between the "words" and this fundamentally makes writing any kind of SQL that contains spaces, impossible. The first thing that came to mind is using a non breaking space this will parse with SQL quite nicely as a regular space however might not be treated as a break in the search string by PHP, and indeed this is the case. The URLencoded version of this character is %A0, so now we have to substitute every space with this character in our injection.


This parses correctly without error and neatly closes off the query and renders the search results page as expected.

So we do the normal thing while probing for UNION based SQL attacks, we want to UNION our own data set onto the bottom of a legit result set, this means knowing the right number of columns in the result set otherwise our UNION will fail with an error stating that we have an unequal number of columns. The typical trick is to tease out the number of columns with an ORDER BY, you can give this operator a number and if the column exists then it will order the results, if it doesn't exist you'll get an error saying so.

Let's try ORDER BY 10

No error, 20?
No error, 40?
No error, 50?

Finally an error, dialing back the order by value I teased out the final value as 41

The next step is to UNION that query with my own, just a comma separated string of values, typically you'd name these something unique so that when the final page renders out you can see which of these value is returned back to the page and is visible to be used for data exfiltration. Let's just use consecutive numbers.


And we enormous error.

SELECT DISTINCT(I.productId), I.*, AS SearchScore FROM CubeCart_inventory AS I, CubeCart_category AS C WHERE ( RLIKE '[[:<:]]hello')�union�select�1[[:>:]].*[[:<:]]2[[:>:]].*[[:<:]]3[[:>:]].*[[:<:]]4[[:>:]].*[[:<:]]5[[:>:]].*[[:<:]]6[[:>:]].

I've cut this short because the error is so large but you can see the pattern. Given what we know about this search form it's easy to guess that it's also treating commas as breaking up words in the same way that space does. So all we have to do is write SQL with no commas in it right?


OK so the first obvious thing to try is various different encodings of comma and different kinds of commas from the extended character sets, there's all sorts of different ones you can find, some are documented here.

Sadly none of these worked, MySQL is pretty strict about this, and all my attempts to find a character that worked with MySQL and parsed properly but didn't also split the search term, basically failed.

There's only one thing for it, we need to create a data row in MySQL without actually using any commas at all, after a while of faffing about trying several different methods we found something that worked. You can build a row of data simply creating temporary tables each with one column and then left joined. The principle works like this:

LEFT JOIN (SELECT 'b')t2 ON t1.a=t2.b OR 1=1
LEFT JOIN (SELECT 'c')t3 ON t1.a=t3.c OR 1=1
LEFT JOIN (SELECT 'd')t4 ON t1.a=t4.d OR 1=1
LEFT JOIN (SELECT 'e')t5 ON t1.a=t5.e OR 1=1

Jesus, OK we need 41x of these things, and we need to replace the spaces with non breaking spaces, what's the total length of this url? Well it exceeds 2500 characters. What's the standard for URL limits, well that's revised to 8000 octets but a lot of web servers won't deal with above 2048 characters, so fingers crossed...


And there you have it, the query executes just fine, returns the results page and hey presto field 10 is visible on the page.

There was a few other challenges using this, you can no longer use the common steps you'd normally see recommended with UNION based SQLi, so a lot of improvisation is required, to read something like the information_schema.tables database to see what tables were available was required to be entirely nested inside the 10th column, here's the relevent snippet.


This becomes:


This was throwing illegal mix of collations errors and so some of the tricks I've written about here helped, for some reason unhex(hex()) didn't work but uncompress(compress()) did, which gives us:


In the end I manged to read the database tables out, the columns out. This CubeCart CMS has an admin table with credentials inside, so this turned out to be extremely high risk. I think this post just goes to show how powerful SQL is and what you can do with when you're working inside restricted conditions. It also goes to show that if you want to be good at injecting sites you can't just follow pre-written guides, you need strong SQL knowledge in order to deal with anomalies like this.

Happy Injecting!



It's been a while since I attended DefCon24, my very first Con as it's expensive to fly from the UK, I really enjoyed myself this year and am determined to get back next year. This is just an update post to say that I've secured attendance next year as well, so I will be at DefCon25 with friends.

Part of what makes the experience really great is meeting other like minded people, so if you like this blog and want to get a beer together next year then hit me up on Skype: FrostyHacks