Executing any binary from Oracle

sqlmdx

It is well know that there are many ways to run third party executable from Oracle. In particular external procedures, external tables, dbms_scheduler, Oracle Text to name a few. In this post I will do quick end-to-end demo how to compile and run the binary with external tables followed by an explanation how we can avoid compilation stage.

Step 1. Starting Oracle in docker.

$ docker run --name ora -e ORACLE_PASSWORD=1 gvenzl/oracle-free

Step 2. Getting inside the container.

$ docker exec -it ora bash

Step 3. Installing gcc.

bash-4.4$ sudo microdnf install gcc

Step 4. Creating a binary.

bash-4.4$ echo 'int puts(const char *);void main(){puts("One\nTwo");puts("Three");}' > a.c
bash-4.4$ gcc a.c

Step 5. Executing the binary.

bash-4.4$ cat doit.sql 
set feedback on
show release

create directory my_dir as '/opt/oracle';

create table t_ext(text varchar2(30))
organization external
(
  default directory my_dir
  access parameters
  (
    records delimited by newline
    preprocessor 'a.out'
  )
  location('a.out')
);

select * from t_ext;
bash-4.4$ sqlplus -s / as sysdba @doit.sql
release 2326000000

Directory created.


Table created.


TEXT
------------------------------
One
Two
Three

3 rows selected.

So everything was pretty straightforward. Now let’s see how we can create an executable on a server without a compiler… but before that we still will compile the code and aim for a small size. And do this on Windows. 🙂

Our executable is supposed to create a file “world.txt” containing “hello”.

C:\temp>more a.c
#define W32(r) __declspec(dllimport) r __stdcall

W32(void*) CreateFileA(char*, int, int, void*, int, int, void*);
W32(int)   WriteFile(void*, void*, int, int*, void*);
W32(int)   CloseHandle(void*);

mainCRTStartup()
{
    int n;
    char msg[] = "hello";
    void *h = CreateFileA("c:\\temp\\world.txt", 0x40000000, 0, 0, 2, 0, 0);
    WriteFile(h, msg, sizeof(msg) - 1, &n, 0);
    CloseHandle(h);
}

Compiling this with

C:\temp>gcc -nostartfiles -s -Wl,--section-alignment=1 -Wl,--file-alignment=1 a.c

Creating a user

create user u identified by "u";
grant create session, create table to u; 
create or replace directory my_dir as 'c:\temp';
grant read, write, execute on directory my_dir to u;

Now we want to get base64 string for the executable and write it to the file system from Oracle (from this point we use user u to run all SQL commands).

SQL> set serveroutput on
SQL> declare
  2    l_file utl_file.file_type;
  3    l_raw  raw(32000);
  4    l_str  varchar2(32000);
  5  begin
  6    l_file := utl_file.fopen('MY_DIR', 'a.exe', 'rb', 32000);
  7    utl_file.get_raw(l_file, l_raw, 32000);
  8    utl_file.fclose(l_file);
  9    dbms_output.put_line(l_raw);
 10  end;
 11  /
4D5A90000300000004000000FFFF0000B80000000000000040000000000000000000000000000000
0000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD215468
69732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A
2400000000000000504500004C0103002E6964610000000000000000E0000F030B010218A8000000
E400000000000000F0010000F0010000000000000000400001000000010000000400000001000000
04000000000000007C030000F00100006CCB00000300000000002000001000000000100000100000
00000000100000000000000000000000F00200008C00000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000028030000100000000000000000000000
000000000000000000000000000000002E74657874000000A8000000F0010000A8000000F0010000
000000000000000000000000200030602E7264617461000058000000980200005800000098020000
000000000000000000000000400030402E696461746100008C000000F00200008C000000F0020000
000000000000000000000000400030C05589E583EC38C745EA68656C6C66C745EE6F00C744241800
000000C744241400000000C744241002000000C744240C00000000C744240800000000C744240400
000040C7042498024000A12C034000FFD083EC1C8945F4C7442410000000008D45F08944240CC744
2408050000008D45EA894424048B45F4890424A130034000FFD083EC148B45F4890424A128034000
FFD083EC04C9C390FFFFFFFF00000000FFFFFFFF00000000633A5C74656D705C776F726C642E7478
740000004743433A2028693638362D706F7369782D736A6C6A2C206275696C742062792073747261
7762657272797065726C2E636F6D2070726F6A6563742920342E382E330000001803000000000000
000000006C0300002803000000000000000000000000000000000000000000003803000046030000
5403000000000000380300004603000054030000000000005300436C6F736548616E646C65008B00
43726561746546696C654100EE04577269746546696C6500F0020000F0020000F00200004B45524E
454C33322E646C6C00000000

PL/SQL procedure successfully completed.

SQL> declare
  2    l_file utl_file.file_type;
  3    l_raw  raw(32000);
  4  begin
  5    l_raw := hextoraw(
  6      '4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103002E6964610000000000000000E0000F030B010218A8000000E400000000000000F0010000F001000000000000000040000100000001000000040000000100000004000000000000007C030000F00100006CCB0000030000000000200000100000000010000010000000000000100000000000000000000000F00200008C000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000028030000100000000000000000000000000000000000000000000000000000002E74657874000000A8000000F0010000A8000000F0010000000000000000000000000000200030602E7264617461000058000000980200005800000098020000000000000000000000000000400030402E696461746100008C000000F00200008C000000F0020000000000000000000000000000400030C05589E583EC38C745EA68656C6C66C745EE6F00C744241800000000C744241400000000C744241002000000C744240C00000000C744240800000000C744240400000040C7042498024000A12C034000FFD083EC1C8945F4C7442410000000008D45F08944240CC7442408050000008D45EA894424048B45F4890424A130034000FFD083EC148B45F4890424A128034000FFD083EC04C9C390FFFFFFFF00000000FFFFFFFF00000000633A5C74656D705C776F726C642E7478740000004743433A2028693638362D706F7369782D736A6C6A2C206275696C7420627920737472617762657272797065726C2E636F6D2070726F6A6563742920342E382E330000001803000000000000000000006C03000028030000000000000000000000000000000000000000000038030000460300005403000000000000380300004603000054030000000000005300436C6F736548616E646C65008B0043726561746546696C654100EE04577269746546696C6500F0020000F0020000F00200004B45524E454C33322E646C6C00000000'
  7    );
  8
  9    l_file := utl_file.fopen('MY_DIR', 'x.exe', 'wb', 32000);
 10    utl_file.put_raw(l_file, l_raw, true);
 11    utl_file.fclose(l_file);
 12  end;
 13  /

PL/SQL procedure successfully completed.

So effectively we created (copied) binary using string literal in Oracle.

Let’s run a new binary x.exe and see what happens.

SQL> create table t_ext(text varchar2(30))
  2  organization external
  3  (
  4    default directory my_dir
  5    access parameters
  6    (
  7      records delimited by newline
  8      preprocessor 'x.exe'
  9    )
 10    location('x.exe')
 11  );

Table created.

SQL> select * from t_ext;

no rows selected

SQL> exit;

Here we go.

C:\temp>more world.txt
hello

utl_file is granted to PUBLIC even on the latest versions and I’m afraid it will be like this for all future releases for backwards compatibility.

Be careful with the permissions! 🙂

Leave a comment