Last week I had to tokenize a string ... Not for the first time, of course, but ow I decided to create a generic solution for this. In Java it is very simple: There is the java.util.StringTokenizer class which gives a very easy interface to tokenize a string and to access to tokens. I'd like such an API also for PL/SQL and therefore I created some PL/SQL code which should behave similar to the java class: The PL/SQL string tokenizer.

Although there are more PL/SQL string tokenizer code snippets than sand at the beach I've written another implementation: as a database object type. The reason for this is that with this approach you can have multiple instances of your tokenizer in your session at the same time. Using simple PL/SQL packages would require me to code the instance handling by myself - I considered this as too much effort. The java StringTokenizer was the model for the API of the PL/SQL object type.

The following script generated two object types: STRING_TOKENIZER is for dealing with VARCHAR2 contents - and since this is an object type, the SQL limit of 4.000 bytes applies. For larger Strings there is the CLOB_TOKENIZER type. This one uses the DBMS_LOB API for parsing the string and therefore can tokenize any string regardless of its length. Therefore the CLOB_TOKENIZER is slower than STRING_TOKENIZER so for short strings you should always use STRING_TOKENIZER.

As most know SQL injection is (as the name already tells) the "injection" of SQL commands into the database using the vulnerability in the application. SQL injection is done via the application's front end every other user uses. But an attacker enters values which most normal users don't even think about. SQL injection vulnerabilities arise when the application developer concatenates user-supplied parameters without checking into SQL queries and statements. So the following Java (JDBC) code is a typical example for a SQL injection vulnerability.

Now let's assume that the end user provides not the expected value (e.g. 7839) but 7839 or 1=1 as the HTTP parameter EMPNO. Now the whole table gets selected. But SQL injection allows not only to bypass a query filter. Providing 7839 union all (...) allows to select other tables (including dictionary tables) and (of course) every accessible SQL function can now be executed.

Generally most attacks to Oracle databases firstly use a SQL injection vulnerability to get into the database. SQL injection is (so-to-say) the attackers' entry ticket to the database. Depending on who had entered it and what his or her objectives are, more or less damage is produced.

Fortunately it's quite easy to prevent SQL injection attacks - but the fact that SQL injection protection must be comprehensive over each and every dynamic SQL statement is the developer's greatest enemy. You just have always to think about it. The vulnerability in the above java code is easy to handle: Just use bind variables. Using bind variables not only gives you better performance - it also prevents SQL injection since the SQL query is no longer a "dynamic one".

So SQL Injection is a no-brainer for PL/SQL developers? No - PL/SQL allows also to construct dynamic SQL statements - and in this cases SQL injection vulnerabilities are possible. If you're using the DBMS_SQL package or the EXECUTE IMMEDIATE or OPEN ... FOR commands you have to think about SQL injection.

This example is a PL/SQL which could be used for SQL injection attacks, since you can provide any arbitrary string for the procedure parameters. Since the parameters are concatenated into the SQL command without any checking an attacker could again: select any accessible table and execute any accessible function.

Now we come to the solution: The package DBMS_ASSERT can be used to check the user-supplied parameters before concatenating them into the SQL statement. The most important functions are ENQUOTE_NAME and ENQUOTE_LITERAL. Both functions enquote the supplied parameter if the parameter is OK and raise an exception if there's a SQL injection attack.

It is absolutely essential to use ENQUOTE_NAME or ENQUOTE_LITERAL in the correct context. Attempts to detect SQL injection attacks on object names with ENQUOTE_LITERAL fails as well as with ENQUOTE_NAME on literals. Using the correct function in the correct context makes cure that SQL injection attacks will be detected. Applied to the above PL/SQL procedure the code looks as follows:

DBMS_ASSERT is a nice approch to fix SQL injection vulnerabilities in PL/SQL code. The important thing is to use it correctly (say: the correct function). I'd so far to say that the usage of DBMS_ASSERT is a must when using dynamic SQL statements in PL/SQL code. The only thing I have to do in the future is to keep on this.

When you're coding lots of PL/SQL code you might need to determine the sessions's memory consumption sometimes. And sometimes you don't have the time to start a graphical user interface just for that - and there are situations where you just don't have the resources (on your development machine) to start Enterprise Manager oder SQL Developer or any other tool.

Today I've compiled (for the x-th time) a SQL script displaying the memory statistics for a particular database session. And today I'm posting it here - just that I have it ready the next time I need it. And perhaps it's also useful for you ...

You can select the statistic you want to have displayed by changing the LIKE expression in the query. A select distinct name from v$statname shows you the available statistics. Running the script gives you a "snapshot" of the current situation. The output looks like this:

Yesterday a collegue found a bug in my PL/SQL packages for operating system interaction "OS_COMMAND". The error is in the LOB_WRITER_PLSQL package and occurs only on Windows platforms: Binary files are not written correctly into the file system - the resulting files are corrupt. The reason is the procedure FOPEN in the UTL_FILE package - if you want to write binary content on windows platforms make sure that you provide the OPEN_MODE 'wb' and not just 'w'. On Unix/Linux platforms this does not make a difference. The newest version 0.5.2 corrects this. Only the package LOB_WRITER_PLSQL is affected by the "patch".

Last week I was asked for an easy approach to load an entore directory containing files (PDF documents, images, etc) into a database table. SQL*Loader is not appropriate for this since all the content must be in one or multiple known files. But this case is another - the directory is known, but not the files in it.

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle Corporation or Oracle Germany. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine.