X

Celebrating the joy and power of Oracle SQL with the Oracle Developer Advocate team

  • May 15, 2015

Obfuscated SQL Contest: Sean Stuber's Entry

Chris Saxon
Developer Advocate

Continuing the analysis of the Obfuscated SQL Contest entries, this time we're taking a look at Sean Stuber's query.

This is another cunning statement that hides the actual query. This one uses a new feature in 12c, a PL/SQL function defined on-the-fly in a with clause, to convert nonsense into real SQL. The actual query has some impressive tricks including an elaborate comma separated string to row converted and getting the current time via a HTTP request!

Note: while writing this post I noticed that Sean's entry uses UTC for London time which we'd overlooked in the judging process. UTC doesn't take account of daylight savings changes, so we've had to downgrade Sean from runner up to honourable mention.

First we'll look at how Sean converted gibberish into SQL. Simplifying, the driving query is:

with function s
return varchar2
is
s varchar2(1000);
begin
execute immediate translate (
encrypted_string,
q'{"%'()*+,-./123456789:;<=>ABCDEFGHIJKLMNOPQRSTUVWXYZ[]^_abcdefghiklmnopqrstuvwxy|}',
q'{|yxwvutsrqponmlkihgfedcba_^][ZYXWVUTSRQPONMLKJIHGFEDCBA>=<;:987654321/.-,+*)('%"}'
)
into s;
return s;
end;
select s
from dual;

This converts an encrypted string into SQL using translate(). This function takes a string and converts each character listed in the second argument to the corresponding character in the third. The q' mechanism is necessary to enable the single quote character to appear in the list without needing additional quotes. Therefore everything listed in the first q'{}' section above is converted to the equivalent character listed in the second, i.e. " becomes |, % becomes y, ' becomes x and so on.

Viewing the result of the translate, we see the following (formatted for attempted clarity ;):

with
/*
Requires 12c to implement function in WITH clause.
Must be run as SYS or SYSTEM in order to avoid needing a network ACL defined.
This makes no calls to SYSDATE, SYSTIMESTAMP, CURRENT_DATE or CURRENT_TIMESTAMP to retrieve the time
Instead the current time is pulled from the US Naval Observatory's atomic clock
*/
function o2i(p in varchar2) return
interval day to second as
r interval day to second;
s integer := case
when p like '-%' then
-1
else
1
end;
begin
r := numtodsinterval(s * to_number(substr(p, 2, 2)), 'hour');
r := r + numtodsinterval(s * to_number(substr(p, 5, 2)), 'minute');
return r;
end;
a as (
select 'Bangalore,London,New York,Chicago,Denver' a
from dual
),
b as (
select to_timestamp_tz(substr(regexp_substr(dbms_lob.substr(x),
'<BR>.* UTC'), 5), 'Mon. dd, hh24:mi:ss TZR') now
from (
select httpuritype('http://tycho.usno.navy.mil/cgi-bin/timer.pl')
.getclob() x
from dual
)
)
select replace(a, ',', '; ')
from (
select rtrim(
xmlquery(
'//x/text()' passing
xmlagg(xmlelement("x", b || ' ' || c || ',') order by a desc) returning content
), ',') a
from (
select c a, b, to_char(c, '"-" DD-MON-YYYY hh24:mi:ss') c
from (
select b,
cast((select now from b) + c as timestamp) c
from (
select a, b, o2i(tz_offset(c)) c
from (
select
case length(c)
when 9
then 'Asia' || '/' || 'Kolkata'
when 8
then 'America' || '/' || replace(c, ' ', '_')
when 7
then 'US' || '/' || 'Central'
when 6
then nvl(decode(substr(b, 2, 1), 'O', 'UTC'), 'US' || '/' || 'Mountain')
end c, a, b
from (
select a, substr(b, 1, 3) b, b c
from (
select upper(b) b, c a, initcap(b) c
from (
select c, a, b
from (
select a, b c,
regexp_substr(a, '[^,]+', 1, b) b
from a
full outer join
(select to_number(column_value) b
from xmltable((select '1' || ' to ' ||
((select length(
replace(
regexp_replace( a, '[^,]', 'x'),
'x', null
)
)
from a
) + 1)
from dual
)
)
) c
on b < length(a)
)
)
)
)
)
)
)
)
)

Aha! Now we have something that resembles a "normal" select statement!

Execute immediate in the PL/SQL function is the method that actually runs this statement. The output of this dynamic SQL is the string of locations and times asked for in the question.

So how does the real query work? Let's take a look.

The core of this are two queries defined in the with clause. The first simply selects out the locations we're interested in:

select 'Bangalore,London,New York,Chicago,Denver' a
from dual

The second gets the current time by making a HTTP request to the US Naval Observatory clock!

select to_timestamp_tz(
substr(
regexp_substr(
dbms_lob.substr(x),
'<BR>.* UTC'
), 5
),
'Mon. dd, hh24:mi:ss TZR'
) now
from (
select httpuritype('http://tycho.usno.navy.mil/cgi-bin/timer.pl').getclob() x
from dual
)

The HTTPURIType request returns a HTML document. This contains (amongst other things) the current time in UTC. With nifty regular expressions and substr operations this is extracted and converted into a timestamp with time zone.

At this point it gets tricky ;)

The string of place names (Bangalore,London,New York,Chicago,Denver) is converted to the number four. Not content with something obvious such as counting the number of commas, Sean uses regular expressions to first convert this to a string of "x"s (xxxxxxxxx,xxxxxx,xxxxxxxx,xxxxxxx,xxxxxx). These are then stripped out using replace to leave four commas. These are then "counted" by finding the length of this string!

select length( 
replace(
regexp_replace( 'Bangalore,London,New York,Chicago,Denver'
, '[^,]', 'x'),
'x', null
)
)
from dual

The value four is then passed into an XMLTable expression and one added to it. This exploits the XMLTable('1 to <N>') function which creates rows with the values in the range specified. Simplifying, the query is now:

select to_number(column_value) b
from xmltable((select '1' || ' to ' ||
((select 4
from dual
) + 1
)
from dual
)
)

This gives us rows numbered one to five. These are then full outer joined back to the string of locations! Each row is assigned a location by using a regular expression to find each text string. To this point the query is:

select regexp_substr(a, '[^,]+', 1, b) b
from (
select 'Bangalore,London,New York,Chicago,Denver' a
from dual
)
full outer join (
select to_number(column_value) b
from xmltable('1 to 5')
) c
on b < length(a)

Which returns:

B                                      
---------
Bangalore
London
New York
Chicago
Denver

The most hideously convoluted way to split elements of a comma separated list into rows I've ever seen!

The output of this passed through a couple more layers of nested selects. These convert the initcap versions of the place names into the three characters necessary for the output (Bangalore => BAN, London => LON, etc.) with some renaming of the columns thrown in for good measure. After this we have the following:

         A B   C                                      
---------- --- ------------
1 BAN BANGALORE
2 LON LONDON
3 NEW NEW YORK
4 CHI CHICAGO
5 DEN DENVER

The query then supplies time zones for each location by checking the length of the location in a case statement. Denver and London are the same length, so some it performs extra jiggery pokery to choose between these based on whether or not the second character is 'O':

case length(c)
when 9 then
'Asia' || '/' || 'Kolkata'
when 8 then
'America' || '/' || replace(c, ' ', '_')
when 7 then
'US' || '/' || 'Central'
when 6 then
nvl(
decode(substr(b, 2, 1), 'O', 'UTC'), 'US' || '/' || 'Mountain'
)
end

These time zone names are then converted into a time offset from UTC using the TZ_Offset() function. At this point the PL/SQL function defined in the with clause - o2i - comes into play. It takes these time zone strings and converts them to day to second intervals, taking care to ensure that the locations west of the Greenwich Meridian have negative intervals.

function o2i(p in varchar2) return interval day to second 
as
r interval day to second;
s integer :=
case
when p like '-%' then
-1
else
1
end;
begin
r := numtodsinterval(s * to_number(substr(p, 2, 2)), 'hour');
r := r + numtodsinterval(s * to_number(substr(p, 5, 2)), 'minute');
return r;
end;

Generating the time for each location is simply a matter of adding these intervals to the current time (as returned by the naval clock). At this point we have the following data:

15/05/2015 16.22:08 BAN - 15-MAY-2015 16:22:08
15/05/2015 10.52:08 LON - 15-MAY-2015 10:52:08
15/05/2015 06.52:08 NEW - 15-MAY-2015 06:52:08
15/05/2015 05.52:08 CHI - 15-MAY-2015 05:52:08
15/05/2015 04.52:08 DEN - 15-MAY-2015 04:52:08

The final step is to convert these rows into a single row and column. Sean does this with XML manipulation. Simplifying, we now have:

with rws as (
select '15/05/2015 16.22:08' a, 'BAN' b, '- 15-MAY-2015 16:22:08' c
from dual union all
select '15/05/2015 10.52:08' a, 'LON' b, '- 15-MAY-2015 10:52:08' c
from dual union all
select '15/05/2015 06.52:08' a, 'NEW' b, '- 15-MAY-2015 06:52:08' c
from dual union all
select '15/05/2015 05.52:08' a, 'CHI' b, '- 15-MAY-2015 05:52:08' c
from dual union all
select '15/05/2015 04.52:08' a, 'DEN' b, '- 15-MAY-2015 04:52:08' c
from dual
)
select rtrim(
xmlquery(
'//x/text()' passing
xmlagg(
xmlelement("x", b || ' ' || c || ',') order by a desc
) returning content
), ','
) a
from rws;

The XMLElement call creates a fragment wrapped in "x" tags (e.g. "<x>BAN - 15-MAY-2015 16:22:08,</x>"). The XMLAgg() function then combines these elements into one document, collapsing our five rows down into one. The <x> tags are removed by using XMLQuery, which extracts the value from these elements.

Finally the query removes the trailing comma and converts the rest into semicolons to ensure the output matches the requested format!

Nice work Sean! This is the only solution that didn't rely on in-built functions to get the time. Combined with hiding the real SQL, this featured many of the aspects we were looking for - little used and misused features. In the next post we'll analyze the winning solution from Stelios Vlasopoulos.

Join the discussion

Comments ( 2 )
  • Sean Stuber Friday, May 15, 2015

    Thank you for the write up and I'm glad you appreciated my efforts. I'm disappointed with myself for missing the London DST. For some reason I was convinced London didn't observe DST so UTC seemed sufficient.

    But, that's my mistake and fair in the ranking downgrade.

    A coworker particularly liked your phrasing "At this point it gets tricky ;)"

    I'll take this line as win "The most hideously convoluted way to split elements of a comma separated list into rows I've ever seen!"

    Thank you!

    I'm looking forward to the next one.

  • Chris Saxon Sunday, May 17, 2015

    It's still a great entry Sean. I'm on London time, so I'm not sure how I managed to miss that the time was an hour out when testing the entries!

    Thanks for your entry - I look forward to seeing what you can come up with in the next quiz :)

Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.