Oracle PL/SQL and Microsoft Transact-SQL (T-SQL) codes are very different and in most cases more insecure than conventional programming codes such as PHP, .NET, Java, and the like. For example, Oracle has historically suffered from multiple PL/SQL injection vulnerabilities in code within the built-in database packages that are shipped by default with the database product. PL/SQL code executes with the privileges of the definer, and therefore has been a popular target for attackers looking for a reliable way to elevate their privileges. So much so that Oracle itself has ironically published a paper dedicated to educating developers
on how to produce secure PL/SQL (www.oracle.com/technology/tech/pl_sql/pdf/how_to_write_injection_proof_plsql.pdf).
However, a stored procedure can run either with the rights of the caller (authid current_user) or with the rights of the procedure’s owner (authid definer). You can specify this behavior with the authid clause when creating a procedure.
Programming codes such as T-SQL and PL/SQL are not usually made available to you in handy text files, though. To analyze the source of a PL/SQL procedure you have two options. The first is to export the source code from the database. To achieve this you can use the dbms_metadata package. You can use the following SQL∗Plus script to export the Data Definition Language (DDL) statements from the Oracle database. DDL statements are SQL statements that define or alter a data structure such as a table. Hence, a typical DDL statement is create table or alter table:
-- Purpose: A PL/SQL script to export the DDL code for all database objects -- Version: v 0.0.1
-- Works against: Oracle 9i, 10g and 11g
-- Author: Alexander Kornbrust of Red-Database-Security GmbH --
set echo off feed off pages 0 trims on term on trim on linesize 255 long 500000 head off --
execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,‘STORAGE’,false); spool getallunwrapped.sql
--
select ‘spool ddl_source_unwrapped.txt’ from dual; --
-- create a SQL scripts containing all unwrapped objects
select ‘select dbms_metadata.get_ddl(’’’||object_type||’’’,’’’||object_name||’’’,’’’|| owner||’’’) from dual;’
from (select ∗ from all_objects where object_id not in(select o.obj# from source$ s, obj$ o,user$ u where ((lower(s.source) like ‘%function%wrapped%’) or (lower (s.source) like ‘%procedure%wrapped%’) or (lower(s.source) like ‘%package%wrapped%’)) and o.obj#=s.obj# and u.user#=o.owner#))
where object_type in (‘FUNCTION’, ‘PROCEDURE’, ‘PACKAGE’, ‘TRIGGER’) and owner in (‘SYS’) order by owner,object_type,object_name;
--
-- spool a spool off into the spool file. select ‘spool off’ from dual;
spool off --
-- generate the DDL_source --
@getallunwrapped.sql quit
The second option available to you is to construct your own SQL statements to search the database for interesting PL/SQL codes. Oracle stores PL/SQL source codes within the ALL_SOURCE and DBA_SOURCE views; that is, if the code has not been obfuscated
(obfuscation is a technique used to convert human-readable text into a format that is not easily read). You can do this by accessing the TEXT column from within one of the two views. Of immediate interest should be any code that utilizes the execute immediate or dbms_sql function. Oracle PL/SQL is case-insensitive, so the code you are searching for could be constructed as EXECUTE, execute, or ExEcUtE, and so forth. Therefore, be sure to use the lower(text) function within your query. This converts the value of text to lowercase so that your LIKE statement will match all of these eventualities. If unvalidated input is passed to these functions, just like within the previous application programming language examples, it may be possible to inject arbitrary SQL statements. You can use the following SQL statement to obtain the source for PL/SQL code:
SELECT owner AS Owner, name AS Name, type AS Type, text AS Source FROM dba_source WHERE ((LOWER(Source) LIKE ‘%immediate%’) OR (LOWER(Source) LIKE ‘%dbms_sql’)) AND owner=‘PLSQL’;
Owner Name Type Source
--- PLSQL DSQL PROCEDURE execute immediate(param); Owner Name Type Source
---
PLSQL EXAMPLE1 PROCEDURE execute immediate(‘select count(∗) from ’||param) into i; Owner Name Type Source
---
PLSQL EXAMPLE2 PROCEDURE execute immediate(‘select count(∗) from all_users where user_id=’||param) into i;
The output from the search query has presented three very likely candidates for closer inspection. The three statements are vulnerable because user-controlled data are passed to the dangerous functions without being validated. However, similar to application developers, database administrators (DBAs) often first copy parameters to locally declared variables. To
search for PL/SQL code blocks that copy parameter values into dynamically created SQL strings you can use the following SQL statement:
SELECT text AS Source FROM dba_source WHERE name=‘SP_STORED_PROCEDURE’ AND owner=‘SYSMAN’ order by line;
Source
--- 1 CREATE OR REPLACE PROCEDURE SP_StoredProcedure (input IN VARCHAR2) AS 2 sql VARCHAR2;
3 BEGIN
4 sql:=‘SELECT field FROM table WHERE field =’’’ || input || ’’’’; 5 EXECUTE IMMEDIATE sql;
6 END;
The preceding SQL statement has found a package that dynamically creates an SQL statement from user-controlled input. It would be worth taking a closer look at this package. You can use the following SQL statement to dump the source for the package so that you can inspect things a little more closely:
SELECT text AS Source FROM dba_source WHERE name=‘SP_STORED_PROCEDURE’ AND owner=‘SYSMAN’ order by line;
Source
--- 1 CREATE OR REPLACE PROCEDURE SP_ StoredProcedure (input IN VARCHAR2) AS 2 sql VARCHAR2;
3 BEGIN
5 EXECUTE IMMEDIATE sql; 6 END;
In the preceding case, the input variable is taken directly from the user input and concatenated with the SQL string sql. The SQL string is passed to the EXECUTE function as a parameter and is executed. The preceding Oracle stored procedure is vulnerable to SQL injection even though the user input is passed to it as a parameter.
You can use the following PL/SQL script to search all PL/SQL codes in the database to find a code that is potentially vulnerable to SQL injection. You will need to closely scrutinize the output, but it should help you to narrow your search:
-- Purpose: A PL/SQL script to search the DB for potentially vulnerable -- PL/SQL code
-- Version: v 0.0.1
-- Works against: Oracle 9i, 10g and 11g
-- Author: Alexander Kornbrust of Red-Database-Security GmbH --
select distinct a.owner,a.name,b.authid,a.text SQLTEXT from all_source a,all_procedures b
where (
lower(text) like ‘%execute%immediate%(%||%)%’ or lower(text) like ‘%dbms_sql%’
or lower(text) like ‘%grant%to%’
or lower(text) like ‘%alter%user%identified%by%’ or lower(text) like ‘%execute%immediate%’’%||%’
or lower(text) like ‘%dbms_ddl.create_wrapped%’
or lower(text) like ‘%dbms_hs_passthrough.execute_immediate%’ or lower(text) like ‘%dbms_hs_passthrough.parse%’
or lower(text) like ‘%owa_util.bind_variables%’ or lower(text) like ‘%owa_util.listprint%’ or lower(text) like ‘%owa_util.tableprint%’ or lower(text) like ‘%dbms_sys_sql.%’ or lower(text) like ‘%ltadm.execsql%’
or lower(text) like ‘%dbms_prvtaqim.execute_stmt%’ or lower(text) like ‘%dbms_streams_rpc.execute_stmt%’ or lower(text) like ‘%dbms_aqadm_sys.execute_stmt%’
or lower(text) like ‘%dbms_streams_adm_utl.execute_sql_string%’ or lower(text) like ‘%initjvmaux.exec%’
or lower(text) like ‘%dbms_repcat_sql_utl.do_sql%’
or lower(text) like ‘%dbms_aqadm_syscalls.kwqa3_gl_executestmt%’ )
and lower(a.text) not like ‘% wrapped%’ and a.owner=b.owner
and a.name=b.object_name
and a.owner not in (‘OLAPSYS’,‘ORACLE_OCM’,‘CTXSYS’,‘OUTLN’,‘SYSTEM’,‘EXFSYS’, ‘MDSYS’,‘SYS’,‘SYSMAN’,‘WKSYS’,‘XDB’,‘FLOWS_040000’,‘FLOWS_030000’,‘FLOWS_030100’,
order by 1,2,3
To analyze the source of a T-SQL procedure from within a Microsoft SQL Server database prior to Microsoft SQL Server 2008 you can use the sp_helptext stored procedure. The sp_helptext stored procedure displays the definition that is used to create an object in multiple rows. Each row contains 255 characters of the T-SQL definition. The definition resides in the definition column in the sys.sql_modules catalog view. For example, you can use the following SQL statement to view the source code of a stored procedure:
EXEC sp_helptext SP_StoredProcedure;
CREATE PROCEDURE SP_StoredProcedure @input varchar(400) = NULL AS DECLARE @sql nvarchar(4000)
SELECT @sql = ‘SELECT field FROM table WHERE field = ’’’ + @input + ’’’’ EXEC (@sql)
In the preceding example, the @input variable is taken directly from the user input and concatenated with the SQL string (@sql). The SQL string is passed to the EXEC function as a parameter and is executed. The preceding Microsoft SQL Server stored procedure is vulnerable to SQL injection even though the user input is being passed to it as a parameter.
Two commands that you can use to invoke dynamic SQL are sp_executesql and EXEC(). EXEC() has been around since SQL 6.0; however, sp_executesql was added in SQL 7. sp_executesql is a built-in stored procedure that takes two predefined parameters and any number of user-defined parameters. The first parameter, @stmt, is mandatory and contains a batch of one or more SQL statements. The data type of @stmt is ntext in SQL 7 and SQL 2000, and nvarchar(MAX) in SQL 2005 and later. The second parameter, @params, is optional. EXEC() takes one parameter which is an SQL statement to execute. The parameter can be a concatenation of string variables and string literals. The following is an example of a vulnerable stored procedure that uses the sp_executesql stored procedure:
EXEC sp_helptext SP_StoredProcedure_II;
CREATE PROCEDURE SP_StoredProcedure_II (@input nvarchar(25)) AS
DECLARE @sql nvarchar(255)
SET @sql = ‘SELECT field FROM table WHERE field = ’’’ + @input + ’’’’ EXEC sp_executesql @sql
You can use the following T-SQL command to list all of the stored procedures in the database:
SELECT name FROM dbo.sysobjects WHERE type =‘P’ ORDER BY name asc
You can use the following T-SQL script to search all stored procedures within an SQL Server database server (note that this does not work on SQL Server 2008) to find a T-SQL code that is potentially vulnerable to SQL injection. You will need to closely scrutinize the output, but it should help you to narrow your search:
-- Description: A T-SQL script to search the DB for potentially vulnerable -- T-SQL code
-- @text – search string ‘%text%’
-- @dbname - database name, by default all databases will be searched --
ALTER PROCEDURE [dbo].[grep_sp]@text varchar(250), @dbname varchar(64) = null
AS BEGIN
SET NOCOUNT ON;
if @dbname is null begin --enumerate all databases.
DECLARE #db CURSOR FOR Select Name from master…sysdatabases declare @c_dbname varchar(64)
while @@FETCH_STATUS <> -1begin execute grep_sp @text, @c_dbname FETCH #db INTO @c_dbname
end
CLOSE #db DEALLOCATE #db end
elsebegin declare @sql varchar(250) --create the find like command
select @sql = ‘select ’’’ + @dbname + ’’’ as db, o.name,m.definition’ select @sql = @sql + ‘ from ‘+@dbname+’.sys.sql_modules m’
select @sql = @sql + ‘ inner join ‘+@dbname+’…sysobjects o on m.object_id=o.id’ select @sql = @sql + ‘ where [definition] like ‘‘%’+@text+‘%’’’
execute (@sql) end
END
Make sure you drop the procedure when you’re finished! You can invoke the stored procedure like this:
execute grep_sp ‘sp_executesql’; execute grep_sp ‘EXEC’;
You can use the following T-SQL command to list user-defined stored procedures on an SQL Server 2008 database:
You can use the following T-SQL script to search all stored procedures within an SQL Server 2008 database server and print their source, if the respective line is uncommented. You will need to closely scrutinize the output, but it should help you to narrow your search:
DECLARE @name VARCHAR(50) -- database name DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.procedures; OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0
BEGINprint @name
-- uncomment the line below to print the source -- sp_helptext ‘‘+ @name + ’’
FETCH NEXT FROM db_cursor INTO @name END
CLOSE db_cursor DEALLOCATE db_cursor
There are two MySQL-specific statements for obtaining information about stored procedures. The first one, SHOW PROCEDURE STATUS, will output a list of stored procedures and some information (Db, Name, Type, Definer, Modified, Created, Security_type, Comment) about them. The output from the following command has been modified for readability:
mysql> SHOW procedure STATUS;
| victimDB | SP_StoredProcedure_I | PROCEDURE | root@localhost | DEFINER | victimDB | SP_StoredProcedure_II | PROCEDURE | root@localhost | DEFINER | victimDB | SP_StoredProcedure_III | PROCEDURE | root@localhost | DEFINER
The second command, SHOW CREATE PROCEDURE sp_name, will output the source of the procedure:
mysql> SHOW CREATE procedure SP_StoredProcedure_I \G ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ Procedure: SP_ StoredProcedure
sql_mode:
CREATE Procedure: CREATE DEFINER=‘root’@’localhost’ PROCEDURE SP_ StoredProcedure (input varchar(400))
BEGIN
SET @param = input;
SET @sql = concat(‘SELECT field FROM table WHERE field=’,@param); PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt; End
Of course, you can also obtain information regarding all stored routines by querying the information_schema database. For a database named dbname, use this query on the INFORMATION_SCHEMA.ROUTINES table:
SELECT ROUTINE_TYPE, ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA=‘dbname’;