Hi everyone
Sometimes you do a penetration test of a 2-tier application – a fatthick 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 fatthick client app, not every fatthick 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 proceduresfunctions 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 PLSQL.
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 proceduresfunctions 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 Immediatecommand, 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 procedurefunction 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 functionprocedure 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 asthat 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 proceduresfunctions is not a vulnerability, but if it is not necessary it should be treated as a vulnerability, and when stored functionsprocedures 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:
https://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:
https://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 funcvulnerable 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_sourcesystem 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.
But what else is dangerous in stored code SQLi? if a system implements its DAL (data access layer) with stored procedures – either due to performance or security reasons, if the stored code builds dynamic SQL with concatenation, the system will still be vulnerable.
BTW, if someone asks you – stored procedures can be used to preventSQLi as indicated by OWASP, although it’s not the best way because dynamic SQL will cause it to be vulnerable, and because the call itself to the stored procedure, can be also vulnerable as I demonstrated in my SQL Injection Anywhere white paper (https://drive.google.com/file/d/0B3_AmubjewYTMmY4MDRiNTAtYmVkMi00NGQ4LTg3NjEtY2ZlNTdiYjZlYzUy/view?ddrp=1&hl=en).
So, we already understand what is the risk of SQLi in stored procedures and functions, but what is the mitigation if you do need to use dynamic SQL in stored procedures and functions?
The answer seems familiar – parameterized queries! Inside stored code!
This is a vulnerable function of MS-SQL:
ALTER PROCEDURE sp_GetProduct(@Name NVARCHAR(50))
AS
BEGIN
DECLARE @sqlcmd NVARCHAR(MAX);
SET @sqlcmd = N’SELECT * FROM tbl_Product WHERE Name = ”’ + @Name + ””;
EXECUTE(@sqlcmd)
END
And this is the same function with a fixed code:
ALTER PROCEDURE sp_GetProduct(@Name NVARCHAR(50))
AS
BEGIN
DECLARE @sqlcmd NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX);
SET @sqlcmd = N'SELECT * FROM tbl_Product WHERE Name = @Name';
SET @params = N'@Name NVARCHAR(50)';
EXECUTE sp_executesql @sqlcmd, @params, @Name;
END
Mind the red lines where the change takes place.
In oracle, if you need to use dynamic string SQL, Oracle’s official recommendation is to escape special characters using the QUOTE function, as there is no parameterized-queries-like mechanism.
The following example is vulnerable:
SQLExec("SELECT NAME, PHONE FROM PS_INFO WHERE NAME='" |
&UserInput | "'", &Name, &Phone);
This example is not vulnerable:
SQLExec("SELECT NAME, PHONE FROM PS_INFO WHERE NAME='" |
Quote(&UserInput) | "'", &Name, &Phone);
Have a great week 🙂
