Cybersecurity Insights

Zacharias Pigadas

Introducing: XOR-Based SQL Injection

25/10/17 10:27

Having such an exposure to application related testing means we have seen our share of vulnerabilities. These range across different categories, attempts of mitigation, good practices, bad practices, the full monty. Every once in a while, a vulnerability appears whose exploitation makes you scratch your head, scream at the computer screen, or just walk away in the hopes that the solution will present itself in the next morning.

This was the case a few months ago with a SQL injection vulnerability in the login screen of an application. Our initial excitement turned to despair when we identified we were only able to inject up to 50 characters and the more recent case, which we will discuss later in the post.

Exploitability is a huge factor in step 2 of an application assessment as it can put vulnerabilities in, but, more importantly - take out of immediate remediation. Extra diligence should be put in to trying to exploit the vulnerabilities that a scanner or a manual penetration test identified.

Identifying SQL injection via a properly configured scanner can be an easy matter as it is a deterministic problem; identifying exploitability though is a very different cup of tea. In fact, this was discussed in a recent blog post - Getting the most out of your web application penetration test.

So now that the scene is set, let’s move to the vulnerability at hand. To cut a long story short, this is also SQL injection related, we will use: as the vulnerable URL and parameter for the remainder of that post to protect the innocent.

What this script (getprotocolstring.asp) did was retrieve a single field that corresponded to a pipe(|) delimited protocol definition, break it up, assign each field to the respective data type and return it in a page to the user. Few initial observations to set the background are presented below:

  1. The protocol parameter was expecting a string - this was identified by looking at the valid values this parameter was getting through the application (e.g. ?protocol=XXX).
  2. Backend database was Microsoft SQL Server - this was identified by issuing two requests; (two single quotes) e.g. protocol=XXX’ and ?protocol=XXX’’.
  3. This was a blind SQL injection – it was identified as the application was responding with a custom error page when invalid SQL statements were issued.
  4. We were able to break out of the original query and stack requests of our own by issuing ‘');select @@version;--‘.
  5. We were able to get two different pages to use in True/False blind SQL injection, protocol=XYZ returned the protocol definition for protocol XYZ while protocol=ABC did not return anything back as such a protocol did not exist in the database.

All of the above led us to believe the actual query being posted was something like:

table 1-1.jpg

We initially tried exploiting the query one byte at a time and that was when we hit the wall. We identified that the database, not the application, was issuing a database error – and hence a custom error page back. When we were using some of the string based Microsoft SQL Server built-in functions such as SUBSTRING, LEFT, RIGHT, STUFF, PATINDEX, MID and CHARINDEX. These are all the functions that are used in order for an attacker to retrieve database content back one byte at a time.

We knew it was the database layer that was denying because we were getting the error page back ONLY when the call to the function was being evaluated at the database layer and not any time it was appearing in the contents of the protocol parameter. This was observed by issuing the two following requests:

  1.');select SUBSTRING(@@version,1,1);-- : Error page was returned by the web application
  2.; select SUBSTRING(@@version,1,1);-- : No error page back but an empty result set

The difference between the two is that in the second one, a single quote, is not used to break out of the initial query. All our input is being evaluated as one huge string, which results in the following being executed and an empty result set.

table 2-1.jpg

Also, we were getting the same results when any of the SUBSTRING, LEFT, RIGHT, STUFF, PATINDEX, MID and CHARINDEX functions were called. This being the case, we set off to bang our collective heads to the wall, trying to come back with a way to exploit this and get data out of the database.

Eventually we came up with two ways to achieve this, the “kindofworkingbutcanonlyselectuptosixteencharacters” way and the “easybutneedingsomespecialprivilegestocreatetables” one. The user that application was using to connect to the database did not have privileges to create a table on the database so we settled with the first one, “XOR Based Blind SQL Injection, AKA “kindofworkingbutcanonlyselectuptosixteencharacters”.



As you can see by the name, there is a limitation associated with that way. It is related to being able to select only 16 characters off any given string, 8 from the beginning and 8 from the end. In a nutshell, this was achieved by:

  1. Using the ASCII() function to get the ASCII value of the leftmost character of a given string. Selection of the leftmost character is implied in this case so select ASCII(@@version) will get you 77, which is the ASCII value for the M character. Functionality wise this was like having access to the LEFT() function.
  2. Using XOR to cancel out characters from the left part of the string and move a character to the right. Multiple steps were chained to achieve that:
  3. We used the CAST() function to convert the value we wanted to select to a VARCHAR data type;
    • We used the CAST() function again to convert the value from point (a) above to the VARBINARY data type 
    • We XORed the value produced on point 2 above with an appropriate bigint This number was calculated from previous identified characters from the left of the string.

Before going on to the examples let’s provide some background knowledge on the methods and datatypes above.

The excerpt below is taken from and the explanation of Arguments is the juice of it.


Consequently, you can use the XOR to perform a bitwise OR between a varbinary and an integer related datatype. Bigint is the biggest integer based data type on Microsoft SQL Server taking up 8 bytes and that is where the limitation lies of only being able to select 8 characters from the start of the string. Using the REVERSE() function allows use of the same procedure to select 8 characters from the end of the string, which brings us to 16 characters in total. If the value we wanted to select was less than or equal to 16 characters, we would be able to select all of it comfortably. If it was more than that, then the middle part of the string would be missed.

Now let's move to examples that will hopefully put all the pieces above into place. We will use the db_name() register to retrieve the name of the database we are connected to. We will use the protocol strings above to illustrate when we will be getting the True and where we would be getting the false page.

table 3-1.jpg

If the ascii value of the first character of the db_name() register is 82, then the query above will return XYZ. If this is used as a subquery on the initial protocol selection query, it will return the protocol definition of XYZ. In case it is not, it will get back ABC that does not have a protocol definition; hence, you will get an empty string/page. For example, here we are using a demo MS SQL Server 2012 and the ReportServer$SQLEXPRESS database; the ascii value of 'R' is indeed 82 (0x52) and we have identified the first character. If it wasn't identified, then by “walking” that number (82) we would be able to eventually identify it.

Moving on the next character:

table 4-1.jpg

Let’s break down the actual important part of the above query as everything around it is the same as with the first character:

table 5-1.jpg

Progressing to the third leftmost character, we will transform the query to

table 6-1.jpg

As such, by walking and identifying the characters in each position and then moving to the one on the right we are able to retrieve up to 8 characters of the requested value. Replacing db_name(), in the query above, with REVERSE(db_name()) will turn the string around and allow us to retrieve characters from the end of the string.



This way involves creating a temporary table you can input your value on and select it from there. One starts by creating a table with a single field of varchar data type and length equal to that of the value wished to be retrieved from the database. The True and False condition from above can help in that direction.

table 7-1.jpg

Using the query above and walking the <number> placeholder you can identify the length of the db_name() register, in this case it is 23. The leftmost character can be retrieved as in the previous method.

table 8-1.jpg

Table Number range Table: Getting the leftmost character of a value

To move to the second leftmost character, you create a temporary table with a single field that has a length equal to the actual length of the string minus 1. This table can be populated with the value we want to retrieve. The idea is to use REVERSE() to turn the string around and INSERT INTO that table directly. Microsoft SQL Server will auto truncate the value inserted on that field to the length of the field. The table below shows the sequence of commands in order to retrieve the first couple of characters of the db_name() register:

table 9-1.jpg

You can see that this is much cleaner than the previous one, unfortunately, the user we had on that engagement did not have access rights to  create a table. 

As you can see, some vulnerabilities take a bit of work to be exploited properly. If you are in any doubt, consider a manual web application penetration test. This will ensure you have no directly exploitable vulnerabilities, SQL injection or otherwise. That way no vulnerability can be miscategorised as not-exploitable, hence, left out of the “Address Immediately” list and lead to a compromise from a more tech savvy adversary.


View Penetration Testing Services

Need Help?



Byham, R. Cyuer, C. 2017. ^ (Bitwise Exclusive OR) (Transact-SQL). Retrieved from:





David Kirkpatrick
Penetration Testing: The Quest For Fully UnDetectable Malware

Malware continues to be one of the main attack vectors used by criminals to compromise user and ...

Read More