Oracle, white spaces and unexpected behaviour

Last week I saw the blog entry „select 1.x from t1“ from Laurent concerning white spaces in select statements and Tom Kyte’s answer with a short explanation. Tanel Poder wrote a blog entry „Can you write a working SQL statement without using any whitespace?“ too.

In my opinion and from the security perspective making whitespaces optional in SQL statements is a bad idea because it’s an unexpected behavior. And this is always a bad idea.

Here a real life example from Oracle itself:
Two years ago I found a SQL Injection Vulnerability in the web component of XMLDB.

The exploit was looking like:

http://url/xmldb?param1=’||(select sysdate from dual)||‘

The result was a HTTP page containing the current date in an Oracle error message, a common exploit technique used by attackers.

The bugfix from the Oracle developer responsible for this component was to filter the URL for white spaces. Whenever a whitespace was part of the URL, the query was rejected. That’s why it was still possible to use functions (e.g. SYS_CONTEXT, …) but select statements were refused.

At that time I was not aware that SQL statements can be constructed without white spaces.

But with the knowledge from Laurent’s and Tanel’s blog entries I could rewrite the exploit


A quick check in the Oracle PL/SQL code shows that some Oracle packages are using whitespaces as token separator (with the function instr()). I was also able to create a buffer overflow with alter session (11g) in SQL*Plus using this technique.  I will digg deeper…

Quick question to my readers: Is this just an Oracle behavior or also possible in other databases like SQLServer or DB2.

Leave a Reply

You must be logged in to post a comment.