The Art of Software Security Assessment

mark December 27th, 2006

SQL injection vulnerabilities have plagued applications for many years. When a dynamic SQL query is constructed with any sort of user-controllable input, there exists the potential for an attacker to perform arbitrary SQL queries, which might lead to sensitive information disclosure or modification. Developers wanting to protect their applications from these kinds of attacks have typically instituted filtering of user data for SQL metacharacters, moved their database query code into stored procedures, or replaced their dynamic SQL statements with prepared SQL. Prepared SQL statements are precompiled SQL queries that accept user-defined parameters without allowing for SQL injection attacks to occur. Since the SQL query is compiled beforehand, the user’s data is never parsed by the SQL parser, and thus isn’t capable of triggering metacharacter attacks.

In Chapter 8, "Strings and Metacharacters," we discuss a (previously known) technique that is a slight variation of SQL injection in scenarios where prepared statements aren’t in use. The technique I’m referring to is SQL truncation, which essentially revolves around subverting an application’s query logic by filling up a fixed-size buffer with parameter data, thereby truncating some of the query parameters. The example we use is:

int search_orders(char *post_detail, char *sess_account)
{
    char buf[1024];
    int rc;

    post_detail = escape_sql(post_detail);
    sess_account = escape_sql(sess_account);

    snprintf(buf, sizeof(buf), "SELECT * FROM orders WHERE detail LIKE \"'%%%s%%'\" AND "
             "account = '%s'", post_detail, sess_account);

    rc = perform_query(buf);

    if(rc > 0)
        return 1;
    return 0;
}

Essentially, the problem here is that if an excessively long post_detail parameter is supplied, the buf variable can be filled up, with the AND clause being silently truncated as a result. An attacker might be able to exploit this truncation problem and see other user’s orders.

As it turns out, Bala Neerumalla from Microsoft has taken this idea a step further in a cool article that appeared in MSDN magazine (available here) and also in a speech he presented at BlackHat (available here). In both of these presentations, he talks about SQL injection and truncation in the context of T-SQL stored procedures in a MSSQL Server environment. The general gist of his work is that stored procedures that build dynamic SQL statements can be vulnerable to injection when building queries or escaping input into fixed length character buffers (VARCHAR and NVARCHAR datatypes in T-SQL speak). An example of a vulnerable stored procedure might look like this:

CREATE PROCEDURE vulnProc
(
    @parameter1    NVARCHAR(100),
    @parameter2    NVARCHAR(100)
)
AS
BEGIN
    DECLARE @stmt NVARCHAR(128)

    SET @stmt = 'SELECT * FROM table WHERE param1=' + QUOTENAME(@parameter1, '''') 
            + ' AND param2=' + QUOTENAME(@parameter2, '''') + '
    EXEC(@stmt)

    ... more code ...
END
GO

Neerumalla points out that code like that listed above is vulnerable to SQL truncation when constructing the @stmt query string, in much the same way as the C code we presented in chapter 8. Since the @stmt variable only has room for 128 characters, a significantly long @parameter1 will cause the second half of the query to be silently truncated, the consequences of which can vary depending on the nature of the query. He also presents variations on this basic attack premise that allow for arbitrary SQL injection in certain cases. In each case, he shows how seemingly safe escaped dynamic SQL procedures are vulnerable to SQL injection due to chopping off parts of queries (like in the previous example), or chopping off parts of escaped strings — particularly dangerous if the escaped string is chopped partway through a character escape.

One other interesting facet of stored procedures that build dynamic SQL statements that is not explicitly mentioned in Neerumalla’s article is that stored procedures that take fixed-length arguments will silently truncate their arguments if the arguments supplied are longer than the length specified in the procedure declaration. This is interesting in scenarios where arguments have been pre-escaped by an application that calls the stored procedure, rather than strings being escaped in the stored procedure itself. Consider the following procedure:

CREATE PROCEDURE vulnProc
(
    @parameter1    NVARCHAR(100),
    @parameter2    NVARCHAR(100)
)
AS
BEGIN
    DECLARE @stmt NVARCHAR(1024)

    SET @stmt = 'SELECT * FROM table WHERE param1=''' + @parameter1 
            + ''' AND param2=''' + @parameter2  + '''

    EXEC(@stmt)

    ... more code ...
END
GO

This slightly modified example doesn’t do any sort of purification on its input arguments, and so is seemingly vulnerable to SQL injection. However, let’s assume that a C application that utilizes this procedure escapes input before passing it along to this stored procedure in the following way:

#define PROCSTRING "EXEC vulnProc "

int CallStoredProcedure(CString *param1, CString *param2)
{
    CString *sqlQuery;
    int rc; 

    sqlQuery = new CString(PROCSTRING);
    sqlQuery += "'";
    sqlQuery += SQL_EscapeQuotes(param1);
    sqlQuery += "', '";
    sqlQuery += SQL_EscapeQuotes(param2);
    sqlQuery += "'";

    rc = SQL_ExecuteQuery(sqlQuery);

    ... more code ...
}

So, the calling application is intended to protect the stored procedure from potential SQL injection attacks. However, you can see that the string that is built to call the stored procedure is a dynamic length string, and there are no restrictions upon the length of param1 and param2. So, if they are longer than 100 characters, they will be silently truncated. So, if param1 was 99 ordinary characters long and the 100th character was a quote, the resulting string would look like this:

AAA ... (99 characters) ... AAA''

But, due to truncation, VulnProc would see the string like this:

AAA ... (99 characters) ... AAA'

Uh oh! We now have a string with the dangerous quote character that we were trying to strip out. So, we have a potential vulnerability here.

In summary, moving SQL query functionality out of the application code and into the database layer makes sense in a lot of cases, but it doesn’t guarantee that SQL injection is no longer on an issue. For a more detailed coverage of the subject of SQL injection/truncation techniques, make sure you check out the MSDN article we mentioned earlier, and our Stored Procedure SQL Injection Cheat Sheet.

• Windows , Auditing , SQL , Web