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

http://url/xmldb?param1=’||(select/**/sysdate/**/from”DUAL”)||’

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.

3 Responses to “Oracle, white spaces and unexpected behaviour”

  1. Maxim sagt:

    mysql> select/**/current_user;
    +—————-+
    | current_user |
    +—————-+
    | root@localhost |
    +—————-+
    1 row in set (0.00 sec)

    Best regards

    Maxim

  2. Gary sagt:

    From SQl Server 2005 SQLCMD
    1> select/*a*/count(*)/*b*/from/*c*/information_schema.tables
    2> go
    ———–
    559
    (1 rows affected)
    1> select top 3 1.x from information_schema.tables
    2> go
    x

    1
    1
    1

    (3 rows affected)
    1>

  3. Maxim and Gary,

    thank you for the update.

    Regards

    Alex

Leave a Reply

You must be logged in to post a comment.