X

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

Obfuscated SQL Contest Winners!

Chris Saxon
Developer Advocate

 

Thanks to everyone who entered the Obfuscated SQL Contest! This ran on the PL/SQL Challenge from March - April. It asked players to produce ingenious, creative, quirky and downright weird SQL to return the current time in the timezones Bangalore, London, New York, Chicago and Denver.

There were 53 entries, many of which provoked serious head scratching amongst the judges to determine how these queries actually work! After much deliberating in an exceptionally close run contest the judges have chosen the following entries as winners:

 

Overall winner: Stelios Vlasopoulos

Runner up: James Su

Honourable mention: Sean Stuber, Anton Scheffer, Laurent Schneider, TonyC

These entries all fully got into the spirit of the contest producing ingenious and convoluted SQL. Let’s look at these in more detail to see why:

Stelios

A beast of a query that uses a huge number of features, abuses HTTP calls and has convoluted expressions “just because”!

The features used by this query include:

  • Model Clause
  • Numerous unnecessary, string, number and hex conversions
  • With clause
  • Multiple regular expressions
  • A HTTP call to get the timezone
  • XML parsing
  • Row generation using "connect by level" and "group by cube"
  • Unnecessary calculations
  • Cross join

Sean

An ingenious solution with an encrypted SQL string that's decrypted using inline PL/SQL in SQL. Key feature (mis)use:

  • HTTP call to get the time
  • Inline PL/SQL
  • Execute immediate to convert "junk" to real SQL
  • XML parsing
  • Full outer join

James

An elegantly complex solution that hides the actual SQL statement in a string of spaces!

  • A string of spaces and tabs is converted into an actual select statement via string manipulation
  • The actual query is executed and converted to XML using DBMS_XMLGEN.getxmltype

All three are brilliant examples of obscure SQL. Congratulations to Stelios, Sean and James!

You can view these entries at the bottom of this post. If you'd like to view all the entries head here. Stay tuned for blog posts dedicated to each of the winners entries, dissecting them in detail to explain how they work.

In addition to these three, there are several other high quality entries worthy of mention.

Anton Scheffer

A truly convoluted select statement that includes a bucket list of features. In addition to many already mentioned it also includes:

  • Analytic functions
  • MULTISET operators
  • LNNVL
  • DBMS_DEBUG_VC2COLL to convert a list to an array
  • Calls to DBMS_RANDOM!

With all these features this would be a strong contender for the title. Unfortunately this doesn't always produce the correct output, so lost out for not fully meeting the requirements.

Laurent Schneider

Another devious query using a stack of features. Those not already listed above include:

  • JSON manipulation
  • Rows generated using SYS.ODCIVARCHAR2LIST
  • Different methods of timezone naming
  • A recursive inline PL/SQL function

This also failed to meet the requirements. The timezone for Chicago and Denver aren't right, the output is wrong for these places. With some small tweaking this would also be in the running for the top spots.

TonyC

Uses common table expressions (WITH clause) combined with mind bending string manipulation to extract the dates. Key features not highlighted in other solutions:

  • Reverses a string with the timezones listed
  • Calls WM_CONCAT to convert rows into a string

Thanks again to everyone who took part and helped make this an enjoyable contest. We'll run more of these in future, so stay tuned!

NOTE 15 May 2015:

Sean's entry was originally selected as a runner up. Upon further analysis the judges noticed that it uses UTC for London time, meaning it doesn't take account of daylight savings. Consequently this entry was downgraded to honourable mention.

Here are the winning statements:

Stelios' SQL

with d1 as
(
select regexp_substr(z,'[^,]+',1,3*level-2) a,
       regexp_substr(z,'[^,]+',1,3*level-1)*power(1e1,-6) b,
       regexp_substr(z,'[^,]+',1,3*level)*(case when mod(3*level,3)=0 and rownum > 1 then -1 else 1 end)* power(1e1,-6) c
from (
select to_char(regexp_substr (x.a,'\d{23}',1,rn),'FM999999G99999999G999999999', 'NLS_NUMERIC_CHARACTERS=.,') z
from (
select
utl_raw.cast_to_varchar2(utl_raw.bit_xor(hextoraw('5B4F5D50
4E3831323939323337393037373638313239373736373937383531353130333
537303030313136373733373836393837343037383530393130373339363832
383536373732373334313838313833323038373632333137373638363937383
339373432303433313034393931353331'),hextoraw(trim(to_char(470
188516729,'XXXXXXXXXX'))))) a
from dual
) x
cross join (select rownum rn from (select 1 num from dual group by cube (1,2,3)) where rownum<=sqrt(25))) y
connect by level <= (length(regexp_replace(z,'[^,]+')) + 3)/3
),
d2 as
(select ct, x, y from d1
 model
 partition by (rownum rn)
 dimension by (0 dim)
 measures (cast( a as varchar2(65)) as ct, a, cast(b as varchar2(30)) as x, b, cast(c as varchar2(30)) as y, c)
 rules upsert iterate (3)
 (
   ct[0]=(case when iteration_number = 0 then null else ct[0] end )||
   chr(regexp_substr(a[0],'\d{2}',1,iteration_number+1)),
    x[0]=rawtohex(to_char(b[0])),y[0]=rawtohex(to_char(c[0]))
 )
 order by rn
 )
select
   rtrim(xmltransform
   (sys_xmlagg(sys_xmlgen(new_time)),
     xmltype
     (
       '<?xml version="1.0"?><xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
          <xsl:template match="/">
            <xsl:for-each select="/ROWSET/NEW_TIME">
              <xsl:value-of select="text()"/>; </xsl:for-each>
          </xsl:template>
        </xsl:stylesheet>'
     )
  ).getstringval(),'; ') timezones
from
(
select ct ||' - '||to_char(current_timestamp at time zone tz,'DD-MON-YYYY HH24:MI:SS') new_time
from
(
select z.ct, p.column_value.extract('//text()').getstringval() as tz
from
(select d2.ct,
        httpuritype(utl_raw.cast_to_varchar2(hextoraw('68747
4703A2F2F687474703A2F2F77732E67656F6E616D65732E6F72672F74696D657
A6F6E653F6C61743D'
        ||d2.x||'266C6E673D'||d2.y||'26757365726E616
D653D73766C61323939'))).getxml() req
  from d2
) z, table(xmlSequence(z.req.extract(utl_raw.cast_to_varchar2
(hextoraw('2F67656F6E616D65732F74696D657A6F6E652F74696D65
7A6F6E654964'))))) p
)
)
/

Sean's SQL

--Requires 12c to implement function in WITH clause.

--Must be run as SYS or SYSTEM and database server must have access to internet

--The internet call is safe, but please do reverse engineer the text to evaluate the inner workings.

WITH FUNCTION s
RETURN VARCHAR2
IS
s VARCHAR2(1000);
BEGIN
EXECUTE IMMEDIATE TRANSLATE(
q'{HVKW
pu
M:.*6-:, on< +1 63/4:3:2+ 9*2<+612 62 HVKW <4>*,:q
R*,+ =: -*2 >, LFL 1- LFLKZR 62 1-;:- +1 >)16; 2::;628 > 2:+(1-5 _]S ;:962:;q

K76, 3>5:, 21 <>44, +1 LFL[_KZs LFLKVRZLK_ROs ]JMMZQKA[_KZ 1- ]JMMZQKAKVRZLK_RO +1 -:+-6:): +7: +63:
V2,+:>; +7: <*--:2+ +63: 6, /*44:; 9-13 +7: JL Q>)>4 P=,:-)>+1-%x, >+136< <41<5
up

YJQ]KVPQ 1n6w/ VQ I_M]W_Mnv
MZKJMQ VQKZMI_S [_F KP LZ]PQ[
_L
- VQKZMI_S [_F KP LZ]PQ[d
, VQKZXZM eb ]_LZ HWZQ / SVTZ xryx KWZQ ro ZSLZ o ZQ[d
^ZXVQ
- eb QJRKP[LVQKZMI_Sw, u KPAQJR^ZMwLJ^LKMw/s ns nvvs x71*-xvd
- eb - t QJRKP[LVQKZMI_Sw, u KPAQJR^ZMwLJ^LKMw/s ks nvvs x362*+:xvd
MZKJMQ -d
ZQ[d
> _L wLZSZ]K x^>28>41-:sS12;12sQ:( F1-5s]76<>81s[:2):-x > YMPR [J_Svs
=
_L wLZSZ]K KPAKVRZLK_ROAKEwLJ^LKMwMZXZGOALJ^LKMw[^RLASP^qLJ^LKMw'vs xc^Maqu JK]xvs kvs xR12q ;;s 77nle36e,, KEMxv
21(
YMPR wLZSZ]K 7++/*-6+%/:wx7++/epp+%<71q*,21q2>)%q364p<86r=62p+63:-q/4xvq8:+<41=wv ' YMPR [J_Svv
LZSZ]K MZOS_]Zw>s xsxs xd xv
YMPR wLZSZ]K MKMVRw
GRSNJZMFwxpp'p+:'+wvx
O_LLVQX GRS_XXwGRSZSZRZQKw|'|s = "" x x "" < "" xsxv PM[ZM ^F > [ZL]v MZKJMQVQX ]PQKZQKvs
xsxv
>
YMPR wLZSZ]K < >s =s KPA]W_Mw<s x|r| [[rRPQrFFFF 77nle36e,,xv <
YMPR wLZSZ]K =s ]_LKwwLZSZ]K 21( YMPR =v t < _L KVRZLK_ROv <
YMPR wLZSZ]K >s =s 1n6wKEAPYYLZKw<vv <
YMPR wLZSZ]K ]_LZ SZQXKWw<v
HWZQ f
KWZQ
x_,6>x "" xpx "" xT145>+>x
HWZQ g
KWZQ
x_3:-6<>x "" xpx "" MZOS_]Zw<s x xs xAxv
HWZQ h
KWZQ
xJLx "" xpx "" x]:2+->4x
HWZQ i
KWZQ
QISw[Z]P[ZwLJ^LKMw=s ns ovs xPxs xJK]xvs
xJLx "" xpx "" xR1*2+>62xv
ZQ[
<s
>s
=
YMPR wLZSZ]K >s LJ^LKMw=s os mv =s = <
YMPR wLZSZ]K JOOZMw=v =s < >s VQVK]_Ow=v <
YMPR wLZSZ]K <s >s =
YMPR wLZSZ]K >s
= <s
MZXZGOALJ^LKMw>s
xDBsCtxs
os
=v
=
YMPR >
YJSS PJKZM UPVQ
wLZSZ]K KPAQJR^ZMw]PSJRQAI_SJZv =
YMPR GRSK_^SZw
wLZSZ]K xox
"" x +1 x
"" w wLZSZ]K SZQXKWw
MZOS_]Zw
MZXZGOAMZOS_]Zw
>s
xDBsCxs
x'xvs
x'xs
QJSSvv
YMPR >v
t ov
YMPR [J_Svvv <
PQ = c SZQXKWw>vvvvvvvvvv}',
q'{"%'()*+,-./123456789:;<=>ABCDEFGHIJKLMNOPQRSTUVWXYZ[]^_abcdefghiklmnopqrstuvwxy|}',
q'{|yxwvutsrqponmlkihgfedcba_^][ZYXWVUTSRQPONMLKJIHGFEDCBA>=<;:987654321/.-,+*)('%"}'
)
INTO s;

RETURN s;
END;

SELECT s
FROM dual

James' SQL (note: ensure spaces and tabs are preserved or this solution won't work)

SELECT SUBSTR(s,INSTR(s,'<S>')+3,INSTR(s,'</S>')-INSTR(s,'<S>')-3)

   FROM ( SELECT DBMS_XMLGEN.getxmltype(LISTAGG(CHR((INSTR(S,b,1,3*LEVEL-2)-DECODE(LEVEL,1,0,INSTR(S,b,1,3*LEVEL-3))-1)*25         +(INSTR(S,b,1,3*LEVEL-1)-INSTR(S,b,1,3*LEVEL-2)-1)*5         +INSTR(S,b,1,3*LEVEL)-INSTR(S,b,1,3*LEVEL-1)-1         +32)) WITHIN GROUP(ORDER BY LEVEL))S   FROM (       SELECT '                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     ' ||'                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      ' ||'                                                                       ' S,chr(9) b FROM DUAL ) CONNECT BY LEVEL<=REGEXP_COUNT(S,b)/3 );

Join the discussion

Comments ( 2 )
  • Steven Feuerstein Monday, May 11, 2015

    That is some seriously crazy SQL. I look forward to your explanations. It is hard for me to believe that such different statements (for example, the first and third) can both produce the desired results.

  • Natalka Saturday, May 23, 2015

    James Su's is my personal favorite. SQL in the spaces, how crazy is that! Nice one James.

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