Obfuscation contest

I have been challenged to participated (my script : lsc2.sql) and now challenged to explain my query by Chen
Chen Shapira

Ok, I give a try.

To make the query unreadable and unformatable I used no space, no new line, and I started by q’<'>‘ to confuses sqlinform.

Ok, I added undocumented constructs like

connect by .1 < 1.
connect by .25 > rownum/100.

with 2 connect by, the first is ignored. The second is buggy in 10gR1 and returns 25 rows in 10gR1 (but 24 rows in 9i, 10gR2, 11g).

and group by () which means only one group. Note this clause is mandatory, you cannot remove it when using an aggregate function and a scalar subquery like in
select (select 1 from dual), count(*) from dual group by ();

The utl_raw and to_char converts a number to the string Oracle Community. I will keep terse on the formula.
The scalar subquery inside TAN returns 1. The regexp removes some characters out of the extract tag. _x0032_ is the alias of the column “2”. The v$reservedwords use UNIQUE to sort the rows (UNIQUE was sorting in 10gR1 for UNIQUE/DISTINCT) and the MAX(rownum) retrieve the 316th row (FROM) and 845th row (SELECT). Remember ROWNUM is evaluated BEFORE aggregation, therefore ROWNUM 845 exists! Note the “+” is an alias and not a string!

With the SELECT and FROM keyword I build a new query that I dynamically evaluates with dbms_xmlgen.getxml. Rest of the first part is fancy calculation.

The dburi is also dynamic.


Well, I could have obfuscated the text a bit more but I was short of time… This simply return the name of the package that have a procedure called GETKEY. Which is DBMS_OBFUSCATION_TOOLKIT_FFI. This is not very clean as the query would fail if you have a PROC.GETKEY in your schema. Well, I wanted to add this not very well know mechanism to query the database.

Last part is in dict, I select the pattern ‘[COTTON+XE]{4,}’ in dict and return the Mode (or modal score), which is CONTEXT.

A bit formatting and I get

Oracle Community_Obfuscation_Contest

Published by

Laurent Schneider

Oracle Certified Master

9 thoughts on “Obfuscation contest”

  1. Objection, your honor : The query should be formated and have a least some sort of meaning like the one that produce a yearly calandar like unix command ‘cal’ (this one is really impressive). Otherwise it is going to turn at who can pile the more regexp and math functions.

  2. :-) I used math function quite economically to translate a list of numbers to a hex string ! One of the objective was to show obscure techniques. But well, I could do cal too :

    select * from (
    select * from dual model dimension by (0 day, 1 week) 
    measures (0 c)
    c[for day from 0 to 6 increment 1, for week from 0 to 4 increment 1]=
    c[any,any]=case when c[cv(),cv()] between 
      1 and extract(day from last_day(sysdate)) then c[cv(),cv()] end))
    pivot (max(c) for day in (
      0 as mon,1 as tue,2 as wed,3 as thu,4 as fri,5 as sat,6 as sun))
    order by week;

    but is this really obscure?

  3. hummm… lemme think….really…..hummm…hard….hard…. Yes it is not evident. The construct within the rule section is not cristal clear and I am lucky to have found the excellent tutorial of Joe on the caluse model http://www.sqlsnippets.com/en/topic-11663.html.
    And even, this query is the soft version using the 11g pivot. I saw one without it for 10g with a bunch of connect by. Was fun, I gave up when I met the second connect by.

    However, I vote for this query over the lsc2, this one has a palatable goal.
    is it possible to do an inverse of matrix using the pivot and model clause ? that would be usefull, awfully complicated so that you put it in the interview under the nose of the candidate and ask what the query does.

  4. I have not done this yet. It is surely possible, but I do not know myself how to calculate inverse matrix.

    To multiply matrix I could imagine :

    SQL> create table m1 as
      select 1 x, 1 y, 10 val from dual 
      union all select 1,2,20 from dual
      union all select 2,1,30 from dual
      union all select 2,2,40 from dual
      union all select 3,1,50 from dual
      union all select 3,2,60 from dual
      union all select 4,1,70 from dual
      union all select 4,2,80 from dual;
    Table created.
    SQL> create table m2 as
      select 1 x, 1 y, 100 val from dual 
      union all select 1,2,200 from dual
      union all select 1,3,300 from dual
      union all select 2,1,400 from dual
      union all select 2,2,500 from dual
      union all select 2,3,600 from dual;
    Table created.
    SQL> select * from m1 pivot (max(val) for y in (1,2))
             X          1          2
    ---------- ---------- ----------
             1         10         20
             2         30         40
             3         50         60
             4         70         80
    SQL> select * from m2 pivot (max(val) for y in (1,2,3))
             X          1          2          3
    ---------- ---------- ---------- ----------
             1        100        200        300
             2        400        500        600
    SQL> select * from (
    select * from dual model
      m1 on (select * from m1) dimension by (x,y) measures (val)
      m2 on (select * from m2) dimension by (x,y) measures (val)
      dim1 on (select max(x) x,max(y) y, 0 dummy from m1) dimension by (dummy) measures (x,y)
      dim2 on (select max(x) x,max(y) y, 0 dummy from m2) dimension by (dummy) measures (x,y)
    dimension by (1 x,1 y) measures (0 val)
    rules iterate (100) until (dim2.x[0]=iteration_number+1)
    (val[for x from 1 to dim1.x[0] increment 1, for y from 1 to dim2.y[0] increment 1]=
    nvl(val[cv(),cv()],0)+m2.val[iteration_number+1,cv(y)]*m1.val[cv(x),iteration_number+1] ))
    pivot (max(val) for y in (1,2,3))
             X          1          2          3
    ---------- ---------- ---------- ----------
             1       9000      12000      15000
             2      19000      26000      33000
             3      29000      40000      51000
             4      39000      54000      69000

  5. Hooo…….a pivot feeded by 2 nested loop out of 5 dimensions… we are reaching new hight of pain. The query is not made of cryptic or exotic functions, i can undertand every single word apart though I have hard time to understand the whole picture. The worst query are the ones made of simple words but still you don’t understand: the longer you look, the more idiot you feel.

    Back home, I will tell wife that it took me 5 hours to understand 10 lines.

Leave a Reply