Our other main product PFCLObfuscate is also written in C with Lex and Yacc for the parser. The utilities for PFCLObfuscate are also written in C. But PFCLObfuscate is a program to process (parse) PL/SQL programs and protect them by converting them into a different form and also in version 2.0 by adding license type features automatically.
I also have a great interest in PL/SQL for many years; indeed my first PL/SQL program was actually written in C. I wrote a Pro*C program that generated PL/SQL code for testing an application. That PL/SQL code was driven by lookup tables that configured obfuscation and also numbers of records annd sources to sample from. Its purpose was to generate large amounts of data for the main applications testing and as the PL/SQL was working on applications tables it could be generated. Therefore I generated PL/SQL from Pro*C and C and that PL/SQL generated SQL insert statements based on config tables for all of the tables of the database that needed to be populated. This of course included referential connections. So it was fun to write my first PL/SQL program in C all those years ago and now we use C again to write PL/SQL (obfuscated of course) in PFCLObfuscate; so nothing has changed!
This interest in PL/SQL is why i was inspired to create PFCLObfuscate to protect IPR in PL/SQL for people. I have also written many many utilities in PL/SQL over the years . The biggest was oscan.sql which is a huge SQL*Plus script that was used to audit databases until we created PFCLScan. Oscan is around 45,000 lines of mostly PL/SQL. I have also written tools to analyse privileges such as find_all_privs.sql and scripts to assess who has particular roles, system privileges or access to objects on the database. I also wrote a password cracker completely in PL/SQL and even a PL/SQL unwrapper completely in PL/SQL; so I also love to write PL/SQL code and indeed I will still often prototype things in PL/SQL before adding them to products such as PFCLScan.
Indeed, we have a new training course about designing practical audit trails in Oracle databases to focus on the possible core audit facilities in the database including core audit, unified audit, system triggers, DML triggers and more. The focus is to concentrate on the database layer, actions, privilege actoons and possible attacks and as part of this class we have created a simple audit framework that includes a simple firewall, privilege monitoring and more. This is completely SQL*Plus based and mostly PL/SQL. When i was writing that some time ago I made a note to discuss a couple of things on PL/SQL. One thing I miss when writing PL/SQL is the simpleness of C. In C its great to write idioms such as:
i++;
i+=2;
or other elegant constructs; so i wondered if instead of:
i:=i+1;
i:=i+2;
in PL/SQL can we do the same as C. Oracle in stdspec.sql create procedures and functions such as "=" and ">=" which resolve to a special syntax called BUILTIN that we cannot use; most liklely because the C thats called is hard coded to work as SYS only and be linked to the right PL/SQL in the ICD vector table. But we can create a procedure called:
create or replace procedure "++"(i in out number) is
begin
i:=i+1;
end;
/
And call it like this:
declare
i number:=0;
begin
"++"(i);
dbms_output.put_line(i);
"++"(i);
dbms_output.put_line(i);
end;
/
Which results in the same desired affect:
SQL> @z
Connected.
Procedure dropped.
Function dropped.
Procedure created.
1
2
PL/SQL procedure successfully completed.
It is not as elegant as the C syntax but it is quite close and for a C programmer its intuitive to write "++"(i), actually its probably more intuitive to write i++ not ++1 but we won't split hairs now. It would be nice of course to write ++i in PL/SQL but we cannot so easily. In PL/SQL I also miss printf() in PL/SQL. The C function has the great syntax
int printf(condt char *fmt, ...);
The elipse (the "...") is the syntax in C that means there is a variable number of arguments to the function that are read and resolved at run time based on the format string. There is a limited printf in PL/SQL - well in the database, the UTL_LMS.FORMAT_MESSAGE() which allows %s and %d from printf and not more and not with the width and precision formats. In the C printf() there are widths and precisions and also lots more options for different data types such as CHAR, LONG and hexidecimals and much more. The UTL_LMS.GET_MESSAGE is interesting as it uses "..." in its specification so Oracle have added "..." for its own use but not for us. The prototype is:
FUNCTION format_message(format IN VARCHAR2 CHARACTER SET ANY_CS, args ...)
RETURN VARCHAR2 CHARACTER SET format%CHARSET;
If we try and use this syntax:
SQL> create or replace procedure printf(fmt in varchar2, args ...) is
2 begin
3 null;
4 end;
5 /
Warning: Procedure created with compilation errors.
SQL> sho err
Errors for PROCEDURE PRINTF:
LINE/COL ERROR
-------- -----------------------------------------------------------------
1/35 PLS-00999: implementation restriction (may be temporary) ellipsis
not allowed in this context
SQL>
We cannot, pity, maybe in the future?. Its possible to work around this with a VARRAY but the call to the procedure in PL/SQL is not quite as elegant as we need to create the VARRAY as we pass it in. If we dig deeper we can see that Oracle passes the variable args list to C and the C handles this variable args list. As the C that handles it using the "AS LANGUAGE C" syntax via a trusted library, is a specific C function to do the language format and simple printf its not going to be possible to use this syntax ourselves until Oracle make the syntax generic. A few people have created printf functions for PL/SQL by now. Scott Stevens has a great PL/SQL printf that shows quite a lot of printf functionalliy in PL/SQL. If you see books like "The Standard C Library" then you can appreciate to write printf() in C is a huge task so to replicate it fully in PL/SQL is equally as challenging.
If you dig also into stdspec.sql then another hint of possible language extras that we cannot use and that may be added:
-- The following data types are generics, used specially within package
-- STANDARD and some other Oracle packages. They are protected against
-- other use; sorry. True generic types are not yet part of the language.
type "
Generics are a great feature that we use a lot in .NET in PFCLScan (think templates in C++) so it would also be great to add generics to PL/SQL. I would also like to see the precompiler add true #defines to PL/SQL so that they are the same as C. You could possibly hack the current pre-compiler to do this but my reason would be to do a Bjarne on PL/SQL and add syntax or constructs not possible such as
i++
where i is substituted with "++"(i) at compile time. This would look horrible with the $IF etc from the PL/SQL precompiler and would spoil the look and not acheive the intent of using elegant syntax as the $IF $var is NULL $THEN etc would look much worse.
We could also maybe use defines to achieve calls such as "++"(i) but this is a SQL*Plus construct not PL/SQL so not much use in pure PL/SQL called from the database when compiled.
We could also use PFCLObfuscate to achieve this. We could write PL/SQL code with "..." and also i++; or i+=2; or even (some code)?"TRUE":"FALSE" or similar and then use PFCLObfuscate as a pre-compiler to convert it into compilable PL/SQL in a similar way to CFront converting (literal description!) C++ into C to compile it with CFront. The trick with CFront was that it was also compiled with CFront so bootstrap isses ensued whena new machine had to support it. What is the point of writing PL/SQL with unsupported syntax? not totally sure at this stage. The thought process.
We could also simulate the C syntax for i++ or i+=2 with a procedure/function pair. First we could run this code:
declare
i number;
begin
i:=0;
dbms_output.put_line('i='||i);
i:=i+1;
dbms_output.put_line('i='||i);
end;
/
This gives (in SQL*Plus):
i=0
i=1
PL/SQL procedure successfully completed.
When run through SQL*Plus. If we instead made "i" a procedure and function, so the variable "i" is no longer a variable so that we can get the same effect as i++ or i+=2 then we can do that also as an alternative to a function such as "++"(i). Here is the code:
-- instead make i a function
declare
i_self number:=0;
procedure i (lv in varchar2) is
begin
if(lv='++') then
i_self:=i_self+1;
elsif(lv='+=2') then
i_self:=i_self+2;
end if;
end;
function i return number is
begin
return(i_self);
end;
begin
--i:=0;
dbms_output.put_line('i='||i);
i('+=2');
dbms_output.put_line('i='||i);
end;
/
And we can now run this in SQL*Plus and the results are here:
i=0
i=2
PL/SQL procedure successfully completed.
SQL>
So obviously it works. There is a lot more code than simply writing i:=i+1; or i:=1+2; though and its not the same syntax as C as we need to cover the ++ or +=2 with a string passed to the procedure BUT its neat. We could extend this to pass the value "2" so that we could easily do +=3 or +=7 or whatever or we could parse it from the string.
OK, whats the point of all of this? apart from that it's interesting; well for me just to take the language and push it a little to see what it does is fun; this also helps us in research for PFCLObfuscate and also particularly in the license protections and tamper proofing we are doing at the moment so that customers can sell licenses to their PL/SQL products and enforce that in the database when customers code is deployed by using PFCLObfuscate.
Finally I got an email a week or so ago to tell me that my two slots at the UKOUG tech 2014 conference in Liverpool, UK in December have been accepted. I am doing an Oracle security round table and also a one hour live demo around securing annd locking down Oracle. There will be no slides and just live demos!, should be fun. I am going to do a high level audit of my database to show the issues; i am going to do some simple hacks of my database and application and then I am going to apply some lock down around hardening, users, defaults, passwords, profiles and also some least privilege issues and finish with the same hacks and show if its improved my database.
I also have agreed to speak at the OUG Ireland in Dublin on 24th September and I will do the same one hour live lock down demo and also my talk on secure coding in PL/SQL so that will be nice to be over in Dublin again.
