The Art of Software Security Assessment

jm December 26th, 2006

One thing we’ve been finding increasingly over the last year or so is a lot more instances of SQL injection within stored procedures. In order to set the stage for Mark’s SQL Truncation post, we’re presenting a brief cheat-sheet on how to audit for these issues.

Oracle PL/SQL

References
1. Stephen Kost’s An Introduction to SQL Injection Attacks for Oracle Developers
2. Wiley’s The Database Hacker’s Handbook
3. Pete Finnigan’s SQL Injection and Oracle, Part One
4. David LitchField’s Oracle PL/SQL Injection

Auditing
You are looking for three things: EXECUTE IMMEDIATE, the use of DBMS_SQL package, and dynamic cursors. The best way to find these is to do a case insensitive substring search for the following: EXEC, DBMS_SQL, and OPEN. Use the above references to help you further evaluate the issues you find, but basically you’re looking for a SQL query string being pieced together manually, incorporating user-malleable input.

Examples
These are taken from Stephen Kost’s An Introduction to SQL Injection Attacks for Oracle Developers, which is the best paper about these issues we’ve found.

Example 1 - EXECUTE IMMEDIATE

CREATE OR REPLACE PROCEDURE demo(name IN VARCHAR2) AS
    sql VARCHAR2;
    code VARCHAR2;
BEGIN
...
    sql := 'SELECT postal-code FROM states WHERE state-name = ''' || name || '''';
    EXECUTE IMMEDIATE sql INTO code;
    IF code = 'IL' THEN ...
...
END; 

Example 2 - DBMS_SQL

CREATE OR REPLACE PROCEDURE demo(name IN VARCHAR2) AS 
    cursor_name INTEGER; 
    rows_processed INTEGER;
    sql VARCHAR2(150);
    code VARCHAR2(2);
BEGIN
...
    sql := 'SELECT postal-code FROM states WHERE state-name = ''' || name || '''';
    cursor_name := dbms_sql.open_cursor;
    DBMS_SQL.PARSE(cursor_name, sql, DBMS_SQL.NATIVE);
    DBMS_SQL.DEFINE_COLUMN(cursor_name, 1, code, 10);
    rows_processed := DBMS_SQL.EXECUTE(cursor_name);
    DBMS_SQL.CLOSE_CURSOR(cursor_name);
...
END;

Example 3 - Dynamic Cursors

CREATE OR REPLACE PROCEDURE demo(name IN VARCHAR2) AS 
    sql VARCHAR2;
    ...
BEGIN
...
    sql := 'SELECT * FROM states WHERE state-name = ''' || name || '''';
    OPEN cursor_states FOR sql;
    LOOP
        FETCH cursor_states INTO rec_state
        EXIT WHEN cursor_states%NOTFOUND;
        ...
    END LOOP;
    CLOSE cursor_status;
...
END; 

Microsoft SQL Server T-SQL

References
1. Erland Sommarskog’s The Curse and Blessings of Dynamic SQL
2. Wiley’s The Database Hacker’s Handbook
3. Chris Taylor’s SQL Injection - Are Parameterized Queries Safe?
4. MSDN article on SQL Injection
5. T-SQL references on EXECUTE and sp_executesql

Auditing
You are looking for two things: the use of EXECUTE (which has the synonym EXEC), and the use of the sp_executesql stored procedure. You want to do a search for EXEC, EXECUTE and sp_executesql, so a single case-insensitive search for EXEC should be sufficient. Use the above references to help further evaluate the results of the search. Basically, you’re looking for the use of a dynamically constructed SQL query string that incorporates user-malleable data.

Examples
The first example is taken from Erland Sommarskog’s The Curse and Blessing of Dynamic SQL. The second is taken from Chris Taylor’s SQL Injection - Are Parameterized Queries Safe?

Example 1 - EXEC

CREATE PROCEDURE search_orders @custid   nchar(5)     = NULL,
                               @shipname nvarchar(40) = NULL
AS 
    DECLARE @sql nvarchar(4000)
    SELECT @sql = ' SELECT OrderID, OrderDate, CustomerID, ShipName ' +
                  ' FROM dbo.Orders WHERE 1 = 1 '
    IF @custid IS NOT NULL
       SELECT @sql = @sql + ' AND CustomerID LIKE ''' + @custid + ''''
    IF @shipname IS NOT NULL
       SELECT @sql = @sql + ' AND ShipName LIKE ''' + @shipname + ''''
    EXEC(@sql)

Example 2 - sp_executesql

create proc VulnerableDynamicSQL(@userName nvarchar(25))
as
    declare @sql nvarchar(255)
    set @sql = 'select * from users where UserName = '''
               + @userName + ''''
    exec sp_executesql @sql

• Auditing , SQL , Web