I was looking at a particularly bad example of a website littered with tens of thousands of spam comments.
There didn’t appear to be a real comment on the entire site, the oldest spam dated back several years and the most recent was barely a few days old. It seemed clear that the site owner either didn’t know or didn’t care that this was happening, so them clearing up the mess any time soon seemed unlikely.
I wondered if it would be possible for me to remove the comments and links myself. Clearly I could try to find some contact details and send the site owner a polite email about it, but that’s not a very interesting thing to consider. As a thought experiment, and ignoring the obvious legal and ethical issues – I found it an interesting problem to consider over the next few days.
The most straightforward way to clean up the spam myself would be via direct access to the database that stores them. With that not available, I’d need to find a vulnerability in the site that I could exploit to run my own arbitrary database queries.
Identifying a target
My notional target site was a photo gallery, and it was easy to identify which software was behind it through the “Powered by Zenphoto” link at the bottom of the page, and the presence of this in the HTML source:
Zenphoto 1.2.5 dates back to June 2009, so I figured there’d be quite a good chance of there being some known security issues with it. I wasn’t wrong.
Identifying a vulnerability
With the target software identified, I downloaded and installed a copy of the same version of Zenphoto on a local computer. I could then attack it at my leisure and to no-one else’s detriment.
Helpfully for me, Zenphoto historically doesn’t appear to have been a very secure bit of software. There are at least fifteen published Zenphoto vulnerabilities; some of them claimed that they resulted in SQL database query injection – exactly what I wanted to achieve.
Of the published vulnerabilities, CVE-2009-4564 looked particularly promising. It applied to the version of Zenphoto I was examining, and detailed a ‘blind SQL injection’ exploit that would extract the administrator username and password hash from the target site. It also included some other information that I’d find useful later on.
Unfortunately though, what it didn’t do was work. This particular exploit requires that the ‘Zenpage’ plugin be enabled on the target site. I knew that this wasn’t the case because when I enabled it on my own installation, the version information included in the HTML code of every page (shown above) then included it in the list of plugins.
That particular vulnerability was out, and nothing else from the list immediately jumped out at me as applying to my target software version. Rather than checking each published vulnerability in turn (boring) I thought that, given the poor security history displayed so far, I’d have a good chance of coming up with a working exploit myself.
One of the public vulnerabilities on the list (for a later version of Zenphoto) steered me in the direction of album title handling. As luck would have it, I found a straightforward (and unpublished) vulnerability in 1.2.5 after a few minutes of looking.
I was experimenting with URLs like the following – which just displays the image raw.png from the album test-album hosted on my local machine:
I noticed that simply adding a " symbol to the end of the URL resulted in this:
From my point of view, an SQL syntax error was gold dust! It even helpfully explains what went wrong: the query sent to the database ends:
Note the repeated double quotation marks at the end. Exactly what I’d provided in the URL for the album name parameter was sent to the database without any alteration. From the script’s point of view, that’s a very bad idea, because there’s nothing to stop a determined attacker (me) from completely altering what the SQL query actually does.
As an example, the following database query:
Can be executed by simply pointing a web browser to this URL (split over two lines for readability):
&a=test-album" AND UNION SELECT id, password FROM zp_administrators WHERE "1
The beginning and end of the SQL query is hard-coded in the target software, but because it naïvely inserts the given “album name” into the query verbatim, the function it performs can be modified inserting another query into the middle of it. In this case, the resulting query:
- Closes the quotation mark that was opened just before test-album
- Adds an impossible condition to the first SELECT statement, which was intended to retrieve the id number and theme name for a specific album. It will therefore return zero rows from the database.
- Combines the (empty) results of the first query with those of a new one, which retrieves the id number and password of a row from the zp_administrators table.
- Ends the query neatly by accounting for the hard-coded final quotation mark that will always appear in in the final SQL query sent by Zenphoto.
The underlying application will receive from the database what it thinks is the id number of an album and the name of a theme, but is actually the administrator password from a different table altogether. This is a basic SQL injection exploit, and at this point I expected Zenphoto to send back an HTML page to me containing those bits of information – as long as it mentioned what it thought was the ‘album theme’, I’d now have the administrator password.
Unfortunately, it didn’t.
Designing an exploit
As it turns out, I’d found a blind SQL injection vulnerability, so called because while arbitrary queries can be executed, their output isn’t returned. In this particular case, as long as the query doesn’t return an error, Zenphoto sends back (via an HTTP 302 redirect) a page saying the requested image can’t be found in the given album. Which isn’t especially surprisingly considering the album name that was provided!
SELECT statements are by their nature read-only and can’t be used to modify the contents of the database. As this part of the SQL statement is hard-coded in the target software, directly removing the spam comments through a database query wouldn’t be possible. Instead, I’d have to extract the administrator password from the database and then use that to log in manually.
Perhaps surprisingly, there are a multitude of ways to accomplish this, even without being able to receive any output from a query in a traditional manner. The three main ways that I could think of were:
- Causing an SQL error to occur that – as part of the error message – contains the information I wanted to extract. As seen in the first screenshot of this post, Zenphoto sends back a neatly formatted message in case of error.
- Causing an SQL error if a particular condition is true, and no error if the condition is false. Guessing each character of the password separately, and using the resulting error (or lack of) to determine if the guess for that character was correct or not.
- Rather than causing an error, using a query that takes a long time to complete if the condition is true, and very quickly if the condition was false. Otherwise the same guessing method as above.
I went with the third option for a few different reasons: I was curious as to how well it would work, I’d never attempted it before myself, and it was how the exploit detailed in CVE-2009-4564 purported to function.
So I wrote a short Python script to help me test particular conditions automatically. Each condition is tested by constructing and sending a query that takes a long time to return if the condition is true. If the condition is false, the response should come back roughly as fast as every other page on the site.
Here’s an example of such a query:
That query tests the condition “is the length of the username in the zp_administrators table less than 5 characters long?”. Of course I know the name of the table and field from my own installation, and for the moment am assuming there is only one user defined there.
If the condition is true, the IF() function will cause the BENCHMARK(1000000, SHA1(”)) part of the query to be executed. If the condition is false, it will merely return zero. The “magic” part is here is using the BENCHMARK() function to repeatedly perform an action – in this case calling the SHA1() function with an empty parameter – five million times.
Exactly what SHA1(”) does is irrelevant here. It happens to be a function with a short name (for neatness) that takes a relatively long time to execute. On my test system, five million calls to SHA1() take around 2.2 seconds. Not a huge amount of time, but more than enough to identify a response indicating that the tested condition was true – it would take over two seconds longer to come back!
Establishing a baseline for comparison is as straightforward as testing known true and false values and timing the responses that come back.
Using the exploit
I knew from looking at my own copy of Zenphoto that it stored the password for a user as an MD5 hash, more specifically as a string of 32 hexadecimal digits. Furthermore, I knew from the notes that came with CVE-2009-4564 that merely providing this MD5 hash in an HTTP cookie would be enough to authenticate myself as that user – no cracking of the password required! Yet more evidence of poor security.
I could find out all 32 hexadecimal digits that comprise the MD5 hash by testing each of the 16 possible values for each digit in turn. However, that could take 15 (assuming that the 16th guess is unnecessary due to excluding all of the other possibilities) guesses for each value. Over the whole password hash digits that would require 480 individual HTTP requests. Not very neat.
Thinking I was being quite clever, I imagined using some of the built-in MySQL functions to parse the MD5 hash as a 128-bit number. Then with one query – is the resulting value less than half of the maximum value it could possibly be? – I could halve the possible search space. Taking that result and performing the same query on it would again halve the possible answers. Rather neatly, by halving a 128-bit number range 128 times, there’s only one possible value left. That would take 128 HTTP requests, every time. Much better than the previous worst-case scenario.
Here’s a much smaller example. Supposing a value is known to be between 0 and 7. In this specific example the answer is 5.
|0||1||2||3||4||5||6||7||value less than 4? no|
|0||1||2||3||4||5||6||7||value less than 6? yes|
|0||1||2||3||4||5||6||7||value less than 5? no|
Initially, from the algorithm’s point of view, the answer could be any of eight different possibilities.
- By testing the condition “is the value less than 4”, which half of the possible values the answer lies in can be ascertained. The answer isn’t less than 4, so it can only be 4, 5, 6 or 7.
- Knowing that, the next condition tested is whether the answer is less than 6. It is, 6 and 7 can be excluded as possibilities. There are now only two possible answers.
- The next and final test reveals that the answer is not less than 5, leaving 5 itself as the only possible answer.
I was briefly quite confident that I could use this technique to narrow down the MD5 hash value quite quickly. Unfortunately and in retrospect not surprisingly, MySQL doesn’t support numbers as large as 128 bits. No problem, I could split the 32 digit number string into two 16 digit numbers and use the bisecting algorithm on each. Then I could just concatenate the two numbers to give me the original MD5 hash value.
Each half of the hash can be bisected down to a single possibility with 64 queries. Knowing that, it’s easy to see that bisecting each of the 32 digits – each a 4-bit value – at a time requires exactly the same number of steps as treating the whole hash as a huge number. That’s a practical way around the MySQL number size limit, and gives a certain amount of redundancy to the process – a timing glitch will only affect the digit that’s currently being determined.
I updated my hacked together script to use this method and after a minor hiccup – having to rewrite my query slightly to avoid using the ‘<' character that Zenphoto stripped out - I pointed it at my local Zenphoto installation and a few seconds later I was staring at the hash of my own password.
As it happens, discovering each digit in turn also gives the process quite a nice movie-style password hacking effect. Here's what it looks like in action, running against my local server (of course) and with a lower number of iterations to speed things up a bit:
Finally I had the answer to my original question. With a valid (stolen) password hash set in an HTTP cookie, I could simply access the target’s Zenphoto administration interface and delete the spammy comments on the site myself. I could also attempt to crack the actual password that was used if I so wished.
Along the way I’d demonstrated (if only to myself) that significant information can be exfiltrated from a database using only boolean tests, and that those tests can be implemented by measuring differences in response times.
Hopefully I’ve also demonstrated why it’s important to keep software up to date – your website security is only as strong as your weakest link – and why it’s important for software to properly sanitise user-supplied data in SQL queries!