SQL Review and Native Compilation

I just started reading a new web site on PL/SQL programming. It is actually hosted by Oracle. Today I read a review of the different ways to execute SQL in PL/.SQL. They are embedded SQL, Native Dynamic SQL, and the DBMS_SQL API.

Embedded SQL is easy. You just code up the SQL like normal. There are some caveats like specifying the variable into which you select some data. But it is very natural. Here is a hint. Make sure you define aliases for the table you select from. Then use these aliases to qualify the columns you select.

Native Dynamic SQL is when you run the commands with an EXECUTE IMMEDAITE. You should exercise care to protect against SQL injection. I have written about this before. This style is fast than DBMS_SQL. Note that DBMS_SQL is the old way of executing dynamic SQL. It is not entirely obsolete. If you do not know the return values until run time, this is the only option. But most times you can skip it.

Now let’s talk briefly about native compilation. PL/SQL is interpreted. Normally it gets compiled down to M-code. The M-code is executed by the PL/SQL Virtual Machine (PVM). However for speed purposes you can go native. In the Oracle 9i and 10g days, native compilation would transform your PL/SQL to code in the C programming language. This C code would be compiled by a C compiler on the operating system. The result was a library that could run the code very fast on the machine.

Things change up a little bit for native compilation in Oracle 11g. But let’s leave that for a future blog post.

Pro*C Mystery Revealed

Our customer said there were too many problems with our system. And development was not resolving problems fast enough. The customer shared their concerns with management. Management put the heat on team leads. Now my team lead wants more and more bugs fixed faster. I had to push back and say problems will be resolved when they get resolved. However I did attempt to be diligent in problem resolution. There was one last problem to resolve. The fix sounded simple when I determined the cause. But I was encountering some strange Pro*C behavior. Here is the code I was trying to use:

void getType(char *pszType)
{
EXEC SQL BEGIN DECLARE SECTION;
char *pszLocalType = pszType;
END SQL BEGIN DECLARE SECTION;

EXEC SQL
SELECT type
INTO :pszLocalType
FROM myTable;
}

No matter how many times I ran the code, I always ended up with an empty string. However I got an actual value when I ran the SQL manually from SQL*Plus. What gives? Can you spot the problem? It took a while for me to realize what I was doing wrong. The variable into which I was trying to retrieve the data was defined as a pointer to the memory passed into the function. I really needed to have some space defined within the DECLARE section to store the SQL results. Then I could copy the value to the calling function. Here is a slightly updated result that worked:

void getType(char *pszType)
{
EXEC SQL BEGIN DECLARE SECTION;
char szLocalType[10+1];
END SQL BEGIN DECLARE SECTION;

EXEC SQL
SELECT type
INTO :szLocalType
FROM myTable;

Strcpy(pszType,szLocalType);
}

SQL Injection

SQL injection is an attack whereby the user gets to execute some command via a trick. You can prevent such behavior by using bind variables for arguments to SQL. You should also valid all inputs.

There are some other techniques which limit the ability for a SQL injection attack. Limit the privs you give to any given user. Also use invoker rights via AUTHID CURRENT_USER to ensure that commands that do get through run at the right priority.

Limit the number of routines that are exposed in a package specification. This will close the gap that a SQL injection hack can penetrate. Stay away from dynamic SQL. Get rid of debug messages and tracing. You don't want a hacker to know anything about the system you have developed.

You can employ fuzzing to test for SQL injection vulnerabilities. This will not prove you are secure, but can uncover some weaknesses. Test each parameter to your functions separately.

Curse of the Trigger

A tester found a problem in our application. I traced it back to some test configuration data in the database. That seemed like an easy fix. I wrote a one-time PL/SQL script to delete the test data. I had our DBA team implement the fix in the tester's database.

Luckily our DBA did a little quality assurance himself. He inspected the contents of my script. Then he checked whether the test records got deleted. They did not. It seems a trigger fired and prevent the delete. Doh. I should have checked that.

I like writing PL/SQL code. So I was not worried that the results of promoting my fix were a failure. That just meant I could write more code. I researched the logic of the trigger. Then I added some more code to detect child records of the test data. I wrote code to move those record to depend on another foreign key. Bingo. I even stayed late at work knocking out the updated fix.

Tracing and Profiling

You can employ an Oracle trace to view the execution path of your PL/SQL. This can be enabled either by a session parameter or a compile switch within your code. The data is accessible through some Oracle views. You must run the "tracetab.sql" file in order to set up these views.

A related topic is that of profiling. This is where you find out which portions of your code get executed the most. You can use this to choose which code you need to optimize. Profiling gets turned on and off with the DBMS_HPROF built in package.

Just like with trace, you need to run the "dbmshprof.sql" script to set up the tables for profiling. There is even an option to view the profiling output in HTML form. Next time I am going to talk about the hot topic of SQL injection attacks. Until then, be good.

Use the Source Luke

PL/SQL starts out as ASCII text files. Then you compile that source code into the database. Once it is there you can inspect what has been compiled. The old school way to do this is to inspect the USER_SOURCE view. Specifically you can query the TEXT column. It has your source code.

There are more advanced ways to check up on the code in the database. The SQL Developer tool has a function to search PL/SQL source code. You can also check out the ALL_ARGUMENTS view. It has parameter information for packages.

One setting that helps control your ability to analyze source code is PLSCOPE_SETTING. This can be set at either the session of system level. This allows you to analyze identifiers. You access this data using views such as ALL_IDENTIFIERS. Finally there is the DBMS_METADATA built in package. You can use it to get object definitions. The output is in XML format. Enjoy.