Monday tech: Stored procedures and functions SQL Injection, Oracle code execution and mitigation
Hi everyone
Sometimes you do a penetration test of a 2-tier application
– a fat\thick client application.
A 2-tier thick client application is an app that connects
directly to the database, without any server side code that implements business
logic and proxies requests to the DB.
Any 2-tier application is a fat\thick client app, not every
fat\thick client app is a 2-tier app, as thick client apps can connect to a
regular server (using web services for example).
In 2-tier apps, all of the business logic is implemented in
the DB level, including access control (authentication, authorization) and
stored procedures\functions inside the DB.
This stored code is written in SQL and can be executed
inside a select statement (function) or with an execute command (procedure).
In SQL Server this is called Transact-SQL, and Oracle DB
this is called PL\SQL.
Then you execute a penetration test and test a 2-tier app,
you test permissions, unauthorized data access and data manipulation and other
authorization-based vulnerabilities.
What can you possible test with SQL Injection when you
already have direct DB access with your own hardened DB user that enables you
to execute only certain select and other SQL statements?
Apparently, stored SQL procedures\functions can create and
execute dynamic SQL statements with string manipulation and as a result, can
also be vulnerable to SQL Injection.
In Oracle DB you use an Execute Immediate
command, and in Microsoft SQL Server you simply use exec, execute
or sp_executesql with a string.
For example:
Oracle:
Execute
immediate 'select * from table';
MS-SQL:
EXECUTE
sp_executesql ‘select * from table’;
Exec
‘select * from table’;
Execute
‘select * from table’;
If you concatenate strings that arrives from the outside as
a parameter, you get an stored procedure\function SQL injection:
Oracle Function: (procedures also present)
Function
func(param as varchar2) returns varchar2
{
…
Execute immediate 'select * from table where column =''' || param || '''';
…
}
{
…
Execute immediate 'select * from table where column =''' || param || '''';
…
}
MS SQL procedure: (functions also present)
CREATE
PROCEDURE proc @Param Varchar(500)
AS
…
exec 'select * from table where column =''' + Param + '''';
…
GO
…
exec 'select * from table where column =''' + Param + '''';
…
GO
This can be exploited by calling the vulnerable
function\procedure with a payload inside the parameter:
Oracle:
Oracle:
Select *
from dual where 0 >= (select count(my.func('aaa''; execute system.cool_func(''cool_param'')--')) from dual)--
MS SQL:
Execute
proc ‘aaa’’; exec
master..xp_cmdshell ‘’ping 8.8.8.8’’--‘;--
So why is this helpful for 2-tier apps? You can execute any
SQL within your sandboxed hardend SQL connection anyway, why would you want to
execute SQL inside a stored procedure or function?
Elevation of privileges – that’s why.
Some functions and procedures can elevate their privileges
in order to execute code that requires elevated or even admin permissions.
In MS SQL there is a command that is called execute as
that allows you to change the current code section’s permissions, and elevate
the permissions if needed.
This is how a vulnerable SQL Injection stored procedure with
elevated privileges (the famous SA user) looks like:
Package my
{
Procedure
func(param as varchar2) returns varchar2
{
…
Execute as 'sa';
Exec ('select * from table where column =''' + param + '''');
…
}
}
In oracle there is also an option to inherit (or revoke)
privileges from the procedure package’s owner, that can be a high-privileges
user publishing a procedure to low privileged users.
Privilege elevation in stored procedures\functions is not a vulnerability, but if it is not necessary it should be treated as a vulnerability, and when stored functions\procedures SQL Injection is involved, it becomes critical.
But this is not the only exploitation of stored code SQLi, the is another (theoretical) usage of stored functions SQL injection which is Remode Code execution.
In most DBs if you try to end the current (usually SELECT
SQL command) with a semicolon in order to start a new command, for example to
modify (UPDATE) or delete information, you will be stopped at the DB connection
driver level. If you try this for example in Oracle DB:
http://www.hackedsite.com/page.aspx?paramter=’
or 1=1--;update sensitive table set
value=’malicious value’--
This will generate an “illegal character” error in the
Oracle driver itself. The driver does not allow Oracle DB to receive more than
one command at a time, and will stop any attempt to end the current command and
start a new one.
So how can you still end the current command and start a new
command in Oracle DB in order to – for example – execute an Update or Delete
operation?
You need to find a SQL injection in an Oracle stored
function that uses dynamic SQL building (“EXECUTE IMMEDIATE’), trigger this
function in your SQL injection, and then perform a second SQL injection to this
function. Why will this be helpful? Because dynamic SQL building in stored
functions does allow execution of multiple commands with semicolons.
So all you really have to do is to find a vulnerable
function that builds dynamic SQL with string concatenation, for example a
function that looks like this:
Function func(param as varchar2)
returns varchar2
{
…
Execute immediate 'select * from table where column =''' || param || '''';
…
}
{
…
Execute immediate 'select * from table where column =''' || param || '''';
…
}
Mind that you specifically need a function, because a
function can be activated within different parts of an SQL statement, unlike
stored procedure that needs to be activated with a dedicate “execute” command.
So how would a successful double SQL injection RCE
attack would look like? Here is an example:
http://www.hackedsite.com/page.aspx?paramter=’
or 1=func(‘aaa’’; update sensitive table set value=’’malicious value’’--‘)--
Note that in the first SQL injection we activate the func
vulnerable function, and then we send a second SQL injection payload that
contains a semicolon character, and enables us to end the current SQL command
in the stored function, and start a new command – in this case an example of an
update command the compromises the integrity of the information, not only the
confidentiality as in a regular SQL Injection attack.
So, in conclusion: In order to find Oracle double SQL
Injection RCE exploits you need to search for stored functions that builds
dynamic SQL in a non-secured manner (you can search all_source
system table that contains all the stored functions and procedures code), and then
trigger this function in order to execute a second SQL Injection and gain a RCE
capabilities.
It is important to notice that this technique is mostly
theoretical and I have never encountered such a real case in the wild, because
SQL Injection is becoming less and less common, and because finding this kind
of double SQL Injection is hard and time consuming, and in SQL Injection in
penetration tests, you’ll probably pull out some sensitive information as a PoC
and just stop there, without ever testing for RCE capabilities.
If you do encounter SQL Injection in Oracle DB, it will be very cool if you can test it for RCE.
Read more »
posted by gilcohen @ 8:59 AM
